Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_STATS [resend chomped version]

RE: DBMS_STATS [resend chomped version]

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Fri, 18 Jun 2004 16:14:17 -0700
Message-ID: <35CFD500D7BDCE43B9030BBA5979DC18015A3742@ussccem13.corp.hds.com>


Jonathan,

>Agree completely - it's a bit of a luxury to have the time,
>and hard to get the correct information, but every (complex)
>system needs a table-driven stats gathering process to
>minimize the work done, and maximise the return on effort.

Someone on this list suggested (don't remember who) turning on Table monitoring on most (if not all) tables and using a script to analyze the top-N tables by 'staleness' (i.e. %age rows changed) in a cyclic fashion. This way, active tables would get analyzed more often while keeping down the system-load and avoiding a carpet-bomb type analyze.

One of the questions then is the amount of overhead that MONITORING would place on the system - in the SGA as well as on processing. My very sketchy understanding (based on Steve Adams' website) is that the updates to the in-memory structures behind DBA_TAB_MONITORING is unlatched and not overhead-heavy. However, there was no hard evidence. Do you have any? Is this data collected off the mechanism that updates values in V$SQL? [Apps has a _lot_ of tables (about 15,000), although some of them may be inactive based on what modules have been implemented]

Any thoughts on this welcome!
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional!

Received on Fri Jun 18 2004 - 18:11:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US