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: Eagle Fan <eagle.f_at_gmail.com>
Date: Thu, 30 Nov 2006 12:15:13 +0800
Message-ID: <4415a5ed0611292015h3f5bd1cat884c4fc3774f508@mail.gmail.com>


hi Gorman:

Thanks for your replay.

We need to check latch detail info when databases have "latch free" contentions.

We don't know when the contention happens.

From my experiences, most of the time, "latch free" contention was a spike,and it just last a few minutes.

When we nitified , the porblem had already gone. So the log can help us to figure out what happened at that spike time

On 11/30/06, Tim Gorman <tim_at_evdbt.com> wrote:
>
> 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
>

-- 
Eagle Fan

Oracle DBA

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 29 2006 - 22:15:13 CST

Original text of this message

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