Splat !
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/11.2.0.2.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 ?
DBA_FEATURE_USAGE_STATISTICS
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 ?
Digging Deeper
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
- wri$_dbu_usage_sample
- wri$_dbu_dbu_feature_usage
- wri$_dbu_feature_metadata
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…
WRI$_DBU_USAGE_SAMPLE
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.
WRI$_DBU_FEATURE_USAGE
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…
WRI$_DBU_FEATURE_METADATA
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.
Share this:
The Anti-Kyte 
I have also come to this after doing some research and next task seems to map out what all features fall in which packs and how to manually see if they are enabled and queries to find if they are enabled.
but here when u say the packages DBMS_SWRF_REPORT_INTERNAL is wrapped what does that means?
LikeLike
Shipra,
I’ve taken the next step you suggested – i.e. identifying which features fall into which packs.
You can find the outcome in this post.
As for identifying what is enabled, that’s a bit more tricky. The fact is that everything in these packs is accessible, whether or not you actually have a license.
This applies even if you set the appropriate database parameters/tool configuration options. You can see an example of this ( using SQLDeveloper) here.
I have formulated an approach that will further restrict access to the packs for which you are not licensed, which you can find here.
In answer to your question about what wrapping is…Oracle provides a utility to allow you to store PL/SQL program units in an encrypted format.
Oracle supplied code uses this technique quite a lot.
Morgan’s Library has quite a good example of how this works.
As a result, it makes it very hard to work out what the original source code was, just from querying the data dictionary.
HTH
Mike
LikeLike
Hello, Thanks for the great article. Do you know of any way to clear out or purge all FUS history on a database? My research indicates it not standard or “recommended”. Is it possible? Any serious risks?
LikeLike
Yogi,
You’re right, this sort of thing is not standard or recommended.
Oracle uses this information for license audits so this is not really surprising.
Additionally, I don’t know if there is some independent internal mechanism to track feature usage which would remain unaffected by a full purge of the visible FUS data.
DBMS_FEATURE_USAGE_INTERNAL.CLEANUP_DATABASE might do this but it seems to be for Oracle’s use only. Simply running it locally (passing a value of TRUE) will give you an error :
exec dbms_feature_usage_internal.cleanup_database(true)
ORA-20015: Cleanup on local Database id for DB Feature Usage not allowed
In summary, it may be technically possible to do this. However, there are likely to be some fairly big legal and ethical risks in doing so.
HTH,
Mike
LikeLike
According to my tests so far, and contrary to what I expected, Oracle does not seem to trigger a Diagnostic pack license usage entry when directly selecting e.g. from DBA_HIST% views or underlying WRH$% tables. It only triggered when calling one of the AWR report PLSQL APIs.
LikeLike
Andy,
that’s interesting.
The examples in this article were tested on Oracle 11g.
I know that things have improved somewhat in terms of preventing access to these packs in subsequent releases.
As you are probably aware, the following DBA_HIST% views were not part of the Diagnostic Pack in 11g :
DBA_HIST_SNAPSHOT
DBA_HIST_DATABASE_INSTANCE
DBA_HIST_SNAP_ERROR
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
DBA_HIST_UNDOSTAT
Am I correct in assuming that your testing is not centred on any of these ?
Also, could you confirm which Oracle version you are testing on ?
Thanks,
Mike
LikeLike