(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		: LIO_Rt_Analysis1b.txt
Author		: Craig Shallahamer, craig@orapub.com
Original	: 23-June-2011
Last		: 17-Jul-2011

Purpose         : The purpose of this script, actually many small scripts dumped into this
single file, is to enable experimental analysis regarding the change (if any) of
cpu time and non-idle wait time per buffer get (LIO) when the number of CBC latches is increased during
heavy broad CBC latch contention.

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

-- You only need to do this once.

-- connect as dba (monitoring) user

drop table results;
create table results ( 
  sample_no number, elapsed_s number, lio_tot number, wait_time_s number, sys_cpu_s number
);

-----------------------------------------
-------- ACTUAL EXPERIMENTAL RUN
-----------------------------------------

-- For every sample collection, do:
-- Set the number of cbc latches; _db_block_hash_latches
-- Do a clean shutdown normal and startup

-- connect as dba user (I use system)

connect system/manager


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;
/


-- disable AWR collection or your numbers can be strangely skewed.
execute dbms_workload_repository.modify_snapshot_settings(interval => 0);
-- disable resource manager to ensure Oracle CPU activity is not throttled down. If this occurs
-- you will see a quantum related wait event.
alter system set resource_manager_plan = '';
show parameter resource_manager_plan
-- make the result_cache is set to MANUAL
show parameter result_cache_mode
show parameter threaded_execution

-- 1. Start the CBC load and set it stabilize for 3 minutes.
-- 2. Run the OraPub OSM script rtpctx.sql (or something like it) with at least a 30 sec interval
--    - This is an OraPub OSM script to get cpu and wait time
--    - This is to ensure there truly is no stress other than serially processing buffers (LIO).

-- Copy/Paste the below

def sample_interval=180
def total_samples=90
def chill=5

commit;
truncate table results;

exec dbms_lock.sleep(&chill);

set tab off
set serveroutput on
begin
declare
  sample number;
  time_t0 timestamp;
  time_t1 timestamp;
  elapsed_s number;
  lio_t0 number;
  lio_t1 number;
  lio    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;
  db_time_t0   number;
  db_time_t1   number;
  db_time_s    number;
begin
for sample in 1..&total_samples
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' and event not like 'SQL*Net%';
  select sum(value)/1000000 into sys_cpu_t0 from v$sys_time_model where stat_name in ('DB CPU','background cpu time');
  select current_timestamp into time_t0 from dual;

  dbms_lock.sleep(&sample_interval);

  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' and event not like 'SQL*Net%';
  select sum(value)/1000000 into sys_cpu_t1 from v$sys_time_model where stat_name in ('DB CPU','background cpu time');
  select current_timestamp into time_t1 from dual;

  select op_get_interval_s(time_t1-time_t0) into elapsed_s 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 results values (sample,elapsed_s,lio,wait_time_s,sys_cpu_s);
  commit; -- So we can see progress more quickly.

  dbms_lock.sleep(&chill);
end loop;
commit;
end;
end;
/


-----------------------------------------
------ Output for Analysis
-----------------------------------------

show parameter %hash%latches

select * from results;


set tab off
set pagesize 0
col sample_no noprint
select sample_no, sample_no||','||elapsed_s||','||lio_tot||','||wait_time_s||','||sys_cpu_s from results order by 1;

select	sample_no, 
	lio_tot/(1000*elapsed_s) l_lio_ms,
	sys_cpu_s*1000/lio_tot st_ms_lio,
	wait_time_s*1000/lio_tot qt_ms_lio,
        (sys_cpu_s+wait_time_s)*1000/lio_tot rt_ms_lio
from	results
order by 1;

select sys_cpu_s*1000/lio_tot st_ms_lio from results;