on Nov 15, 2018Article Content
| Article Number | 000036901 |
| Applies To | RSA Product Set: Adaptive Authentication (OnPrem) |
| Issue | Performance issues was reported in RSA Adaptive Authentication (on Premise) and support/engineering team needed Oracle AWR report to further troubleshoot. |
| Resolution | Summary: Oracle solutions for gathering performance related data has been its built-in tool, called Statspack in the earlier version of Oracle before 10g release. With Oracle 10g, Oracle provided an additional tool to gather performance related data which is called the Automatic Workload Repository (AWR). Date from the AWR can be gathered using both command line and UI based interfaces. From the command line, specific sql scripts would be executed. From the UI, the report is generated by using the Oracle Enterprise Manager (OEM). Usage: Customers using Oracle databases with their Adaptive Authentication (on Premise) implementation, workload snapshots are captured every 1 hour by default and the information is retained for 8 days (retention time period is configurable based on needs). Each snapshot is given a unique ID number, and by default includes one hour of performance data. The start and stop time are selected based on the time period for which data is required. Using Oracle Enterprise Manager interface to generate an AWR report: The example below is demonstrated using Oracle 12c version 12.1.0.2.0. If Oracle OEM is accessible to generate an AWR report, you would 1. Log into OEM 2. Go to the “Performance” tab 3. Click on Performance Hub then the following screen would appear  4. Select the time period you wish the report would be generated  5. Click on AWR report button to save the report to your local system.  Using the command line interface to generate an AWR report: There are several different types of command line AWR reports that can be generated. Typically, Oracle Technical support will specify which one (or ones) they require for assistance with the diagnosis of a performance problem. These scripts include: 1) awrrpt.sql - SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids. 2) awrrpti.sql - SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance. 3) awrsqrpt.sql - SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement. 4) awrsqrpi.sql - SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL. 5) awrddrpt.sql - SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods. 6) awrddrpi.sql - SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance. The most common script used is the awrrpt.sql, which selects data based on the beginning and end range snapshot ids. An example of this is shown below. Example: 1) Set default location to $ORACLE_HOME/rdbms/admin 2) Run sqlplus as sysdba 3) Execute awrrpt.sql script 4) Respond to prompts based on date/time required for information requested. This includes: a. type of output (html is default, you can also enter text) b. # of days to look for snapshot (typically 1-7days) c. Snapshot ID start value (based on output from shown from b) d. Snapshot ID end value e. name of output file. 5) exit sqlplus SQL> @awrrpt.sql; Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1485409238 ORCL 1 orcl Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'. 'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active report Enter value for report_type: html Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 1485409238 1 ORCL orcl WIN2008R2 Using 1485409238 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 <return> without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- orcl ORCL 9104 01 Nov 2018 00:00 1 9105 01 Nov 2018 01:00 1 9106 01 Nov 2018 02:00 1 9107 01 Nov 2018 03:00 1 9108 01 Nov 2018 04:00 1 9109 01 Nov 2018 05:00 1 9110 01 Nov 2018 06:00 1 9111 01 Nov 2018 07:00 1 9112 01 Nov 2018 08:00 1 9113 01 Nov 2018 09:00 1 9114 01 Nov 2018 10:00 1 9115 01 Nov 2018 11:00 1 9116 01 Nov 2018 12:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 9115 Begin Snapshot Id specified: 9115 Enter value for end_snap: 9116 End Snapshot Id specified: 9116 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_9115_9116.html. To use this name, press <return> to continue, otherwise enter an alternative. The report will be saved in the local system. |
| Notes | Note: This AAoP knowledge base is not intended as a replacement for Oracle Support documents regarding the use of Oracle AWR but only acts as a quick reference. Please use Oracle’s documentation for complete details on this utility. |