Tuesday, February 2, 2010

DBA_FEATURE_USAGE_STATISTICS

# Did some research into view DBA_FEATURE_USAGE_STATISTICS. It is based on the following joins.

create or replace view dba_feature_usage_statistics as
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 */


Tables are initialised by procedure DBMS_FEATURE_REGISTER_ALLFEAT which calls the DBMS_FEATURE_USAGE package.

E.g

/**********************
* Audit Options
**********************/

declare
DBFUS_AUDIT_STR CONSTANT VARCHAR2(1000) :=
'select count(*), NULL, NULL from audit$ where exists ' ||
'(select 1 from v$parameter where name = ''audit_trail'' and ' ||
'upper(value) != ''FALSE'' and upper(value) != ''NONE'')';
begin
dbms_feature_usage.register_db_feature
('Audit Options',
dbms_feature_usage.DBU_INST_ALWAYS_INSTALLED,
NULL,
dbms_feature_usage.DBU_DETECT_BY_SQL,
DBFUS_AUDIT_STR,
'Audit options in use.');
end;

#Looks like feature usage collection is triggered by procedure DBMS_FEATURE_WCR_CAPTURE based on details in wri$_dbu_usage_sample, so updating that table can trigger collection.

update wri$_dbu_usage_sample set last_sample_date = last_sample_date -7, last_sample_date_num = last_sample_date_num - sample_interval
where dbid=1336168478 and version = '10.2.0.4.0';