Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: sql tuning on X$ table
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 bykslltnum;
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_at_gmail.com> 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_at_gmail.com> 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_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
>
>
>
-- Eagle Fan Oracle DBA -- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 30 2006 - 02:55:49 CST
![]() |
![]() |