How to generate execution plan created from AWR snapshot?

Using $ORACLE_HOME/rdbms/admin/awrsqrpt.sql one can generate execution plan from AWR by passing start snap id, end snap id and SQL ID.

It will ask for most of the same inputs like awrrpt.sql, type of report (html/text), start snap id, end snap id, SQL id and report name and create a output in the file specified.

Example:
SQL> @?/rdbms/admin/awrsqrpt.sql

Script to automate generation AWR for a day

— The following anonymous PL/SQL code can be used to generate a script which can be run to generate AWR report for one day. Currently the script runs to create report for yesterday’s report. It creates a report between each snap.
set serveroutput on
set feedback off
spool rpt.sql
set termout off
set linesize 200
set trimspool on
DECLARE
dbid v$database.dbid%TYPE;
dbname v$database.name%TYPE;
-- snap history get snap ids
CURSOR l_cur IS
SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot
WHERE begin_interval_time >= trunc(SYSDATE) – 1 and end_interval_time <= trunc(SYSDATE) + 1/24
ORDER BY begin_interval_time;
l_rec l_cur%ROWTYPE;
l_prev_rec l_cur%ROWTYPE;
l_begin_date VARCHAR2(30);
l_end_date VARCHAR2(30);
BEGIN
SELECT dbid, name INTO dbid, dbname FROM v$database;

