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
— 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
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;
-- drop snapshot, it will not generate an error if the snap id is out of range
SQL> EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>1, high_snap_id=>10);
PL/SQL procedure successfully completed.
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:
Space used by AWR
SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name like ‘%AWR%’;
OCCUPANT_DESC SPACE_USAGE_KBYTES
—————————————————————- ——————
Server Manageability – Automatic Workload Repository 233152
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
