Advance compression, Diagonostic pack

Advance Compression Diagnostic Pack: The Ultimate Solution for Efficient Data Compression

If you’re looking to optimize your database’s performance, Oracle’s Diagnostics Pack and Tuning Pack offer a range of tools that can help. Here’s a brief overview of the tools included in each pack:

Diagnostics Pack

• Automatic Database Diagnostic Monitor (ADDM): An automated tool that focuses on the database’s most intensive operations, drilling down into the performance to proactively determine root cause.
• Automatic Workload Repository (AWR): A repository that collects statistics at predetermined intervals on the workloads within the database. The AWR provides a historical reference for performance changes over time, including establishment of performance baselines, and adds great value to the capacity planning process.
• Active Session History (ASH): A key component of AWR, ASH samples session activity every second and stores it in views, replacing the need for more manual utilities such as SQL trace. DBAs typically use the v$active_session_history view to isolate performance problems with individual database sessions.
• Data Dictionary Views: With some exceptions, data dictionary views beginning with dba_addm, dba_hist, or dba_advisor are part of these management packs, and accessing them trigger a licensing event.

Tuning Pack

• SQL Access Advisor: Advice on how to optimize schema design in order to maximize query performance. This feature takes input from a variety of sources, including AWR, to analyze a workload and provides recommendations on index creation and deletion, partition creation, and materialized views creation.
• SQL Tuning Advisor: Statistics analysis, SQL profiling, and access path analysis with recommendations on how to optimize SQL. There is also an automatic mode that allows the database to automatically implement recommendations for conditions in which at least a three-fold improvement would result.
• Real-Time SQL Monitoring: is a crucial feature of the Tuning Pack. It provides automatic monitoring of SQL statements, PL/SQL blocks, or composite database operations that are considered expensive. In a production environment, if a performance issue arises, Real-Time SQL Monitoring is the only way for a DBA to determine what SQL statements are running while the problem is occurring.

 Access to the sql_monitor and sql_plan_monitor views requires Tuning Pack licenses.

To check the status of the Diagnostic Pack, execute the following command:

SQL> show parameter control_management

To enable the Diagnostic Pack in Oracle, set the value of CONTROL_MANAGEMENT_PACK_ACCESS to either DIAGNOSTIC or DIAGNOSTIC+TUNING. Use the following commands to enable the Diagnostic Pack:

-- Enable diagnostic pack only
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = "DIAGNOSTIC";

-- Enable diagnostic and tuning pack
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS= "DIAGNOSTIC+TUNING" ;

To disable the Diagnostic Pack, execute the following command:

ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = "NONE";

The statistics_level parameter is used to collect statistics of the Database and operating system. It has three values: ALL, TYPICAL, and BASIC. The default value is TYPICAL, which is a collection of all major statistics required for the database. If more detailed information is needed, use ALL. If you want to stop all AWR, ADDM, and advisory, set it to BASIC.

To view the current value of statistics_level, execute the following command:

SQL> show parameter statistics_level

Click here to download the options_packs_usage_statistics.sql script