Home » RDBMS Server » Performance Tuning » URGENT:-Tuning a query
URGENT:-Tuning a query [message #115909] Fri, 15 April 2005 03:31 Go to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Hi Gurus,

I have this query which is taking 10 mins. to execute. I have been assigned the task of tuning it.

I need the help of you experts. Pls. give your comments that what changes can be done in this query so that it performs well.I dont have any idea regarding re-writing the SQL's, so please help me.

Query:-

select tsw.WORKSHEET_ID,sc.case_id,sc.PLAINTIFF,sc.DEFENDANT ,

(select sc1.CASE_NUM from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'A') adv_case_id,

(select sc1.CASE_NUM from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_case_id,

(select sc1.PLAINTIFF from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_PLAINTIFF,

(select sc1.DEFENDANT from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_DEFENDANT

from arv_sop_worksheet tsw,arv_usop_subscription ups ,ARV_BUSINESS_NAME tbn,arv_sop_case sc

where tsw.WORKSHEET_ID = sc.WORKSHEET_ID and tsw.STATUS_CD=33003 and exists (select 'X' from arv_sop_action_item sai where tsw.WORKSHEET_ID =sai.WORKSHEET_ID and sai.ACTION_ITEM_STATUS_CD in (38003,38002,38008)) and tsw.Bus_Name_Id = TBN.BUS_NAME_ID and tbn.ENTITY_ID = UPS.object_id

and tsw.LAST_MODIFIED_DATE between to_date('1/1/2001 6:41:37','dd/mm/yyyy HH24:MI:SS') and to_date('8/4/2005 6:41:37','dd/mm/yyyy HH24:MI:SS')

and ups.channel_id = 1020000001 and OBJECT_TYPE_CD = 11 -- 153151

union

select tsw.WORKSHEET_ID,sc.case_id,sc.PLAINTIFF,sc.DEFENDANT ,

(select sc1.CASE_NUM from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'A') adv_case_id,

(select sc1.CASE_NUM from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_case_id,

(select sc1.PLAINTIFF from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_PLAINTIFF,

(select sc1.DEFENDANT from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_DEFENDANT

from arv_sop_worksheet tsw,arv_usop_subscription ups ,ARV_BUSINESS_NAME tbn,arv_sop_case sc

where tsw.WORKSHEET_ID = sc.WORKSHEET_ID and tsw.STATUS_CD=33003 and exists (select 'X' from arv_sop_action_item sai where tsw.WORKSHEET_ID =sai.WORKSHEET_ID and sai.ACTION_ITEM_STATUS_CD in (38003,38002,38008)) and tsw.Bus_Name_Id = TBN.BUS_NAME_ID and tbn.entity_id in

(select entity_id from arv_affl_membership where affl_id = UPS.object_id)

and tsw.LAST_MODIFIED_DATE between to_date('1/1/2001 6:41:37','dd/mm/yyyy HH24:MI:SS') and to_date('8/4/2005 6:41:37','dd/mm/yyyy HH24:MI:SS')

and ups.channel_id = 1020000001 and OBJECT_TYPE_CD = 31

union

select tsw.WORKSHEET_ID,sc.case_id,sc.PLAINTIFF,sc.DEFENDANT ,

(select sc1.CASE_NUM from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'A') adv_case_id,

(select sc1.CASE_NUM from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_case_id,

(select sc1.PLAINTIFF from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_PLAINTIFF,

(select sc1.DEFENDANT from arv_sop_case sc1 where sc1.WORKSHEET_ID = sc.WORKSHEET_ID and sc1.CASE_TYPE = 'C') consolide_DEFENDANT

from arv_sop_worksheet tsw,arv_usop_subscription ups ,ARV_BUSINESS_NAME tbn,arv_sop_case sc

where tsw.WORKSHEET_ID = sc.WORKSHEET_ID and tsw.STATUS_CD=33003 and exists (select 'X' from arv_sop_action_item sai where tsw.WORKSHEET_ID =sai.WORKSHEET_ID and sai.ACTION_ITEM_STATUS_CD in (38003,38002,38008)) and tsw.Bus_Name_Id = TBN.BUS_NAME_ID and tbn.entity_id in

(select entity_id from arv_subgroup_membership where subgroup_id = UPS.object_id)

and tsw.LAST_MODIFIED_DATE between to_date('1/1/2001 6:41:37','dd/mm/yyyy HH24:MI:SS') and to_date('8/4/2005 6:41:37','dd/mm/yyyy HH24:MI:SS')
and ups.channel_id = 1020000001 and OBJECT_TYPE_CD = 32;

Explain plan:-
SELECT STATEMENT, GOAL = FIRST_ROWS
59 3 393
SORT UNIQUE 59 3 393
UNION-ALL
NESTED LOOPS 10 1 124
NESTED LOOPS 9 1 82
NESTED LOOPS 8 1 73
NESTED LOOPS 3 1 51
INDEX RANGE SCAN ARROW TUSOP_SUBSCRIPTION_UX1 1 39
INDEX RANGE SCAN ARROW BUS_NAME_ENTITYID_NDX 1 2 24
TABLE ACCESS BY INDEX ROWID ARROW TSOP_WORKSHEET 6 1 22
INDEX RANGE SCAN ARROW SOP_WORKSHEET_BUS_NM_PNDX 377
TABLE ACCESS BY INDEX ROWID ARROW TSOP_ACTION_ITEM 2 1 9
INDEX RANGE SCAN ARROW TSOP_ACTION_ITEM_WID_PNDX 1
TABLE ACCESS BY INDEX ROWID ARROW TSOP_CASE 2 5 210
INDEX RANGE SCAN ARROW SOP_CASE_COMPOSE_CMPCASE_PNDX 1 5
NESTED LOOPS 11 1 134
NESTED LOOPS 10 1 92
NESTED LOOPS 9 1 83
NESTED LOOPS 4 1 61
NESTED LOOPS 3 1 49
INDEX RANGE SCAN ARROW TUSOP_SUBSCRIPTION_UX1 1 39
INDEX RANGE SCAN ARROW AFFL_MEMBERSHIP_NDX 1 65 650
INDEX RANGE SCAN ARROW BUS_NAME_ENTITYID_NDX 1 2 24
TABLE ACCESS BY INDEX ROWID ARROW TSOP_WORKSHEET 6 1 22
INDEX RANGE SCAN ARROW SOP_WORKSHEET_BUS_NM_PNDX 377
TABLE ACCESS BY INDEX ROWID ARROW TSOP_ACTION_ITEM 2 1 9
INDEX RANGE SCAN ARROW TSOP_ACTION_ITEM_WID_PNDX 1
TABLE ACCESS BY INDEX ROWID ARROW TSOP_CASE 2 5 210
INDEX RANGE SCAN ARROW SOP_CASE_COMPOSE_CMPCASE_PNDX 1 5
NESTED LOOPS 11 1 135
NESTED LOOPS 10 1 93
NESTED LOOPS 9 1 84
NESTED LOOPS 4 1 62
NESTED LOOPS 3 1 50
INDEX RANGE SCAN ARROW TUSOP_SUBSCRIPTION_UX1 1 39
INDEX RANGE SCAN ARROW SUBGROUP_MEM_SGID_PNDX 1 122 1342
INDEX RANGE SCAN ARROW BUS_NAME_ENTITYID_NDX 1 2 24
TABLE ACCESS BY INDEX ROWID ARROW TSOP_WORKSHEET 6 1 22
INDEX RANGE SCAN ARROW SOP_WORKSHEET_BUS_NM_PNDX 377
TABLE ACCESS BY INDEX ROWID ARROW TSOP_ACTION_ITEM 2 1 9
INDEX RANGE SCAN ARROW TSOP_ACTION_ITEM_WID_PNDX 1
TABLE ACCESS BY INDEX ROWID ARROW TSOP_CASE 2 5 210
INDEX RANGE SCAN ARROW SOP_CASE_COMPOSE_CMPCASE_PNDX 1 5

Version:-9.2.0.5 O.S.:-win-2003

Pls. provide me some tips in order to performance tune the query.

Thanks in advance.

Milind.

Report message to a moderator

Re: URGENT:-Tuning a query [message #116066 is a reply to message #115909] Sun, 17 April 2005 07:31 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Some quick suggestions:
- Consider using a "UNION ALL" instead of "UNION"
- Consider rewriting your query to eliminate as much SELECT clauses as possible in your main select.

MHE

Report message to a moderator

Previous Topic: how to check whether index is rebuilt or not?
Next Topic: large sort
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 22 23:39:55 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.