Tuesday, August 12, 2014

DBA_FEATURE_USAGE_STATISTICS

Oracle introduced this view in version 10. It looks like this in version 11:
 
SQL> desc dba_feature_usage_statistics
Name                       Null?    Type
-------------------------- -------- ------------------
DBID                       NOT NULL NUMBER
NAME                       NOT NULL VARCHAR2(64)
VERSION                    NOT NULL VARCHAR2(17)
DETECTED_USAGES            NOT NULL NUMBER
TOTAL_SAMPLES              NOT NULL NUMBER
CURRENTLY_USED                      VARCHAR2(5)
FIRST_USAGE_DATE                    DATE
LAST_USAGE_DATE                     DATE
AUX_COUNT                           NUMBER
FEATURE_INFO                        CLOB
LAST_SAMPLE_DATE                    DATE
LAST_SAMPLE_PERIOD                  NUMBER
SAMPLE_INTERVAL                     NUMBER
DESCRIPTION                         VARCHAR2(128)
 
SQL>
 
As its name suggests, it allows you to see if a database uses a particular Oracle feature or not. In Oracle 11, it has over 150 entries:
 
SQL> l
  1  select count(*)
  2* from dba_feature_usage_statistics
SQL> /
 
  COUNT(*)
----------
       152
 
SQL>
 
Some of the features reported are shown below:
 
SQL> l
  1* select name from dba_feature_usage_statistics
SQL> /
 
 
NAME
-------------------------------------------------------
Encrypted Tablespaces
MTTR Advisor
Multiple Block Sizes
OLAP - Analytic Workspaces
OLAP - Cubes
Oracle Managed Files
Oracle Secure Backup
Parallel SQL DDL Execution
Parallel SQL DML Execution
Parallel SQL Query Execution
Partitioning (system)
Partitioning (user)
Oracle Text
PL/SQL Native Compilation
Real Application Clusters (RAC)
Recovery Area
Recovery Manager (RMAN)
RMAN - Disk Backup
RMAN - Tape Backup
Etc
 
I checked in a database where I did not think that SQL Loader had been used with the Direct Path option. The value in the DETECTED_USAGES column was zero:
 
SQL> l
  1  select detected_usages
  2  from dba_feature_usage_statistics
  3  where name =
  4* 'Oracle Utility SQL Loader (Direct Path Load)'
SQL> /
 
DETECTED_USAGES
---------------
              0
 
SQL>
 
I checked in a database where I had used SQL Loader with the Direct Path option a few times. The value in the DETECTED_USAGES column seemed far too high:
 
SQL> l
  1  select detected_usages
  2  from dba_feature_usage_statistics
  3  where name =
  4* 'Oracle Utility SQL Loader (Direct Path Load)'
SQL> /
 
DETECTED_USAGES
---------------
            231
 
SQL>
 
I will do some research and once I know how this figure is calculated, I will return to this post and update it.