Howdy,
I have a query which was taking an extremely long time
to complete. The OPTIMIZER_MODE in the init.ora file is set to CHOOSE,
meaning it will use the ALL_ROWS method to determine its access paths. I
determined the query was not using the indexes I thought it should. When I
changed to use the FIRST_ROWS optimizer_mode, the query ran in under a
second. Now FIRST_ROWS is used to minimize response time; ALL_ROWS is used
to minimize total execution time. I'm trying to figure out the difference.
Using FIRST_ROWS, this is the output from tkprof:
select ra.originatingclli, ra.terminatingclli,
r.originatingclli,
r.terminatingclli, r.deletedigits, r.prefixdigits,
rs.originatingclli,
rs.terminatingclli from routingassignmentpersistent ra,
route r, routesegmentroute rs1, routesegmentpersistent rs
where networktype = 'A
C' and networksubtype
= 'POTS' and ra.objid = r.ROUTINGASSIGNPERSFINAL_OBJID and
r.objid = ROUTE_OBJID and ROUTESEGMENTPERSISTENT_OBJID =
rs.objid
and rownum < 5 and ra.startdate is not null
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.00 0.01 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 35
0 4
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 4 0.00 0.01 0 35
0 4
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 29 (NTOPBIG)
Rows Row Source Operation
------- ---------------------------------------------------
4 COUNT STOPKEY
4 NESTED LOOPS
4 NESTED LOOPS
2 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID
ROUTINGASSIGNMENTPERSISTENT
1 INDEX RANGE SCAN (object id 16976)
2 TABLE ACCESS BY INDEX ROWID ROUTE
2 INDEX RANGE SCAN (object id 16962)
5 TABLE ACCESS BY INDEX ROWID ROUTESEGMENTROUTE
5 INDEX RANGE SCAN (object id 16971)
4 TABLE ACCESS BY INDEX ROWID ROUTESEGMENTPERSISTENT
7 INDEX UNIQUE SCAN (object id 16965)
Rows Execution Plan
-------
---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
4 COUNT (STOPKEY)
4 NESTED LOOPS
4 NESTED LOOPS
2 NESTED LOOPS
1 TABLE ACCESS GOAL: ANALYZED
(BY INDEX ROWID) OF
'ROUTINGASSIGNMENTPERSISTENT'
1 INDEX GOAL: ANALYZED (RANGE
SCAN) OF
'IX_ROUTINGASSIGNMENTPERSIST5' (NON-UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED
(BY INDEX ROWID) OF
'ROUTE'
2 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'IX_ROUTE4'
(NON-UNIQUE)
5 TABLE ACCESS GOAL: ANALYZED
(BY INDEX ROWID) OF
'ROUTESEGMENTROUTE'
5 INDEX GOAL: ANALYZED (RANGE
SCAN) OF
'IX_ROUTESEGMENTROUTE3'
(NON-UNIQUE)
4 TABLE ACCESS GOAL: ANALYZED
(BY INDEX ROWID) OF
'ROUTESEGMENTPERSISTENT'
7 INDEX GOAL: ANALYZED (UNIQUE
SCAN) OF
'IX_ROUTESEGMENTPERSISTENT'
(UNIQUE)
Using the ALL_ROWS mode, this is the tkprof
output from the same query:
select ra.originatingclli, ra.terminatingclli,
r.originatingclli,
r.terminatingclli, r.deletedigits, r.prefixdigits,
rs.originatingclli,
rs.terminatingclli from routingassignmentpersistent ra,
route r, routesegmentroute rs1, routesegmentpersistent rs
where networktype = 'A
C' and networksubtype
= 'POTS' and ra.objid = r.ROUTINGASSIGNPERSFINAL_OBJID and
r.objid = ROUTE_OBJID and ROUTESEGMENTPERSISTENT_OBJID =
rs.objid
and rownum < 5 and ra.startdate is not null
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.02 0.01 0 0
0 0
Execute 2 0.05 0.06 0 0
5 0
Fetch 2 40.91 471.49 197287 803265
3128 4
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 5 40.98 471.56 197287 803265
3133 4
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 29 (NTOPBIG)
Rows Row Source Operation
------- ---------------------------------------------------
4 COUNT STOPKEY
4 MERGE JOIN
4 SORT JOIN
344066 NESTED LOOPS
166100 HASH JOIN
102265 TABLE ACCESS BY INDEX ROWID
ROUTINGASSIGNMENTPERSISTENT
164524 INDEX RANGE SCAN (object id 16976)
709657 TABLE ACCESS FULL ROUTE
344066 TABLE ACCESS BY INDEX ROWID ROUTESEGMENTROUTE
510165 INDEX RANGE SCAN (object id 16971)
4 SORT JOIN
155184 TABLE ACCESS FULL ROUTESEGMENTPERSISTENT
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: ALL_ROWS
4 COUNT (STOPKEY)
4 NESTED LOOPS
4 NESTED LOOPS
344066 HASH JOIN
166100 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ROUTINGASSIGNMENTPERSISTENT'
102265 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ROUTE'
164524 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF
'ROUTESEGMENTROUTE'
709657 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IX_ROUTESEGMENTROUTE3' (NON-UNIQUE)
344066 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF
'ROUTESEGMENTPERSISTENT'
510165 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'IX_ROUTESEGMENTPERSISTENT' (UNIQUE)
So how can the optimizer think that this second way can
possibly be better? Whether you base it on total execution time, resource
cost, or whatever?
Bill Carle
AT&T
Database Administrator
816-995-3922
wcarle_at_att.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), NLCIO
INET: wcarle_at_att.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Dec 03 2001 - 13:03:12 CST
