Re: sql tuning on X$ table

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@xxxxxxxxx> 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

Other related posts: