Five Oracle Tuning and Diagnostics Pack Facts You Should Know

As Oracle continues to audit customers with increasing regularity, one topic that we are frequently hearing about from our clients is related to the licensing of Tuning and Diagnostics Packs. While many customers have been using them for years – some even unwittingly – most are unaware that these tools provide a very critical function in managing the performance of their applications. And at $12,500 per processor, licensing them is not an insignificant spend, as it represents more than 25% of the cost of an Oracle Database Processor license. For this reason, when pricing Oracle Database Enterprise Edition licenses, we tend to bake this number into the cost, which increases the $47,500 list price to a cool $60K.

With all of this in mind, here are five “pack facts” you should be aware of:

1. The Tuning and Diagnostics Packs are only available with the Enterprise Edition of the Oracle Database.
2. Diagnostics Packs are a pre-requisite for licensing Tuning Packs. We’ve seen situations in which customers own Tuning Pack licenses without Diagnostics Pack licenses (which makes us wonder why there aren’t better controls in the Oracle sales process for preventing such a situation).
3. The counts for all optional packs must match the metrics and counts of Oracle Database licenses for each database in which the packs are being used. We see frequent examples where the counts do not match.
4. If you are using Enterprise Manager (EM) to manage your databases, you are most likely using Tuning and Diagnostics Packs. In 10g, EM went from using legacy statspack utility data to using AWR data and, while this was not a highly published event, that fact does not absolve customers from following the rules. There are some steps you need to take to remove links in EM that could trigger a license event.
5. You don’t need to be using EM to have to license the packs – simply accessing the underlying data structures could trigger a license event. Though there are some exceptions, keeping track of them is not straightforward.

All of this confusion makes both licensing and supporting Oracle Database a challenge. Often times Oracle Support will not ask a customer if they are licensed for these packs prior to requesting performance data. In addition, your managed service provider will often assume that these packs are licensed if they find evidence that they have been used previously.

How to determine Tuning and Diagnostics Packs usage

In Oracle Database 11g and higher, determining whether the Tuning and Diagnostics Packs are enabled in your database is as easy as reviewing the control_management_pack_access parameter in v$parameters. Values could be NONE, DIAGNOSTIC, or DIAGNOSTIC+TUNING. To determine whether AWR is actually in use, you would need to run a query on the dba_feature_usage_statistics view. If it is, you are most likely benefiting from using the packs and just didn’t know it. Oracle does provide both the option_usage.sql and used_options_details.sql scripts to determine which packs and features are being used.

If you are unsure whether you are using the Packs, ask your DBA to check for you. Then reconcile that information against your latest Oracle Support renewal. Remember that licensing is considered on a CPU basis, so you will need to check each database you have under management and cross reference that information with the servers on which those databases are running. While most technical resources aren’t familiar with Oracle licensing policies, Data Intensity does have licensing specialists that can help with this complicated exercise.

If you’re not currently utilizing (or are licensed for) Tuning or Diagnostic Packs, you may want to consider the following benefits of each:

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 an 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_historyview to isolate performance problems with individual database sessions.
• Data Dictionary Views – With some exceptions, data dictionary views beginning with dba_addm, dba_hist ordba_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 – The most frequent use of Tuning Pack is typically real-time SQL monitoring. If a production environment experiences a performance issue, this is the only way for a DBA to determine what SQL statements are running while the problem is occurring.
• Data Dictionary Views – Access to the sql_monitor, and sql_plan_monitor views require Tuning Pack licenses.

So what’s your next move if you have a performance issue and don’t own licenses for Tuning and Diagnostics Packs? Here are a few suggestions:

• Customers can use the legacy “statspack” utility, but the functionality will be significantly less than the features contained within the packs. Data is stored in tables owned by PERFSTAT. It’s also much more cumbersome to manage, which can impact the efficiency of your internal DBA team or external managed service provider.
• Views for dba_hist snapshot, database_instance, snap_error, seg_stat, seg_stat_obj, and undostat are exceptions that can be accessed as part of the “Automatic Segment Advisor” and “Undo Advisor” features without licensing a Diagnostics Pack. SQL Trace and TKPROF are still available, but they will require significant manual work to develop recommendations and conclusions.

Need more information?

Check out My Oracle Support Notes 1490798.1, 276103.1, 94224.1, 1361401.1, 1490798.1, and 1674024.1. These are all good sources of reference material. In addition, Chapter 2 (Options and Packs) of the Oracle Database Licensing Information User Manual contains a significant amount of information on this topic.