*** How To Install And Use *** Here are the steps: 1. Decide on a dba-like Oracle user to be the monitoring user. The security requirements can be granted like this: def monitoruser=mon1 <-- whatever user you want grant dba to &monitoruser; grant select on v_$session to &monitoruser; grant select on v_$sqlstats to &monitoruser; grant execute on dbms_lock to &monitoruser; I suspect you can get a way w/out granting dba to the user. You will know if you need more permissions because the procedure will fail very quickly when run. 2. Create the results table, package, functions, and procedures... - Add this product to your cart and check out. When you check out, you will see a link on your screen. Follow this and you'll eventually get to where you can download a zip file which contains a single text file. This file contains all the DDL you need. - Just copy/paste the entire file into the monitoring user's sqlplus session. - That's it! 3. Find a sql id to monitor. This may help: select sql_id,plan_hash_value,executions,substr(sql_text,1,60) the_sql from v$sqlstats where sql_text like '%&whatyoulookingfor%'; -or- select username, sid, sql_id from v$session where status='ACTIVE'; 4. Monitor. Here is the usage: exec op_sample_elapsed_v3.sample( sample_time, sql_id, precision, options) - sample_time. The sample time in seconds. - sql_id. The SQL statement's sql_id. - precision. Either low, normal, or high. I recommend using low or normal. - options. Enter 'none'. Here is an example: -- Make sure to set serveroutput on so you can see the summarized ending output. set serveroutput on exec op_sample_elapsed_v3.sample(60,'5hy19uf6q4unx','low','none'); 5. Report on the collected data. select elapsed_time_s||',' from op_elapsed_samples; Note: The collected sample data in the table op_elapsed_samples is deleted at the beginning of every collection, that is, execution of the sample procedure. And email the output to orapub.craig@ gmail.com and I'll reply with a nice statistical analysis! That's it! Enjoy!
