Re: Performance of DBMS_STATS vs ANALYZE

From: Charlotte Hammond <charlottejanehammond_at_yahoo.com>
Date: Mon, 24 May 2004 02:40:04 -0700 (PDT)
Message-ID: <20040524094004.56368.qmail@web20730.mail.yahoo.com>

Hi Connor,

Thanks for the suggestion. Fortunately/unfortunately most of my sorts are already in memory (or at worst single pass) - this schema has many tables but none are particularly big. I guess it's the "many tables" that's the problem.

A trace shows nearly 90% of elapsed time is CPU, so I can't see anything that can be tuned that easily.

However, it's not so much a problem - it's doesn't *really* need to be speed up (for now) - I was just concerned that the shiney new DBMS_STATS was much slower than rusty old ANALYZE and thought I might be doing something wrong. From the responses I've received, it seems that this is expected behaviour, especially with large numbers of tables.

Many thanks to everyone on this thread for their input.

Charlotte
- Connor McDonald <hamcdc_at_yahoo.co.uk> wrote:
  > Hi Charlotte,
  >
  > One effective way of speeding up a dbms_stats is to
  > throw a trace on the process and then see what
  > took the longest. More often than not its the big
  > amount of sorting and aggregation that goes on.
  >
  > Some tweaks to crank up sort_area_size often work
  > some magic !
  >
  > hth
  > connor

Do you Yahoo!?
Yahoo! Domains - Claim yours for only $14.70/year

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 Mon May 24 2004 - 04:37:03 CDT