Re: db file scattered read

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 7 Aug 2004 01:16:24 -0400

On 08/06/2004 10:23:25 PM, Wolfgang Breitling wrote:
> I hope you're joking again.
> 
> Enabling "MONITORING" does not cause Oracle to automatically gather 
> statistics. As Justin pointed out, that doesn't happen until Oracle 10. In 
> 9 you still need to schedule a gather_database|schema_stats "gather stale" 
> or "gather auto" job.

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?

> 
> In theory, setting dynamic sampling to 4 should cause Oracle 9 to sample 
> statistics at parse time. 

That is what manual says. To be exact, here is how I got to the level 4:
# Level 0: Do not use dynamic sampling.
# Level 1: Sample all tables that have not been analyzed if the following 
criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) 
this unanalyzed table is joined to another table or appears in a subquery or 
non-mergeable view; (3) this unanalyzed table has no indexes; (4) this 
unanalyzed table has more blocks than the number of blocks that would be used 
for dynamic sampling of this table. The number of blocks sampled is the default 
number of dynamic sampling blocks (32).
# 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.


Levels 5-9 merely increase the number of blocks used for estimate, while level 
10
uses all the blocks. That all happens at parse time. The default number of 
blocks to
sample is 32 or whatever the value of _optimizer_dyn_smp_blks is. Now, 8*32=256 
and
that is an acceptable number of blocks to sample, but if you boost it to 128, 
you'll
read 5824 blocks, what, in case of 8k-block gives more then 45M TO READ AT 
PARSE TIME.
Do I need to tell you how long the parse is going to take if you boost it that 
much?
Did I test it? Yes, I did, on EMP and DEPT tables. I deleted statistics for the 
occasion
and re-run some queries. I was sure that it wasn't doing of RBO because I got 
hash 
join as the method of choice for 

select ename,loc from emp e,dept d where e.deptno=d.deptno;

If you drop primary keys, Oracle 10g will chose hash join. Hash join cannot be 
chosen 
by RBO, so it's CBO. If the tables have no statistics, then the only way how 
CBO can
be used is to do dynamic sampling. What is the overhead, I cannot tell because 
tables
are too small. 


> Do you have any experience that it actually 
> works? I have tried on occasion to set dynamic sampling at the session 
> level, but unless the tables in the sql do not have statistics (in which 
> case you run the risk that the CBO is bypassed for the RBO if you have 
> optimizer_mode=choose => note to self to test if dynamic_sampling > 1 
> changes that "rule"), I have the strong impression that the CBO in many 
> cases does NOT sample statistics but simply uses the existing statistics. 
> Unless you jack up the level to much higher than 4, and even then not 
> always. I have to do more tests to be more certain.

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 was on version 10 (and he never said 
he was),
then dynamic sampling is questionable at best. One deficiency of the dynamic 
sampling 
mechanism is that it is rather crude. If I set dynamic sampling level to 4, it 
will 
sample 32 blocks for both EMP table and table with gigabytes in size. I don't 
think 
that a single "underscore" parameter is a good method to achieve that.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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: