==== AWR views ====
* V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
* V$METRIC - Displays metric information.
* V$METRICNAME - Displays the metrics associated with each metric group.
* V$METRIC_HISTORY - Displays historical metrics.
* V$METRICGROUP - Displays all metrics groups.
* DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
* DBA_HIST_BASELINE - Displays baseline information.
* DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
* DBA_HIST_SNAPSHOT - Displays snapshot information.
* DBA_HIST_SQL_PLAN - Displays SQL execution plans.
* DBA_HIST_WR_CONTROL - Displays AWR settings.
==== Run an AWR report interactively ====
=== Create a starting snapshot ===
exec dbms_workload_repository.create_snapshot();
** Run tests here **
=== Create an intermediate snapshot ===
exec dbms_workload_repository.create_snapshot();
** Now wait exactly the same time as the tests took to run **
=== Create an ending snapshot ===
exec dbms_workload_repository.create_snapshot();
=== Run an AWR report ===
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
==== Change awr frequency and retention ====
echo "exec dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => 267840);"|sqlplus -s / as sysdba
==== Latch, mutex and beyond ====
A fine example of how to write scripts from SQL*Plus that interact with the shell underneath
Found [[https://andreynikolaev.wordpress.com/demonstration-scripts/cursor_pin_s_contention-sql/|here at andreynikolaev.wordpress.com]]
/*
This file is part of demos for "Mutex Internals" seminar v.04.04.2011
Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru)
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
* /
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
==== Script to detect latency issues ====
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;