OraFAQ Forum: Performance Tuning » URGENT:-Performance Tuning a query

Home » RDBMS Server » Performance Tuning » URGENT:-Performance Tuning a query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

URGENT:-Performance Tuning a query [message #110529]

Tue, 08 March 2005 08:12

milind_sri
Messages: 70
Registered: February 2005
Location: Pune

Member

Hello Experts,

I am trying to tune this query which is taking 10 mins. to execute. I need your suggestions on this that how i can improve the response time of this query. Can anybody provide me with some suggestions regarding some changes in where clause or anything else which will improve the response time of this query. Pls. help me on this as this is urgent and a matter of high priority.Version is 9.2.0.5 and O.S. win-2003

Query:-

SELECT /*+FIRST_ROWS*/

(SELECT /*+FIRST_ROWS*/
I.CUSTOMER_ID
from ARV_SOP_ACTION_ITEM AI,
ARV_SOP_RECIPIENT_INFO RI,
AV_ANY_INDIVIDUAL I
where AI.WORKSHEET_ID = W.WORKSHEET_ID and AI.DELIVERABLE_CD = 12007 and
AI.DELIVERY_METHOD_CD != 13014 and
AI.ACTION_ITEM_STATUS_CD in (38002, 38003) and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
RI.RECIPIENT_ID = I.INDIVIDUAL_ID and RI.RECIPIENT_ID is not null) CustomerNoOfPriRecp,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
RI.RECIPIENT_NAME
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12007 and
AI.DELIVERY_METHOD_CD != 13014 and
AI.ACTION_ITEM_STATUS_CD in (38002, 38003)),
' -- ')
FROM DUAL) PrimaryRecpName,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
RI.PHONE
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12007 and
AI.DELIVERY_METHOD_CD != 13014 and
AI.ACTION_ITEM_STATUS_CD in (38002, 38003)),
' -- ')
FROM DUAL) PrimaryRecpPhone,

(SELECT /*+FIRST_ROWS*/
I.CUSTOMER_ID
from ARV_SOP_ACTION_ITEM AI,
ARV_SOP_RECIPIENT_INFO RI,
AV_ANY_INDIVIDUAL I
where AI.WORKSHEET_ID = W.WORKSHEET_ID and AI.DELIVERABLE_CD = 12008 and
AI.DELIVERY_METHOD_CD not in (13016, 13017) and
AI.ACTION_ITEM_STATUS_CD = 38002 and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
RI.RECIPIENT_ID = I.INDIVIDUAL_ID and RI.RECIPIENT_ID is not null and
ROWNUM = 1) CCRecipientCustomerNo,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
RI.RECIPIENT_NAME
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12008 and
AI.ACTION_ITEM_STATUS_CD = 38002 and
AI.DELIVERY_METHOD_CD not in (13016, 13017) and
ROWNUM = 1),
' -- ')
FROM DUAL) CCRecpName,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
RI.PHONE
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12008 and
AI.ACTION_ITEM_STATUS_CD = 38002 and
AI.DELIVERY_METHOD_CD not in (13016, 13017) and
ROWNUM = 1),
' -- ')
FROM DUAL) CCRecpPhone,

(SELECT /*+FIRST_ROWS*/
nvl(trim(E.TRUE_NAME), '--')
from ARV_BUSINESS_NAME BN, ARV_ENTITY E
where BN.BUS_NAME_ID = W.BUS_NAME_ID and E.ENTITY_ID = BN.ENTITY_ID) EntityName,

(SELECT /*+FIRST_ROWS*/
nvl(trim(J.JURIS_NAME), '--')
from ARV_JURISDICTION J
where J.JURIS_ID = W.REP_JURIS_ID) JurisWhereProcessWasServed,

(SELECT /*+FIRST_ROWS*/
ST.NAME
from AV_SERVICE_TEAM ST
where ST.SERVICE_TEAM_ID = W.OWNING_TEAM_ID) TeamReceivingProcess,

(SELECT /*+FIRST_ROWS*/
trim(STM.FIRST_NAME) || ' ' || trim(STM.LAST_NAME)
from AV_SERVICE_TEAM_MEMBER STM
where W.ASSIGNED_TO = STM.SERVICE_TEAM_MEMBER_ID) AssignedTo,

W.WORKSHEET_ID LogNumber,

(SELECT /*+FIRST_ROWS*/
count(*)
from ARV_SOP_CASE SC
where SC.WORKSHEET_ID = W.WORKSHEET_ID and SC.CASE_TYPE = 'S') NumberOfCases,

