Oracle Diagnostic Pack and Tuning Pack Licensing

Oracle Diagnostic and Tuning Pack licensing works as follows:

• Available Only with Enterprise Edition: These packs are exclusive to the Oracle Database Enterprise Edition.
• Prerequisite Relationship: Licensing the Diagnostics Pack is necessary before you can license the Tuning Pack.
• Matching License Counts: The counts for these packs must align with the metrics of Oracle Database licenses for each database they are used in.
• Enterprise Manager Usage: Using Oracle Enterprise Manager with these packs could trigger a licensing event.
• Direct Access Implications: Accessing the underlying data structures of these packs may also trigger a licensing event.

Oracle Diagnostic Pack Licensing

Five key “pack facts” to keep in mind:

• The Tuning and Diagnostics Packs are only available with the Oracle Database Enterprise Edition.

• Diagnostics Packs are a pre-requisite for licensing Tuning Packs.

• The counts for all optional packs must match the metrics and counts of Oracle Database licenses for each database where the packs are being used.

• Using Enterprise Manager (EM) to manage databases could trigger a licensed event. To prevent this, you must remove links that could trigger such an event.

• Simply accessing the underlying data structures could also trigger a licensed event.

Calculating Licensing Requirements for Diagnostic and Tuning Packs

When using Oracle’s Diagnostic and Tuning Packs, ensuring that your licenses match your Enterprise Edition (EE) Database licenses is essential.

These packs are considered additional options for the Enterprise Edition database, meaning that if you license your database, the packs must follow the same licensing structure.

Here’s what you need to know to calculate licensing requirements:

1. Matching Licensing Types

• If your database is licensed by CPU, your Diagnostic and Tuning Packs must also be licensed by CPU.
• The Diagnostic and Tuning Packs must follow the same NUP count if your database is licensed by Named User Plus (NUP).
• Dependency: The Tuning Pack license requires the Diagnostic Pack license. This means you cannot license the Tuning Pack without licensing the Diagnostic Pack.

2. Core Factor Calculation

Oracle calculates the number of processor licenses required using a core factor, which depends on the processor type. For example, Intel processors have a core factor of 0.5, meaning that two cores equal one processor license.

The formula for processor licenses is:
Processor Licenses = Total Cores × Core Factor

3. Example Calculation

Scenario:

• You use a server with 16 Intel cores to run Oracle Database Enterprise Edition (DB EE).
• You want to use both the Diagnostic and Tuning Packs.

Step 1: Calculate the required processor licenses for DB EE.

• Total Cores: 16
• Core Factor: 0.5
• Processor Licenses = 16 × 0.5 = 8 Processor Licenses

Step 2: Match the Diagnostic and Tuning Pack licenses.

• Since the Diagnostic and Tuning Packs must match the DB EE license, you will need eight processor licenses for each pack.

Step 3: Ensure the tuning pack is dependent on the diagnostic pack.

• Licensing the Tuning Pack requires licensing the Diagnostic Pack. To cover your usage, you will purchase 8 Diagnostic Pack and 8 Tuning Pack licenses.

Identifying the use of Diagnostics and Tuning Pack

Determining whether the Tuning and Diagnostics Packs are activated in your Oracle database is straightforward. These packs provide critical database performance optimization and troubleshooting features but must be properly licensed to avoid compliance issues.

Below is a step-by-step guide to identify their usage.

How to Check if Tuning and Diagnostics Packs Are Active

1. Check the Control Management Pack Access Parameter

• Use the v$parameters View to examine the control_management_pack_access parameter.
  • Purpose: This parameter indicates whether the Tuning and Diagnostics Packs are enabled in your database.
  • Example Query:sqlCopy codeSELECT name, value FROM v$parameter WHERE name = 'control_management_pack_access';
  • Values: Possible values include DIAGNOSTIC+TUNING, DIAGNOSTIC, or NONE, which defines the enabled packs.

2. Run a Query on the DBA Feature Usage Statistics View

• Query the DBA_FEATURE_USAGE_STATISTICS view to check if specific features like the Automatic Workload Repository (AWR) are used.
  • Purpose: This helps identify active features associated with Tuning and Diagnostics Packs.
  • Example Query:sqlCopy codeSELECT feature_name, currently_used, last_usage_date FROM dba_feature_usage_statistics WHERE feature_name LIKE '%Automatic Workload Repository%';

3. Use Oracle’s Provided Scripts

• Oracle offers two scripts to identify which packs and features are being used:
  • option_usage.sql: Provides a summary of options and features in use.
  • used_options_details.sql: Offers a more detailed breakdown of specific feature usage.
  • Execution: Run these scripts in SQL*Plus to generate a report.

What to Do If You’re Unsure About Usage

Ask Your DBA

• Your Database Administrator (DBA) is the go-to expert for clarifying the usage of Tuning and Diagnostics Packs. They can provide detailed insights and assist in reviewing configuration and feature usage.

Cross-Reference Information

• Ensure that feature usage aligns with the physical servers hosting your databases. Licensing for these packs is typically based on CPU usage, so verifying that all instances are accounted for is essential.

Conduct a Database Audit

• Oracle license compliance scripts can help identify discrepancies when preparing for a database audit.
  • Why It’s Important: Regular audits ensure compliance with Oracle licensing policies and prevent potential financial penalties.
  • How to Use: Combine the outputs of option_usage.sql and used_options_details.sql with Oracle’s compliance tools to generate a comprehensive report.

Staying Compliant with Oracle Licensing

• Document Everything: Maintain records of feature usage, scripts run, and database configurations for future reference.
• Understand Licensing Requirements: Oracle’s licensing for Tuning and Diagnostics Packs depends on the number of CPUs and the enabled features. Review your license agreements to avoid misunderstandings.
• Monitor Regularly: Regularly check for pack usage to ensure new features are not unintentionally activated.

The Benefits of Tuning and Diagnostics Packs

The Tuning Pack and Diagnostics Pack offer advanced tools for organizations managing Oracle databases. These tools optimize database performance, simplify troubleshooting, and maintain operational efficiency.

1. Diagnostics Pack

The Diagnostics Pack is designed to monitor and analyze the health and performance of your database. Its features enable proactive management and faster resolution of issues.

• Automatic Database Diagnostic Monitor (ADDM)
  • What It Does: Automatically analyzes the database’s performance and identifies potential bottlenecks or inefficiencies.
  • Why It Matters: Provides actionable recommendations to resolve performance issues, ensuring optimal database health.
  • Example: ADDM can pinpoint a slow SQL query affecting application response time and suggest indexing or parameter adjustments.
• Automatic Workload Repository (AWR)
  • What It Does: Collects and processes performance statistics, storing them for historical analysis and self-tuning purposes.
  • Why It Matters: AWR helps identify trends and recurring issues, enabling proactive management before problems escalate.
  • Example: Use AWR reports to compare database performance before and after a system upgrade.
• Active Session History (ASH)
  • What It Does: Samples session activity in the database to provide insights into what events sessions are waiting for and what resources are being utilized.
  • Why It Matters: Offers granular insights into real-time activity, enabling rapid diagnosis of performance issues.
  • Example: If a user reports slow query performance, ASH can identify the exact session and event causing the delay.
• Data Dictionary Views
  • What It Does: Provides detailed information about database objects and system status.
  • Why It Matters: Offers a centralized view of database metadata, crucial for monitoring and troubleshooting.
  • Example: Use these views to check the status of indexes or monitor system parameters.

2. Tuning Pack

The Tuning Pack focuses on optimizing database performance, ensuring efficient query execution and resource utilization.

