Online Statistics Gathering for Bulk Loads Oracle Database 12C release 1 (12.1)

In Oracle 12c some bulk transactions like "create table as" and "insert into select from" will automatically collect statistics.  Previously DBAs needed to collect statistics manually after the insert, or run with statistics from previous inserts which may not properly reflect the data.

Starting with Oracle 12c, the default behavior of database is to gathers statistics during bulk loads like CTAS or INSERT SELECT.  However for some reason , if you want to go back to pre-12c behavior , you can do so with a new hint NO_GATHER_OPTIMIZER_STATISTICS hint.

 release 12C Oracle automatically gathers statistics for following operations:

• CREATE TABLE AS SELECT
• INSERT INTO … SELECT on en empty table using direct path

It’s very similar behavior to statistics gathering done during a CREATE INDEX or INDEX REBUILD.No init.ora or any manual settings are required to turn on this feature. Now you can save a lot of time by skipping extra step to collect statistics

See illustration of examples comparing Oracle11g and Oracle12c

Oracle 11g

SQL> SELECT * FROM V$VERSION;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE    11.2.0.2.0      Production
TNS for Linux Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> CREATE TABLE MY_OBJECTS_NOSTATS AS SELECT * FROM MY_OBJECTS;

Table created.

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Database is 11g, Hint is ignored

Table created.

SQL>  CREATE TABLE MY_OBJECTS_NOSTATS_HINT  AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Database is 11g, Hint is ignored

Table created.

SQL> SELECT TABLE_NAME, LAST_ANALYZED , NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME LIKE ‘MY_OBJECTS%’; ==> Database is 11g, Stats are not gathered for all tables

TABLE_NAME                     LAST_ANAL   NUM_ROWS     BLOCKS
—————————— ——— ———- ———-
MY_OBJECTS
MY_OBJECTS_STATS
MY_OBJECTS_NOSTATS
MY_OBJECTS_NOSTATS_HINT

Oracle12c

SQL> SELECT BANNER FROM V$VERSION;

BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
PL/SQL Release 12.1.0.1.0 – Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 – Production
NLSRTL Version 12.1.0.1.0 – Production

SQL> CREATE TABLE MY_OBJECTS_NOSTATS AS SELECT * FROM MY_OBJECTS;

Table created.

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Default behavior ;hint is of no help here.

Table created.

SQL> CREATE TABLE MY_OBJECTS_NOSTATS_HINT  AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Hint instructing Oracle not to collect Stats

Table created.

SQL> SET LINESIZE 200
SQL> COLUMN TABLE_NAME FOR A40
SQL> SELECT TABLE_NAME, LAST_ANALYZED , NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME LIKE ‘MY_OBJECTS%’; ==> Stats are gathered for all tables except table MY_OBJECTS_NOSTATS_HINT which was created with NO_GATHER_OPTIMIZER_STATISTICS hint

TABLE_NAME                               LAST_ANAL   NUM_ROWS     BLOCKS
—————————————————————————————————————
MY_OBJECTS                                26-AUG-13      90908       1561
MY_OBJECTS_NOSTATS                26-AUG-13      90908       1561
MY_OBJECTS_STATS                    26-AUG-13      90908       1561
MY_OBJECTS_NOSTATS_HINT

4 rows selected.

SQL>

==================================================================