(c)2011 OraPub, Inc.
Author  : Craig Shallahamer, craig@orapub.com
Date    : 29-July-2011
Warranty: There is none. Use at your own risk.

This script is used, in part, to gather SQL statement elapsed time based on three data
collection sources/strategies: SQL tracing, instrumentation, and OraPub's Elapsed Time Sampler.
For more information, see the Sampler's web page and Craig's blog about "True SQL Elapsed Times... gathering"

-----------------------------------------------------------------------
APPLICATION USER STUFF...
-----------------------------------------------------------------------

** 1. Review, set for your environment, copy/paste into sqlplus

connect / as sysdba

def appluser=appl1
def applpswd=appl1

drop user &appluser cascade;
create user &appluser identified by &applpswd;
grant create session, connect, resource to &appluser;

grant select on v_$session to &appluser;
grant select on v_$sqlstats to &appluser;
grant execute on dbms_lock to &appluser;

connect &appluser/&applpswd

create or replace function op_get_interval_s(i_intrvl interval day to second) return number is
begin
	return extract(day from i_intrvl) * 86400 + extract(hour from i_intrvl) * 3600 + extract(minute from i_intrvl) * 60 + extract(second from i_intrvl);
	exception
		when others then begin return null; end;
end;
/

-- Create some sample data...
--
drop table op_sql_test;
create table op_sql_test as select * from all_objects where rownum<50001;
select count(*) from op_sql_test;
declare
	i number;
begin
	for i in 1..6
	loop
		insert into op_sql_test select * from op_sql_test;
		commit;
	end loop;
end;
/

-- Create and run the sql statement that we will be timing using various methods.
--
def the_sql_stmt="/* OraPub 1 */ sum(object_id) from op_sql_test where status||'' != 'abc' union select sum(object_id) from op_sql_test where status||'' != 'abc'"
select &the_sql_stmt;
select	sql_id,plan_hash_value,executions,substr(sql_text,1,60) the_sql
from	v$sqlstats
where   sql_text like '%OraPub%';


** 2. Now find the SQL_ID for the above sql statement.

This is what it looks like on my system.

SQL_ID	      PLAN_HASH_VALUE EXECUTIONS THE_SQL
------------- --------------- ---------- ------------------------------------------------------------
ckm9htkurfsfz	    161874334	       4 select sql_id,plan_hash_value,executions,substr(sql_text,1,6
5hy19uf6q4unx	   2051451234	     268 select /* OraPub 1 */ sum(object_id) from op_sql_test where

** 3. Do this once for each sample collection series

connect &appluser/&applpswd

drop table op_elapsed_instr_time;
create table op_elapsed_instr_time(elapsed_sec number);

** 4. Copy/paste this into sqlplus when the MONITORING user is monitoring.

-- For a 90  second sample period, copy/paste about 20 times
-- For a 300 second sample period, copy/paste about 40 times 
-- For a 600 second sample period, copy/paste about 80 times 

-- START ----------- (copy and paste the below...MULTIPLE TIMES)

variable time_mark varchar2(1000);
begin
  select to_char(current_timestamp,'DD-MON-YYYY HH.MI.SS.FF') into :time_mark from dual;
end;
/
select &the_sql_stmt;
declare
  tdelta number;
  t0     timestamp;
  t      number;
begin
  t0 := to_timestamp(:time_mark,'DD-MON-YYYY HH.MI.SS.FF');
  t := op_get_interval_s( current_timestamp-t0 );
  insert into op_elapsed_instr_time values (t);
  dbms_output.put_line('elapsed time (sec) was '||t);
end;
/
exec dbms_lock.sleep(exp(1.8*dbms_random.normal));


-- END ------------------------

-----------------------------------------------------------------------
AS THE MONITORING USER
-----------------------------------------------------------------------

** 1. Choose a monitor user.

def monitoruser=mon1
def monitorpswd=mon1

The user will need typical need: (pehaps not DBA, but it will do the job)

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;

** 1. Connect as the monitoring user

connect &monitoruser/&monitorpswd


** 1. Create monitoring user ddl

- Edit, review, copy/paste, and run the text in MonitorDDLXXX.txt
- Make sure to run the wrapped code as this is the main collection package, OP_SAMPLE_ELAPSED_V3
- Do a describe on OP_SAMPLE_ELAPSED_V3 and it should look like this:

SQL> desc OP_SAMPLE_ELAPSED_V3
PROCEDURE SAMPLE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SAMPLE_SECS_IN 		NUMBER			IN
 SQL_ID_IN			VARCHAR2		IN
 PRECISION_IN			VARCHAR2		IN
 OPTIONS_IN			VARCHAR2		IN
 KEY_IN 			VARCHAR2		IN

** 2. Get the shell script that parses and summarizes elapsed times from a trace file.

The file is for sure in the analysis pack (if you are reading this from the blog entry) and
also is linked directly from the blog.

** 3. Monitor: Prepare

connect &monitoruser/&monitorpswd

-- Determine where the trace file will be written
--
show parameter user_dump_dest

-- Verify the sql statement's sql_id
select	sql_id,plan_hash_value,executions,substr(sql_text,1,60) the_sql
from	v$sqlstats
where   sql_text like '%OraPub%';

** 4. Monitor: Execute

-- If you want a load placed on the system, make sure that is currently running.
-- Paste the sqlid directly into both the sql_trace call and the procedure BELOW.
-- Make sure you have already running the application user's sql (Appl User's Step 4 above)
-- Now copy/paste the below into sqlplus

set serveroutput on
alter system set events 'sql_trace [sql:5hy19uf6q4unx]';
exec op_sample_elapsed_v3.sample(600,'5hy19uf6q4unx','low','none','beta1');
alter system set events 'sql_trace off';

Here's an example of what the output will look like.

OraPub Elapsed Time Sampler, v3c:26-July-2011
sql_id           : 5hy19uf6q4unx
sample time (s)  : 600
precision        : normal
options          : none
actual executions: 80
sample details .....
.  count         : 79
.  minimum (s)   : 1.6119
.  median  (s)   : 2.4167
.  average (s)   : 2.64538
.  max     (s)   : 6.22145

At this point you should have both a trace file, data loaded into op_elapsed_samples,
and from the application user data loaded into op_elapsed_instr_time.

** 5. Process trace file data

- Go to the trace file directory and locate the trace file
- Get the shell script to paste the trace file
- In the shell script, set both the trace file name and the sqlid.
- Execute the shell script

** 6. Process (report) the sampled elapsed time data (from the monitoring user)

-- Note: Sometimes the first sample is obviously bogus. If so, don't include it.
--       This usually only occurs on the very first collection period.

connect &monitoruser/&monitorpswd

select sample_no, elapsed_time_s, sid from op_elapsed_samples;
select sample_no||','||elapsed_time_s from op_elapsed_samples;
select elapsed_time_s||',' from op_elapsed_samples;
select elapsed_time_s from op_elapsed_samples;

** 7. Process (report) the instrumented elapsed time data (from the application user)

connect &appluser/&applpswd

select elapsed_sec||',' from op_elapsed_instr_time;

Note: Some some reason (didn't following it through) the elapsed times for the application user were 43200 seconds too high... 
      So I used this SQL to get the "low" precision instrumented data.
select (elapsed_sec-43200)||',' from op_elapsed_instr_time;

** 8. Analyze the data.

My objective is to create a low overhead tool for gathering elapsed times for a specific sqlid.
If the procedure results are similar to the trace and instrumented results, then I will have
succeeded. The analysis was done in Mathematica and is detailed in the blog.