Online Statistics Gathering in Oracle 12c

Online Statistics Gathering, As name implies in oracle we can get Stats automatically Gathered on a object while object is getting created in schema.

For online stats , Oracle Carry on Data which were scan\access during Data load in case of table or Index Creations for stats gathering, while creating that objects.So we don’t need to re scan same set of information to capture metadata\stats pertaining to that object.

Pre 12c Version , Same was only applied to Indexes.

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '11.2.0.3';

CREATE TABLE TBL_TEST_1 AS 
SELECT ROWNUM RN , MOD(ROWNUM,4) COL1 FROM DUAL CONNECT BY ROWNUM < 10000;

SELECT NUM_ROWS, LAST_ANALYZED 
FROM DBA_TABLES WHERE TABLE_NAME = 'TBL_TEST_1';

 NUM_ROWS LAST_ANALYZED
---------- -------------

CREATE INDEX IDX_TEST ON TBL_TEST_1(RN);

SELECT NUM_ROWS, LAST_ANALYZED, (SAMPLE_SIZE/NUM_ROWS)*100 ANALYZED_PERCENTAGE
FROM DBA_INDEXES WHERE INDEX_NAME = 'IDX_TEST';

 NUM_ROWS LAST_ANALYZED  ANALYZED_PERCENTAGE
---------- ------------- -------------------
 9999     04-APR-14      100

--As we can see Stats for index were automatically gathered while 
index were getting created in schema.
-- Analyzed percentage for index was 100 percentage.

Let see , how it work in Oracle 12c version.

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '12.1.0.1';

CREATE TABLE TBL_TEST_12C AS 
SELECT ROWNUM RN , MOD(ROWNUM,4) COL1 FROM DUAL CONNECT BY ROWNUM < 10000;

SELECT NUM_ROWS, LAST_ANALYZED, (SAMPLE_SIZE/NUM_ROWS)*100 ANALYZED_PERCENTAGE
FROM DBA_TABLES WHERE TABLE_NAME = 'TBL_TEST_12C';

 NUM_ROWS LAST_ANALYZED ANALYZED_PERCENTAGE
----------       ------------- -------------------
 9999             04-APR-14              100
SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, HISTOGRAM
FROM DBA_TAB_COLS WHERE TABLE_NAME = 'TBL_TEST_12C';

COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
------------- ------------ ---------- --------
RN           9999          0          NONE
COL1         4             0          NONE 

--Table and Column Stats were Gathered , without revisiting table blocks.
--Analyzed percentage in case of online stats gathering are 100%
--histogram will not get created, during online stats gathering.

Plan for CTAS (CREATE TABLE AS )in Oracle 12c.

CREATE TABLE STATEMENT Cost: 3 Cardinality: 1 
 5 LOAD AS SELECT PERF12C.TBL_TEST_12C 
 4 OPTIMIZER STATISTICS GATHERING Cost: 2 Cardinality: 1 
 3 COUNT 
 2 CONNECT BY WITHOUT FILTERING Filter Predicates: ROWNUM<10000 
 1 FAST DUAL Cost: 2 Cardinality: 1

-- For online Stats Gathering, by Piggybacking 
the statistics gather as part of the data loading 
is use to get the metadata\Stats about Table.

If in case , we need to disable online statistics gathering in 12c. Oracle provided below hint for the same.

CREATE TABLE TBL_TEST_12C AS 
SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ROWNUM RN , MOD(ROWNUM,4) COL1 
FROM DUAL CONNECT BY ROWNUM < 10000;

Plan

CREATE TABLE STATEMENT Cost: 3 Cardinality: 1 
 5 LOAD AS SELECT PERF12C.TBL_TEST_12C 
 3 COUNT 
 2 CONNECT BY WITHOUT FILTERING Filter Predicates: ROWNUM<10000 
 1 FAST DUAL Cost: 2 Cardinality: 1

 

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features and tagged , , . Bookmark the permalink.

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