Note: There is a similar tool in the OSM toolkit called, sqlelget.sql. It's similar to this tool but simpler and very cool. It was demonstrated in the OraPub Online Institute seminar entitled, Using Skewed Performance Data To Your Advantage.
SQL Elapsed Time Sampler
For important SQL statements, you need more than just the average elapsed time. Why?
Because it is extremely unlikely the average elapsed time is what your users are experiencing.
To get true elapsed times, you must gather actual elapsed times... and that is what this free tool does.
However, gathering accurate SQL statement elapsed times without hammering your production system is difficult.
Four options exist: SQL tracing, instrumentation, OraPub's Elapsed Time Collector, and this new tool,
OraPub's SQL Elapsed Time Sampler. The cost and benefits of each is discussed in Craig's blog entry
True SQL Elapsed Times... gathering.
Once you have gathered some data, you will want to numerically and visually understand the data. You can either email OraPub, and we will perform the analysis for you for free. Or, you can do the same yourself by following our tutorial using the free R Statistics package; it installs in minutes on Windows, Mac OSX, and Linux.
Here is how to install the tool and collect the elapsed time data:
*** How To Install And Use ***
Here are the steps (it's much simpler than it looks):
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
where sql_text like '%&whatyoulookingfor%';
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
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.
6. Analyze the collected data.
Here are two options:
1. Check out our R Statistics package (it's free) tutorial.
It's short and to the point! You can easily numerically and visually analyze your data.
2. And email the output to orapub.general@ gmail.com and
we will reply with a nice statistical analysis!
That's it! Enjoy!