(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 cbc_results;
create table cbc_results ( 
  sample_no number, elapsed_s number, lio number, wait_time_s number, sys_cpu_s number, db_time_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
-- Check the key instance parameters:

connect / as sysdba
select  rpad(i.ksppinm, 35) || ' = ' || v.ksppstvl parameter
from    x$ksppi         i,
        x$ksppcv        v
where   v.indx = i.indx
and     v.inst_id = i.inst_id
and     i.ksppinm in ('_db_block_hash_buckets','_db_block_hash_latches','_db_blocks_per_hash_latch','db_block_buffers','db_cache_size')
order by i.ksppinm
/

-- connect as dba user (I use system)

connect system/manager

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

-- 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 a cbc latch issue before the data is collected.

-- Copy/Paste the below

def sample_interval=180
def total_samples=60
def chill=4

commit;
truncate table cbc_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 sum(value)/1000000 into db_time_t0 from v$sys_time_model where stat_name in ('DB 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 db_time_t1 from v$sys_time_model where stat_name in ('DB time');
  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 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 db_time_t1-db_time_t0 into db_time_s from dual;
  select sys_cpu_t1-sys_cpu_t0 into sys_cpu_s from dual;

  insert into cbc_results values (sample,elapsed_s,lio,wait_time_s,sys_cpu_s,db_time_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 cbc_results;


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