Re: create table and create index monitor or nomonitor deprecated in 10g?

• Jared Still <jkstill_at_gmail.com> wrote:

> On Fri, May 30, 2008 at 9:17 AM, Yong Huang <yong321_at_yahoo.com> wrote:
>
> > Modification Monitoring: _dml_monitoring_enabled
> > This 10g param allows you to do what you used to be able to do in 9i with
> > alter
> > table (no)monitoring, but not at table level any more; it's system wide
> > either
> > all monitoring or all nomonitoring. But I think it¡¯s still better to
> > disable
> > this parameter and leave statistics_level to typical than set
> > statistics_level
> > to basic when you want to disable table monitoring. Documentation writers
> > must
> > have a dilemma, though; they probably wish the first underscore were
> > dropped.
> >
> >
> There's at least one exception.
>
> If you are stuck on 10.2.0.2 (not allowed to upgrade) then you best not
> use ASMM, and have statistics_level = basic, as well as db_cache_advice=off.
>
> While benchmarking a 10.2.0.2 database a nasty performance bug appeared.
>
> See ML 5918631.8 and 3452409.8 regarding waits on 'simulator lru latch'
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist

Thanks, Jared. I can't view 5918631.8 but I read 3452409.8 before. My article also says (hope April Sims won't mind) that "Whatever %simulator$ latch activity is probably due to these nearly unused advisors (people that know don't use it; people that don't know never check it!)." I was talking about "Shared Pool Advice: _library_cache_advice" but the idea is the same. Whether it's 10.2.0.2 or not, if you see too much simulator related latch contention, turn off db_cache_advice and _library_cache_advice. There're a few other %advice% params but it's better to work with Oracle to pinpoint exactly the one that caused the problem. Most (or all?) can be done with alter system dynamically so checking the effect is quick. db_cache_advice is particularly bad because you almost always give the rest of memory to buffer cache, and using it means 100 bytes is wasted for each buffer (Note:148511.1).

Yong Huang       

--
http://www.freelists.org/webpage/oracle-l