anatomy of DBA_FEATURE_USAGE_STATISTICS

In Oracle database the vie DBA_FEATURE_USAGE_STATISTICS is given to

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 */

DBU_INST_ALWAYS_INSTALLED means the test is run always. If DBU_INST_OBJECT it's only tested, if the object in INST_CHK_LOGIC exists.

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;

source: How to Manually Refresh Dba_feature_usage_statistics (Doc ID 1629485.1)

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)