Re: sql tuning on X$ table
- From: "Eagle Fan" <eagle.f@xxxxxxxxx>
- To: "jame tong" <jametong@xxxxxxxxx>
- Date: Thu, 30 Nov 2006 16:55:49 +0800
hi Tong:
Thanks for join.
Query v$latch also needs to access full x$ksllt and it does much more
things.
That's why we use x$ table to do the monitoring.
SQL> set autotrace on
SQL> select latch#,name,gets,sleeps,misses from v$latch where misses >= 100;
LATCH#
NAME
GETS SLEEPS MISSES
---------- ----------------------------------------------------------------
---------- ---------- ----------
15
messages
2687612 0 791
96 active checkpoint queue
latch 341223 0 593
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 VIEW
3 2 FILTER
4 3 SORT (GROUP BY)
5 4 FIXED TABLE (FULL) OF 'X$KSLLT'
6 1 SORT (JOIN)
7 6 FIXED TABLE (FULL) OF 'X$KSLLD'
And I did a simple test , the results show that your sql took much more CPU
time :)
SQL> select STATISTIC#,name from v$statname where name like '%CPU%';
STATISTIC# NAME
---------- ----------------------------------------------------------------
11 CPU used when call started
12 CPU used by this session
251 OS User level CPU time
252 OS System call CPU time
253 OS Other system trap CPU time
SQL> select * from v$mystat where statistic#=12;
SID STATISTIC# VALUE
---------- ---------- ----------
1676 12 5
SQL>
SQL> select kslltnum latch#,sum(kslltwgt) gets,sum(kslltwff) misses,
2 sum(kslltwsl) sleeps from x$ksllt group by
kslltnum;
LATCH# GETS MISSES SLEEPS
---------- ---------- ---------- ----------
0 0 0 0
1 1 0 0
................
98 1.4435E+11 506340308 24626912
..............
241 rows selected.
SQL> select * from v$mystat where statistic#=12;
SID STATISTIC# VALUE
---------- ---------- ----------
1676 12 666
SQL> select latch#,name,gets,sleeps,misses from v$latch where misses >= 100;
LATCH#
NAME
GETS SLEEPS MISSES
---------- ----------------------------------------------------------------
---------- ---------- ----------
................
98 cache buffers chains
1.4435E+11 24626995 506347321
99 cache buffer handles
385974970 256 182589
.................
38 rows selected.
SQL> SQL>
SQL> select * from v$mystat where statistic#=12;
SID STATISTIC# VALUE
---------- ---------- ----------
1676 12 2844
SQL> select kslltnum latch#,sum(kslltwgt) gets,sum(kslltwff) misses,
sum(kslltwsl) sleeps
2 from x$ksllt where kslltnum in (98) group by kslltnum;
LATCH# GETS MISSES SLEEPS
---------- ---------- ---------- ----------
98 1.4436E+11 506354921 24627083
SQL> select * from v$mystat where statistic#=12;
SID STATISTIC# VALUE
---------- ---------- ----------
1676 12 3502
My sql: 661
Yours:
Step1: 2178
Step2: 658
Total: 2836
On 11/30/06, jame tong <jametong@xxxxxxxxx> wrote:
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
--
Eagle Fan
Oracle DBA
Other related posts:
