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 Advertisements Continue reading How to generate execution plan created from AWR snapshot?

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 … Continue reading Script to automate generation AWR for a day

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 … Continue reading How to create/drop AWR 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, … Continue reading Using DBMS_ADVISOR to advice about UNDO tablespace from AWR

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 Continue reading How to create a on-demand AWR snapshot?