Poor Database Query Performance Against CES_CUSTOMERS (Doc ID 1453007.1)

Last updated on FEBRUARY 28, 2017

Applies to:

Oracle Utilities Network Management System - Version 1.8.1.0 to 1.11.0.1 [Release 1.8 to 1.11]
Oracle Network Management for Utilities - DMS - Version 1.8.1.0 to 1.11.0.1 [Release 1.8 to 1.11]
Information in this document applies to any platform.

Symptoms

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

...

 

Cause

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