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: Shivaswamy Raghunath <shivaswamykr_at_gmail.com>
Date: Thu, 30 Nov 2006 09:19:51 -0500
Message-ID: <1e52ad820611300619pfb05dd0t64071b8e1058b40c@mail.gmail.com>


May be you have already given it a try, but just in case you have not. v$event_histogram can give you a fair idea of distribution, though not tell you when.

I have a log off trigger to capture the events of interest on the database I am responsible. So, if someone complains, I know where to go.

Shiva

On 11/30/06, Eagle Fan <eagle.f_at_gmail.com> wrote:
>
> 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_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-l
Received on Thu Nov 30 2006 - 08:19:51 CST

Original text of this message

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