12C FEATURE: _optimizer_gather_stats_on_load

Prior to 12c; and starting from 10.2.0.3 to 11.2 Version; table stats were not gathered automatically but this was not same for the index; for index, stats were and still gathered automatically while creating/rebuilding the Index.

Say test case:

Version 11.1.0.7:

CREATE TABLE TEST_VINEET (X NUMBER, Y NUMBER, Z VARCHAR2(100));

INSERT /*append*/ INTO TEST_VINEET SELECT ROWNUM, MOD(ROWNUM,10), 'DYNAMIC'  FROM DUAL CONNECT BY LEVEL <=100000;

See here no stats gathered for the table which is very obvious.

SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_SPACE, AVG_ROW_LEN, LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME='TEST_VINEET';

TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL

------------------------------ ---------- ---------- ---------- ----------- ---------

TEST_VINEET

CREATE INDEX TEST_VINEET_IDX  ON TEST_VINEET(Y);---à Index created and during Index creation the stats are gathered automatically.

SELECT INDEX_NAME, NUM_ROWS, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS FROM DBA_INDEXES WHERE INDEX_NAME='TEST_VINEET_IDX';

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS

------------------------------ ---------- ---------- ----------- -------------

TEST_VINEET_IDX                    100000          1         194            10

So what is that feature which held responsible for automatic stats gathering on Index??

The answer is _optimizer_compute_index_stats which is default true; and this is resposible for doing such action; Oracle find the compute statistics as obsolete so it did enhancement as like below. so in one sense this quite good if we create table with some data and then create Index to avoid stats gathering on Index; but don’t forget to gather stats on TABLE.

_optimizer_compute_index_stats TRUE

Anyways the article here; is not for 11G or back version; what I came across is that Oracle took one step ahead in 12c and concentrated on Bulk approach. By Bulk approach I mean by

§  CREATE TABLE AS SELECT

§  INSERT INTO … SELECT on en empty table using direct path

Here I did tested for direct path insert; CTAS you can check for learningJ

Version: 12.1.0.1.0

CREATE TABLE TEST_VINEET_1 (X NUMBER, Y NUMBER, Z VARCHAR2(100));

CREATE INDEX TEST_VINEET_1_IDX  ON TEST_VINEET_1(Y);

insert /*+ append */ into TEST_VINEET_1 select rownum, mod(rownum,10), 'DYNAMIC' from dual connect by level <=100000;

commit;

select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed from user_tables where table_name='TEST_VINEET_1';

TABLE_NAME                               NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL

--------------------------------------------------------------------

TEST_VINEET_1                              100000        288          0         16 12-APR-15

See here above and below the stats are automatically gathered for direct path Insert

col column_name format  a20

select column_name, num_distinct, density, num_buckets from user_tab_columns where table_name='TEST_VINEET_1';

COLUMN_NAME          NUM_DISTINCT    DENSITY NUM_BUCKETS

-------------------- ------------ ---------- -----------

X                          100000     .00001           1

Y                              10         .1           1

Z                               1          1           1

select column_name, num_distinct, density, histogram, notes from user_tab_col_statistics where table_name='TEST_VINEET_1' ;

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       NOTES

-------------------- ------------ ---------- --------------- -----------------------------------------

X                          100000     .00001 NONE            STATS_ON_LOAD

Y                              10         .1 NONE            STATS_ON_LOAD

Z                               1          1 NONE            STATS_ON_LOAD

If you concerntrate more then you will get to see notes column that these stats were gathered during the LOAD; so do we have some changes on parameter level as well?

Yes there is “_optimizer_gather_stats_on_load” parameter here is performing such actions and it can be helpful for some cases; anyways it all depends do you really want this or not; as computing statistics is again an overhaead; so it is up to your choice whether to take it or not.

The “_optimizer_gather_stats_on_load” Parameter is TRUE by default.

Also is there anyway we can get to know whether it is gathering stats on not by not looking to the dictionary tables; answer is YES.

You can always check the execution plan and you will able to see the changes that 12C made; See generated plan for the direct insert and you can see here; Plan shows new line here “OPTIMIZER STATISTICS GATHERING”

explain plan for INSERT /*+append*/ INTO TEST_VINEET SELECT ROWNUM, MOD(ROWNUM,10), 'DYNAMIC'  FROM DUAL CONNECT BY LEVEL <=100000;

Explained.

Elapsed: 00:00:00.01

SQL> @plan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 1600317434

------------------------------------------------------------------------

| Id  | Operation                        | Name        | Rows  | Cost  |

------------------------------------------------------------------------

|   0 | INSERT STATEMENT                 |             |     1 |     2 |

|   1 |  LOAD AS SELECT                  | TEST_VINEET |       |       |

|   2 |   OPTIMIZER STATISTICS GATHERING |             |     1 |     2 |

|   3 |    COUNT                         |             |       |       |

|*  4 |     CONNECT BY WITHOUT FILTERING |             |       |       |

|   5 |      FAST DUAL                   |             |     1 |     2 |

------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter(LEVEL<=100000)

Note

-----

   - cpu costing is off (consider enabling it)

So if you question come; that I don’t want to gather stats on the table; then what can be done

1)      Disable the paramtere _optimizer_gather_stats_on_load

Or

2)      Use Hints: NO_GATHER_OPTIMIZER_STATISTICS

See here using the hints; Oracle suppress the stats gatheringJ; no such new line in execution plan and no dictionary table populated.

CREATE TABLE TEST_VINEET (X NUMBER, Y NUMBER, Z VARCHAR2(100));

 SQL> explain plan for

 INSERT /*+append NO_GATHER_OPTIMIZER_STATISTICS*/ INTO TEST_VINEET SELECT ROWNUM, MOD(ROWNUM,10), 'DYNAMIC'  FROM DUAL CONNECT BY LEVEL <=100000;

Commit;

SQL> @plan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 1600317434

----------------------------------------------------------------------

| Id  | Operation                      | Name        | Rows  | Cost  |

----------------------------------------------------------------------

|   0 | INSERT STATEMENT               |             |     1 |     2 |

|   1 |  LOAD AS SELECT                | TEST_VINEET |       |       |

|   2 |   COUNT                        |             |       |       |

|*  3 |    CONNECT BY WITHOUT FILTERING|             |       |       |

|   4 |     FAST DUAL                  |             |     1 |     2 |

----------------------------------------------------------------------

TABLE_NAME                                                                                                                         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL

--------------------------------------------------------------------

TEST_VINEET

So it is up to you what you think; if it really good then use it; if not then make the changes accordingly.:)

Enjoy Learning….