Oracle … as usual

Oracle by Laurent Leturgez

Monitor how are used your table’s columns

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: