Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 4, 2010

When Tuning Oracle is not an Option

By James Koopmann

Before you start monitoring your Oracle database, make sure you understand some of the behind-the-scenes issues of using Oracle monitoring.

In a not too distant land, not long ago, we DBAs leaned very heavily on a set of scripts affectionately called BSTAT & ESTAT. These two scripts, which were originally run by hand, birthed a revolution of historical statistic collections for an Oracle database. Those of us who were smart enough could schedule these two scripts at opportune times and save a series of reports for viewing at a later time. If you were really smart you might even intervene just before the running of ESTAT and capture the ending statistics in some support table. Over the years BSTAT & ESTAT evolved into what Oracle calls Statspack; an improved set of SQL, PL/SQL, and SQL*Plus scripts that allow for the automated collection, storage, and viewing of performance data.

Now, fast forward to Oracle 11g. Statspack, while still available, has been replaced by the Automatic Workload Repository (AWR). Interesting to note, as does Oracle in the documentation, that if your site does not have Automatic Workload Repository and Automatic Database Diagnostic Monitor features then you should/can use Statspack to gather Oracle instance statistics.

This is all fine and well but if you search the Net for installation of Statspack on an Oracle 11g instance you will soon find that Statspack has a few minor errors that crop into your ability to gather these statistics—clearly indicating that Oracle is not as ambitious to keep Statspack working as new versions of Oracle become available. And why would they? AWR is part of Oracle's Diagnostics Pack and a licensed product that brings in revenue. Just check out the Oracle Technology Global Price List as of January 14, 2010.

I’m not going to belabor the point here, Oracle’s Diagnostic Pack is a nice product, requires development cycles, and they probably should charge for its development. What DBAs and management need to be aware of here is that the Oracle Diagnostic Pack is a separately licensed product and you should be aware of what constitutes a breach in your current license. The best place for finding this information, at least currently for Oracle 11g would be within the Oracle Database Licensing Information 11g Release 1 (11.1) book, for the diagnostic pack.

Within the Diagnostic Pack section, you will soon find out that there are a lot of features, packages, tables, views, etc. included that you just might be using without a valid license. Making it even harder to not use Oracle’s Diagnostic Pack is the fact that these features are installed by default, active, and include hooks into Enterprise Manager, various APIs, and Oracle’s Tuning Pack; all of these packs, features, and functions are loaded with tantalizing possibilities that lure the best of DBAs into using their functionality regardless of license. Such lures include, for Oracle Diagnostic Pack there is AWR, ADDM, ASH, performance monitoring, event notification, blackouts, dynamic metrics, monitoring templates, memory-access based performance monitoring, bottleneck detection, and top wait event analysis, and for Oracle’s Tuning Pack there is SQL Access Advisor, SQL Tuning Advisor, Automatic SQL Tuning, SQL Tuning Sets, SQL Monitoring, Automatic Plan Evolution, and Reorganize objects.

Determining whether you’ve dipped into some of these extra-cost features is somewhat difficult at times, especially if you rely on trying to remember all the objects that are specific to these features. For instance, you’d be responsible for knowing when you’ve accessed various DBMS_, DBA_ADDM_, DBA_HIST_, V$ACTIVE_, DBA_ADVISOR_, awr*.sql, add*.sql, or MGMT$ objects. This list is long, changing with each version, and difficult to remember in the heat of performance tuning where DBAs grasp for every query they can find to help them research an issue.

Now we all want to only use those features we’ve actually paid for, I’d doubt (don’t base your licensing on my statements) if Oracle will rush through the doors of your datacenter and penalize you if you happen to use one of these features by accident. The important thing is for you to understand how you can determine if and when you’ve accidentally, or experimentally, used one of these features. This is where the DBA_FEATURE_USAGE_STATISTICS comes in. This view does just what it sounds like; displaying the amount of times a particular feature was used within your database. A simple query like the following will expose whether you’ve used a particular feature. In this case, the SQL is checking for the use of AWR.

            detected_usages detected, 
            total_samples   samples,
            currently_used  used, 
            to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
            sample_interval interval
       FROM dba_feature_usage_statistics
      WHERE name = 'Automatic Workload Repository';

----------------------------- -------- ------- ----- ------------ --------
Automatic Workload Repository        0       3 FALSE 012110:14:09   604800

Notice that there have been not detections, three samples taken, and the last sample date. Of particular interest is the INTERVAL (604800) which is 7 days or 1 week between taking another sample to see if this feature is being used. Very interesting at this point is determining the underlying tables that support the DBA_FEATURE_USAGE_STATISTICS view. A simple SQL statement, utilizing the DBA_VIEWS view, produces the use of three tables (wri$_dbu_usage_sample, wri$_dbu_feature_usage, and wri$_dbu_feature_metadata).

SQL> SELECT text FROM dba_views 
select samp.dbid, fu.name, samp.version, detected_usages, total_samples,
  decode(to_char(last_usage_date, 'MM/DD/YYYY, HH:MI:SS'),
         NULL, 'FALSE',
         to_char(last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 'TRUE',
  currently_used, first_usage_date, last_usage_date, aux_count,
  feature_info, last_sample_date, last_sample_period,
  sample_interval, mt.description
 from wri$_dbu_usage_sample samp, wri$_dbu_feature_usage fu,
      wri$_dbu_feature_metadata mt
  samp.dbid    = fu.dbid and
  samp.version = fu.version and
  fu.name      = mt.name and
  fu.name not like '_DBFUS_TEST%' and   /* filter out test features */
  bitand(mt.usg_det_method, 4) != 4     /* filter out disabled features */

As much as I like to tinker with Oracle I have not yet fully researched the implications, but you can very easily modify the data within these wri$ tables. For instance if I wanted to change the sample interval I could perform the following SQL. Please note I’ve not fully tested the implications so try at your own risk. Maybe in a future article I’ll touch on this.

SQL> Update wri$_dbu_usage_sample set sample_interval = 60;
1 row updated.
SQL> commit;
Commit complete.

Likewise, one might be wondering if they could just change the detected usages and use the features at your own discretion or as the Oracle police are about to enter your datacenter. Let’s get one thing straight, I’m not at all suggesting this. What I would suggest is the disabling of the use of these features so that you don’t get tempted or get caught with your fingers in the cookie jar. Remember, you MUST purchase a license for using the Diagnostic Pack, Tuning Pack, or any of the others defined in the licensing agreement pointed to earlier in this article.

The best way to control access to the Diagnostic Pack or Tuning Pack is to utilize the new CONTROL_MANAGEMENT_PACK_ACCESS init parameter. This parameter can be set to one of three values:

  • DIAGNOSTIC+TUNING: Diagnostic Pack and Tuning Pack functionally is enabled
  • DIAGNOSTIC: Only Diagnostic Pack functionality is enabled
  • NONE: Diagnostic Pack and Tuning pack functionally is disabled

Obviously, we’d like to have the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to NONE. Unfortunately, this parameter is set to DIAGNOSTIC+TUNING by default and can easily be seen with the following command.

SQL> SHOW PARAMETER control_management_pack_access
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING 

To see how this might work let’s SELECT from one of those Diagnostic Pack objects (V$ACTIVE_SESSION_HISTORY) both before and after setting the CONTROL_MANAGEMENT_PACK_ACCESS to NONE. Here is the string of SQL:

SQL> ALTER SYSTEM SET control_management_pack_access=NONE;
System altered.
SQL> ALTER SYSTEM SET control_management_pack_access='DIAGNOSTIC+TUNING';
System altered.

Very clearly, this parameter is dynamic and effectively turns off and on the use of Diagnostic and Tuning Pack objects.

Now we might not like how Statspack has progressed and the extra cost for Diagnostic and Tuning Packs. However, there is an option. Just turn them off. With the additional cost of the Packs and the semi-non-support given to Statspack it would seem that we as DBAs have been pushed back to pre-7 days. This is one negative way to view the issue. The other being we now have some very interesting decisions to make; we can build our own statistical gathering and reporting procedures, employ third-party tools, or use these Oracle Packs and take advantage of Oracle’s vast knowledge of their own database. I know where I stand. As a final note, if you do decide to go the third-party route, make sure they do not use any of the extra-licensed products of Oracle. You don’t want to double dip here and have to effectively purchase two database monitoring solutions.

» See All Articles by Columnist James Koopmann

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM