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: db file scattered read

Re: db file scattered read

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sat, 07 Aug 2004 09:27:17 -0600
Message-Id: <6.1.0.6.2.20040807090039.030424c8@pop.centrexcc.com>


At 11:16 PM 8/6/2004, you wrote:

>I must confess that I've never used it, and I've obviously misread
>the manual. What is the point of monitoring DML against the table, if
>it doesn't update statistics? Just to have DBMS_STATS complete a little
>bit faster? It doesn't seem like a very useful feature. Does it make sense
>in 10g or should I forget about it and start thinking about 10g and dynamic
>sampling?

AFAIK, monitoring and the business of gathering statistics work the same in Oracle 10 as they do in Oracle 9 (some details changed I'm sure). The difference is that during installation Oracle creates the job to do the gather_stale. There is a bit more to it since you can (thankfully) mark tables to be exempt from automatic statistics refreshes.

>That is what manual says. To be exact, here is how I got to the level 4:
># Level 2: Apply dynamic sampling to all unanalyzed tables. The number of
>blocks sampled is two times the default number of dynamic sampling blocks.
># Level 3: Apply dynamic sampling to all tables that meet Level 2
>criteria, plus all tables for which standard selectivity estimation used a
>guess for some predicate that is a potential dynamic sampling predicate.
>The number of blocks sampled is the default number of dynamic sampling
>blocks. For unanalyzed tables, the number of blocks sampled is two times
>the default number of dynamic sampling blocks.
># Level 4: Apply dynamic sampling to all tables that meet Level 3
>criteria, plus all tables that have single-table predicates that reference
>2 or more columns. The number of blocks sampled is the default number of
>dynamic sampling blocks. For unanalyzed tables, the number of blocks
>sampled is two times the default number of dynamic sampling blocks.
>
>and then:
>
> * Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that
> meet the previous level criteria using 2, 4, 8, 32, or 128 times the
> default number of dynamic sampling blocks respectively.
> * Level 10: Apply dynamic sampling to all tables that meet the Level
> 9 criteria using all blocks in the table.

Nowhere in this list is there any indication that all tables will be sampled. Only unanalyzed tables and "tables for which standard selectivity estimation used a guess for some predicate" (whatever that means) and at level 4 for tables with more than one predicate in an attempt to detect attribute dependence. In the latter case the CBO will only sample the combined selectivity of those predicates and in my experience is rarely satisfied with the sampling results from 32 blocks. I usually have to go much higher to the point where it practically scans the entire table. Admittedly I haven't tested it on multi-million row tables, only on tables where the 1024 or 4096 sampled blocks at level 8 or 9 are more than 50% of the total blocks and thus a full scan is done instead of a sample even before level 10.

>That is a VERY new feature. As is the case with all such features, it
>should be
>used cautiously. Lyndon (the guy who asked the original question) did not have
>statistics and had to cope with FTS.

If he didn't have any statistics then - assuming he is running with OM=choose (the default and thus a reasonable assumption), the sql will be parsed by the RBO and dynamic sampling therefore ignored.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Sat Aug 07 2004 - 10:23:09 CDT

Original text of this message

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