Re: Performance of DBMS_STATS vs ANALYZE

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Fri, 21 May 2004 08:01:47 -0700 (PDT)
Message-ID: <20040521150147.9668.qmail@web20414.mail.yahoo.com>

• 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
  

v$session_longops will be populated with the full table scans and sort operations that occur during the dbms_stats execution.

  1 select opname, count(1), sum(elapsed_seconds)   2 from v$session_longops
  3* group by opname
system_at_QA25> /

OPNAME                                COUNT(1)

SUM(ELAPSED_SECONDS)

----------------------------------- ----------
--------------------
Gather Table's Index Statistics            108        
           9
Hash Join                                   16        
         460
Table Scan                                   1        
           7

3 rows selected.

hth.

Pd                          

---

Do you Yahoo!?
Yahoo! Domains – Claim yours for only $14.70/year http://smallbusiness.promotions.yahoo.com/offer

---

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 Fri May 21 2004 - 09:58:47 CDT