W.RECEIVED_DATE DateOfSOPReceipt,

(SELECT /*+FIRST_ROWS*/
RM.RECEIVED_METHOD
from ARV_SOP_RECEIVED_METHOD RM
where RM.RECEIVED_METHOD_CD = W.RECEIVED_METHOD_CD) MethodOfReceipt,

nvl((SELECT /*+FIRST_ROWS*/
C.PLAINTIFF
from ARV_SOP_CASE C
where C.CASE_ID = ARFN_GET_SOP_CASE_ID(W.WORKSHEET_ID)),
'--') Plaintiff,

W.NATURE_OF_ACTION NatureOfAction,

(SELECT /*+FIRST_ROWS*/
LT.lawsuit_type
from ARV_LAWSUIT_TYPE LT
where LT.lawsuit_type_cd = W.LAWSUIT_TYPE_CD) LawSuitType,

(SELECT /*+FIRST_ROWS*/
LST.LAWSUIT_SUBTYPE
from ARV_LAWSUIT_SUBTYPE LST
where LST.LAWSUIT_SUBTYPE_CD = W.LAWSUIT_SUBTYPE_CD) LawSuitSubType,

W.DOCUMENT_TYPE DocumentType,

W.ANSWER_DATE AnswerDate,

W.COURT_NAME CourtName,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
'Y'
from ARV_SOP_ACTION_ITEM AI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.DELIVERY_METHOD_CD = 13014 and
AI.ACTION_ITEM_STATUS_CD = 38002),
'N')
FROM DUAL) ISOPPosted,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
'Y'
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12008 and
AI.DELIVERY_METHOD_CD = 13016 and
AI.ACTION_ITEM_STATUS_CD = 38008 and
RI.RECIPIENT_ID in
(SELECT /*+FIRST_ROWS*/
RI.RECIPIENT_ID
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12007 and
AI.DELIVERY_METHOD_CD != 13014 and
AI.ACTION_ITEM_STATUS_CD in (38002, 38003)) and
rownum = 1),
'N')
from dual) EmailSentToPrimaryRecipient,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
'Y'
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12008 and
AI.DELIVERY_METHOD_CD = 13016 and
AI.ACTION_ITEM_STATUS_CD = 38008 and
RI.RECIPIENT_ID in
(SELECT /*+FIRST_ROWS*/
RI.RECIPIENT_ID
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12008 and
AI.DELIVERY_METHOD_CD in
(13010, 13018, 13019, 13020) and
AI.ACTION_ITEM_STATUS_CD = 38002) and ROWNUM = 1),
'N')
from dual) EmailSentToCCRecipient,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
DM.DELIVERY_METHOD
from ARV_SOP_ACTION_ITEM AI, ARV_DI_DELIVERY_METHOD DM
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.DELIVERY_METHOD_CD = DM.DELIVERY_METHOD_CD and
AI.DELIVERABLE_CD = 12007 and
AI.DELIVERY_METHOD_CD != 13014 and
AI.ACTION_ITEM_STATUS_CD in (38002, 38003)),
' -- ')
FROM DUAL) MethodOfService,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
DM.DELIVERY_METHOD
from ARV_SOP_ACTION_ITEM AI, ARV_DI_DELIVERY_METHOD DM
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.DELIVERY_METHOD_CD = DM.DELIVERY_METHOD_CD and
AI.DELIVERY_METHOD_CD in (13010, 13018, 13019, 13020) and
AI.DELIVERABLE_CD = 12008 and
AI.ACTION_ITEM_STATUS_CD in (38002) and ROWNUM = 1),
' -- ')
FROM DUAL) MethodOfServiceForCCRecipient,

/* (SELECT
NVL((SELECT \*+FIRST_ROWS*\
DM.DELIVERY_METHOD
from
ARV_SOP_ACTION_ITEM AI,
ARV_DI_DELIVERY_METHOD DM
where
AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.DELIVERY_METHOD_CD = DM.DELIVERY_METHOD_CD and
AI.DELIVERY_METHOD_CD in (13015) and
AI.DELIVERABLE_CD = 12008 and
AI.ACTION_ITEM_STATUS_CD in (38002) and
ROWNUM = 1
),' -- ')
FROM DUAL) AddActionItemInfo, */

