AWR Data Mining

Collection of useful information about turning AWR Data into useful information to help you tune and administrate your databases.

AWR and your Database Instance

Licensing

Make sure you are licensed! Usage of AWR Reports and AWR Related data are governed by the Diagnostics Pack license. It does not come with your Enterprise Edition license unless you paid for it or negotiated it into your agreement with Oracle. That being said

Settings

View Settings

SQL> select * From dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
5551212555 +00000 01:00:00.0    +00100 00:00:00.0    DEFAULT

Configure Settings

Note all intervals are specified in Minutes!
  • 7 days = 10080
  • 31 days = 44640
  • 90 days = 129600
execute dbms_workload_repository.modify_snapshot_settings(
    interval => 60,
    retention => 129600);

How much disk space is the AWR data taking in my database?

There is a Oracle supplied script called utlsyxsz.sql that shows SYSAUX related sizing including AWR. I have used it and I am not sure where they are getting their numbers from but will review the script in the future. In the meantime this will give you info about the current AWR storage:
WITH AWR AS (select sum(bytes)/1024/1024 as AWR_mb 
               From dba_segments 
              where segment_name like 'WRM%' or segment_name like 'WRH%'),
     TOT AS (SELECT SUM(BYTES)/1024/1024 as TOT_mb
               FROM DBA_SEGMENTS)
SELECT AWR.AWR_mb, TOT.TOT_mb, ROUND(AWR_MB / TOT_MB * 100,2) as Ratio FROM DUAL, AWR, TOT;
And estimate storage required per day:
SET SERVEROUTPUT ON
DECLARE
  AWR_STORAGE_IN_MB NUMBER;
  TOT_DB_SIZE_IN_MB NUMBER;
  RATIO_AWR_TO_DB   NUMBER;
  RETENTION_DAYS    NUMBER;
  STORED_DAYS       NUMBER;
BEGIN
  SELECT SUM(bytes)/1024/1024 as AWR_SIZE_mb 
    into AWR_STORAGE_IN_MB
    From dba_segments 
   where segment_name like 'WRM%' or segment_name like 'WRH%';

  SELECT SUM(BYTES)/1024/1024
    INTO TOT_DB_SIZE_IN_MB
    FROM DBA_SEGMENTS;
    
  SELECT extract( day from ( RETENTION ) )
    INTO RETENTION_DAYS
    FROM dba_hist_wr_control;
    
  select count(distinct trunc(end_interval_time)) 
    INTO STORED_DAYS
    FROM dba_hist_snapshot;
  
  RATIO_AWR_TO_DB := ROUND(AWR_STORAGE_IN_MB / TOT_DB_SIZE_IN_MB * 100,2);
   
   DBMS_OUTPUT.PUT_LINE('*** Current AWR Storage Info ***');
   DBMS_OUTPUT.PUT_LINE('AWR Space Used:'||AWR_STORAGE_IN_MB||'mb for '||STORED_DAYS||' days');
   DBMS_OUTPUT.PUT_LINE('Storage per day: '||ROUND(AWR_STORAGE_IN_MB/STORED_DAYS)||'mb');

   IF (STORED_DAYS < RETENTION_DAYS) THEN
      DBMS_OUTPUT.PUT_LINE('*** Projected Storage Info ***');
      DBMS_OUTPUT.PUT_LINE('Retention Days '||RETENTION_DAYS);
      DBMS_OUTPUT.PUT_LINE('Projected Total Storage Required for AWR Data:'||
          ROUND((AWR_STORAGE_IN_MB/STORED_DAYS)*RETENTION_DAYS,2)||'mb');
   END IF;
   
   DBMS_OUTPUT.PUT_LINE('*** Database Storage Info ***');
   DBMS_OUTPUT.PUT_LINE('TOT_STORAGE_IN_MB '||TOT_DB_SIZE_IN_MB||'mb');
   DBMS_OUTPUT.PUT_LINE('Percent of data in DB used by AWR data: '||RATIO_AWR_TO_DB||'%');
END;
/

Generating AWR Reports

Permissions

Of course DBAs can execute these procs and have permissions to generate reports. If you have a user that you trust to run AWR reports you can grant them SELECT_CATALOG_ROLE and
grant select_catalog_role to &user.;
grant execute on dbms_workload_repository to &user.;

AWR Reports

dbms_workload_repository.awr_report_text()
dbms_workload_repository.awr_report_html()

AWR Difference Report

A useful difference report between two AWR reports can be generated by running awrddrpt.sql or by using the API. If you run awrddrpt.sql then you will be prompted for all required information for the two snapshots you want to compare. Using the API:
select output 
  from table(dbms_workload_repository.awr_diff_report_html( 
    :dbid1,
    :inst_num,
    :bid, :eid,
    :dbid2,
    :inst_num2,
    :bid2, :eid2
    ));

Getting Started Querying AWR Data

