Re: sql tuning on X$ table

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 
--
http://www.freelists.org/webpage/oracle-l

Other related posts: