Darth Debbie has just launched a surprise attack using her current favourite weapon. Never get into a snowball fight with that woman.
Milton Keynes, like much of the UK at the moment, looks less like it’s leafy, quiet self and more like the Arctic Tundra at present.
Being in a part of the country where normally, you can tell the changing of the seasons by the changing temperature of the rain, a little bit of snow always causes chaos.
On the plus side, the national weather forecasters – The Met Office – issue colour-coded weather warnings which, at times like this include the always hilarious Yellow Snow Warning.
Anyway, the reason for the Star Wars references in this post is that I was planning to cover the whole question of license monitoring with particular reference to the Diagnostic and Tuning packs. However, it was getting to be more of a novel than a blog post so I’ve decided to split it down into more manageable chunks…or Episodes.
So, in the style of the Epic Space Opera, please imagine the following paragraph scrolling through space with John Williams and his Orchestra blaring out of your speakers…
Episode 1 – The Phantom AFTER SELECT Trigger
It is a time of license audits.
The sinister Oracle Corporation have besieged the peaceful Database with some arcane licensing restrictions.
The Geeki, keepers of peace and tranquility in the Data Centre are on the case.
Using their mystical powers, they begin to investigate DBMS_FEATURE_USAGE_REPORT to discover
- Where Oracle stores information on Database Features being used
- How this information is collected
- What unnatural force powers are at work to enable the recording of SELECT statements against some tables and views
Queen Debbie ( she’s changed sides in this paragraph) is wondering just how geeky this is all going to get ….
Under the covers of DBMS_FEATURE_USAGE_REPORT
As mentioned in my previous post ( or Episode 0.9 if you prefer), you can find out what features are currently in use on the database by means of the DBMS_FEATURE_USAGE_REPORT package :
SELECT output FROM TABLE( DBMS_FEATURE_USAGE_REPORT.display_text) /
An extract of the output from this query will look something like this :
... DB Feature Usage Summary DB/Release: XE/22.214.171.124.0 -> This section displays the summary of Usage for Database Features. -> The Currently Used column is TRUE if usage was detected for -> the feature at the last sample time. Curr- ently Detected Total Last Usage Feature Name Used Usages Samples Time ----------------------------------------- ----- -------- -------- -------------- Automatic Maintenance - Optimizer Statist TRUE 6 6 01/19/13 17:35 Automatic Maintenance - Space Advisor TRUE 6 6 01/19/13 17:35 Automatic Memory Tuning TRUE 6 6 01/19/13 17:35 Automatic Segment Space Management (syste TRUE 6 6 01/19/13 17:35 Automatic Undo Management TRUE 6 6 01/19/13 17:35 Character Set TRUE 6 6 01/19/13 17:35 Deferred Segment Creation TRUE 6 6 01/19/13 17:35 Job Scheduler TRUE 6 6 01/19/13 17:35 LOB TRUE 6 6 01/19/13 17:35 Locally Managed Tablespaces (system) TRUE 6 6 01/19/13 17:35 Locally Managed Tablespaces (user) TRUE 6 6 01/19/13 17:35 Object TRUE 6 6 01/19/13 17:35 Partitioning (system) TRUE 6 6 01/19/13 17:35 Recovery Area TRUE 6 6 01/19/13 17:35 Resource Manager TRUE 6 6 01/19/13 17:35 SecureFiles (system) TRUE 6 6 01/19/13 17:35 SecureFiles (user) TRUE 6 6 01/19/13 17:35 Server Parameter File TRUE 6 6 01/19/13 17:35 Undo Advisor TRUE 1 6 01/19/13 17:35 Virtual Private Database (VPD) TRUE 6 6 01/19/13 17:35 ADDM FALSE 0 6 ASO native encryption and checksumming FALSE 0 6 ...
So, we have information on whether a feature has been used, how many times it’s been used and when usage was last detected.
Incidentally, there is also a display_html function in this package which will return the same information.
All interesting stuff. But what if we want to go a bit deeper ? We may want to see just the features that are currently used, or even ones that have ever been used.
We can’t apply a predicate to this particular query, so where else can we get this information from ?
The DBA_FEATURE_USAGE_STATISTICS view is a bit more like it. As well as the name of the feature there is a (sometimes) helpful description. For example, if you’re wondering what “Automatic Maintenance – Optimizer Statistics Gathering” means…
SELECT description FROM dba_feature_usage_statistics WHERE name LIKE 'Automatic Maintenance - Optimizer%'
So, we can now get a listing of just the features in use from this view :
SELECT NAME, detected_usages, first_usage_date, last_usage_date FROM dba_feature_usage_statistics WHERE currently_used = 'TRUE' / NAME DETECTED_USAGES FIRST_USAGE_DATE LAST_USAGE_DATE ---------------------------------------------------------------- --------------- ---------------- --------------- Automatic Maintenance - Optimizer Statistics Gathering 6 07-OCT-12 19-JAN-13 Automatic Maintenance - Space Advisor 6 07-OCT-12 19-JAN-13 Character Set 6 07-OCT-12 19-JAN-13 Locally Managed Tablespaces (system) 6 07-OCT-12 19-JAN-13 Locally Managed Tablespaces (user) 6 07-OCT-12 19-JAN-13 Partitioning (system) 6 07-OCT-12 19-JAN-13 Recovery Area 6 07-OCT-12 19-JAN-13 Resource Manager 6 07-OCT-12 19-JAN-13 Server Parameter File 6 07-OCT-12 19-JAN-13 Virtual Private Database (VPD) 6 07-OCT-12 19-JAN-13 LOB 6 07-OCT-12 19-JAN-13 Object 6 07-OCT-12 19-JAN-13 SecureFiles (user) 6 07-OCT-12 19-JAN-13 SecureFiles (system) 6 07-OCT-12 19-JAN-13 Job Scheduler 6 07-OCT-12 19-JAN-13 Automatic Memory Tuning 6 07-OCT-12 19-JAN-13 Automatic Segment Space Management (system) 6 07-OCT-12 19-JAN-13 Automatic Undo Management 6 07-OCT-12 19-JAN-13 Deferred Segment Creation 6 07-OCT-12 19-JAN-13 Undo Advisor 1 19-JAN-13 19-JAN-13 20 rows selected
Well that’s something of an improvement. But how and when is this information collected ?
Another question that springs to mind, which is of more relevance to our current quest is – how does the database detect that a view that’s part of the Diagnostic or Tuning Pack API has been accessed via a SELECT statement ?
As DBA_FEATURE_USAGE_STATISTICS is a view, we can progress our investigation by examining it’s definition :
SELECT text FROM dba_views WHERE owner = 'SYS' AND view_name = 'DBA_FEATURE_USAGE_STATISTICS' /
The output is :
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 */
The view then is built on three tables
Incidentally, there is some advice knocking about that tables starting wri$ should not be queried as this will cause a license usage to be recorded. However, as the view itself is not part of any additional pack and querying the view does not cause this, we can safely say that this does not apply to these three tables.
Documentation on these tables seems to be a bit sparse so let’s see what we can find out…
This table contains information about the last time the feature usage audit was run, as well as the interval between runs (i.e. number of seconds between the last run and the next run).
The feature usage audit itself is initiated by the MMON background process.
It looks as if this table will only ever hold a single row of data.
Incidentally, the last_sample_date_num looks to be the number of seconds between the last_sample_date and midnight on 1st January 1970 :
SELECT TO_CHAR( last_sample_date, 'DD-MON-YYYY HH24:MI'), TO_CHAR( TO_DATE('01-JAN-1970 00:00', 'DD-MON-YYYY HH24:MI') + (last_sample_date_num /60/60/24), 'DD-MON-YYYY HH24:MI') FROM sys.wri$_dbu_usage_sample / SAMPLE_DATE SAMPLE_DATE_NUM -------------------- -------------------- 19-JAN-2013 17:35 19-JAN-2013 17:35
NOTE – it is possible to run the feature usage audit manually using DBMS_FEATURE_USAGE_INTERNAL.EXEC_DB_USAGE_SAMPLING.
You can also use this framework to monitor usage of any application features you may choose.
There’s a great example of this on the PSOUG site.
This table holds the result from each feature usage audit run.
It contains details such as the first and last usage dates and the number of detected usages.
it seems to provide the bulk of the information we can see in the view :
SELECT name, first_usage_date, last_usage_date FROM sys.wri$_dbu_feature_usage WHERE detected_usages > 0 / NAME FIRST_USAGE_DAT LAST_USAGE_DATE ------------------------------------------------------------ --------------- --------------- Automatic Maintenance - Optimizer Statistics Gathering 07-OCT-12 19-JAN-13 Automatic Maintenance - Space Advisor 07-OCT-12 19-JAN-13 Character Set 07-OCT-12 19-JAN-13 Locally Managed Tablespaces (system) 07-OCT-12 19-JAN-13 Locally Managed Tablespaces (user) 07-OCT-12 19-JAN-13 Partitioning (system) 07-OCT-12 19-JAN-13 Recovery Area 07-OCT-12 19-JAN-13 Resource Manager 07-OCT-12 19-JAN-13 Server Parameter File 07-OCT-12 19-JAN-13 Virtual Private Database (VPD) 07-OCT-12 19-JAN-13 LOB 07-OCT-12 19-JAN-13 Object 07-OCT-12 19-JAN-13 SecureFiles (user) 07-OCT-12 19-JAN-13 SecureFiles (system) 07-OCT-12 19-JAN-13 Job Scheduler 07-OCT-12 19-JAN-13 Automatic Memory Tuning 07-OCT-12 19-JAN-13 Automatic Segment Space Management (system) 07-OCT-12 19-JAN-13 Automatic Undo Management 07-OCT-12 19-JAN-13 Deferred Segment Creation 07-OCT-12 19-JAN-13 Undo Advisor 19-JAN-13 19-JAN-13 20 rows selected
The last of these three tables is probably the most relevant in our quest for the elusive SELECT trigger…
This table contains the name of the feature as it appears in DBA_FEATURE_USAGE_STATISTICS. It also contains the code used to detect usage in the USG_DET_LOGIC column.
Remember, AWR is part of the Diagnostic Pack, usage of which includes direct access to the command-line APIs ( packages and views) i.e. a SELECT statement.
So, can we get somewhere by looking at the detection method for usage of the AWR Report feature ?
with last_period as (select * from sys.wrm$_wr_usage where upper(feature_type) like 'REPORT' and usage_time >= (select nvl(max(last_sample_date), sysdate-7) from sys.wri$_dbu_usage_sample) ) select decode (count(*), 0, 0, 1), count(*), feature_list from last_period, (select substr(sys_connect_by_path(feature_count, ','),2) feature_list from (select feature_count, count(*) over () cnt, row_number () over (order by 1) seq from (select feature_name || ':' || count(*) feature_count from last_period group by feature_name) ) where seq=cnt start with seq=1 connect by prior seq+1=seq) group by feature_list;
So, the usage itself is recorded in the WRM$_WR_USAGE table.
Attempting to track down our mythical SELECT trigger, we can check the dependencies for this table :
SELECT owner, name, type FROM dba_dependencies WHERE referenced_owner = 'SYS' AND referenced_name = 'WRM$_WR_USAGE' / OWNER NAME TYPE ---------- ------------------------------ --------------- SYS DBMS_SWRF_REPORT_INTERNAL PACKAGE BODY
Here we hit a dead-end as the DBMS_SWRF_REPORT_INTERNAL package is wrapped.
It’s a similar story with ADDM.
At this stage, it looks very much as if the selecting of a view or table is recorded by means of the MMON background process.
In the next episode…
So, our valiant search for the SELECT trigger has come to nothing.
Additionally, nowhere in these tables does it explicitly state which features are additional cost options.
All is not lost however. We have found some useful information on how Oracle’s internal feature auditing routines operate.
In the next post, we’ll continue our journey into the Dark Side by trying to pin down exactly which objects are included in the Diagnostic and Tuning Packs.
In the meantime, watch out for the Yellow Snow.