Re: sql tuning on X$ table

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 29 Nov 2006 19:44:36 -0700

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2006