Activity Search Timing Out When Postal And Activity Status Filters Are Used
Last updated on MARCH 01, 2018
Applies to:Oracle Utilities Mobile Workforce Management - Version 18.104.22.168.13 to 22.214.171.124.0 [Release 2.2 to 2.3]
Information in this document applies to any platform.
On : 126.96.36.199.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.
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
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 hundreds of Community platforms