/*('DELIVERABLE - ' ||
(SELECT
D.DELIVERABLE_NAME
from
ARV_DI_DELIVERABLE D
where
D.DELIVERABLE_CD = AI.DELIVERABLE_CD)
|| ' ' || ', DELIVERY METHOD - ' ||
(SELECT
DM.DELIVERY_METHOD
from
ARV_DI_DELIVERY_METHOD DM
where dm.delivery_method_cd = AI.DELIVERY_METHOD_CD)
|| ' ' || ', ACTION ITEM STATUS - ' ||
(SELECT
AIS.ACTION_ITEM_STATUS
from
ARV_ACTION_ITEM_STATUS AIS
where
AIS.ACTION_ITEM_STATUS_CD = AI.ACTION_ITEM_STATUS_CD)) AddActionItemInfo, */

(SELECT /*+FIRST_ROWS*/
SC.COMMENTS
from ARV_SOP_COMMENT SC
where SC.WORKSHEET_ID = W.WORKSHEET_ID and SC.COMMENT_TYPE = 'R') WorksheetRemarks,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
'Y'
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERY_METHOD_CD = 13015 and
AI.DELIVERABLE_CD = 12006 and
AI.ACTION_ITEM_STATUS_CD = 38002 and
RI.RECIPIENT_ID in
(SELECT /*+FIRST_ROWS*/
RI.RECIPIENT_ID
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12007 and
AI.DELIVERY_METHOD_CD != 13014 and
AI.ACTION_ITEM_STATUS_CD in (38002, 38003)) and
ROWNUM = 1),
'N')
FROM DUAL) PhoneActionSentToPrimaryRecp,

(SELECT NVL((SELECT /*+FIRST_ROWS*/
'Y'
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERY_METHOD_CD = 13015 and
AI.DELIVERABLE_CD = 12006 and
AI.ACTION_ITEM_STATUS_CD = 38002 and
RI.RECIPIENT_ID in
(SELECT /*+FIRST_ROWS*/
RI.RECIPIENT_ID
from ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI
where AI.WORKSHEET_ID = W.WORKSHEET_ID and
AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID and
AI.DELIVERABLE_CD = 12008 and
AI.DELIVERY_METHOD_CD in
(13010, 13018, 13019, 13020) and
AI.ACTION_ITEM_STATUS_CD = 38002) and ROWNUM = 1),
'N')
FROM DUAL) PhoneActionSentToCCRecp,

(SELECT decode((SELECT /*+FIRST_ROWS*/
AM.AFFL_ID
from arv_affl_membership AM, ARV_BUSINESS_NAME BN
where BN.BUS_NAME_ID = W.BUS_NAME_ID and
AM.ENTITY_ID = BN.ENTITY_ID),
null,
(SELECT /*+FIRST_ROWS*/
count(*)
from ARV_REPRESENTATION R, ARV_BUSINESS_NAME BN
where R.ENTITY_ID = BN.ENTITY_ID and
BN.BUS_NAME_ID = W.BUS_NAME_ID),
(SELECT /*+FIRST_ROWS*/
count(*)
from ARV_REPRESENTATION R, ARV_AFFL_MEMBERSHIP AM
where R.ENTITY_ID = AM.ENTITY_ID and
AM.AFFL_ID =
(SELECT /*+FIRST_ROWS*/
AM.AFFL_ID
from ARV_AFFL_MEMBERSHIP AM, ARV_BUSINESS_NAME BN
where BN.BUS_NAME_ID = W.BUS_NAME_ID and
AM.ENTITY_ID = BN.ENTITY_ID)))
from DUAL) RepUnitsforRecpEntity

from ARV_SOP_WORKSHEET W
where
--W.WORKSHEET_ID = 510000015
trunc(W.CREATED_DATE) = '25-JUN-1994'

Expalin Plan:-

