Re: sql tuning on X$ table

First of all, I would question that this query must be running that often.
Do you really need/use its results?
Probably not. So decrease the frequency of the query. If the tool doesn't
allow it - my take would be to throw it away. :)
If it does allow it but it's the default behavior to run this select every
few seconds - I would still throw it away.


On 11/29/06, Eagle Fan <eagle.f@xxxxxxxxx> 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





--
Best regards,
Alex Gorbachev

The Pythian Group
Sr. Oracle DBA

http://www.pythian.com/blogs/author/alex/
http://blog.oracloid.com

Other related posts: