Slow performance when attempting to complete PSR order Due Date tasks (Doc ID 872214.1)

Last updated on MARCH 28, 2017

Applies to:

Oracle Communications MetaSolv Solution - Version 6.0.15 to 6.0.15 [Release 6.0.15]
Information in this document applies to any platform.
Reviewed for relevance on October 22nd, 2015

Symptoms

Slow performance encountered when attempting to complete Due Date tasks.  Users will report that the Due Date task they're attempting to complete hangs or hourglasses for a long period of time.  The DBA may report the poor performing query appearing in the WORKLOAD REPOSITORY report as needing to be optimized by the application vendor. 

The following SQL can help identify the problem by showing the process that is consuming the highest amount of CPU resources on the database:

select p.spid "Thread ID",
       b.name "Background Process", 
       s.username "User Name", 
       s.osuser "OS User", 
       s.status "STATUS", 
       s.sid "Session ID", 
       s.serial# "Serial No.", 
       s.program "OS Program",
       sa.SQL_TEXT,sa.CPU_TIME/1000000,
       sa.ELAPSED_TIME/1000000
from v$process p, v$bgprocess b, v$session s  , v$SQL Sa
        where s.paddr = p.addr
        and b.paddr(+) = p.addr
        and s.sql_address =   sa.address
        and s.sql_hash_value =  sa.hash_value
        and s.status='ACTIVE'
order by sa.CPU_TIME DESC 

 

Below is a sample execution plan showing the poor performing query:

SELECT DISTINCT ADDR.GA_INSTANCE_ID_STATE_CD 
FROM 
ASAP.SI_LOC SILOC, ASAP.SI_EUL_USAGE SIEULU, ASAP.ADDRESS ADDR WHERE 
SILOC.SERV_ITEM_ID = :B1 AND (SILOC.TO_EFF_DT IS NULL OR SILOC.TO_EFF_DT > 
SYSDATE) AND ADDR.ADDRESS_ID = SILOC.ADDRESS_ID AND ADDR.ACTIVE_IND = 'Y' 
AND SIEULU.SI_LOC_ID = SILOC.SI_LOC_ID AND SIEULU.SI_EUL_USAGE_SEQ = 
(SELECT MAX(ASAP.SI_EUL_USAGE.SI_EUL_USAGE_SEQ) FROM ASAP.SI_EUL_USAGE 
WHERE ASAP.SI_EUL_USAGE.SI_LOC_ID = SILOC.SI_LOC_ID) AND ROWNUM =1 

call      count       cpu      elapsed           disk       query     current       rows 
-------  ------  --------   ----------     ----------  ----------  ---------- ---------- 
Parse        1       0.00          0.00             0           0           0          0 
Execute    276       0.02          0.01             0           0           0          0 
Fetch      276    3092.78       3670.90       4339225     4655092        1292        275 
-------   ------ --------    ----------    ----------  ----------  ---------- ---------- 
total      553    3092.80       3670.92       4339225     4655092        1292        275 

Misses in library cache during parse: 0 
Optimizer goal: CHOOSE 
Parsing user id: 59 (ASAP) (recursive depth: 1) 
unable to set optimizer goal 
ORA-01986: OPTIMIZER_GOAL is obsolete 

parse error offset: 33 

Rows Execution Plan 
------- --------------------------------------------------- 
0 SELECT STATEMENT GOAL: CHOOSE 
0  SORT (UNIQUE) 
0   COUNT (STOPKEY) 
0    NESTED LOOPS 
0     HASH JOIN 
0      NESTED LOOPS 
0       TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 
           'SI_LOC' (TABLE) 
0       INDEX GOAL: ANALYZED (RANGE SCAN) OF 
           'FKIDX_SI_LO_TY__SE_IT' (INDEX) 
0       TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 
            'ADDRESS' (TABLE) 
0       INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 
            'PKIDX_ADDRESS' (INDEX (UNIQUE)) 
0       VIEW OF 'VW_SQ_1' (VIEW) 
0         SORT (GROUP BY) 
0          INDEX GOAL: ANALYZED (FULL SCAN) OF 
               'PK_SI_EUL_USAGE' (INDEX (UNIQUE)) 
0       INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_SI_EUL_USAGE' 
             (INDEX (UNIQUE)) 

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