When Tuning Oracle is not an Option
February 4, 2010
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 statisticsclearly 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.
Im not going to belabor the point here, Oracles 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 Oracles Diagnostic Pack is the fact that these features are installed by default, active, and include hooks into Enterprise Manager, various APIs, and Oracles 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 Oracles 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 youve 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, youd be responsible for knowing when youve 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 weve actually paid for, Id doubt (dont 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 youve 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 youve used a particular feature. In this case, the SQL is checking for the use of AWR.
SQL> SELECT name, 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'; NAME DETECTED SAMPLES USED LAST_SAMPLE INTERVAL ----------------------------- -------- ------- ----- ------------ -------- 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 WHERE view_name = 'DBA_FEATURE_USAGE_STATISTICS'; TEXT ------------------------------------------------------------------------ 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', 'FALSE') 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 where 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 Ive not fully tested the implications so try at your own risk. Maybe in a future article Ill 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. Lets get one thing straight, Im not at all suggesting this. What I would suggest is the disabling of the use of these features so that you dont 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:
Obviously, wed 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 lets 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> SELECT count(*) FROM V$ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 165 SQL> ALTER SYSTEM SET control_management_pack_access=NONE; System altered. SQL> SELECT count(*) FROM V$ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 0 SQL> ALTER SYSTEM SET control_management_pack_access='DIAGNOSTIC+TUNING'; System altered. SQL> SELECT count(*) FROM V$ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 340
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 Oracles 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 dont want to double dip here and have to effectively purchase two database monitoring solutions.