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:…
— 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…
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'); --…
-- 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; --…
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…
