Re: sql tuning on X$ table

I think you can just focus on a small set of frequent used latches ..

1. get the small sets of latches, eg: select latch#,name,gets,sleeps,misses from v$latch where misses >= 100;

  you can also get these latches just from statspack of the most waited latches.

2. just query the latches from the previous sets. select .... from x$ksllt where kslltnum in (98,16,17,...)

On 11/30/06, Eagle Fan <eagle.f_at_gmail.com> wrote:
>
> hi Gorman:
>
> Thanks for your replay.
>
> We need to check latch detail info when databases have "latch free"
> contentions.
>
> We don't know when the contention happens.
>
> From my experiences, most of the time, "latch free" contention was a
> spike,and it just last a few minutes.
>
> When we nitified , the porblem had already gone. So the log can help us to
> figure out what happened at that spike time
>
> On 11/30/06, Tim Gorman < tim_at_evdbt.com> wrote:
> >
> > How much time is being spent in the "latch free" wait-event? If there
> > is not a large amount of time spent waiting on this wait-event, then
> > running this query is a waste of CPU.
> >
> > Hope this helps...
> >
> >
> > Eagle Fan wrote:
> > > hi:
> > >
> > > We have a monitoring tool to monitor database performance.
> > >
> > > It run the following sql every few seconds and it cost a lot of CPU
> > > times. About 20% of total CPU time.
> > >
> > > select kslltnum latch#,sum(kslltwgt) gets,sum(kslltwff) misses,
> > > sum(kslltwsl) sleeps from x$ksllt group
> > > by kslltnum;
> > >
> > > The sql is used to collect latch statistics, latch#, gets, misses,
> > > sleeps and then get the top heavy latch contentions.
> > >
> > > I have no idea how to tune this sql. It's on x$ table.
> > >
> > > I have read kyle's presentation about direct SGA access and I think it
> > > may help the sql.
> > >
> > > Does anyone have experiences of using direct SGA access on production
> > > system?
> > >
> > > Any risks? How many performance gains?
> > >
> > > Thanks
> > >
> > > --
> > > Eagle Fan
> > >
> > > Oracle DBA
> >
>
>
>
> --
> Eagle Fan
>
> Oracle DBA

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