-- Data collection script def total_samples=31 def test_time=60 def chill=4 -- Disable experiment killing stuff... set tab off -- 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 set serveroutput on begin declare bogus number; loop_cntr number; sample number; time_t0 timestamp; time_t1 timestamp; elapsed_s number; uc_t0 number; uc_t1 number; uc_tot 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 op_results; commit; dbms_lock.sleep(&chill); for sample in 1..&total_samples loop execute immediate 'truncate table op_waits'; insert into op_waits select * from v$system_event; select value into uc_t0 from v$sysstat where name='user commits'; 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 current_timestamp into time_t0 from dual; dbms_lock.sleep(&test_time); select current_timestamp into time_t1 from dual; select value into uc_t1 from v$sysstat where name='user commits'; 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 get_interval_s(time_t1-time_t0) into elapsed_s from dual; select uc_t1-uc_t0 into uc_tot 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 op_results values (sample,elapsed_s,uc_tot,wait_time_s,sys_cpu_s); dbms_output.put_line('sample='||sample||' elapsed_s='||elapsed_s||' uc_tot='||uc_tot); dbms_lock.sleep(&chill); end loop; commit; end; end; /