Tracking Database Feature Usage with DBA_FEATURE_USAGE_STATISTICS

Leave a reply

How to track the feature usage in Oracle

Introduction

In enterprise database environments, knowing which features are in use is important for improving performance, saving costs, and managing compliance. Oracle offers a built-in view, DBA_FEATURE_USAGE_STATISTICS, to assist database administrators (DBAs) in tracking feature usage over time. This blog explains how to use this useful view for monitoring and reporting.

What is DBA_FEATURE_USAGE_STATISTICS?

DBA_FEATURE_USAGE_STATISTICS is an Oracle system view that tracks how often database features are used and when they were last accessed. It offers insights into the usage of various features within a database instance.

Key Columns in DBA_FEATURE_USAGE_STATISTICS

The table consists of multiple columns, but the most important ones include:

  • FEATURE_NAME: The name of the feature being tracked.
  • VERSION: The Oracle version in which the feature is being used.
  • DETECTED_USAGES: The number of times the feature has been used.
  • FIRST_USAGE_DATE: The first date the feature was used.
  • LAST_USAGE_DATE: The most recent date the feature was used.
  • AUX_COUNT: Additional count for the feature (specific to some features).
  • DESCRIPTION: A brief explanation of the feature.

How to Query DBA_FEATURE_USAGE_STATISTICS

SELECT DBID,NAME, VERSION, DETECTED_USAGES, FIRST_USAGE_DATE, LAST_USAGE_DATE
FROM dba_feature_usage_statistics 
ORDER BY LAST_USAGE_DATE DESC;

Query to get datea of usage feature in report:

column name  format a60
column detected_usages format 999999999999

select u1.dbid, u1.name,
       u1.detected_usages,
       u1.currently_used,
       u1.version
from   dba_feature_usage_statistics u1
where  u1.version = (select max(u2.version)
                     from   dba_feature_usage_statistics u2
                     where  u2.name = u1.name)
and    u1.detected_usages > 0
order by name;

Use Cases of Feature Usage Tracking

1. License Compliance

Some Oracle features need extra licenses. By looking at this view, DBAs can find out if any paid features are used without the right licenses, helping to avoid compliance issues.

2. Performance Optimization

Tracking feature usage helps find unused features. Organizations can save resources by turning off features that are not used.

Leave a Reply