Collection of useful information about turning AWR Data into useful information to help you tune and administrate your databases.
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!
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;
/
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
));
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;
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;
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 < snap_id
and snap_id <= :eid
group by sql_id),
-- Calculate DB Time for range
dbtimecalc as ( SELECT ((select value From DBA_HIST_SYS_TIME_MODEL where stat_name='DB time' and snap_id = :eid) -
(select value From DBA_HIST_SYS_TIME_MODEL where stat_name='DB time' and snap_id = :bid)) dbtime
FROM DUAL
)
select *
from (select nvl((sqlstat.elap/1000000), to_number(null)) ElapsedTimeSecs,
nvl((sqlstat.cput/1000000), to_number(null)) CPUTimeSecs,
sqlstat.exec TotalExecutions,
decode(sqlstat.exec, 0, to_number(null), (sqlstat.elap / sqlstat.exec / 1000000)),
(100 * (sqlstat.elap / (SELECT dbtime FROM dbtimecalc))) norm_val,
sqlstat.sql_id,
to_clob(decode(sqlstat.module, null, null , 'Module: ' || sqlstat.module)),
nvl(sqltext.sql_text, to_clob('** SQL Text Not Available **'))
from sqlstat,
dba_hist_sqltext sqltext
where sqltext.sql_id(+) = sqlstat.sql_id
and sqltext.dbid(+) = :dbid
order by nvl(sqlstat.elap, -1) desc, sqlstat.sql_id)
where rownum <= :tsql_max
-- and (rownum <= :tsql_min or norm_val > :top_pct_sql);
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
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;
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