Poor Database Query Performance Against CES_CUSTOMERS
Last updated on FEBRUARY 28, 2017
Applies to:Oracle Utilities Network Management System - Version 188.8.131.52 to 184.108.40.206 [Release 1.8 to 1.11]
Oracle Network Management for Utilities - DMS - Version 220.127.116.11 to 18.104.22.168 [Release 1.8 to 1.11]
Information in this document applies to any platform.
The overall system performance dropped considerably during a storm.
Numerous users complained that the system was unresponsive.
The DBService log contained an ISIS dump whose Sql Command History section showed several queries that took a long time to return.
Sql Command History (oldest to newest):
sql command: 'SELECT ACCOUNT_NUMBER,CITY_STATE,ADDRESS,PHONE_AREA,PHONE_NUMBER,FIRST_NAME,LAST_NAME,CRIT_TYPE FROM CES_CUSTOMERS WHERE sentry_id = '850476'' returned: 1 after 1168 ms
sql command: 'SELECT /*+ USE_NL(supply_node_log) */ f.h_cls, f.h_idx, s.feeder, count(*), sum(snl.customer_count) FROM supply_node_log snl, supply_nodes s, feeders f WHERE snl.event_cls = 800 AND snl.event_idx = 619301 AND snl.supply_cls = s.h_cls AND snl.supply_idx = s.h_idx AND RTRIM(s.feeder) = RTRIM(f.feeder_name) AND s.active = 'Y' AND f.active = 'Y' AND snl.active = 'Y' GROUP BY f.h_cls, f.h_idx, s.feeder ' returned: 3 after 53261 ms
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