My Oracle Support Banner

Activity Search Timing Out When Postal And Activity Status Filters Are Used (Doc ID 2367940.1)

Last updated on MARCH 01, 2018

Applies to:

Oracle Utilities Mobile Workforce Management - Version 2.2.0.3.13 to 2.3.0.2.0 [Release 2.2 to 2.3]
Information in this document applies to any platform.

Symptoms

On : 2.2.0.3.13 version, Common Dispatcher Interface

Activity Search portal is very slow, or timing out, when using some filter criteria such as postal and activity status.

SQL for Zone M1-ACTQ1:

 

 

I removed the ‘/*+ LEADING(T) */’ and found the explain plan to show exponential improvement.
We checked and found M1_TASK_ADDR.POSTAL has an index and that’s why it is fast. But M1_TASK.BO_STATUS_CD is part of a composite index.

Activity search SQL –
Filters used while search are highlighted in yellow below. When we give one input - [(F14) AND TA.POSTAL LIKE :F14] , the results get returned in a few secs on Application end . When we give both inputs -[(F6) AND T.BO_STATUS_CD = :F6] and
[(F14) AND TA.POSTAL LIKE :F14], we get a time out on Application.

Questions.
1. Query is much faster from backend after removing ‘/*+ LEADING(T) */’. Explain plans attached with and without ‘/*+ LEADING(T) */’. Can ‘/*+ LEADING(T) */’ be removed from the Zone

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.