AWR(Automatic Workload Repository)

 

Intro:

-oracle have  provided many performance gathering and reporting tools over the years

-to monitor the performance metrics "UTLBSTAT/UTLESTAT

-In oracle 8i The stactspack functionality was introduced

-In oracle 9i the functionality had been extended

-In oracle 10g statspack has evolved into AWR (Automatic Workload Repository)

 

How It Works:

-It resides in SYSAUX tablespace and by default, snapshots are generated once every 60mins and mainted for 7 days

 

Uses:

-depends on the output a series of statistics based on the differences b /w  snapshots that may be used to investigate performance and other issues.

 

Types:

-Awr reports can be generated by various SQL scripts to satisfy various requirements

-the report is availble on HTML and TEXT format

1)awrrpt.sql   = displays various statistics for a range of snapshot id

2)awrrpti.sql  = displays statistics for a range of snapshot id on specified database and instance

3)awrsqrpt.sql = display statistics of a particular SQL statement for a range of snapshot ids, run this report to debug the performance of a particular SQL statement

4)awrsqrpi.sql = displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL

5)awrddrpt.sql = compares  detailed performance attributes and configuration settings b/w two selected time periods

6)awrddrpi.sql = compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

 

To generate Snapshot manually:

used the script below


 

begin


DBMS_WORKLOAD_REPOSITORY.Create_snapshot()


end;


/


 

Workload Repository Views:

The following workload repository views are available:

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.

V$METRIC - Displays metric information.

V$METRICNAME - Displays the metrics associated with each metric group.

V$METRIC_HISTORY - Displays historical metrics.

V$METRICGROUP - Displays all metrics groups.

DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.

DBA_HIST_BASELINE - Displays baseline information.

DBA_HIST_DATABASE_INSTANCE - Displays database environment information.

DBA_HIST_SNAPSHOT - Displays snapshot information.

DBA_HIST_SQL_PLAN - Displays SQL execution plans.

DBA_HIST_WR_CONTROL - Displays AWR settings.

 

Automation of AWR Reports:

-We can use the dbms_workload_repository.awr_report_text in a bash script and be using cron you can run it on a daily basis for automated reports.


Scenarios of AWR taking:

-The performance has slowly degraded over time due to increased user activity

-The performance of DB over time has been degraded due to changing or increased content

-The performance of DB has become slower after the patchset has been applied or app server  version changed

-The performance of DB has become slower after the database version changes or patchset has been applied

 

Steps to generate AWR:

  In Linux:

 

$ORACLE_HOME/rdbms/admin/awrrpt.sql

 


  In Windows:

C:\Users>sqlplus '/ as sysdba'



SQL*Plus: Release 11.2.0.2.0 Production on Tue May 23 17:17:54 2017



Copyright (c) 1982, 2010, Oracle.  All rights reserved.



Enter password:



Connected to:


Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production



SQL> exec dbms_workload_repository.create_snapshot



PL/SQL procedure successfully completed.




SQL> @?/rdbms/admin/awrrpt.sql



Current Instance


~~~~~~~~~~~~~~~~



   DB Id    DB Name      Inst Num Instance


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


 2804950687 DHONI               1 xe




Using 2804950687 for database Id


Using          1 for instance number




Specify the number of days of snapshots to choose from


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Entering the number of days (n) will result in the most recent


(n) days of snapshots being listed.  Pressing without

specifying a number lists all completed snapshots.



Enter a value for num_days:


(Press "ENTER" if we need to view over all snapshot )


Listing all Completed Snapshots


                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

xe           DHONI             1208 18 May 2017 09:45      1


                               1209 18 May 2017 10:30      1

                               1210 18 May 2017 11:30      1

                               1211 18 May 2017 12:30      1

                               1212 18 May 2017 13:30      1

                               1213 18 May 2017 14:30      1

                               1214 18 May 2017 15:30      1

                               1215 18 May 2017 16:30      1

                               1216 18 May 2017 17:30      1


                               1217 19 May 2017 09:44      1

                               1218 19 May 2017 10:30      1

                               1219 19 May 2017 11:33      1

                               1220 19 May 2017 12:30      1

                               1221 19 May 2017 13:30      1

                               1222 19 May 2017 14:30      1

                               1223 19 May 2017 15:30      1

                               1224 19 May 2017 16:30      1

                               1225 19 May 2017 17:30      1


                               1226 22 May 2017 11:36      1


                               1227 22 May 2017 12:30      1

                               1228 22 May 2017 13:30      1

                               1229 22 May 2017 14:30      1

                               1230 22 May 2017 15:30      1

                               1231 22 May 2017 16:30      1

                               1232 22 May 2017 17:30      1


                               1233 22 May 2017 18:30      1


                               1234 23 May 2017 09:49      1

                               1235 23 May 2017 10:30      1

                               1236 23 May 2017 11:30      1

                               1237 23 May 2017 12:30      1

                               1238 23 May 2017 13:42      1

                               1239 23 May 2017 14:30      1

                               1240 23 May 2017 15:16      1

                               1241 23 May 2017 16:30      1

                               1242 23 May 2017 16:36      1

                               1243 23 May 2017 17:17      1

                               1244 23 May 2017 17:18      1



Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1235

Begin Snapshot Id specified: 1235


Enter value for end_snap: 1236

End   Snapshot Id specified: 1236




(here we need to type the Snapshot id which is our DB had performance issue and took the snapshot which used took diagnose the issue )

 

 


 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_1235_1236.html.  To use this name,

press to continue, otherwise enter an alternative.


Enter value for report_name: awr_test_ana.html


(--------------)

(--------------)

(--------------)


End of Report



Report was written to awr_report_techpaste.com.html


SQL>exit

 

 

Source:Oracle,Google