Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Saving information about sql staements in a database table
Hi,
not an answer for you but i'm interested to knonw if you find how to do this
In mssql, we can do this with the tool profiler, in UDB with event monitor
but i don't know too in oracle
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
Received on Fri Aug 27 2004 - 09:01:07 CDT