Message-ID: <456E4594.9070902@evdbt.com>
Date: Wed, 29 Nov 2006 19:44:36 -0700
From: Tim Gorman <tim@evdbt.com>
Organization: Evergreen Database Technologies, Inc.
User-Agent: Thunderbird 1.5.0.8 (Windows/20061025)
MIME-Version: 1.0
To: oracle-l@freelists.org
CC: eagle.f@gmail.com
Subject: Re: sql tuning on X$ table
References: <4415a5ed0611291831q2631b2bby9826b6d244522d@mail.gmail.com>
In-Reply-To: <4415a5ed0611291831q2631b2bby9826b6d244522d@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: tim@evdbt.com

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