FAQ
I create a table which is a snapshot of v$sql. I want to find all the
inserts, updates, and deletes run against a list of tables
if I do like '%INSERT%INTO%TABLE_NAME%' I will double count if it is INSERT
INTO select * from my table.

If I do, '%INSERT INTO TABLENAME%' I am not sure if v$sql will store an
extra space if a developer makes inserts with more than one space between
into and table name. Even if I am wrong on these assumptions, I spent a
while trying to figure this out and am really curious. How do I do the
following

My Question:

How do I find the next word (not just the character, I want the tablename)
after a previous word. Some I don't care about spaces, but I want to find
the next word (table name in this case) after the word INTO?

Second part of the question. We also pull sql_id, buffer_gets, executions,
etc... out of the AWR repository and store those for each snapshot. I want
to take the sql_ids from v$sql and them sum the executions (and possibly
other metrics)
to see activity over time or even at certain times. The reason for this is
that I want to know how many inserts, updates, and deletes I do on average
and to see if there are spikes at certain times (such as I am running a
batch load). I am thinking of using stadard deviation function then look for
times where the the activity is mroe than 1.5 Standard deviations and things
like that. We track a bunch of metrics, so I will look at several things .I
have a few questions.

I know I will double count when I search on DML for a table, since oracle
will parse both the pl/sql and the sql statements in memory. I already know
I need to handle for this with double counting on executions.
If I have child cursors. Are the executions double counted? So if I have
queries that are exactly the same, but have 2 different sql_ids, can I sum
the executions and that will be correct or am I double counting? I know in
some case
if the query is run from 2 different schemas, it is a different query
Anything else I have to look for to get accurate numbers? Anyone ever do
this?

