Re: db file scattered read
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 15 Aug 2004 10:48:21 -0600
Mladen,
Can you explain a bit what your example is intended to show in response to
my post. As far as I can tell it merely confirms what I had been saying
a) dynamic_sampling=4 does not necessarily guarantee that analyzed tables
will be sampled.
Both tables in your example have no statistics, so they DO get sampled
once you force the CBO
b) IF a table with statistics IS sampled, the sampling results may still
get ignored unless the sample size is a sizeable portion of the total
number of blocks of the table.
In your example, not only are both tables without statistics (so the
IF doesn't apply - sampled statistics ARE preferred over no statistics),
but the tables are so small that the sampling size (32 blocks) exceeds the
number of blocks of the table, i.e. it is not a sample but a full scan.
At 10:17 AM 8/15/2004, you wrote:
>On 08/07/2004 11:27:17 AM, Wolfgang Breitling wrote:
> 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.
>
>Wolfgang, here is a little bit more detailed study of
>OPTIMIZER_DYNAMIC_SAMPLING.
>First, I dropped statistics on the scott user and executed simple query.
>Just as
>you have shown before, the database resorted to RBO and the trace was not
>generated.
>When I forced CBO with the optimizer mode, dynamic sampling kicked in. The
>generated trace
>file is a bit longer, but please, bear with me.
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:
