exec dbms_workload_repository.create_snapshot();
Run tests here
exec dbms_workload_repository.create_snapshot();
Now wait exactly the same time as the tests took to run
exec dbms_workload_repository.create_snapshot();
As we have 3 snapshots here, we can use awrddrpt.sql to make a comarison of the state during the tests and after the tests.
The awr reports can be found in
ls -al $ORACLE_HOME/rdbms/admin/awr*
Calling awrddrpi.sql allows the variables to be setup in advance so that the report can run without asking questions. Handy to produce reports programatically.
define inst_num = 1; define num_days = 3; define inst_name = 'Instance'; define db_name = 'Database'; define dbid = 42375234; define begin_snap = 1211; define end_snap = 1212; define report_type = 'html'; define report_name = /tmp/awr_report_09_10.html @?/rdbms/admin/awrrpti
echo "exec dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => 267840);"|sqlplus -s / as sysdba
A fine example of how to write scripts from SQL*Plus that interact with the shell underneath Found here at andreynikolaev.wordpress.com
/*
This file is part of demos for "Mutex Internals" seminar v.04.04.2011
Andrey S. Nikolaev ([email protected])
https://andreynikolaev.wordpress.com
"Cursor: pin S" contention testcase.
The script spawn several sessions to induce "Cursor: pin S",
creates AWR snapshots and generates AWR report
Beware: The script will restart the test database.
usage: sqlplus /nolog @cursor_pin_s_contention.sql <parallelism>
* /
connect / as sysdba
startup force
set echo off
set define %
set verify off
set head off
set linesize 300
/* spawn parallel processes to create load on mutex */
spool one_thread.tmp
select 'set verify off' from dual;
select 'alter session set session_cached_cursors = 50;' from dual;
select 'begin for i in 1..1000000 loop' from dual;
select ' execute immediate //select 1 from dual where 1=2//;' from dual;
select 'end loop; end;'||chr(10)||'/' from dual;
spool off
/* sleep while AWR initialized */
host sleep 5
exec dbms_workload_repository.create_snapshot();
spool many_threads.tmp
select 'host sqlplus "/ as sysdba" @one_thread.tmp &'
from dba_objects where rownum <= %1;
spool off
@many_threads.tmp
host sleep 50
set echo on
exec dbms_workload_repository.create_snapshot();
set markup html on
set head on
spool mutex_sleep_history.htm
select to_char(SLEEP_TIMESTAMP,'hh:mi:ss.ff') SLEEP_TIMESTAMP,MUTEX_ADDR,MUTEX_IDENTIFIER,MUTEX_TYPE,
GETS,SLEEPS,REQUESTING_SESSION,BLOCKING_SESSION,
LOCATION,MUTEX_VALUE from x$mutex_sleep_history order by sleep_timestamp;
spool off
set define &
set markup html off
set echo off heading on underline on;
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading "Instance" new_value inst_name format a12;
column db_name heading "DB Name" new_value db_name format a12;
column dbid heading "DB Id" new_value dbid format 9999999999 just c;
select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d,
v$instance i;
define num_days=1;
define report_type='html';
col end_snap new_value end_snap;
col begin_snap new_value begin_snap;
select max(snap_id) end_snap from dba_hist_snapshot where dbid=&dbid;
select max(snap_id) begin_snap from dba_hist_snapshot where dbid=&dbid and snap_id < &end_snap;
define report_name='awr_report.htm';
@?/rdbms/admin/awrrpti
exit
Uses AWR reports to test latency issues across data centres.
drop table latency_test;
create table latency_test
(
a number
)
logging
nocompress
nocache
monitoring;
set serveroutput on
declare
cursor c_snaps is
select snap_id
from (select snap_id, end_interval_time from dba_hist_snapshot order by end_interval_time desc)
;
i number;
l_elapsed number;
l_start_snap dba_hist_snapshot.snap_id%type;
l_middle_snap dba_hist_snapshot.snap_id%type;
l_end_snap dba_hist_snapshot.snap_id%type;
begin
dbms_output.enable(null);
-- create an initial snapshot before running the test
dbms_workload_repository.create_snapshot();
for i in 1..100000 loop
insert into latency_test values(12345);
commit write immediate wait;
end loop;
-- create another snapshot after running the test
dbms_workload_repository.create_snapshot();
-- work out the time it took to run the test
open c_snaps;
fetch c_snaps
into l_middle_snap
;
fetch c_snaps
into l_start_snap
;
close c_snaps;
select round((sysdate + (t2 - t1)*1000 - sysdate) * 86.4)
into l_elapsed
from (select begin_interval_time t1
, end_interval_time t2
from dba_hist_snapshot
where snap_id = l_middle_snap
);
-- that was the busy time. now wait the same amount of time again and create another snapshot
dbms_lock.sleep (l_elapsed);
dbms_workload_repository.create_snapshot();
open c_snaps;
fetch c_snaps
into l_end_snap
;
close c_snaps;
dbms_output.put_line('Snaps created: '||l_start_snap||', '||l_middle_snap||', '||l_end_snap);
end;
/
rollback;
drop table latency_test purge;