This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

HowTo: Use AWR snapshot data to provide a history of sequential read values

Scenario: You have changed your back-end storage hardware (SAN array or SATA disk storage) and you want to see a historical overview of sequential read times of your database.

It’s possible to query the Oracle AWR data (provided you have paid for the license), to provide a historical list of sequential read times acccording to the snapshots taken by AWR.
You are obviously limited to the amount of data retained by AWR and the frequency of the AWR snapshots.

set linesize 400 pagesize 400
SELECT event_start.snap_id,
       to_char(snap.begin_interval_time,'DD-MM-YY HH24:MI') as begin_time,
       to_char(snap.end_interval_time,'HH24:MI') as end_time,
                 (event_end.total_waits - nvl(event_start.total_waits, 0)),0, to_number(NULL),
       ((event_end.time_waited_micro -    nvl(event_start.time_waited_micro,0))/1000) / (event_end.total_waits - nvl(event_start.total_waits,0))
),0) avgwait,
       event_end.event_name event_name,
      (event_end.time_waited_micro - nvl(event_start.time_waited_micro,0)/1000000) total_ms,
FROM dba_hist_system_event event_start,
     dba_hist_system_event event_end,
     dba_hist_snapshot snap
WHERE event_end.snap_id = event_start.snap_id + 1
  AND event_end.event_name = 'db file sequential read'
  AND event_start.event_name = event_end.event_name
  AND event_start.snap_id = snap.snap_id
  AND event_start.dbid = snap.dbid
  AND event_start.instance_number = snap.instance_number
  AND snap.begin_interval_time > SYSDATE - 14            -- max 14 days history.
-- AND to_char(snap.begin_interval_time,'HH24') IN ('09','10','11','12','13','14','15','16','17')
-- AND to_char(snap.begin_interval_time,'MI') = '50'
ORDER BY event_start.snap_id;

NOTE: You can restrict the snapshot intervals used to provide “hourly” values by uncommenting the additional two lines.

What’s the Current Size of AWR in the Oracle Database?

If you have to size a new Oracle 10g database, or you just want to increase the AWR snapshot interval, you may want to know how much database space is being used by the current AWR setup.

This is where V$SYSAUX_OCCUPANTS comes in.

SQL> set linesize 300
SQL> col OCCUPANT_NAME format A7
           FROM v$sysaux_occupants where OCCUPANT_NAME ='SM/AWR';

OCCUPAN OCCUPANT_DESC                                         SPACE_USAGE_KBYTES
------- ----------------------------------------------------- ------------------
SM/AWR  Server Manageability - Automatic Workload Repository  107008

The above was taken from a system with a default AWR setup (retention of 7 days, snapping every hour).
This is approximately 14MB per day.

If you were thinking you were going to keep 60 days, you would expect about 900MB of data in SYSAUX.

When Should I Pay For Oracle Diagnostics/Tuning Pack in 10G/11G

If you are unsure about what action will result in you having to pay for a license for the Oracle Diagnostic Pack or Tuning Pack (or other options come to think of it), then you could use the query below to determine the check conditions:


This will output the name of the feature and the description of the check that is performed.
You could also see the check “code” if you include the USG_DET_LOGIC column in the SELECT.

In 10G it is immediately possible to see that the AWR is only classed as used if “At least one Workload Repository Report has been created by the user“.
In 11G, AWR is classed as used if “At least one AWR Baseline has been created by the user” OR “At least one AWR Baseline Template has been created by the user“.

The AWR is licenseable (

So does this mean you can select from those views? Well Oracle do ask you to in some support situations. So maybe it is reasonable to assume so.
The Oracle 11G Diagnostics Pack doc states otherwise: “These features may be accessible through Oracle Enterprise Manager , Oracle SQL Developer and APIs provided with Oracle Database software. The use of these and other features described in the product licensing documentation requires licensing of the Oracle Diagnostic Pack regardless of the access mechanism.“.

The Oracle 11G Tuning Pack *does not* say the same:
But, the tuning pack APIs use features included in the Diagnostic Pack.  Chicken and egg.

I guess it’s open to interpretation. Better stay safe and ensure that you don’t hit any of the checks.

Use of Oracle AWR / ASH leading to bad coding?

I had a brief email exchange with another Oracle guru the other day.
He suggested that the quality of Oracle coding in PL*SQL and Plain Jane ( SQL had gone down hill.

This could be attributed to two factors:
1, The level of coding experience has dropped.  Older more experienced coders have filled into the new architect roles and the void is being filled quickly by newer in-experienced coders.
2, The rigour with which debugging, testing and tuning is performed has become somewhat lax ( ) because there’s just no emphasis on the developers to tune their code when the DBA has such great tools to do it for them.

Is it possible that the use of the additionally licensed tools such as AWR (Automatic Workload Repository) and ASH (Active Session History) introduced in Oracle 10g, have provided an easy mechanism for DBAs to seek out better performance.
I don’t think these tools are just for DBAs, but the way they are marketed makes me feel they are pushed that way.