Saving information about sql staements in a database table

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 Mon Aug 23 2004 - 13:07:51 CDT