• SQL Access Advisor
  • What It Does: Provides recommendations to optimize data access paths, such as creating or modifying indexes, materialized views, or partitioning schemes.
  • Why It Matters: Enhances query performance by ensuring data is accessed in the most efficient way possible.
  • Example: SQL Access Advisor may suggest creating a materialized view to pre-aggregate data for a reporting application, speeding up query response times.
• SQL Tuning Advisor
  • What It Does: Analyzes individual SQL statements and recommends improving their execution.
  • Why It Matters: Helps resolve poorly performing SQL, which is often a source of database slowdowns.
  • Example: SQL Tuning Advisor may recommend rewriting a query or adding a missing index to reduce execution time.
• Real-Time SQL Monitoring
  • What It Does: It monitors SQL statement performance as it executes, providing real-time insights into resource consumption and execution details.
  • Why It Matters: Enables immediate identification and troubleshooting of poorly performing queries.
  • Example: Real-Time SQL Monitoring can highlight a long-running query during a batch job, allowing DBAs to take corrective action.
• Data Dictionary Views
  • What It Does: Offers insights into database metadata similar to the Diagnostics Pack.
  • Why It Matters: Supports fine-tuned analysis and optimization by providing detailed system information.
  • Example: Use these views to monitor table sizes and identify potential candidates for partitioning.

What Should You Do If You Don’t Have Licenses for Tuning and Diagnostics Packs?

If your database is experiencing performance issues and you don’t have licenses for the Tuning and Diagnostics Packs, don’t worry—there are alternative tools and strategies you can use.

Below are some practical solutions to address performance challenges while complying with Oracle’s licensing policies.

1. Utilize the “Statspack” Utility

• What It Is: Statspack is an older, free utility provided by Oracle that collects database performance statistics.
• How It Helps: While it doesn’t offer the advanced automation of the Diagnostics Pack, Statspack can still provide useful insights into system performance, such as wait events and resource usage.
• Limitations: Lacks real-time monitoring and automation features, requiring more manual effort to analyze and interpret reports.
• Example: Use Statspack to capture snapshots of database activity over time and generate performance reports for troubleshooting.

2. Take Advantage of Certain Views

• Available Options: Some database views can be accessed without requiring a Diagnostics Pack license. These include:
  • dba_hist_snapshot: Provides information about snapshot timings.
  • database_instance: Displays details about the database instance.
  • snap_error: Logs any errors encountered during snapshot generation.
  • seg_stat and seg_stat_obj: Show statistics for segments and segment objects.
  • undostat: Displays undo tablespace usage statistics.
• How It Helps: These views can be used to manually analyze performance trends and identify issues without infringing on Oracle licensing rules.
• Example: Query seg_stat to determine which database segments are consuming the most resources.

3. Employ SQL Trace and TKPROF

• What They Are:
  • SQL Trace: A tool for tracing SQL execution to understand query behavior.
  • TKPROF: A tool for formatting SQL Trace output to make it easier to analyze.
• How They Help: These tools allow you to diagnose inefficient SQL statements by analyzing execution plans and identifying bottlenecks.
• Steps:
  • Enable SQL Trace for a session or query.
  • Use TKPROF to format and interpret the trace file.
• Example: If a query takes too long, enable SQL Trace to track its execution and use TKPROF to analyze the results for optimization opportunities.

4. Take Advantage of Oracle Cloud Benefits

• Example: By migrating your on-premises database to Oracle Cloud, you gain access to Automated Workload Repository (AWR) reports and SQL Tuning Advisor as part of the cloud package.
• Included Packs: The Diagnostics and Tuning Packs are included in the service if you use Oracle Cloud.
• Bring Your Own License (BYOL): If you migrate your database license to Oracle Cloud under the BYOL policy, you can use these packs for free without additional licensing fees.
• Why It’s Beneficial: Leveraging Oracle Cloud eliminates the need for separate licenses, offering a cost-effective solution for accessing advanced features.

FAQs

Do you want to know more about our Oracle License Management Services?

Please enable JavaScript in your browser to complete this form.

Name *

Email *

Company

Message *

Submit