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. … More
Category: AWR
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 … More
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 … More
How to drop snapshots?
-- 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, … More
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 … More
How to find space used by AWR?
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 … More
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 … More
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 … More
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 … More
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) … More
