Re: sql tuning on X$ table

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@xxxxxxxxx> 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@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: