Re: EXPLAIN PLAN

Tad Davis wrote:

> What percentage do you use when you run ANALYZE with the ESTIMATE option?
> So far I've only gotten one response to that question. Larger samples do
> seem to improve performance, but there must be a point of diminishing
> returns.

Be very careful when using estimate statistics. There are serious bugs with the analyze command some of which are fixed in v7.2 and higher. The compute option is not very expensive and we usually launch a script that goes out and creates a script dynamically to analyze all tables or some subset of them. If you're interested check out DBA Reports v1.9 for ORACLE by sending mail to marcm_at_computek.net. The bug in estimate is that it puts the wrong values in some of the statistics columns (e.g. 32) for lots of stats. On one table we had it was too big to compute statistics (there is another bug concerning really big tables) so we had to use estimate. The only work around was to log in as SYS and update the underlying stats tables with the correct values (if you can find them). This was so difficult that we opted not to run statistics on this table very often. BTW, the tables was in excess of 11 Gb.

>
> Second, the manual is clear that when you say ANALYZE TABLE, Oracle
> automatically analyzes the indexes too. I'm relying on that; should I?
Yes, indexes do automatically get analyzed when a table is analyzed. You can also individually analyze the indexes if they are dropped or new and you don't want to analyze the table maybe due to time constraints.

Good luck

-- 
||----------------------------------------------------------------------||
|| Marc Marchioli
|| The DataBase Group, Inc.                       voice:  214-528-9459
|| 4011 Travis St.                                fax:    214-528-9459
|| Dallas, TX 75204-7512
|| marcm_at_computek.net                         @
|| (Challenging ORACLE and UNIX every day)    |-}ORACLE,UNIX
||                                            /\-------------->