Poor performance when previewing Item Values of a PSR order (Doc ID 1153593.1)

Last updated on JANUARY 21, 2014

Applies to:

Oracle Communications MetaSolv Solution - Version 6.0.15 and later
Information in this document applies to any platform.
***Checked for relevance on 22-JAN-2014***

Symptoms

MetaSolv Solution performance is slow when attempting to retrieve the Item Values via Service Request Search -> Preview -> Item Values.  The behavior occurs for PSR orders of specific customer accounts.

1. Query for specific problematic PSR order
2. Select Options > Preview...
3. Select Item Values from the Product Service Request Print window
4. Click OK and the application hangs


The DBA may report the following query as the poor performing SQL:

*************************************

select srsi.serv_item_id ,
asap.serv_ord.document_number,
case when nvl(asap.cust_acct.company_name,'-')='-' then
asap.cust_acct.first_nm||' '||asap.cust_acct.last_nm
else
asap.cust_acct.company_name
end cust_acct_company_name,
asap.cust_acct.cust_acct_nbr,
asap.serv_ord.created_by_userid,
asap.serv_req.order_number,
asap.cust_acct.cust_acct_id,
asap.serv_req.service_request_status,
asap.cust_acct.first_nm,
asap.cust_acct.last_nm
from asap.serv_item_rel,
asap.serv_req_si srsi, asap.cust_acct,
asap.serv_ord,
asap.serv_req
where ( asap.serv_req.document_number = asap.serv_ord.document_number )
and
( asap.serv_req.cust_acct_id = asap.cust_acct.cust_acct_id ) and
( ( asap.serv_ord.document_number = :1 ) ) and
srsi.serv_item_id = asap.serv_item_rel.serv_item_id
and serv_item_rel.serv_item_id = serv_item_rel.serv_item_id_rel
and srsi.document_number = :2
and exists ( select 1
from asap.serv_req_si_value,
asap.serv_req_si
where serv_req_si.document_number = :3
and serv_req_si.serv_item_id = srsi.serv_item_id
and serv_req_si.document_number = serv_req_si_value.document_number
and serv_req_si_value.serv_item_id in (
SELECT serv_item_id_rel
FROM asap.serv_item_rel
WHERE level > 0
START WITH serv_item_id_rel = asap.serv_req_si.serv_item_id
CONNECT BY prior asap.serv_item_rel.serv_item_id_rel =
asap.serv_item_rel.serv_item_id and
asap.serv_item_rel.serv_item_id_rel <>
asap.serv_item_rel.serv_item_id )
)

call      count   cpu      elapsed    disk       query      current    rows
-------   ------  -------- ---------- ---------- ---------- ---------- ----------
Parse     1       0.00     0.00       0          0          0          0
Execute   1       0.02     0.02       0          0          0          0
Fetch     5   10117.77  9883.43     969  340203430          0         42
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     7   10117.79  9883.45     969  340203430          0         42

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 464 (APP_MSLV)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: CHOOSE
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'SERV_REQ' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'SERVICE_REQUEST_PK' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'SERV_ORD' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'SERV_ORD_PK'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'CUST_ACCT' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'CUST_ACCT_PK'
(INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'SERV_REQ_SI_PK'
(INDEX (UNIQUE))
0 NESTED LOOPS
0 I (UNIQUE SCAN) OF 'SERV_REQ_SI_PK'
(INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'SERV_REQ_SI_VALUE_PK' (INDEX (UNIQUE))
0 FILTER
0 CONNECT BY (WITH FILTERING)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'SERV_ITEM_REL' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'FK_INSTALLEDSIREL_INSTALLEDSI2' (INDEX)
0 NESTED LOOPS
0 CONNECT BY PUMP
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'INSTALLED_SI_REL_PK' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'SERV_ITEM_REL' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'INSTALLED_SI_REL_PK'
(INDEX (UNIQUE))


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 29.09 29.09
db file sequential read 969 0.05 1.86
*****************************************************************************
***

ALTER SESSION SET EVENTS '10046 trace name context off'


call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   1      0.00     0.00       0          0          0          0
Execute 1      0.00     0.00       0          0          0          0
Fetch   0      0.00     0.00       0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   2      0.00     0.00       0          0          0          0

Misses in library cache during parse: 0
Parsing user id: 464 (APP_MSLV)



*************************************

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