-- Use at your own risk, OraPub is not responsible
-- Author: Craig Shallahamer, orapub.craig@gmail.com
-- 20-Mar-2012

-- This code is used to aid in determining when Oracle's
-- instance statistics are updated.

------------------------
-- Application User Code
------------------------

connect mg/mg

-- Setup
--
-- Create table and load ---
drop table op_interim_data;
create table op_interim_data as select * from all_objects;
insert into op_interim_data select * from op_interim_data;
insert into op_interim_data select * from op_interim_data;
commit write batch nowait;
insert into op_interim_data select * from op_interim_data;
commit;

-- Get my SID so I can easily monitor what this user is doing
--
select sid from v$mystat where rownum=1;

--
-- The experimental SQL ---
--
!date +%s
select to_char(sysdate,'SSSSS') from dual;
set timing on
!date +%s
exec dbms_lock.sleep(10);
!date +%s
select sum(a.object_id+b.object_id+c.object_id)
from   op_interim_data a,
       op_interim_data b,
       op_interim_data c
where  a.object_id = b.object_id
  and  b.object_id = c.object_id
/
!date +%s
/
!date +%s
exec dbms_lock.sleep(5);
!date +%s
select sum(a.object_id+b.object_id+c.object_id)
from   op_interim_data a,
       op_interim_data b,
       op_interim_data c
where  a.object_id = b.object_id
  and  b.object_id = c.object_id
/
!date +%s
/
!date +%s
exec dbms_lock.sleep(10);
!date +%s
select * from dual;
!date +%s
exec dbms_lock.sleep(5);
!date +%s
select * from dual;
!date +%s
exec dbms_lock.sleep(10);
!date +%s
select * from dual;
!date +%s


------------------------
-- DBA Collection Code
------------------------

connect system/manager
set tab off
def sidToMonitor=4544
drop table op_interim_sesstat;
create table op_interim_sesstat as
  select sysdate time_stamp, sid, statistic#, value
  from v$sesstat
  where sid=&sidToMonitor;
declare
  i number;
begin
for i in 1..100
loop
  insert into op_interim_sesstat
    select sysdate, &sidToMonitor, statistic#, value
    from v$sesstat where sid=&sidToMonitor;
  sys.dbms_lock.sleep(1.0);
end loop;
end;
/
commit;

------------------------
-- DBA Reporting Code
------------------------
11: session logical reads
14: cpu used by this session
44: physical read total IO requests
72: physical reads
549: parse count (total)
554: execute count

set tab off

select t1.statistic#,to_number(to_char(t1.time_stamp,'SSSSS'))-51300 stamp, t1.value
from   op_interim_sesstat t1
where  t1.statistic# in (11,14,44,72,549,554)
order by 1,2;

select t1.statistic#,to_number(to_char(t1.time_stamp,'SSSSS'))-51300 stamp, t1.value
from   op_interim_sesstat t0,
       op_interim_sesstat t1
where  t0.statistic# = t1.statistic#
  and  to_number(to_char(t0.time_stamp,'SSSSS')) = to_number(to_char(t1.time_stamp,'SSSSS'))-1
  and  t0.value != t1.value
  and  t1.statistic# in (11,72)
order by 1,2;

-- This code made it easy to copy/paste into the Mathematica notepad.
col np noprint
select t1.time_stamp np,to_number(to_char(t1.time_stamp,'SSSSS'))-51300||','
from   op_interim_sesstat t0,
       op_interim_sesstat t1
where  t0.statistic# = t1.statistic#
  and  to_number(to_char(t0.time_stamp,'SSSSS')) = to_number(to_char(t1.time_stamp,'SSSSS'))-1
  and  t0.value != t1.value
  and  t1.statistic# = 554
order by 1;