Re: regular expression and v$sql question
Date: Fri, 1 Jul 2011 13:49:50 +0200
Message-ID: <BANLkTimz_TRY8SQ0fJNgKbrsnLxnqtXbng_at_mail.gmail.com>
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 <oracledbaquestions_at_gmail.com> 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 <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?
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 01 2011 - 06:49:50 CDT