Re: regular expression and v$sql question
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 1 Jul 2011 10:51:48 -0400
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 <martin.a.berger@xxxxxxxxx>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 <oracledbaquestions@xxxxxxxxx>
> 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?
>
Other related posts:
