Automatic Workload Repository gathers, processes and maintains performance stats used for problem detection and self-tuning the databases. This collective information can be displayed as views and reports (HTML/text) to better show the current database performance status for further analysis. AWR gathers the following data - Object Statistics (access / usage stats of DB segments) Object Statistics (access / usage stats of DB segments) - Time Model Statistics (V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views) Time Model Statistics (V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views) - Some of the System and Session Statistics (V$SYSSTAT and V$SESSTAT views) Some of the System and Session Statistics (V$SYSSTAT and V$SESSTAT views) - ASH (Active Session History) Statistics ASH (Active Session History) Statistics - High load generating SQL Stateme nts High load generating SQL Stateme nts Object Statistics (access / usage stats of DB segments) Time Model Statistics (V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views) Some of the System and Session Statistics (V$SYSSTAT and V$SESSTAT views)

ASH (Active Session History) Statistics High load generating SQL Stateme nts Different Components that uses AWR are: - Automatic Database Diagnostic Monitor Automatic Database Diagnostic Monitor - Undo Advisor Undo Advisor - SQL Tuning Advisor SQL Tuning Advisor - Segment Advisor Segment Advisor Automatic Database Diagnostic Monitor Undo Advisor

SQL Tuning Advisor Segment Advisor By default, Snapshots are generated every hour and retains the statistics in the repository for 8 days. Please find below if you intend to change the default settings: BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 14400, interval => 30, topnsql => 100, dbid => 12345678);

END; / Here , Retention = 14400 minutes (10 days) Snapshot retention period Interval = 30 minutes Snapshot interval period topnsql = Number of Top SQL to flush

dbid = database identifier Different types of AWR Reports for different purposes: For Single Instance Environment: @$ORACLE_HOME/rdbms/admin/awrrpt.sql For Oracle RAC Environment : @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

For a particular SQL Statement : @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql For Comparing the reports : @$ORACLE_HOME/rdbms/admin/awrddrpt.sql SAMPLE ACTIVITY:- 1) Login to DB Server as oracle

2) export ORACLE_SID=PrimeDG 3) cd $ORACLE_HOME/ rdbms/admin 4) sqlplus / as sysdba 5) @awrrpt.sql (or) @?/admin/awrrpt.sql 6) Enter value for report_type: html

Note: Since, we want to open the file in nicely formatted way, we pick html format. 7) Enter value for num_days: 7 Note: you can go back upto 7 days. 8) Enter value for begin_snap: 4697 9) Enter value for end_snap: 4704 10) Enter value for report_name: Test_AWR_Report.html

To pull the report, use WinSCP and pull the html file to you laptop. 10) Start WinSCP and configure it connect to server with “oracle” username. 11) Copy the file form UNIX (right side) to Windows (left side). 12) Double click the file to open the report in browser Others: Query to check the snapshot ID in last 24 hours.

set linesize 200 set pagesize 200 col Begin_interval_time format a30 col end_interval_time format a30 select snap_ID,

Begin_interval_time, end_interval_time from dba_hist_snapshot where begin_interval_time > sysdate-1

order by Begin_interval_time;