24 November, 2013

Gather Statistics Enhancements in 12c -- 5

Oracle tracks the usage of columns as query predicates to determine candidate columns for the creation of histograms.  In earlier versions, we would query SYS.COL_USAGE$ to identify such columns.

Now, 12c has a report.

UPDATE : It seems that the DBMS_STATS.REPORT_COL_USAGE function has been available in some 11.2.0.x patchset release. Although it doesn't appear in the 11.2.0.3 documentation set that I had downloaded it is now visible in the online documentation set updated to 11.2.0.4

First, I run some candidate queries :

SQL> show user
USER is "HEMANT"
SQL> select owner, count(*) from obj_list_2
  2  where owner like 'SYS%'
  3  group by owner;

OWNER     COUNT(*)
---------------- ----------
SYS        41818
SYSTEM   635

SQL> select owner, count(*) from obj_list_2
  2  where owner like 'HEM%'
  3  group by owner;

OWNER     COUNT(*)
---------------- ----------
HEMANT     1

SQL> 

Then, I check for COL_USAGE :

SQL> variable mycolusagerept clob;
SQL> set long 10000000
SQL> begin
  2  :mycolusagerept := dbms_stats.report_col_usage(
  3  ownname=>'HEMANT',
  4  tabname=>'OBJ_LIST_2');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print mycolusagerept;

MYCOLUSAGEREPT
--------------------------------------------------------------------------------
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

MYCOLUSAGEREPT
--------------------------------------------------------------------------------
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR HEMANT.OBJ_LIST_2
.........................................

1. OWNER          : LIKE
###############################################################################


MYCOLUSAGEREPT
--------------------------------------------------------------------------------


SQL> 

The report indicates that the OWNER column has been used as a LIKE predicate.

.
.
.

2 comments:

Anonymous said...

Hi,

i think it's a feature since 11gr2, or i am wrong ?

Hemant K Chitale said...

DBMS_STATS.REPORT_COL_USAGE doesn't appear in the 11.2.0.3 PL/SQL documentation that I have downloaded, although it does now appear in the online documentation updated to 11.2.0.4

Apparently, the function was available in some 11.2.0.x patchset release but didn't appear in the documentation.

Hemant