(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. !! Set the elapsed time sampler's license key correctly !! File : DataCollectionScript1f.txt Author : Craig Shallahamer, craig@orapub.com Original : 19-Aug-2011 Last : 22-Aug-2011 Purpose : To collect instance level data to enable RT analysis changes due change. To collect SQL elapsed times. For this specific script, the objective was to change the number of CBC latches and then observe the change on service time, response time, and SQL elapsed time. : OraPub's E Time Sampler must be installed. This is used to sample SQL elapsed time. ----------------------------------------- -------- SETUP ----------------------------------------- -- -- You only need to do this once. def monitoruser=mon2; def monitorpass=mon2; connect / as sysdba drop user &monitoruser cascade; @cu &monitoruser &monitorpass users temp default grant dba to &monitoruser; grant select on v_$session to &monitoruser; grant select on v_$sqlstats to &monitoruser; grant execute on dbms_lock to &monitoruser; connect &monitoruser/&monitorpass 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 op_exper_results; create table op_exper_results ( sample_no number, elapsed_s number, lio number, wait_time_s number, sys_cpu_s number ); drop table op_exper_results2; create table op_exper_results2 ( master_sample_no number,sql_sample_no number,sql_id varchar2(100),elapsed_s number ); -- Make sure OraPub's E Time Sampler is installed -- drop table op_elapsed_samples; create table op_elapsed_samples ( sample_no number, sql_id varchar2(99), elapsed_time_s number, sid number ); -- -- You may need copy/paste the source code in here. -- desc OP_SAMPLE_ELAPSED_V3 ----------------------------------------- -------- ACTUAL EXPERIMENTAL RUN ----------------------------------------- -- For every sample collection, do: -- Make your change: E.g., 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 &monitoruser/&monitorpass -- Check to ensure the load intensity is what you expect. (this is for Craig's load generator) select * from op_load_params; -- Ensure the SQL you are monitoring has the sql_id you think it does. -- Ensure the sql_id in the main loop is set correctly!!!!! <------------- ============= <<<<<<<<<<<<<<<<<<<< ******** def LookingFor=256untuned -- untuned (see opload: lio_timed.sql) : select sql_id,plan_hash_value,executions,substr(sql_text,1,60) the_sql from v$sqlstats where sql_text like '%&LookingFor%'; -- 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" named wait event. alter system set resource_manager_plan = ''; show parameter resource_manager_plan -- 1. Start the Load; E.g., 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 instance level RT information -- - This is to ensure load is what you intend it to be. -- Copy/Paste the below def sample_interval=600 def total_samples=30 def chill=4 commit; truncate table op_exper_results; truncate table op_exper_results2; exec dbms_lock.sleep(&chill); set tab off set serveroutput on begin declare sample number; time_t0 timestamp; time_t1 timestamp; loop_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; 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; op_sample_elapsed_v3.sample(&sample_interval,'a3gxuy8hqucpt','normal','none','key'); --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 get_interval_s(time_t1-time_t0) into loop_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 op_exper_results values (sample,loop_elapsed_s,lio,wait_time_s,sys_cpu_s); insert into op_exper_results2 select sample,sample_no,sql_id,elapsed_time_s from op_elapsed_samples; delete op_elapsed_samples; commit; -- So we can see progress more quickly. dbms_lock.sleep(&chill); end loop; commit; end; end; / ----------------------------------------- ------ Output for Analysis ----------------------------------------- show parameter %hash%latches col sql_id format a15 set tab off set pagesize 99 col sample_no noprint col master_sample_no noprint col sql_sample_no noprint select * from op_exper_results; select * from op_exper_results2; set pagesize 0 select master_sample_no,sql_sample_no, master_sample_no||','||sql_sample_no||','||elapsed_s||',' from op_exper_results2 order by master_sample_no,sql_sample_no; select sample_no, sample_no||','||elapsed_s||','||lio||','||wait_time_s||','||sys_cpu_s||',' from op_exper_results order by 1;