Re: db file scattered read

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sun, 15 Aug 2004 10:48:21 -0600
Message-Id: <6.1.0.6.2.20040815103703.02f95e18@pop.centrexcc.com>

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_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 Sun Aug 15 2004 - 11:44:50 CDT