Re: db file scattered read
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 07 Aug 2004 09:27:17 -0600
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