Search Discussions

  • Nigel Thomas at Jun 29, 2011 at 4:03 pm
    How do I find the next word (not just the character, I want the tablename)
    after a previous word. Some I don't care about spaces, >but I want to find
    the next word (table name in this case) after the word INTO?

    For your first question - Have a look at Using Regular Expressions in *
    Oracle* Database<http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm>eg
    select columns from v$sql ... where regexp_like(lower(sql_text),
    'insert\s+into\s+tablename')

    Get to know the character classes - \s+ stands in for any number of white
    space characters (it's a Perl character class - you could also use the
    equivalent Posix character class [:space:] - see
    http://www.regular-expressions.info/posixbrackets.html).

    You can also match on word boundaries eg '\btablename\b' would match against
    your tablename but not against anothertablename. However be aware that words
    are alphanumeric so I think another_tablename would count as 2 words. So you
    may need a more complex regex that allows for the tablename to start after
    spaces and to be finished with a space, or an open bracket.

    And of course it could be insert into schema.tablename and there are other
    variants; insert into ... select from, insert into .., (column list) values
    (...), insert into ... values (...) etc.

    Hope that helps

    Regards Nigel
  • Rjamya at Jun 29, 2011 at 4:21 pm
    or you could look the other way and probably use information from v$sql_plan
    ... not sure if it will provide everything you need, but might be a bit
    easier than running regex on complex queries. just watch out for multiple
    plans for same sql_id tho' ... this may help you cut down the list of
    sql_ids of interest to you.

    Raj
  • Dba DBA at Jul 1, 2011 at 2:53 pm
    Thank you. I did not think to look at regular expression documentation. I
    was looking at the oracle documentations and people's examples (they were
    all pretty similiar to oracles examples). This is very helpful.

    On Wed, Jun 29, 2011 at 12:03 PM, Nigel Thomas <
    nigel.cl.thomas_at_googlemail.com> wrote:
    How do I find the next word (not just the character, I want the
    tablename) after a previous word. Some I don't care about spaces, >but I
    want to find the next word (table name in this case) after the word INTO?

    For your first question - Have a look at Using Regular Expressions in *
    Oracle* Database<http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm>eg
    select columns from v$sql ... where regexp_like(lower(sql_text),
    'insert\s+into\s+tablename')

    Get to know the character classes - \s+ stands in for any number of white
    space characters (it's a Perl character class - you could also use the
    equivalent Posix character class [:space:] - see
    http://www.regular-expressions.info/posixbrackets.html).

    You can also match on word boundaries eg '\btablename\b' would match
    against your tablename but not against anothertablename. However be aware
    that words are alphanumeric so I think another_tablename would count as 2
    words. So you may need a more complex regex that allows for the tablename to
    start after spaces and to be finished with a space, or an open bracket.

    And of course it could be insert into schema.tablename and there are other
    variants; insert into ... select from, insert into .., (column list) values
    (...), insert into ... values (...) etc.

    Hope that helps

    Regards Nigel

    --
    http://www.freelists.org/webpage/oracle-l
  • Martin Berger at Jul 1, 2011 at 11:49 am
    It is not the answer to your question, but maybe it solves your problem?

    you can check v$sql_plan instead of v$sql.
    in v$sql_plan you have the column OPERATION and some OBJECT% columns.
    Even I did not test it, you do not have to deal with spaces, regular
    expressions, etc.

    regarding AWR: in AWR you do have snapshots. So please don't sum
    anything. It will be misleading. COUNT fits much better in that
    situation. And even the counts are not 'real' counts, you just can use
    them as a Proxy to the real numbers (which you will never get out of
    AWR).

    I don't understand your target right: Do you want to know the COUNT of
    DMLs on a particular table? (in this case I'd suggest audit) Or the
    amount of time SPENT in DMLs? (in this case the count over ASH/AWR
    makes sense)

    sorry for the additional questions
    Martin
    On Wed, Jun 29, 2011 at 16:21, Dba DBA wrote:
    I create a table which is a snapshot of v$sql.  I want to find all the
    inserts, updates, and deletes run against a list of tables
    if I do like '%INSERT%INTO%TABLE_NAME%' I will double count if it is INSERT
    INTO select * from my table.

    If I do, '%INSERT INTO TABLENAME%' I am not sure if v$sql will store an
    extra space if a developer makes inserts with more than one space between
    into and table name.  Even if I am wrong on these assumptions, I spent a
    while trying to figure this out and am really curious. How do I do the
    following
    My Question:
    How do I find the next word (not just the character, I want the tablename)
    after a previous word. Some I don't care about spaces, but I want to find
    the next word (table name in this case) after the word INTO?
    Second part of the question. We also pull sql_id, buffer_gets, executions,
    etc... out of the AWR repository and store those for each snapshot. I want
    to take the sql_ids from v$sql and them sum the executions (and possibly
    other metrics)
    to see activity over time or even at certain times. The reason for this is
    that I want to know how many inserts, updates, and deletes I do on average
    and to see if there are spikes at certain times (such as I am running a
    batch load). I am thinking of using stadard deviation function then look for
    times where the the activity is mroe than 1.5 Standard deviations and things
    like that. We track a bunch of metrics, so I will look at several things .I
    have a few questions.
    1. I know I will double count when I search on DML for a table, since oracle
    will parse both the pl/sql and the sql statements in memory. I already know
    I need to handle for this with double counting on executions.
    2. If I have child cursors. Are the executions double counted? So if I have
    queries that are exactly the same, but have 2 different sql_ids, can I sum
    the executions and that will be correct or am I double counting? I know in
    some case
    if the query is run from 2 different schemas, it is a different query
    3. Anything else I have to look for to get accurate numbers? Anyone ever do
    this?
    --
    http://www.freelists.org/webpage/oracle-l
  • Dba DBA at Jul 1, 2011 at 2:51 pm
    I am not entirely sure what I want specifically. I just want to know if in
    general my approach would work.
    so a sum wont work and it work across database bounces. So I need to code
    for a db bounce.

    So what awr does is

    most recent snapshot - oldest snapshot

    but i need to code around database bounces since these reset.

    Basically this is what I am trying to do:
    1. find all the DML for a list of tables. I think the regular expression
    above will give me that.
    2. Then look at a few metrics over time. Executions,buffer gets, etc...
    3. I want to look at total, over time. avg over a week or a day. look for
    spikes at certain times of a day (this will tell me if there is a batch
    going on or something else). The spikes are important
    4. I want to do this for about 175 tables so I need to do it with code and I
    want it to be ongoing.
    5. In general I need to know how much dml we do aganst certain tables,
    whether the acitivty is consistent or has big spikes. If it has big spikes,
    when do those big spikes take place.

    This is a very large and very old system. I don't have documentation I can
    go to. There is no perfect way to do this, especially with handling for DB
    bounces, but I think I can use a lag,lead function to find them with some
    pl/sql. Not sure how i'll determine a spike. I'll worry about that later.
    Right now, I want to know if my general approach is correct. If I can get
    the executions out of the awr.
    On Fri, Jul 1, 2011 at 7:49 AM, Martin Berger wrote:

    It is not the answer to your question, but maybe it solves your problem?

    you can check v$sql_plan instead of v$sql.
    in v$sql_plan you have the column OPERATION and some OBJECT% columns.
    Even I did not test it, you do not have to deal with spaces, regular
    expressions, etc.

    regarding AWR: in AWR you do have snapshots. So please don't sum
    anything. It will be misleading. COUNT fits much better in that
    situation. And even the counts are not 'real' counts, you just can use
    them as a Proxy to the real numbers (which you will never get out of
    AWR).
    I don't understand your target right: Do you want to know the COUNT of
    DMLs on a particular table? (in this case I'd suggest audit) Or the
    amount of time SPENT in DMLs? (in this case the count over ASH/AWR
    makes sense)

    sorry for the additional questions
    Martin

    On Wed, Jun 29, 2011 at 16:21, Dba DBA
    wrote:
    I create a table which is a snapshot of v$sql. I want to find all the
    inserts, updates, and deletes run against a list of tables
    if I do like '%INSERT%INTO%TABLE_NAME%' I will double count if it is INSERT
    INTO select * from my table.

    If I do, '%INSERT INTO TABLENAME%' I am not sure if v$sql will store an
    extra space if a developer makes inserts with more than one space between
    into and table name. Even if I am wrong on these assumptions, I spent a
    while trying to figure this out and am really curious. How do I do the
    following
    My Question:
    How do I find the next word (not just the character, I want the
    tablename)
    after a previous word. Some I don't care about spaces, but I want to find
    the next word (table name in this case) after the word INTO?
    Second part of the question. We also pull sql_id, buffer_gets,
    executions,
    etc... out of the AWR repository and store those for each snapshot. I want
    to take the sql_ids from v$sql and them sum the executions (and possibly
    other metrics)
    to see activity over time or even at certain times. The reason for this is
    that I want to know how many inserts, updates, and deletes I do on average
    and to see if there are spikes at certain times (such as I am running a
    batch load). I am thinking of using stadard deviation function then look for
    times where the the activity is mroe than 1.5 Standard deviations and things
    like that. We track a bunch of metrics, so I will look at several things .I
    have a few questions.
    1. I know I will double count when I search on DML for a table, since oracle
    will parse both the pl/sql and the sql statements in memory. I already know
    I need to handle for this with double counting on executions.
    2. If I have child cursors. Are the executions double counted? So if I have
    queries that are exactly the same, but have 2 different sql_ids, can I sum
    the executions and that will be correct or am I double counting? I know in
    some case
    if the query is run from 2 different schemas, it is a different query
    3. Anything else I have to look for to get accurate numbers? Anyone ever do
    this?
    --
    http://www.freelists.org/webpage/oracle-l
  • Saibabu Devabhaktuni at Jul 6, 2011 at 8:19 am
    If I understand your question correctly, you would like to get the list of all
    DML sql statements for a given table and then track their performance metrics
    across awr snapshots.

    Assuming that there are not many new sql statements being introduced often,
    first thing you need to do is to get the mapping between sql_id and the table
    names. Here are few options to do the sql mapping:

    Join v$sql with v$object_dependency (this will work very well for no join sql
    statements, you probably need to eliminate tables doing the select query within
    a DML by excluding their object_names from v$sql_plan versus
    v$object_dependency)
    Join v$active_session_history and v$logmnr_contents by XID column to map
    sql_id from ash with seg_name from logminer. This requires you to use logminer
    on redologs.
    If you can afford to, create statement level triggers on all tables and as
    part of the trigger code insert current sql into the global logging table. You
    can get the current sql (or prev_sql_id) in trigger context by running a query
    against v$session where sid=sys_context('USERENV', 'SID') and
    audsid=sys_context('USERENV', 'SESSIONID')'.
    You can also use fine grained auditing.

    I do not recommend using your own parser to fetch table names out of sql
    statements, unless you know your application really well.

    For tracking the performance metrics across awr snapshots:

    No sql performance metrics are double counted by Oracle. There will be a new
    entry in v$sql for every child cursor that is in use, all metrics reflect usage
    of that particular child cursor. Awr simply rolls them up by sql_id and
    plan_hash_value when it takes the snapshot. If a cursor gets aged out from the
    shared pool, awr will not have any visibility into that. If a cursor gets
    invalidated due to DDL, new child cursor is spawned (old child cursor will be
    purged) and all the performance metrics for the old cursor will be gone from
    v$sql; again awr will not have full visibility into it.
    You can query dba_hist_sqlstat for executions_delta, buffer_gets_delta,
    etc... by sql_id, snap_id, instance_number, plan_hash_value..

    Hope it helps.

    Thanks,
    Sai
    http://sai-oracle.blogspot.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 29, '11 at 2:21p
activeJul 6, '11 at 8:19a
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase