Grokbase › Groups › Oracle › oracle-l › December 2005
FAQ
I may have this requirement to implement something that captures session
details and sessions waits at very short intervals in a 9204 database. AWR
kinda does that in 10g, but this is no 10g.

I am wondering what might be the lightest process that I can run maybe at a
minute interval? Too many and too frequent selects against gv$ are not good,
and I understand that. But I have a feeling that this requirement is coming
down the line.

Question for experts ... if write a really (well as light as I can) process
to get info from x$ksusecst (for waits) and x$ksuse (for session), what
should I look for as potential problems (any specific latches?). Oh yeah and
this will be running on a RAC system.

Feel free to tell me that this is a bad idea, but if the request comes down
I will then use those arguments to say why we shouldn't do it.

TIA

Raj

This space is available for rent.

Search Discussions

  • John Kanagaraj at Dec 13, 2005 at 3:42 pm ⇧
    Raj,


    This isn't a bad idea at all. AWR (among other things) actually collects
    both System level information (sorta like STATSPACK) for AWR/ADDM
    reporting and Session level information by persisting the much more
    granular ASH data. The MMON and MMNL processes (again among other
    things) perform the 'light' part of the collection for both ASH and AWR
    and (I believe) sample SGA directly for ASH information. There was an
    article on IOUG's SELECT mag on performing direct SGA access and I think
    there is something out at Kyle 'X$' Hailey's website (not sure if this
    is still around). You should be able to adapt from there (let me know if
    you can't get the code and I will dig around for it).


    I am actually working on developing this idea of 'backporting' as part
    of a paper for the upcoming Collaborate '06 conference, so you are not
    alone in having this 'bad idea';-) (Has anyone heard back about paper
    selections for '06?)


    Hth,
    John Kanagaraj <><
    DB Soft Inc
    Phone: 408-970-7002 (W)


    Co-Author: Oracle Database 10g Insider Solutions
    http://www.samspublishing.com/title/0672327910


    The opinions and facts contained in this message are entirely mine
    and do not reflect those of my employer or customers **

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of rjamya
    Sent: Tuesday, December 13, 2005 3:06 AM
    To: Oracle Discussion List
    Subject: Simulate AWR like repository in 9i

    I may have this requirement to implement something that captures session
    details and sessions waits at very short intervals in a 9204 database.
    AWR kinda does that in 10g, but this is no 10g.

    I am wondering what might be the lightest process that I can run maybe
    at a minute interval? Too many and too frequent selects against gv$ are
    not good, and I understand that. But I have a feeling that this
    requirement is coming down the line.

    Question for experts ... if write a really (well as light as I can)
    process to get info from x$ksusecst (for waits) and x$ksuse (for
    session), what should I look for as potential problems (any specific
    latches?). Oh yeah and this will be running on a RAC system.

    Feel free to tell me that this is a bad idea, but if the request comes
    down I will then use those arguments to say why we shouldn't do it.

    TIA

    Raj

    This space is available for rent.
  • Rjamya at Dec 13, 2005 at 4:20 pm ⇧
    Yup, considered that, actually played with it for a while, but couldn't make
    it work on 64 bit platform. Now, only if my C was as good as my pl/sql and
    sql ... sigh ...

    Raj
    On 12/13/05, John Kanagaraj wrote:

    Raj,

    This isn't a bad idea at all. AWR (among other things) actually collects
    both System level information (sorta like STATSPACK) for AWR/ADDM reporting
    and Session level information by persisting the much more granular ASH data.
    The MMON and MMNL processes (again among other things) perform the 'light'
    part of the collection for both ASH and AWR and (I believe) sample SGA
    directly for ASH information. There was an article on IOUG's SELECT mag on
    performing direct SGA access and I think there is something out at Kyle 'X$'
    Hailey's website (not sure if this is still around). You should be able to
    adapt from there (let me know if you can't get the code and I will dig
    around for it).

    I am actually working on developing this idea of 'backporting' as part of
    a paper for the upcoming Collaborate '06 conference, so you are not alone in
    having this 'bad idea';-) (Has anyone heard back about paper selections for
    '06?)

    Hth,
    John Kanagaraj <><
    DB Soft Inc
    Phone: 408-970-7002 (W)

    Co-Author: Oracle Database 10g Insider Solutions
    http://www.samspublishing.com/title/0672327910

    ** The opinions and facts contained in this message are entirely mine and
    do not reflect those of my employer or customers **

    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    [email protected]] *On Behalf Of *rjamya
    *Sent:* Tuesday, December 13, 2005 3:06 AM
    *To:* Oracle Discussion List
    *Subject:* Simulate AWR like repository in 9i

    I may have this requirement to implement something that captures session
    details and sessions waits at very short intervals in a 9204 database. AWR
    kinda does that in 10g, but this is no 10g.

    I am wondering what might be the lightest process that I can run maybe at
    a minute interval? Too many and too frequent selects against gv$ are not
    good, and I understand that. But I have a feeling that this requirement is
    coming down the line.

    Question for experts ... if write a really (well as light as I can)
    process to get info from x$ksusecst (for waits) and x$ksuse (for session),
    what should I look for as potential problems (any specific latches?). Oh
    yeah and this will be running on a RAC system.

    Feel free to tell me that this is a bad idea, but if the request comes
    down I will then use those arguments to say why we shouldn't do it.

    TIA
    Raj
    ----------------------------------------------
    This space is available for rent.
    --
    ----------------------------------------------
    This space is available for rent.
    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Põder at Dec 14, 2005 at 12:51 pm ⇧
    Hi,

    Direct SGA access would probably the most CPU efficient method, but you have to account for inconsistent data as you're not using any latching for your reads.

    Otherwise, if you are sampling x$ksuse tables, make sure you include the restricting predicates on:
    1) inst_id - that sampling would be done on each instance locally, otherwise you'll get lot's of interinstance calls, have to spawn PQ slaves etc
    2) if sampling is needed only for specific sessions, query the x$ table using a fixed table index (the indexed x$ table columns can be seen from v$indexed_fixed_column)

    Tanel

    Original Message -----
    From: rjamya
    To: Oracle Discussion List
    Sent: Tuesday, December 13, 2005 5:06 AM
    Subject: Simulate AWR like repository in 9i

    I may have this requirement to implement something that captures session details and sessions waits at very short intervals in a 9204 database. AWR kinda does that in 10g, but this is no 10g.

    I am wondering what might be the lightest process that I can run maybe at a minute interval? Too many and too frequent selects against gv$ are not good, and I understand that. But I have a feeling that this requirement is coming down the line.

    Question for experts ... if write a really (well as light as I can) process to get info from x$ksusecst (for waits) and x$ksuse (for session), what should I look for as potential problems (any specific latches?). Oh yeah and this will be running on a RAC system.

    Feel free to tell me that this is a bad idea, but if the request comes down I will then use those arguments to say why we shouldn't do it.

    TIA

    Raj

    This space is available for rent.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 13, '05 at 5:06a
activeDec 14, '05 at 12:51p
posts4
users3
websiteoracle.com

3 users in discussion

Rjamya: 2 posts John Kanagaraj: 1 post Tanel Põder: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase