regular expression and v$sql question
Date: Wed, 29 Jun 2011 10:21:23 -0400
Message-ID: <BANLkTi=AY8rO1uq+C+79OTAw4vNyh15=qw_at_mail.gmail.com>
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 <SOME OTHER TABLE> 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?
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 29 2011 - 09:21:23 CDT