Automatic Workload Repository

A very short howto for the ‘Automatic Workload Repository’, the successor of perfstat. …

Interval

Some queries; …

Listing below shows one snapshot is taken every 20 minutes, the snapshot is stored for a week (7 days). …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /

SNAP_INTERVAL                    RETENTION
-------------------------------- ------------------------------
+00000 00:20:00.0                +00007 00:00:00.0

Alter the interval to once every hour and store it for a month (31 days); …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> begin
  2     dbms_workload_repository.modify_snapshot_settings
  3        ( interval   => 60
  4        , retention  => 31 * 24 * 60);
  5  end;
  6  /

PL/SQL procedure successfully completed.

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /

SNAP_INTERVAL                    RETENTION
-------------------------------- ------------------------------
+00000 01:00:00.0                +00031 00:00:00.0

History

Five latest snapshots …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select *
  2  from   (
  3         select snap_id, startup_time, begin_interval_time, end_interval_time
  4         from   dba_hist_snapshot
Read more →

dba$log_blocker

Code below will save the information of the ‘blocking session’ in an ORA-00060 situation. …

Since both sessions are equally guilty of the deadlock Oracle picks one session at random to get the ORA-00060, the update will fail for this session and a trace file is written to the user_dump_dest. In this trace file you can find the current SQL statement of the session that got the ORA-00060 and the os pids of the sessions on the server, however there is not as much information as to the session that ’caused the deadlock’ as you might want to see. …

The ‘servererror’ triggers (around since 8i), enable you to write a trigger and storing some usefull information for later analysis. …

Please note the triggers firing on the ‘servererror’ start a separate transaction and commits it after firing the trigger. No need to start an autonomous transaction, since it already is so to say, see Database Application Developer’s Guide – Fundamentals – 9 Coding Triggers – Table 9-3 System Manager Events. …

tables

Create some tables to hold the information I’m interested in. …

[REMI@DB01.REMIDIAN.COM]
SQL> create table log$event
  2     ( event#     number         constraint pk_log$event primary key
  3     , event      varchar2(512)
  4     , eventtime  date
  5     )
  6  /

Table created.

[REMI@DB01.REMIDIAN.COM]
SQL> create table log$session
  2  as
  3 
Read more →