Re: DBMS_STATS

There is no generic answer to that question. Global Stats on partitioned tables do take a long time to collect - and you do need them to be reasonable because any query that the optimizer cannot pin down to exactly one partition at parse time is optimised according to the global stats.

The best bet is to cheat. If you know your data you can analyze the partitions, but use dbms_stats.set_table_stats et. al.
to set the global stats.

Run dbms_stats with sql_trace switched on to see what happens with the 'exotic' options. I think Connor has some comments on his
website - www.oracledba.co.uk - but the
bottom line is "don't be surprised if Oracle wastes computer resources when trying
to automatic the jobs that require you to know the data".

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 26 February 2003 13:23

> When is the best time to gather Global Stats? Using the old
ANALYZE command, I would analyze each partition as it accumulated data. Now that we have global stats, should I be gather global stats each time I analyze a partition? That would naturally increase the length of time to gather stats.
> Oracle recommends deleting global stats on partitioned tables in
11i (running on an 8i database). See bde_last_analyzed.sql on MetaLink. In fact, if you read the comments in this script, it implies that you should never gather global stats on a partitioned table. Any thoughts on this? Has anyone had problems with global stats on 9i?
> Also, does anybody recommend SKEWONLY or AUTO_SAMPLE_SIZE with
DBMS_STATS? My testing shows that this causes the analyze to take longer (which is to be expected). I haven't yet determined if its worth the extra time.
>
> Thanks,
>
>
> Jay Hostetter
> Oracle DBA
> D. & E. Communications
> Ephrata, PA USA
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).