Data … as usual

If you want to know how are used your columns when they are involved in sql queries, you can use a specific function in the dbms_stats package called REPORT_COL_USAGE.

This function will give you what operation have been executed on your table columns, e.g. :

SQL> set lines 150 pages 400 long 20000000 longchunksize 50000
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
-------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
1. AMOUNT_SOLD : EQ RANGE
###############################################################################

In the previous example, we can see the AMOUNT_SOLD column have been accessed with an equality and a range predicates.

If I execute a query which filter on the PROD_ID column, the report will be updated:

SQL> select count(*) from sh.sales where prod_id=400;
COUNT(*)
----------
 0
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
1. AMOUNT_SOLD : EQ RANGE
2. PROD_ID : EQ
###############################################################################

Another feature of this package will report joins that have been done on the table columns (You can have a look to the legend which mentions what the function can report):

SQL> select count(*) from sh.sales s, sh.products p where s.prod_id=p.prod_id;
COUNT(*)
----------
 918843
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
1. AMOUNT_SOLD : EQ RANGE
2. PROD_ID : EQ EQ_JOIN
###############################################################################

If you want to reset usage statistics, use the undocumented procedure RESET_COL_USAGE:

SQL> exec dbms_stats.reset_col_usage('SH','SALES');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
###############################################################################

Note: If you execute a query using a function based index, your column name will be the virtual column name used for applying the function:

SQL> exec dbms_stats.reset_col_usage('SH','SALES');
PL/SQL procedure successfully completed.
SQL> drop index idx;
Index dropped.
SQL> create index idx on sh.sales(amount_sold*2);
Index created.
SQL> set autotrace trace
SQL> select count(*) from sh.sales where amount_sold*2>40;

Execution Plan
----------------------------------------------------------
Plan hash value: 875048923
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 25 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX | 45873 | 582K| 25 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AMOUNT_SOLD"*2>40)

Statistics
----------------------------------------------------------
 12 recursive calls
 0 db block gets
 1828 consistent gets
 1812 physical reads
 0 redo size
 528 bytes sent via SQL*Net to client
 524 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 7 sorts (memory)
 0 sorts (disk)
 1 rows processed
SQL> set autotrace off
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
1. SYS_NC00008$ : RANGE
###############################################################################

If you want to desactivate this feature because your database contains a lot of tables and columns and you don’t want to overload your system, you can set the undocumented parameter “_column_tracking_level” to 0 (default value = 1).

All results of the DBMS_STATS.REPORT_COL_USAGE are based on the COL_USAGE$ dictionary table.

Finally, you can use this method to decide if a column needs a histogram or if it’s an unindexed column that needs one.