SELECT STATEMENT, GOAL = HINT: FIRST_ROWS 40687 65246 15202318
NESTED LOOPS 4 9 513
NESTED LOOPS 3 1 40
TABLE ACCESS BY INDEX ROWID ARROW TSOP_ACTION_ITEM 2 1 28
INDEX RANGE SCAN ARROW SOP_ACTION_ITEM_WDDA_PNDX 2 1
TABLE ACCESS BY INDEX ROWID ARROW TSOP_RECIPIENT_INFO 2 1 12
INDEX UNIQUE SCAN ARROW TSOP_RECIPIENT_INFO_PK 1
VIEW GDS AV_ANY_INDIVIDUAL 2 9 153
UNION-ALL PARTITION
NESTED LOOPS 3 1 38
TABLE ACCESS BY INDEX ROWID GDS TINDIVIDUAL_KEYS 2 1 27
INDEX UNIQUE SCAN GDS TINDIVIDUAL_KEYS_PK 2 1
INDEX RANGE SCAN GDS TCUSTOMER_KEYS_UX3 1 1 11
NESTED LOOPS 6 1 61
NESTED LOOPS 5 1 50
NESTED LOOPS 4 1 38
NESTED LOOPS 3 1 32
TABLE ACCESS BY INDEX ROWID GDS TINDIVIDUAL_KEYS 2 1 27
INDEX UNIQUE SCAN GDS TINDIVIDUAL_KEYS_PK 2 1
INDEX UNIQUE SCAN JDEDTA F0101_PK 1 5
INDEX UNIQUE SCAN JDEDTA F0111_PK 1 6
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1 12
INDEX RANGE SCAN GDS TCUSTOMER_KEYS_UX3 1 1 11
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL SYS DUAL 11 8168
COUNT STOPKEY
NESTED LOOPS 4 4 228
NESTED LOOPS 3 1 40
TABLE ACCESS BY INDEX ROWID ARROW TSOP_ACTION_ITEM 2 1 28
INDEX RANGE SCAN ARROW SOP_ACTION_ITEM_WDDA_PNDX 2 1
TABLE ACCESS BY INDEX ROWID ARROW TSOP_RECIPIENT_INFO 2 1 12
INDEX UNIQUE SCAN ARROW TSOP_RECIPIENT_INFO_PK 1
VIEW GDS AV_ANY_INDIVIDUAL 2 9 153
UNION-ALL PARTITION
NESTED LOOPS 3 1 38
TABLE ACCESS BY INDEX ROWID GDS TINDIVIDUAL_KEYS 2 1 27
INDEX UNIQUE SCAN GDS TINDIVIDUAL_KEYS_PK 2 1
INDEX RANGE SCAN GDS TCUSTOMER_KEYS_UX3 1 1 11
NESTED LOOPS 6 1 61
NESTED LOOPS 5 1 50
NESTED LOOPS 4 1 38
NESTED LOOPS 3 1 32
TABLE ACCESS BY INDEX ROWID GDS TINDIVIDUAL_KEYS 2 1 27
INDEX UNIQUE SCAN GDS TINDIVIDUAL_KEYS_PK 2 1
INDEX UNIQUE SCAN JDEDTA F0101_PK 1 5
INDEX UNIQUE SCAN JDEDTA F0111_PK 1 6
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1 12
INDEX RANGE SCAN GDS TCUSTOMER_KEYS_UX3 1 1 11
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL SYS DUAL 11 8168
NESTED LOOPS 3 1 50
TABLE ACCESS BY INDEX ROWID ARROW TBUSINESS_NAME 2 1 14
INDEX UNIQUE SCAN ARROW BUSINESS_NAME_PK 2 1
TABLE ACCESS BY INDEX ROWID ARROW TENTITY 2 1 36
INDEX UNIQUE SCAN ARROW ENTITY_PK 1
FILTER
TABLE ACCESS BY INDEX ROWID GDS TJURISDICTION_KEYS 2 1 66
INDEX UNIQUE SCAN GDS TJURISDICTION_KEYS_PK 1 1
NESTED LOOPS OUTER 35 51 4539
NESTED LOOPS 15 51 4029
NESTED LOOPS 5 51 2295
NESTED LOOPS 4 1 29
NESTED LOOPS 3 1 20
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 10
INDEX UNIQUE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_PK 1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 10
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
INDEX UNIQUE SCAN JDEDTA F0116_PK 1 9
INDEX RANGE SCAN JDEDTA F0005_PK 1 51 816
INDEX RANGE SCAN JDEDTA F550101_X2 1 1 34
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 10
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
NESTED LOOPS 12 1 156
NESTED LOOPS 11 1 151
NESTED LOOPS OUTER 10 1 142
NESTED LOOPS 9 1 132
NESTED LOOPS 8 1 116
NESTED LOOPS 7 1 106
NESTED LOOPS 6 1 96
NESTED LOOPS 5 1 86
NESTED LOOPS 4 1 70
NESTED LOOPS 3 1 15
TABLE ACCESS BY INDEX ROWID GDS TEMPLOYEE_KEYS 2 1 7
INDEX UNIQUE SCAN GDS TEMPLOYEE_KEYS_PK 1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 8
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0111 2 1 55
INDEX RANGE SCAN JDEDTA F0111_PK 1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 16
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
INDEX RANGE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_UX1 1 1 10
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 10
INDEX UNIQUE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 10
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
INDEX RANGE SCAN JDEDTA F0005_PK 1 1 16
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 10
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
INDEX UNIQUE SCAN JDEDTA F0116_PK 1 9
INDEX RANGE SCAN JDEDTA F550101_PK 1 1 5
SORT AGGREGATE 1 6
INDEX RANGE SCAN ARROW SOP_CASE_COMPOSE_NUM_PNDX 3 3 18
TABLE ACCESS BY INDEX ROWID ARROW TLOOKUP 2 1 25
INDEX UNIQUE SCAN ARROW LOOKUP_PK 1
TABLE ACCESS BY INDEX ROWID ARROW TSOP_CASE 2 1 36
INDEX UNIQUE SCAN ARROW TSOP_CASE_PK 2 1
TABLE ACCESS BY INDEX ROWID ARROW TLOOKUP 2 1 25
INDEX UNIQUE SCAN ARROW LOOKUP_PK 1
TABLE ACCESS BY INDEX ROWID ARROW TLOOKUP 2 1 25
INDEX UNIQUE SCAN ARROW LOOKUP_PK 1
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS BY INDEX ROWID ARROW TSOP_COMMENT 2 1 69
INDEX RANGE SCAN ARROW SOP_COMMENT_WSID_PNDX 1 2
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL SYS DUAL 11 8168
TABLE ACCESS FULL ARROW TSOP_WORKSHEET 40687 65246 15202318
Pls. help me on this.

