Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: sql tuning on X$ table

Re: sql tuning on X$ table

From: Alex Gorbachev <gorbyx_at_gmail.com>
Date: Thu, 30 Nov 2006 22:04:46 -0500
Message-ID: <c2213f680611301904u1832159at8a3c2ec0d5b2e179@mail.gmail.com>


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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 30 2006 - 21:04:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US