| Tuning a query [message #112634] |
Mon, 28 March 2005 08:14  |
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
|
Member |
|
|
Hi,
I have this query which I want to tune.
This query literally hangs and doesnt respond at all.
I need suggestions from you experts on this forum regarding this query that what can i do to make it responsive.
Query:-
select /* [[[Renewal.ServiceCenterMembers]]] */
trim(STM.FIRST_NAME) || ' ' || STM.LAST_NAME ServiceTeamMember,
STMK.SERVICE_TEAM_MEMBER_ID ArrowId,
STMK.DESKTOP_ID DesktopId
from
AV_SERVICE_TEAM_MEMBER STM,
AV_SERVICE_TEAM_MEMBER_KEYS STMK
where
STM.SERVICE_TEAM_MEMBER_ID = STMK.SERVICE_TEAM_MEMBER_ID and
STM.SERVICE_TEAM_ID in
(select T.SERVICE_TEAM_ID ServiceTeamId
from AV_SERVICE_TEAM T, AV_SERVICE_TEAM_KEYS K
where T.SERVICE_TEAM_ID = K.SERVICE_TEAM_ID and
T.SERVICE_CENTER_ID =
(select T.SERVICE_CENTER_ID
from
AV_SERVICE_CENTER_KEYS K,
AV_SERVICE_CENTER T
where
T.SERVICE_CENTER_ID = 16 and
T.SERVICE_CENTER_ID = K.SERVICE_CENTER_ID and
(T.SERVICE_CENTER_TYPE = 'SVG' or
(T.SERVICE_CENTER_TYPE = 'DIV' and
T.SERVICE_CENTER_ID in (396, 398, 399, 407))) and rownum = 1))
order by STM.LAST_NAME asc
Execution plan:-
SELECT STATEMENT, GOAL = FIRST_ROWS 2148 1 77
SORT ORDER BY 2148 1 77
NESTED LOOPS SEMI 2140 1 77
NESTED LOOPS 324 1 74
NESTED LOOPS 163 1 14
VIEW GDS AV_SERVICE_TEAM_MEMBER 162 1 3
SORT UNIQUE 162 1 258
TABLE ACCESS BY INDEX ROWID JDEDTA F0111 2 1 160
NESTED LOOPS 153 1 258
NESTED LOOPS OUTER 152 1 98
NESTED LOOPS 151 1 85
NESTED LOOPS 150 1 77
NESTED LOOPS 148 3 207
NESTED LOOPS 147 1 56
NESTED LOOPS 146 1 48
NESTED LOOPS 145 1 41
HASH JOIN 144 17 578
INDEX RANGE SCAN JDEDTA F0005_PK 3 2 32
NESTED LOOPS 142 700 12600
INDEX FULL SCAN GDS TORGANIZATIONAL_UNIT_KEYS_UX1 5 701 5608
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 10
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
INDEX UNIQUE SCAN JDEDTA F550101_PK 1 7
INDEX UNIQUE SCAN JDEDTA F0116_PK 1 7
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 8
INDEX UNIQUE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_PK 1
INDEX RANGE SCAN JDEDTA F0150_PK 1 14 182
INDEX FULL SCAN GDS TEMPLOYEE_KEYS_PK 4 1 8
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 8
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 13
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
INDEX RANGE SCAN JDEDTA F0111_PK 1 1
INDEX RANGE SCAN GDS TEMPLOYEE_KEYS_PK 1 1 11
VIEW GDS AV_SERVICE_TEAM_MEMBER 162 1 60
SORT UNIQUE 162 1 258
TABLE ACCESS BY INDEX ROWID JDEDTA F0111 2 1 160
NESTED LOOPS 153 1 258
NESTED LOOPS OUTER 152 1 98
NESTED LOOPS 151 1 85
NESTED LOOPS 150 1 77
NESTED LOOPS 148 3 207
NESTED LOOPS 147 1 56
NESTED LOOPS 146 1 48
NESTED LOOPS 145 1 41
HASH JOIN 144 17 578
INDEX RANGE SCAN JDEDTA F0005_PK 3 2 32
NESTED LOOPS 142 700 12600
INDEX FULL SCAN GDS TORGANIZATIONAL_UNIT_KEYS_UX1 5 701 5608
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 10
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
INDEX UNIQUE SCAN JDEDTA F550101_PK 1 7
INDEX UNIQUE SCAN JDEDTA F0116_PK 1 7
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 8
INDEX UNIQUE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_PK 1
INDEX RANGE SCAN JDEDTA F0150_PK 1 14 182
INDEX FULL SCAN GDS TEMPLOYEE_KEYS_PK 4 1 8
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 8
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 13
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
INDEX RANGE SCAN JDEDTA F0111_PK 1 1
VIEW SYS VW_NSO_1 1817 1 3
FILTER
NESTED LOOPS 1816 1 138
NESTED LOOPS 1815 1 131
NESTED LOOPS OUTER 1814 1 124
NESTED LOOPS 1813 1 111
NESTED LOOPS 1812 1 104
NESTED LOOPS OUTER 1811 1 94
NESTED LOOPS 1810 1 81
NESTED LOOPS 1809 44 3256
NESTED LOOPS 937 4359 278976
NESTED LOOPS 936 4359 265899
MERGE JOIN CARTESIAN 64 4359 231027
MERGE JOIN CARTESIAN 62 6 270
MERGE JOIN CARTESIAN 61 2 58
VIEW SYS VW_NSO_2 60 1 13
COUNT STOPKEY
NESTED LOOPS 60 1 34
NESTED LOOPS 31 1 16
VIEW GDS AV_SERVICE_CENTER 30 1 13
SORT UNIQUE 30 2 696
UNION-ALL
NESTED LOOPS OUTER 7 1 348
NESTED LOOPS 6 1 335
NESTED LOOPS 5 1 289
NESTED LOOPS 4 1 54
NESTED LOOPS 3 1 18
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 8
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_UX1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F550101 2 1 36
INDEX UNIQUE SCAN JDEDTA F550101_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0116 2 1 235
INDEX UNIQUE SCAN JDEDTA F0116_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0005 2 1 46
INDEX RANGE SCAN JDEDTA F0005_PK 1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 13
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
NESTED LOOPS OUTER 7 1 348
NESTED LOOPS 6 1 335
NESTED LOOPS 5 1 289
NESTED LOOPS 4 1 54
NESTED LOOPS 3 1 18
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 8
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
TABLE ACCESS BY INDEX ROWID JDEDTA F550101 2 1 36
INDEX UNIQUE SCAN JDEDTA F550101_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0116 2 1 235
INDEX UNIQUE SCAN JDEDTA F0116_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0005 2 1 46
INDEX RANGE SCAN JDEDTA F0005_PK 1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 13
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
INDEX UNIQUE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_PK 1 1 3
VIEW GDS AV_SERVICE_CENTER 30 1 18
SORT UNIQUE 30 2 696
UNION-ALL
NESTED LOOPS OUTER 7 1 348
NESTED LOOPS 6 1 335
NESTED LOOPS 5 1 289
NESTED LOOPS 4 1 54
NESTED LOOPS 3 1 18
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 8
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_UX1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F550101 2 1 36
INDEX UNIQUE SCAN JDEDTA F550101_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0116 2 1 235
INDEX UNIQUE SCAN JDEDTA F0116_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0005 2 1 46
INDEX RANGE SCAN JDEDTA F0005_PK 1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 13
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
NESTED LOOPS OUTER 7 1 348
NESTED LOOPS 6 1 335
NESTED LOOPS 5 1 289
NESTED LOOPS 4 1 54
NESTED LOOPS 3 1 18
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 8
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_UX1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F550101 2 1 36
INDEX UNIQUE SCAN JDEDTA F550101_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0116 2 1 235
INDEX UNIQUE SCAN JDEDTA F0116_PK 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0005 2 1 46
INDEX RANGE SCAN JDEDTA F0005_PK 1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 13
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
BUFFER SORT 61 2 32
INDEX RANGE SCAN JDEDTA F0005_PK 1 2 32
BUFFER SORT 60 2 32
INDEX RANGE SCAN JDEDTA F0005_PK 1 2 32
BUFFER SORT 62 701 5608
INDEX FULL SCAN GDS TORGANIZATIONAL_UNIT_KEYS_UX2 2 701 5608
TABLE ACCESS BY INDEX ROWID GDS TORGANIZATIONAL_UNIT_KEYS 2 1 8
INDEX UNIQUE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_PK 1
INDEX UNIQUE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_PK 1 3
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 10
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
INDEX UNIQUE SCAN JDEDTA F550101_PK 1 7
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 13
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
TABLE ACCESS BY INDEX ROWID JDEDTA F0101 2 1 10
INDEX UNIQUE SCAN JDEDTA F0101_PK 1
INDEX UNIQUE SCAN JDEDTA F550101_PK 1 7
TABLE ACCESS BY INDEX ROWID JDEDTA F0150 2 1 13
INDEX RANGE SCAN JDEDTA F0150_UX1 1 1
INDEX UNIQUE SCAN JDEDTA F0116_PK 1 7
INDEX UNIQUE SCAN JDEDTA F0116_PK 1 7
INDEX RANGE SCAN GDS TORGANIZATIONAL_UNIT_KEYS_UX1 2 1 8
Version:-Oracle 9.2.0.5 OS:- windows 2003
Pls. help me in this regard.
Any help is appreciated.
Thanks in advance.
Milind.
|
|
|
|
| Re: Tuning a query [message #112654 is a reply to message #112634] |
Mon, 28 March 2005 10:39  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Look into using the with clause to break out portions of the query you can. Also try rewriting to use inline views instead of so many nested subqueries. Make sure you have up to date statistics including histograms.
Also post using the code tags to preserve query formatting. Also I don't understand the purpose of the join between STM and STMK at the top, and likewise with K and T at the next level.
It would also help if we knew more about your data, such as a data model or description of the tables in question and their sizes and the overall in english goal you want to accomplish.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
