regular expression and v$sql question
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Jun 2011 10:21:23 -0400
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.
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?
Other related posts:
