FAQ
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

Search Discussions

  • Tim Gorman at Nov 30, 2006 at 2:44 am
    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
    --
    http://www.freelists.org/webpage/oracle-l
  • Eagle Fan at Nov 30, 2006 at 4:15 am
    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 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

    --
    http://www.freelists.org/webpage/oracle-l
  • Jame tong at Nov 30, 2006 at 8:00 am
    I think you can just focus on a small set of frequent used latches ..

    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 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
    --
    http://www.freelists.org/webpage/oracle-l
  • Eagle Fan at Nov 30, 2006 at 8:55 am
    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 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
  • Shivaswamy Raghunath at Nov 30, 2006 at 2:19 pm
    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 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 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
  • Alex Gorbachev at Dec 1, 2006 at 3:04 am
    First of all, I would question that this query must be running that often.
    Do you really need/use its results?
    Probably not. So decrease the frequency of the query. If the tool doesn't
    allow it - my take would be to throw it away. :)
    If it does allow it but it's the default behavior to run this select every
    few seconds - I would still throw it away.
    On 11/29/06, 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
    --
    Best regards,
    Alex Gorbachev

    The Pythian Group
    Sr. Oracle DBA

    http://www.pythian.com/blogs/author/alex/
    http://blog.oracloid.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Anjo Kolk at Dec 1, 2006 at 2:57 pm
    Mr Eagle Fan,

    Tuning something gives you 2 possible choices:
    1) make something go faster
    2) execute it less
    (3) understand what you are doing

    I have monitered and tuned quite a bit of databases and there is always a
    trade of on overhead of monitoring and detail of information. If this query
    takes so much of your time, it is because you execute so often or you do so
    little other work in the database.

    I have plenty of experience with Direct SGA attach and I think you should
    stay away from it. Go back to the 2 (3) step program above and start fixing
    your monitoring tool.

    Anjo
    On 11/30/06, 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
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Poder at Dec 1, 2006 at 3:41 pm
    Anjo, you forgot (4) - blame someone else and raise capex for more CPUs!


    Tanel.

    Subject: Re: sql tuning on X$ table

    Mr Eagle Fan,


    Tuning something gives you 2 possible choices:

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 30, '06 at 2:31a
activeDec 1, '06 at 3:41p
posts9
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase