Re: Saving information about sql staements in a database table

Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl> wrote in message news:<6bjki0dcti6pqso7ohr2o2oro4u3ceb7vs_at_4ax.com>...
> On 23 Aug 2004 11:07:51 -0700, premmehrotra_at_hotmail.com (Prem K
> Mehrotra) wrote:
>
> >I want to save some information about each sql statement as it is run
> >in my own database table, e.g.,
> >
> >1. sql statement
> >2. time it started running
> >3. time it finished running
> >=> I know "running" is vague but I will settle whatever Oracle uses
> >when it gives the time taken by a sql statement in tkprof output
> >4. no of rows accessed by the sql statement
> >5. amount of disk i/o done
> >etc
> >
> >I know Oracle provides some information in v$ views but v$ views seem
> >to have cumulative information, e.g., if the statement is executed
> >multiple times, it will show
> >the statement only once and accumulate no of rows accessed (as shown
> >in tkprof
> >output. What I am after is information is gathered separately for
> >sql_statement, start_time_stamp. For example, if the same statement
> >was run 5 times, I will have five rows in the table and not one row.
> >
> >I know by using statspack, one can collect information over various
> >periods of time, but I am after a trigger based approach, i.e.,
> >record information for each statement separately and not accumulate
> >information over a period.
> >
> >If I have such information, I can generate histograms of execution
> >time of various sql statements and find why they started running slow
> >all of a sudden,
> >
> >I apologize if I am vague in what I am asking for, but if someone can
> >provide me pointers, it will be a good start. Thanks a lot.
> >
> >
> >Prem
>
> I don't think you are on the right track here. First of all, you can't
> fire a trigger on a select. Secondly, getting information various
> execution times won't tell you why a statement is running slow. Ie you
> may have a different session performing a FTS, causing your session a
> high number of consistent gets. Your histogram won't show you that
> session.

Sybrand:

Thanks. I still think the information I am looking for can be useful, e.g.,
I will get execution time for a statement at different times and then it will be interesting to find the root cause of variations in times.

As for as trigger, I was just giving an example. If Oracle wanted to provide such information, they can perhaps implement an insert trigger on one of the tables.

Problem which have with v$ views such as v$sqlarea, v$sql, the information
there is cumulative, i.e., if the same statement was executed hundred times, I will see a sum of rows_processed.

Prem Received on Tue Aug 24 2004 - 16:06:41 CDT