RE: What's your opinion: ALL_ROWS vs FIRST_ROWS

Regarding you dd query on extents, when you use FIRST_ROWS/ALL_ROWS, cost based is used whether an object has statistics or not. And when going against un-analyzed objects like the dictionary, where rule based is preferred, your dd scripts can really suffer because they will still use cost based methods. Your inserting the RULE hint into your dd script gets you back to rule (obvious man I am). Setting to choose would do the same as long as you don't have stats on the dd objects. Ran into this at a place where they had ALL_ROWS set -- lots of my scripts would take forever.

On a side note, recursive SQL issued by the DB will still use CHOOSE even when you have set ALL_ROWS/FIRST_ROWS, and without stats on the dd objects, will then fall to rule. This "choose" mode can be seen in a raw trace file, indicated by the og=4 value (1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose).

PARSING IN CURSOR #2 len=83 dep=1 uid=0 oct=3 lid=0 tim=102303376 hv=365454555 ad='2f1cfc8'
select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1
END OF STMT
PARSE #2:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=102303377

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Bishop
> Lewis
> Sent: Thursday, October 31, 2002 3:19 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: What's your opinion: ALL_ROWS vs FIRST_ROWS
>
>
> Yes, we have noticed significant response problems after upgrading from
> 8.0.5 to 8.1.7.4 using FIRST_ROWS. It seems that something new in 8i is
> affecting onwards affects the performance.
>
> For example we have a standard DBA script to collect information
> on extents
> against a specified user/table and response went up to 282 seconds.
> Inserting the RULE hint in the script returned the response time back to
> it's normal 3 seconds. Not had time to investigate but there are obviously
> some implications here with FIRST_ROWS.
>
> Lewis Bishop

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).