Re: Performance penalty when turn on timed_statistics

Giorgos Tsiamitas wrote:
>
> TFM (Oracle Reference) warns "Normally, TIMED_STATISTICS should be FALSE"
>
> They say so because timed statistics mainly serves the database tuning
> process - and normally you dont tune so often, so it's better to turn timed
> statistics off to avoid possible overhead.
>
> On the other hand you need timed statistics when you monitor the database
> thru dynamic performance views, so in this case times statistics should be
> always on.
>
> The performance impact of timed statistics depends on the hardware, platform
> and database activity. The best thing is to enable it (so you have this info
> available always and dont bother with ALTER SYSTEM or ALTER SESSION). If you
> experience constant performance degradation then turn it off (turn it on
> only when it's absolutely necessary, e.g. when you tune).
>
> Regards,
> Giorgos
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:thbutcg89d8e52_at_beta-news.demon.nl...
> >
> > "Ken Chung" <chungcck_at_netvigator.com> wrote in message
> > news:9f4plf$9bq99_at_imsp212.netvigator.com...
> > > Have anyone test/know the performance impact of setting timed_statistics
> > > to true. One told me that it does affect the performance too much.
> > > However, some books said that never turn on timed_statistics to true
> > > on production environments. It will slow down the database.
> > >
> > >
> > > Regards,
> > > Ken
> > >
> > >
> > >
> > >
> >
> > I have never seen any noticeable impact. Keep in mind, most of these
> > statistics are gathered anyway, and the only difference you now keep track
> > of the time.
> > I have also read several times you should enable it always, and I have
 never
> > seen warnings against it.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >
> >

Excluding some of the recent bugs, I would say that there is no reason why timed stats should ever be turned off. Lets say in even the absolute worst case, that it added 10% overhead. Leaving it off is equivalent to saying "I've got my application/hardware/database tuned to within 90% of its absolute optimal performance"...

Given that 99% of databases run on buffered file systems, they don't have a leg to stand on...

Cheers
Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"