sql tuning on X$ table

From: Eagle Fan <eagle.f_at_gmail.com>
Date: Thu, 30 Nov 2006 10:31:50 +0800
Message-ID: <4415a5ed0611291831q2631b2bby9826b6d244522d@mail.gmail.com>

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 Received on Wed Nov 29 2006 - 20:31:50 CST