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.
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 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> 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 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.
