regular expression and v$sql question

From: Dba DBA <>
Date: Wed, 29 Jun 2011 10:21:23 -0400
Message-ID: <>

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?
Received on Wed Jun 29 2011 - 09:21:23 CDT

Original text of this message