Re: dbms_stats / 9204

Oradba Linux wrote:
>
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:419C9CAB.1659_at_yahoo.com...
> > Oradba Linux wrote:
> > >
> > > Connor McDonald wrote:
> > > > Oradba Linux wrote:
> > > >
> > > >>I have gathered statistics for all objects in our prod database.
> > > >>How do you guys find out the next time to gather stats on the table?
> > > >>Do you regularly put them in a schedule ? My intention is avoid
> wasting
> > > >>cpu cycles gathering statistics unless it is needed.
> > > >
> > > >
> > > > check out the MONITORING clause for tables
> > > >
> > > > hth
> > > > connor
> > > Hello Connor
> > >
> > > I have just ordered your book. I am liking the reviews a lot. Hopefully
> > > i will not be disappointed.
> > > The reason i dont want to turn on monitoring for all tables is the
> > > overhead. Is this overhead too much? I am looking for opinions.
> > > Also it is a datawarehouse/reporting db.
> > > I am on oracle 9204 / sun os 32 bit
> >
> > There is an overhead but I'll doubt you'll notice it. Its predominantly
> > memory based (and latch-less) with the data only being flushed down the
> > the data dictionary at regular intervals.
> >
> > In fact, in v10, all tables are monitored as a default.
> >
> > hth
> > connor
> > --
> > Connor McDonald
> > Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> > ISBN: 1590592174
> >
> > web: http://www.oracledba.co.uk
> > web: http://www.oaktable.net
> > email: connor_mcdonald_at_yahoo.com
> >
> > Coming Soon! "Oracle Insight - Tales of the OakTable"
> >
> > "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
> > and...he will sit in a boat and drink beer all day"
> >
> > ------------------------------------------------------------
>
> 1. Will i not notice this on a heavy OLTP DB? Is this so lightweight?
> 2. My question is more what if 10% of the table which has say 60 mil rows
> have changed, do i have to gather statistics again?
> 3. If i go do an update without actually changing the data, it will still
> show up in dba_tab_modifications view.
> It will not happen in a live system guaranteed. does this mean that i
> should gather stats again
> 4. According to metalink support note <> it is says it is enough to
> gather stats using 5% estimate. Has anybody seen this not to
> work well.
> I want to know how statistics gathering is handled in real live system
> OLTP/DSS.
>
> Thanks in advance

Don't get me wrong - I'm not advocating you gather stats all the time. I just said that monitoring should not have a noticeable impact.

My preference is to only calculate stats when things go bad - why would you do so otherwise ?

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------