Re: sql tuning on X$ table
- From: "jame tong" <jametong@xxxxxxxxx>
- To: eagle.f@xxxxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Nov 2006 16:00:37 +0800
I think you can just focus on a small set of frequent used latches ..
1. get the small sets of latches,
eg: select latch#,name,gets,sleeps,misses from v$latch where misses >=
100;
you can also get these latches just from statspack of the most waited
latches.
2. just query the latches from the previous sets.
select .... from x$ksllt where kslltnum in (98,16,17,...)
On 11/30/06, Eagle Fan <eagle.f@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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
Other related posts:
