Re: Saving information about sql staements in a database table

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 Bakker, Senior Oracle DBA