display information about database feature usage statistics.That's all I can find in The (12.1) Docu.
Just in case anyone asks - DBA_FEATURE_USAGE_STATISTICS is not mentioned in the license guide at all:
Still for me it's interesting what's behind the view, and how it's content is populated.
So I started to analyze it's anatomy.
Starting with the view definition (slightly rewritten for better readability):
SELECT samp.dbid, fu.name, samp.version, fu.detected_usages, samp.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, fu.first_usage_date, fu.last_usage_date, fu.aux_count, fu.feature_info, samp.last_sample_date, lsamp.ast_sample_period, samp.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%' /* filter out test features */ AND Bitand(mt.usg_det_method, 4) != 4 /* filter out disabled features */
This view is quite simple, and all important columns of wri$_dbu_usage_sample and wri$_dbu_feature_usage are shown in the projection.
The filters are only to hide some rows used for internal testing.
So the interesting part are those columns of wri$_dbu_feature_meatadata which are not shown.
In this table are 2 groups of columns:
The first group are the columns INST_CHK_METHOD and INST_CHK_LOGIC.
They describe if and how to check, if a detailed detection is needed at all.
INST_CHECK_METHOD has 3 possible values right now:
|DBU_INST_ALWAYS_INSTALLED||1||/* no test required */|
|DBU_INST_OBJECT||2||/* object name in LOGIC */|
|DBU_INST_TEST||4||/* only test */|
The detection itself is described in the columns USG_DET_METHOD and USG_DET_LOGIC.
USG_DET_METHOD has 3 possible values:
|DBU_DETECT_BY_SQL||1||/* SQL specified in USG_DET_LOGIC */|
|DBU_DETECT_BY_PROCEDURE||2||/* Procedure specified in USG_DET_LOGIC */|
|DBU_DETECT_NULL||4||/* only test for _DBFUS_TEST_SQL_7 */|
If it's set to DBU_DETECT_BY_SQL, the SQL stored in USG_DET_LOGIC is executed and it's result stored. In case it's DBU_DETECT_BY_PROCEDURE, the procedure is called. It has 3 parameters (with different names, but the type is always the same):
Argument Name Type In/Out Default ------------- ------ ------ ------- IS_USED NUMBER OUT DATA_RATIO NUMBER OUT CLOB_REST CLOB OUT
DATA_RATIO will be stored in AUX_COUNT, CLOB_REST in FEATURE_INFO.
These infos should be sufficient to analyze everything inside and related to DBA_FEATURE_USAGE_STATISTICS.
During the research I found 2 ways to do a immediate run of all tests. Instead of the default weekly run. Those update wri$_dbu_feature_usage - so the results are stored.
The first one is to kick mmon to run the usage sampling out of schedule:
alter session set events 'immediate trace name mmon_test level 6';
The second method is to run the sampling in the current session. This is quite nice for any kind of tracing:
alter session set "_SWRF_TEST_ACTION"=53;
But be aware both methods can create a higher DETECTED_USAGES than weeks between FIRST_USAGE_DATE and LAST_USAGE_DATE.
For a proper mapping between entries in DBA_FEATURE_USAGE_STATISTICS and options/packs there is a script options_packs_usage_statistics.sql provided by My Oracle Support in Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c (Doc ID 1317265.1)