New in Oracle 12c: _optimizer_gather_stats_on_load

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing.

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) – and the same thing happens with an IAS (Insert Append Select).

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter.

• Oracle White Paper: Best Practices for Gathering Statistics – Page 13 (PDF: 15)
  http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf
• Oracle White Paper: What to Expect from the Optimizer – Page 19 (PDF: 21)
  http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
• Oracle Database 12c Data Warehousing Guide:
  https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG8382 

Quoting from the first paper:

Online statistics gathering

In Oracle Database 12c, online statistics gathering “piggybacks” statistics gather as part of a direct-path
data loading operation such as, create table as select (CTAS) and insert as select (IAS) operations.
Gathering statistics as part of the data loading operation, means no additional full data scan is required
to have statistics available immediately after the data is loaded.

The parameter controlling this change is not mentioned:

• _optimizer_gather_stats_on_load

The default is TRUE since Oracle 12.1.0.1 – the parameter or functionality did not exist before Oracle Database 12c.
.

Things to Know

The online stats gathering for IAS can happen only if the object you are loading data into is empty. You’ll recognize the feature when you see a line saying OPTIMIZER STATISTICS GATHERING in the execution plan. Additionally you’ll find STATS_ON_LOAD in the NOTES column of below query:

select COLUMN_NAME, NUM_DISTINCT, DENSITY, HISTOGRAM, NOTES from USER_TAB_COL_STATISTICS where TABLE_NAME=’MYTAB’ ;

Consider now that first of all immediate stats gathering may not be desired as it will mean additional load to the system. Even though the feature has been designed to generate as little load as possible it’s still doing something in the background. Hence there may be situations where you’d like to switch it off, e.g. with a hint:

insert /*+append NO_GATHER_OPTIMIZER_STATISTICS*/ into MYTAB select …

And how about the number of parallel slaves creating the statistics? 

There’s no easy answer to this based on the information I have at the moment – but I’d highly recommend this article about parallel execution in Oracle:

• Understanding Parallel Execution – Part 1/2
  by Randolf Geist
  • http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-1-1872400.html 
  • http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-2-1872405.html 

–Mike