Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: FIRST_ROWS vs. ALL_ROWS

RE: FIRST_ROWS vs. ALL_ROWS

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Mon, 03 Dec 2001 12:22:11 -0800
Message-ID: <F001.003D3A42.20011203114031@fatcity.com> 









Hi Bill, 




It's been a while since I messed with the optimizer but I'll take a stab.  




FIRST_ROWS is for something like forms, where returning something quickly, even if it is just a few rows, is important.  



ALL_ROWS is meant for throughput.  The execution plan for ALL_ROWS looks like something you may see in a data mart/warehouse.  (I have rarely seen the STAR join actually work.)  Remember in dm/dw split-second reports are not realistic.  



What type of app is this?  How fresh are your stats?  When was the last time you deleted stats and re-analyzed?  Any histograms?  There are a ton of things to take into account here.  



Anyway I hope I helped somehow. 




Lisa Koivu


Oracle Database Monkey.


Fairfield Resorts, Inc.


954-935-4117




-----Original Message-----

From:   Carle, William T (Bill), NLCIO [SMTP:wcarle_at_att.com]
Sent:   Monday, December 03, 2001 1:45 PM
To:     Multiple recipients of list ORACLE-L


Subject:        FIRST_ROWS vs. ALL_ROWS




                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 - 14:22:11 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US