Getting started with AWR

• Do I have license to enable AWR reports:

 Enterprise editions have license for AWR and tuning packs. Check Control_management_pack_access parameter to figure out which server manageability pack is active.The following packs are available:

• The DIAGNOSTIC pack includes AWR, ADDM, and so on.
• The TUNING pack includes SQL Tuning Advisor, SQLAccess Advisor, and so on.

The default value for this parameter in Enterprise Edition is  DIAGNOSTIC+TUNING and for all other editions is none.
SQL> show parameters control_management%;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING



• Is AWR enabled on my server:

Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL parameter. The statistics_level paramter should be set to TYPICAL or ALL to enable statistics gathering by the AWR

In the following example, AWR reporting is disabled.

SQL> show parameters statistics_level ;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

statistics_level                     string      BASIC

You can enable it with the following statement :

• alter system set statistics_level='ALL';

• How often are AWR reports generated:

By default , snapshots are captured once every hour and retained in the database for 8 days.

SQL> SQL> col retention format a20
SQL>  col snap_interval format a20
SQL> select dbid, snap_interval, retention from dba_hist_wr_control;

   DB Id    SNAP_INTERVAL        RETENTION
----------- -------------------- --------------------
 1622729499 +00000 01:00:00.0    +00008 00:00:00.0

 You can adjust the interval  and retention of snapshot generation by issuing the following command:

EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  interval  =>  60,
  retention =>  20160);


Interval is specified in minutes

The following script manually generates AWR report:



if [[ $# -lt 1 ]]

then

  echo "runawr.sh  <DELAY(RUNTIME)> "

  exit 1

fi



RUNTIME=$1



sqlplus / as sysdba << !

execute dbms_workload_repository.create_snapshot();

!



sleep $RUNTIME





sqlplus / as sysdba << !



var  endSnapshot number ;

var startSnapshot number;

var startSnapshot number;

var myDbid number ;

var myInst      number;







execute select dbms_workload_repository.create_snapshot() into :endSnapshot from dual ;



execute select :endSnapshot - 1  into :startSnapshot from dual;

show user ;

execute select dbid into :myDbid from v\$database;

execute select instance_number into :myInst from v\$instance;



print :endSnapshot ;

print :startSnapshot ;

print :myDbid ;

print :myInst;





spool awr_report.txt



select * from table (dbms_workload_repository.awr_report_text(:myDbid,:myInst,:startSnapshot,:endSnapshot));

!