Thanks in advance.

Milind.

Report message to a moderator

Re: URGENT:-Performance Tuning a query [message #110571 is a reply to message #110529]

Tue, 08 March 2005 14:30

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I see from your explain plan that you have indexes. Have your tables, indexes, and indexed columns been recently analyzed? I do not think that you need all of those first_rows hints, but I do not know that it causes any harm to have them either. I would be inclined to remove all but the first first_rows hint. Have you tried running each subquery separately to see which part is taking the longest? One thing that I would do is eliminate some of the unnecessary levels of subqueries and extra calls to the dual table. For example, the following subquery:

(SELECT NVL ((SELECT /*+FIRST_ROWS*/ 
                     RI.RECIPIENT_NAME 
              FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI 
              WHERE  AI.WORKSHEET_ID = W.WORKSHEET_ID 
              AND    AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID 
              AND    AI.DELIVERABLE_CD = 12007 
              AND    AI.DELIVERY_METHOD_CD != 13014 
              AND    AI.ACTION_ITEM_STATUS_CD in (38002, 38003)), ' -- ') 
 FROM   DUAL)

can be replaced with:

(SELECT NVL (RI.RECIPIENT_NAME, '--') 
 FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI 
 WHERE  AI.WORKSHEET_ID = W.WORKSHEET_ID 
 AND    AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID 
 AND    AI.DELIVERABLE_CD = 12007 
 AND    AI.DELIVERY_METHOD_CD != 13014 
 AND    AI.ACTION_ITEM_STATUS_CD in (38002, 38003))

I would make replacements like that in each possible such place. It may allow the optimizer to select a better execution plan that can join tables more directly, without the dual table in the middle.

Report message to a moderator

Re: URGENT:-Performance Tuning a query [message #110572 is a reply to message #110529]

Tue, 08 March 2005 14:43

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

On second thought, I think I see why you have the extra calls to the dual table. If you just want to replace a value when it is null, then you do not need the outer query. However, if you want to supply a value when the inner query does not return a row at all, then you will need the outer query. So, I guess, I would try executing each subquery separately and finding which is taking the longest, then examine that subquery in more detail to see what can be done. I noticed that your last where clause relies on an implicit date conversion, which is generally a bad idea, because it will cause the query to fail if the nls_date_format does not match.

Report message to a moderator

Re: URGENT:-Performance Tuning a query [message #110577 is a reply to message #110529]

Tue, 08 March 2005 15:09

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Another thing that you might try is moving the inline views in the select clause to the from clause without the calls to dual and nvl, then outer joining them and putting the nvl functions in the outer select, so instead of:

SELECT /*+FIRST_ROWS*/  
        (SELECT /*+FIRST_ROWS*/ 
                I.CUSTOMER_ID 
         FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI, AV_ANY_INDIVIDUAL I 
         WHERE  AI.WORKSHEET_ID = W.WORKSHEET_ID 
                AND AI.DELIVERABLE_CD = 12007 
                AND AI.DELIVERY_METHOD_CD != 13014 
                AND AI.ACTION_ITEM_STATUS_CD in (38002, 38003) 
                AND AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID 
                AND RI.RECIPIENT_ID = I.INDIVIDUAL_ID 
                AND RI.RECIPIENT_ID is not null) CustomerNoOfPriRecp,  
        (SELECT NVL ((SELECT /*+FIRST_ROWS*/ 
                             RI.RECIPIENT_NAME 
                      FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI 
                      WHERE  AI.WORKSHEET_ID = W.WORKSHEET_ID 
                      AND    AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID 
                      AND    AI.DELIVERABLE_CD = 12007 
                      AND    AI.DELIVERY_METHOD_CD != 13014 
                      AND    AI.ACTION_ITEM_STATUS_CD in (38002, 38003)), ' -- ') 
         FROM   DUAL) PrimaryRecpName,  
...
FROM   ARV_SOP_WORKSHEET W 
WHERE  trunc (W.CREATED_DATE) = '25-JUN-1994'
/

you would have:

SELECT /*+FIRST_ROWS*/  
       CustomerNoOfPriRecp.customer_id as CustomerNoOfPriRecp,
       NVL (PrimaryRecpName.recipient_name, '--') as PrimaryRecpName
...
FROM   ARV_SOP_WORKSHEET W,
        (SELECT I.CUSTOMER_ID, ai.worksheet_id
         FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI, AV_ANY_INDIVIDUAL I 
         WHERE  AI.DELIVERABLE_CD = 12007 
         AND    AI.DELIVERY_METHOD_CD != 13014 
         AND    AI.ACTION_ITEM_STATUS_CD in (38002, 38003) 
         AND    AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID 
         AND    RI.RECIPIENT_ID = I.INDIVIDUAL_ID 
         AND    RI.RECIPIENT_ID is not null) CustomerNoOfPriRecp,  
        (SELECT RI.RECIPIENT_NAME, ai.worksheet_id 
         FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI 
         WHERE  AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID 
         AND    AI.DELIVERABLE_CD = 12007 
         AND    AI.DELIVERY_METHOD_CD != 13014 
         AND    AI.ACTION_ITEM_STATUS_CD in (38002, 38003)) PrimaryRecpName,
...
WHERE  trunc (W.CREATED_DATE) = to_date ('25-JUN-1994', 'dd-MON-yyyy')
AND    w.worksheeet_id = CustomerNoOfPriRecp.worksheet_id
AND    w.worksheet_id = PrimaryRecpName.worksheet_id (+)
/

That would allow the tables to join directly and eliminate the calls to the dual table, but still allow for a value substitution when the subquery does not produce a row.

Report message to a moderator

Re: URGENT:-Performance Tuning a query [message #110579 is a reply to message #110529]

Tue, 08 March 2005 15:23

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I also noticed that you have a number of subqueries that select from the same tables, with different filtering conditions. You could eliminate some duplication by using the with clause or subquery factoring clause. So, instead of:

SELECT (SELECT some_column
        FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI 
        WHERE  AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID
        AND    some_filter_conditions),
       (SELECT some_other_column
        FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI 
        WHERE  AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID
        AND    some_other_filter_conditions)

you could have:

WITH repeating_query AS
  (SELECT *
   FROM   ARV_SOP_ACTION_ITEM AI, ARV_SOP_RECIPIENT_INFO RI 
   WHERE  AI.RECIPIENT_INFO_ID = RI.RECIPIENT_INFO_ID)
SELECT (SELECT some_column
        FROM   repeating_query 
        WHERE  some_other_filter_conditions),
       (SELECT some_other_column
        FROM   repeating_query
        WHERE  some_filter_conditions)

Report message to a moderator

Re: URGENT:-Performance Tuning a query [message #110651 is a reply to message #110579]

Wed, 09 March 2005 08:15

milind_sri
Messages: 70
Registered: February 2005
Location: Pune

Member

Thanks a lot Barbara for your response. You are doing a magnificent job on this forum.

Thanks again.

Milind.

Report message to a moderator

Previous Topic:	Issue of low buffer hit ratio
Next Topic:	ora-02287 sequnce not allowed here

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 23:36:10 CST 2024