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.

.
.
.

AIOUG Sangam'13 Day Two 09-Nov-13

I attended these sessions on Day Two :
Managing & Troubleshooting Cluster - 360 degrees.  Syed Jaffer Hussain
Indexes Usage in database : Tips and Tricks.  Phani Arega
Oracle 12c Clusterware upgrade - Best Practices.  Syed Jaffer Hussain
DBA to Data Scientist with Oracle Big Data.  Satendra Kumar
Deploy Oracle RAC 12c in minutes.  P S Janakiram
Big Data for Oracle Professionals.  Arup Nanda

.
.
.

AIOUG Sangam'13 Day One 08-Nov-13

I attended these sessions on Day One :
Partitioning Tips and Tricks.  Arup Nanda
Statistics Gathering.  Arup Nanda
Multitenant Database in 12c.  Arup Nanda
Oracle HA and DR for Cloud Computing.  Gaja Krishna Vaidyanatha
What is new in RMAN in 12c?  Aman Sharma
Hands On with Oracle VM.  Anuj Verma.

.
.
.