Intro to the Data Model

ToDo

All History Related Views

SELECT * 
  FROM dba_views 
 WHERE view_name LIKE 'DBA_HIST%';

What are the most useful views?

Snapshot Related DBA_HIST_SNAPSHOT SQL Related DBA_HIST_SQLSTAT DBA_HIST_SQL_PLAN Instance Related DBA_HIST_SGASTAT DBA_HIST_IOSTAT_FUNCTION

DBA_HIST_SNAPSHOT - The Glue

Most Relations revolve around DBA_HIST_SNAPSHOT
FieldDescription
SNAP_IDUnique snapshot ID
DBIDDatabase ID for the Snapshot
INSTANCE_NUMBERUsually 1 - Can be >1 for RAC systems
STARTUP_TIMEStartup time when Snapshot was taken.
BEGIN_INTERVAL_TIMEBeginning period for snapshot
END_INTERVAL_TIMEActual time snapshot was taken
STARTUP_TIME important! Counters do not persist across instance restarts AWR Report generation procedures will throw error Any querying should take this into account If you see snaps older than your purge retention window hanging around don't be alarmed - they are probably associated with a Baseline. Baseline snaps are not purged after retention window. See DBA_HIST_BASELINE for more info on configured Baselines.

Snapshots From Previous 7 Days

SELECT * 
  FROM DBA_HIST_SNAPSHOT 
 WHERE BEGIN_INTERVAL_TIME >= (SYSDATE-7)
 ORDER BY BEGIN_INTERVAL_TIME;

AWR Queries - Advisor Related

SGA Advisory - Will doubling SGA size help/

According to the Oracle SGA Advisor that is! The following query will give the current DB Time and I/O info and projected DB Time and I/O info if the SGA is doubled.
-- SGA Advisory Summary for current and SGA X 2 sizing
WITH snaps as (
select snap_id, begin_interval_time from dba_hist_snapshot),
SGAAdviceCur as (select snap_id, sga_size, estd_db_time, estd_physical_reads from dba_hist_sga_target_advice where SGA_SIZE_FACTOR=1),
SGAAdviceNew as (select snap_id, sga_size as sga_size_new, 
                      estd_db_time as estd_db_time_new, 
                      estd_physical_reads as estd_physical_reads_new 
                 from dba_hist_sga_target_advice 
                where SGA_SIZE_FACTOR=2)
SELECT snaps.SNAP_ID,
       TO_CHAR(snaps.BEGIN_INTERVAL_TIME,'MM/DD/YYYY HH24:MI') as RecordDate, 
       SGAAdviceCur.SGA_SIZE, SGAAdviceNew.SGA_SIZE_NEW,
       SGAAdviceCur.ESTD_DB_TIME, SGAAdviceNew.ESTD_DB_TIME_NEW,
       ROUND(SGAAdviceNew.ESTD_DB_TIME_NEW/SGAAdviceCur.ESTD_DB_TIME*100,2) as NewTimePct,
       SGAAdviceCur.ESTD_PHYSICAL_READS, SGAAdviceNew.ESTD_PHYSICAL_READS_NEW,
       ROUND(SGAAdviceNew.ESTD_PHYSICAL_READS_NEW/SGAAdviceCur.ESTD_PHYSICAL_READS*100,2) as NewReadsPct
from snaps left outer join SGAAdviceCur on snaps.snap_id=SGAAdviceCur.snap_id
         left outer join SGAAdviceNew on snaps.snap_id = SGAAdviceNew.snap_id
 ORDER BY begin_interval_time;

AWR Queries - Getting Information about SQL

Quick overview of SQL Performance for 1 SQL_ID

This query will show you AWR activity captured for a particular SQL_ID. Output includes the capture interval, plan hash value, number of executions, and the elapsed per exec. It is useful for getting an idea of how a SQL statement is performing and locating volatile explain plans.
select TO_CHAR(begin_interval_time,'MM/DD/YYYY HH24:MI') as BEGIN_INTERVAL, 
       TO_CHAR(end_interval_time,'MM/DD/YYYY HH24:MI') as END_INTERVAL, 
       sql_id, 
       plan_hash_value, 
       executions_total, 
       ROUND(elapsed_time_total/1000000,2) as elapsed,
       ROUND((elapsed_time_total/1000000)/executions_total,2) as elapsed_per_exec
from dba_hist_sqlstat join dba_hist_snapshot on dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id 
where sql_id IN (':sql_id')
ORDER BY DBA_HIST_SQLSTAT.SNAP_ID DESC;

SQL By Elapsed Time from AWR Data

-- SQL By Elapsed Time from AWR Data
--
-- :dbid     - Database ID
-- :instance_number - Instance number (usually 1)
-- :begin_snap_id      - Beginning Snap Id
-- :end_snap_id      - Ending Snap Id
-- :max_statement_count - Max Number of statements to return

WITH
-- Gather SQL Statistics for range 
sqlstat as (select sql_id, 
                   max(module) module, 
                   sum(elapsed_time_delta) elap, 
                   sum(cpu_time_delta) cput, 
                   sum(executions_delta) exec 
              from dba_hist_sqlstat 
             where dbid = :dbid 
               and instance_number = :inst_num 
               and :bid  :top_pct_sql); 

AWR Warehouse Operations

Listing databases added to an AWR Warehouse with EMCLI

$ emcli login username=username
Enter password

Login successful

$ emcli awrwh_list_src_dbs

Databases uploading to the AWR Warehouse:
Target Name               Target Type      Owner   Version          Snapshot Upload Status  Snapshots Uploaded
db1.business.com          rac_database     DMANN   11.2.0.4.180116  Enabled                 123
db2.business.com          rac_database     DMANN   12.1.0.2.0       Enabled                 456

Adding a DB to AWR Warehouse with EMCLI

Ensure preferred credentials are set and owned by desired user.

TODO

Add using emcli verb

$ emcli login username=username
Enter password

Login successful
$ emcli awrwh_add_src_db -target_name=db1.business.com -target_type=rac_database
Target db1.business.com successfully added to the AWR warehouse.

Kick off upload job

TODO

Removing a DB from AWR Warehouse with EMCLI

remove using emcli verb

$ emcli login username=username
Enter password

Login successful

$ emcli awrwh_remove_src_db -target_name=db1.business.com -target_type=rac_database

AWR Warehouse Queries

Some queries to help navigate the AWR warehouse:

Databases/Instances with info in AWR Repository

SELECT DBID, 
       DB_NAME, 
       INSTANCE_NUMBER, 
       INSTANCE_NAME, 
       HOST_NAME 
  FROM DBA_HIST_DATABASE_INSTANCE 
ORDER BY DB_NAME, INSTANCE_NUMBER;

Generating AWR Reports - Helper Script

REM David Mann - http://ba6.us - @ba6dotus
REM AWR for Single Instance

COLUMN DBID FORMAT 9999999999
COLUMN INS FORMAT 9999
COLUMN SNAP_ID FORMAT 999999
COLUMN BEGIN_INTERVAL_TIME FORMAT A16
COLUMN END_INTERVAL_TIME FORMAT A16
COLUMN MINUTES FORMAT 999
SELECT DBID,
       INSTANCE_NUMBER AS INS,
       SNAP_ID,
       EXTRACT(day from 24*60*(END_INTERVAL_TIME-BEGIN_INTERVAL_TIME)) AS MINUTES,
       TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') AS END_INTERVAL_TIME
  FROM DBA_HIST_SNAPSHOT 
 ORDER BY BEGIN_INTERVAL_TIME;

undefine DBID
undefine INSID
undefine STARTSNAPID
undefine ENDSNAPID


ACCEPT STARTSNAPID PROMPT "Enter begin Snap ID : " 
ACCEPT ENDSNAPID PROMPT "Enter begin Snap ID : " 

COL dat1 NEW_VALUE DBID
SELECT DBID as dat1 FROM DBA_HIST_SNAPSHOT WHERE SNAP_ID=&STARTSNAPID;

COL dat2 NEW_VALUE INSID
SELECT INSTANCE_NUMBER as dat2 FROM DBA_HIST_SNAPSHOT WHERE SNAP_ID=&STARTSNAPID;


--SELECT TO_CHAR(DBID)||'|'||TO_CHAR(INSTANCE_NUMBER)||'|'||TO_CHAR(SNAP_ID)||'|'||TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') AS "DBID|INST_NUM|SNAP_ID|END_TIME"
 --FROM dba_hist_snapshot order by BEGIN_INTERVAL_TIME;

column report_name new_value report_name;
select 'awr_'||(select NAME from v$database where DBID = &&DBID)||'_'||&&INSID||'_'||
(select to_char(END_INTERVAL_TIME,'yyyymmdd.hh24mi') from dba_hist_snapshot where SNAP_ID=&&STARTSNAPID)||'_'||
(select to_char(END_INTERVAL_TIME,'yyyymmdd.hh24mi') from dba_hist_snapshot where SNAP_ID=&&ENDSNAPID)
||'.html' report_name 
from dual;

SET FEEDBACK OFF 
SET HEADING OFF 
SET LINESIZE 32000 
SET LONG 32000 
SET LONGCHUNKSIZE 32000 
SET PAGESIZE 0 
SET SPACE 0 
SET TERMOUT OFF 
SET TRIMSPOOL ON
SET VERIFY OFF
SET SERVEROUTPUT ON
exec DBMS_OUTPUT.ENABLE(5000000);


spool &&report_name
select output from table(sys.dbms_workload_repository.awr_report_html(&DBID,&INSID,&STARTSNAPID,&ENDSNAPID));
spool off
set termout on