(c)2011, OraPub, Inc.

Warranty: There is none. Use at your own risk.
Use at your own risk. OraPub is not responsible for the research related script.

File		: CR_AnalysisScript1a.txt
Author		: Craig Shallahamer, craig@orapub.com
Original	: 07-Mar-2011
Last		: 07-Mar-2011

Purpose         : The purpose of this script, actually many small scripts dumped into this
single file, is to enable experimental analysis regarding the impact of
consistent read activity. 

Usage           : To use this script you need two sessions. In session one, run the
SETUP and do the BASELINE test. In session two, run the CREATE CR BUFFERS
but do NOT commit after the update. In session one, run the BASELINE
test again...and you should see an increase in just about every statistic
collected! Enjoy! Craig.

----------------------------------------------
SETUP
----------------------------------------------

create or replace function 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;
/

drop table rc_results;
create table rc_results ( sample_no number, elapsed_s number, lio number, cg number, wait_time_s number, sys_cpu_s number );

drop sequence s1;
create sequence s1; 

drop table rc_test;
create table rc_test as 
  select sysdate update_stamp, 
         s1.nextval update_seq, 
         t1.* 
  from all_objects t1;
insert into rc_test select * from rc_test;
insert into rc_test select * from rc_test;
commit;
insert into rc_test select * from rc_test;
insert into rc_test select * from rc_test;
commit;
insert into rc_test select * from rc_test;
insert into rc_test select * from rc_test;
commit;
insert into rc_test select * from rc_test;
insert into rc_test select * from rc_test;
commit;
-- On my test system, there were 18,277,632 rows in the table.
select count(*) from rc_test;

----------------------------------------------
BASELINE TEST
----------------------------------------------

--
-- Do a clean shutdown normal and startup
--
-- Connect as the object creation user...not sys.

set tab off
set timing on
-- warm up the cache (buffer or PGA)
select sum(update_seq) from rc_test;
select sum(update_seq) from rc_test;
select sum(update_seq) from rc_test;
select sum(update_seq) from rc_test;
select sum(update_seq) from rc_test;

set serveroutput on
begin
declare
  bogus number;
  sample number;
  time_t0 timestamp;
  time_t1 timestamp;
  elapsed_s number;
  lio_t0 number;
  lio_t1 number;
  lio    number;
  cg_t0  number;
  cg_t1  number;
  cg     number;
  wait_time_t0 number;
  wait_time_t1 number;
  wait_time_s  number;
  sys_cpu_t0   number;
  sys_cpu_t1   number;
  sys_cpu_s    number;
begin
delete from rc_results;
commit;
for sample in 1..60
loop
  select value into lio_t0 from v$sysstat where name='session logical reads';
  select sum(time_waited)/100 into wait_time_t0 from v$system_event where wait_class != 'Idle';
  select sum(value)/1000000 into sys_cpu_t0 from v$sys_time_model where stat_name in ('DB CPU','background cpu time');
  select value into cg_t0 from v$sysstat where name='consistent gets';
  select current_timestamp into time_t0 from dual;

  select sum(update_seq) into bogus from rc_test;

  select current_timestamp into time_t1 from dual;
  select value into lio_t1 from v$sysstat where name='session logical reads';
  select sum(time_waited)/100 into wait_time_t1 from v$system_event where wait_class != 'Idle';
  select sum(value)/1000000 into sys_cpu_t1 from v$sys_time_model where stat_name in ('DB CPU','background cpu time');
  select value into cg_t1 from v$sysstat where name='consistent gets';

  select get_interval_s(time_t1-time_t0) into elapsed_s from dual;
  select cg_t1-cg_t0 into cg from dual;
  select lio_t1-lio_t0 into lio from dual;
  select wait_time_t1-wait_time_t0 into wait_time_s from dual;
  select sys_cpu_t1-sys_cpu_t0 into sys_cpu_s from dual;

  insert into rc_results values (sample,elapsed_s,lio,cg,wait_time_s,sys_cpu_s);
  dbms_output.put_line('sample='||sample||'  elapsed_s='||elapsed_s);
end loop;
commit;
end;
end;
/

select * from rc_results;
--select elapsed_s||',' from rc_results;
--select cg||',' from rc_results;
--select lio||',' from rc_results;
--select wait_time_s||',' from rc_results;
--select sys_cpu_s||',' from rc_results;
select sample_no||','||elapsed_s||','||lio||','||cg||','||wait_time_s||','||sys_cpu_s||',' from rc_results;


----------------------------------------------
CREATE CR BUFFERS
----------------------------------------------

Must be run in ANOTHER SESSION so the transaction is different!
Let the update complete (but don't commit) before the BASELINE test.

-- do not commit or exit SQL*Plus until after the baseline test completes;
--
declare
  cntr number;
begin
for cntr in 1..2
loop
  update rc_test
  set    update_stamp=sysdate
  where  rownum<1000000;
end loop;
end;
/
--commit;