dbawiki

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

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;