OPEN l_cur;
FETCH l_cur INTO l_rec;
WHILE l_cur%FOUND
LOOP
l_prev_rec := l_rec;
FETCH l_cur INTO l_rec;
IF l_cur%found THEN
l_begin_date := to_char(l_prev_rec.BEGIN_INTERVAL_TIME, 'YYYYMMDD_HH24MI');
l_end_date := to_char(l_prev_rec.end_interval_time, 'YYYYMMDD_HH24MI');
dbms_output.put_line('-- ' || l_begin_date
|| ' – ' || l_end_date);
dbms_output.put_line('define inst_num = 1;');
dbms_output.put_line('define num_days = 0;');
dbms_output.put_line('define inst_name = ''' || dbname || ''';');
dbms_output.put_line('define db_name = ''' || dbname || ''';');
dbms_output.put_line('define dbid = ' || dbid || ';');
dbms_output.put_line('define report_type = ''html'';');
dbms_output.put_line('define begin_snap = ' || l_prev_rec.snap_id);
dbms_output.put_line('define end_snap = ' || l_rec.snap_id);
dbms_output.put_line('define report_name = /tmp/awr_' || dbname
|| '_' || l_begin_date || '_' || l_end_date || '.html');
dbms_output.put_line('@?/rdbms/admin/awrrpti');
END IF;
END LOOP;
CLOSE l_cur;
END;
/

Sample Output from rpt.sql:
-- 20111125_0000 – 20111125_0100
define inst_num = 1;
define num_days = 0;
define inst_name = ‘TESTDB’;
define db_name = ‘TESTDB’;
define dbid = 2547329494;
define report_type = ‘html’;
define begin_snap = 384
define end_snap = 385
define report_name = /tmp/awr_TESTDB_20111125_0000_20111125_0100.html
@?/rdbms/admin/awrrpti
-- 20111125_0100 – 20111125_0200
define inst_num = 1;
define num_days = 0;
define inst_name = ‘TESTDB’;
define db_name = ‘TESTDB’;
define dbid = 2547329494;
define report_type = ‘html’;
define begin_snap = 385
define end_snap = 386
define report_name = /tmp/awr_TESTDB_20111125_0100_20111125_0200.html
@?/rdbms/admin/awrrpti

How to create/drop AWR baseline?

To create a baseline of performance of the system, one create a baseline and used for later comparisons. If a snapshot id passed in is invalid the procedure will return an error message indicating it is invalid. The snapshot baseline will be kept till it is dropped manually.

SQL> EXEC dbms_workload_repository.create_baseline(start_snap_id=>1, end_snap_id=>10, baseline_name=>'First baseline');

-- If cascade is TRUE, it will also drop the snapshot in the baseline.
-- If cascade is FALSE, it will only drop the baseline (not the snapshot)
SQL> EXEC dbms_workload_repository.drop_baseline(baseline_name=>'First baseline', cascade=>false);

-- view baseline name, start and end snap id
SQL> select BASELINE_NAME, START_SNAP_ID, END_SNAP_ID from dba_hist_baseline;

Using DBMS_ADVISOR to advice about UNDO tablespace from AWR

declare
task_id number;
name varchar2(100);
descr varchar2(100);
obj_id number;
-- get the min and max snap id from AWR
CURSOR l_cur IS
SELECT min_snap_id, begin_interval_time, max_snap_id, end_interval_time
FROM dba_hist_snapshot d,
(select min(snap_id) as min_snap_id, max(snap_id) max_snap_id FROM dba_hist_snapshot) minmaxsnap
WHERE d.snap_id = minmaxsnap.min_snap_id
or d.snap_id = minmaxsnap.max_snap_id
order by begin_interval_time;
l_min_snap l_cur%ROWTYPE;
l_max_snap l_cur%ROWTYPE;
-- Get advisor information
CURSOR l_undo_advisor(p_task_id IN dba_advisor_findings.task_id%TYPE) IS
select type, message, more_info from dba_advisor_findings where task_id = p_task_id;
l_rec_advisor l_undo_advisor%ROWTYPE;
begin
open l_cur;
fetch l_cur INTO l_min_snap;
fetch l_cur INTO l_max_snap;
close l_cur;
-- name of advisor task
name := 'UNDO_TABLESPACE_' || l_min_snap.min_snap_id || '_' || l_max_snap.max_snap_id;
descr := 'Check Undo Tablespace';
dbms_advisor.create_task('Undo Advisor', task_id, name, descr);
dbms_advisor.create_object(name, 'UNDO_TBS', NULL, NULL, NULL, 'null', obj_id);
-- Set parameters
dbms_advisor.set_task_parameter(name, 'TARGET_OBJECTS', obj_id);
dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', 106);
dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', 423);
dbms_advisor.set_task_parameter(name, 'INSTANCE', 1);
dbms_output.put_line('Task Name: ' || name);
dbms_output.put_line('Task Id: ' || task_id);
dbms_advisor.execute_task(name);
open l_undo_advisor(task_id);
fetch l_undo_advisor INTO l_rec_advisor;
IF l_undo_advisor%FOUND THEN
dbms_output.put_line('Type: ' || l_rec_advisor.type);
dbms_output.put_line('Message: ' || l_rec_advisor.message);
dbms_output.put_line('More Info: ' || l_rec_advisor.more_info);
ELSE
dbms_output.put_line('No Information found');
END IF;
close l_undo_advisor;
dbms_advisor.delete_task(name);
end;
/

Output:
Task Name: UNDO_TABLESPACE_106_426
Task Id: 466
Type: PROBLEM
Message: The undo tablespace is OK.
More Info:

How to create a on-demand AWR snapshot?

Using dbms_workload_repository.create_snapshot() one can create AWR snapshot.

Example: Creates the snapshot and returns the snap id. If ‘ALL’ is not passed it defaults to typical (statistics_level).

SQL> select dbms_workload_repository.create_snapshot('ALL') from dual;

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
——————————————
856

1 row selected.

To view snapshot

SQL> select end_interval_time, error_count from DBA_HIST_SNAPSHOT where snap_id = 856;

END_INTERVAL_TIME ERROR_COUNT
————————————————————————— ———–
25-AUG-10 11.03.17.486 PM 0

How to generate AWR report?

There are few scripts that can be used to generate AWR HTML or text reports.
@?/rdbms/admin/awrrpt.sql – It is an interactive script that can be used to generate HTML or text reports. The script prompts for type of report requested and prompts for number of days of snapshots to choose from so based on that it displays snap ids to choose the start and end snapshot it.


796 27 Sep 2009 17:00 1
797 27 Sep 2009 18:00 1
798 27 Sep 2009 19:00 1
799 27 Sep 2009 20:00 1
800 27 Sep 2009 21:00 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 795
Begin Snapshot Id specified: 795

Enter value for end_snap: 800
End Snapshot Id specified: 800

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_795_800.html. To use this name,
press to continue, otherwise enter an alternative.
..

Another way of generating a report is one can query dba_hist_snapshot to find the snap id for the interval you are looking for.

SQL> column begin_interval_time format a25
SQL> column end_interval_time format a25
SQL> SELECT dbid, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by begin_interval_time;
DBID SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
———- ———- ————————- ————————-

1992878807 798 27-SEP-09 06.00.13.120 PM 27-SEP-09 07.00.14.116 PM
1992878807 799 27-SEP-09 07.00.14.116 PM 27-SEP-09 08.00.15.051 PM
1992878807 800 27-SEP-09 08.00.15.051 PM 27-SEP-09 09.00.16.049 PM

SQL> SELECT output FROM TABLE (dbms_workload_repository.awr_report_text(1992878807, 1, 799, 800));

OUTPUT
——————————————————————————–
WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Release RAC Host
———— ———– ———— ——– ———– — ————
TEST 1992878807 TEST 1 10.2.0.1.0 NO localhost.lo

Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 799 27-Sep-09 20:00:15 15 2.0
End Snap: 800 27-Sep-09 21:00:16 15 2.0
…..
user_dump_dest /u01/oradata/admin/TEST/udump
————————————————————-

OUTPUT
——————————————————————————–

End of Report

@?/rdbms/admin/awrrpti.sql – Script can be used for the specified database and instance so the script in addition will prompt for dbid and instance number.
@?/rdbms/admin/awrddrpt.sql – Script can be used to generate a combine report of 2 different snapshots for comparison of performance in one report so it will prompt for two pairs of snapshot snapshots to compare against.
@?/rdbms/admin/awrinfo.sql – Script print AWR information like space usage by AWR, subset of AWR snap ids, Automatic Database Diagnostic Monitor (ADDM), Active Session History (ASH) information.

How to change AWR retention, interval, topnsql?

Using dbms_workload_repository.modify_snapshot_settings one can modify retention, interval and topnsql.

— get the dbid which is needed to passs to dbms_workload_repository.modify_snapshot_settings
SQL> select dbid from v$database;

DBID
———-
1992878807

— retention=>value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800), max value can be set by passing a value of 0 which means forever
— internal=>60min (snap at this interval), a value of 0 will turn off AWR
— topnsql – top N sql size, specify value of NULL will keep the current setting
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60, topnsql=>100, dbid=>1992878807);

PL/SQL procedure successfully completed.

— shows retention and interval after it was modified
SQL> select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval,
extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval,
topnsql
from dba_hist_wr_control;

Snapshot Interval Retention Interval topnsql
—————– —————— ———-
60 64800 100

-- Change snapshot interval to 30mins
SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>30);
PL/SQL procedure successfully completed.

How to find AWR snapshot interval and retention settings?

Using this SQL one can find the snapshot interval and snapshot retention.

SQL> SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
FROM dba_hist_wr_control;

Snapshot_Interval Retention_Interval
—————– ——————
60 10080

How to change AWR snapshot interval and/or retention