Understand the license detail required for AWS reports in Oracle

AWR is a reporting tool from Oracle that records database performance information to help with issues.

By default, AWR collects information on database activity. Features like Automatic Segment Advisor and Undo Advisor rely on this information from AWR. It accesses some AWR views without needing Pack Licenses, so it must be enabled.

Note: You need a license to access AWR, ASH, and ADDM reports. The Diagnostic and Tuning Packs come at an additional cost. If you don’t have a license, you can use Statspack, a third-party tool, or create your own monitoring methods.

When we obtain AWR, ADDM, and ASH reports manually, we are using information that requires a Diagnostic Pack license. It is the user’s responsibility to not violate license agreements.

Checking the usage of AWR

column name format a30
SELECT name,
detected_usages,
currently_used,
TO_CHAR(last_sample_date,'DD-MON-YYYY:HH24:MI') last_sample
FROM dba_feature_usage_statistics
WHERE name = 'AWR Report' ;

NAME                           DETECTED_USAGES CURRE LAST_SAMPLE
------------------------------ --------------- ----- -----------------
AWR Report                                    02-JAN-2024:04:30

Control the Package usage:

The CONTROL_MANAGEMENT_PACK_ACCESS parameter allows you to control which packs are utilized. 

SHOW PARAMETER control_management_pack_access

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

Change to value None:

ALTER SYSTEM SET control_management_pack_access=NONE;

Change the value of parameter to Diagnostic+tunning

alter system set control_management_pack_access="DIAGNOSTIC+TUNING";

AWR usages:

AWR is an important process for evaluating how effectively the Automatic Workload Repository is being utilized in database performance tuning. By meticulously examining AWR reports, database administrators can gain insights into the overall health of the database system, identify performance bottlenecks, and monitor resource consumption patterns over time. This involves analyzing various metrics such as CPU and memory usage, I/O statistics, and wait events, which can reveal critical information about the workload characteristics. Additionally, regularly checking the usage of AWR allows for proactive adjustments and optimizations, ultimately contributing to better database performance and improved response times for end-users.