Re: db file scattered read

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_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
-----------------------------------------------------------------