12c New Feature – Online Statistics Gathering for Bulk Loads

How many times have we had to execute a CREATE TABLE AS SELECT or INSERT INTO…SELECT that involved a large amount of data then wondered why queries against this table are slow? Did we forget to gather statistics after the fact perhaps?

Well in Oracle Database 12c there is a new feature that will gather online statistics for you when performing bulk loads when using either one of two bulk loading methods:

1) CREATE TABLE AS SELECT

2) INSERT INTO … SELECT into an empty table using a direct path insert

 

Example – Default Behavior (CREATE TABLE AS SELECT)

---

Let’s take a look at how the default behavior works. First will start with the CREATE TABLE AS SELECT (aka CTAS). I have a table loaded with about 500K rows and we’ll use this to make a copy of it using CTAS.

First let’s see what is going on by showing an explain plan.

SQL> explain plan for
  2  create table mytable_stats as select * from mytable;

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 32212168

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |               |   500K|  7812K|   642   (1)| 00:00:01 |
|   1 |  LOAD AS SELECT                  | MYTABLE_STATS |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |               |   500K|  7812K|   411   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL             | MYTABLE       |   500K|  7812K|   411   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

10 rows selected.

As we can see there is an additional line in the explain plan output that shows us the extra step being taken to gather optimizer statistics. Now let’s actually create the table and see when our table statistics were collected.

SQL> create table mytable_stats as select * from mytable;

Table created.

SQL> select table_name, last_analyzed from user_tables where table_name = 'MYTABLE_STATS';

TABLE_NAME       LAST_ANALYZED
---------------- -------------
MYTABLE_STATS    25-JUN-15

The statistics were collected automatically!

 

Example – Default Behavior (INSERT INTO)

---

Now let’s try the INSERT INTO and see what the default behavior looks like.

explain plan for
insert /*+ APPEND */into mytable_stats select * from mytable;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------

Plan hash value: 32212168

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |               |   500K|  7812K|   411   (1)| 00:00:01 |
|   1 |  LOAD AS SELECT                  | MYTABLE_STATS |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |               |   500K|  7812K|   411   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL             | MYTABLE       |   500K|  7812K|   411   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

10 rows selected.

Now let’s actually perform the INSERT INTO and see when our table statistics were collected.

insert /*+ APPEND */into mytable_stats select * from mytable;

500004 rows created.

commit;

Commit complete.

select table_name, last_analyzed from user_tables where table_name = 'MYTABLE_STATS';

TABLE_NAME LAST_ANALYZED
---------------- --------------
MYTABLE_STATS 25-JUN-15

Again the table statistics were collected automatically.

 

No Stats Please!

---

There may be certain circumstances where you may not want to collect table statistics. Perhaps on an extremely large data set we may choose instead to import statistics. We have the option, with the use of a hint, to not gather table statistics. Let’s take a look at how we do that. We’ll use the same source table with 500K rows.

 

Example – No Statistics Behavior (CREATE TABLE AS SELECT)

---

Let’s see what the explain plan shows us when we execute a CTAS with a hint to not gather the statistics.

SQL> explain plan for
2 create table mytable_nostats as select /*+NO_GATHER_OPTIMIZER_STATISTICS */* from mytable;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 32212168

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 500K| 7812K| 642 (1)| 00:00:01 |
| 1 | LOAD AS SELECT | MYTABLE_NOSTATS | | | | |
| 2 | TABLE ACCESS FULL | MYTABLE | 500K| 7812K| 411 (1)| 00:00:01 |
------------------------------------------------------------------------------------------

9 rows selected.

This time we can see that the gather statistics are not collected automatically.  We provided the hint to not do so…let’s see what happens when we perform the action.

SQL> create table mytable_nostats as select /*+NO_GATHER_OPTIMIZER_STATISTICS */* from mytable;

Table created.

SQL> select table_name, last_analyzed from user_tables where table_name = 'MYTABLE_NOSTATS';

TABLE_NAME       LAST_ANALYZED
---------------- -------------
MYTABLE_NOSTATS

As we expected, the table statistics are not collected.

 

Example – No Statistics Behavior (INSERT INTO)

---

Now let’s look at the explain plain for the INSERT INTO when we don’t use the APPEND hint.

explain plan for
insert into mytable_nostats select * from mytable;

select * from table(dbms_xplan.display);

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

Plan hash value: 1015944200

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                 |   500K|  7812K|   411   (1)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | MYTABLE_NOSTATS |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | MYTABLE         |   500K|  7812K|   411   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

9 rows selected.

We can see that the plan does not include a step to gather those table statistics.  Now let’s actually perform the INSERT INTO.

SQL> insert into mytable_nostats select * from mytable;

500004 rows created.

SQL> commit;

Commit complete.
SQL> select table_name, last_analyzed from user_tables where table_name = 'MYTABLE_NOSTATS';

TABLE_NAME LAST_ANALYZED
---------------- --------------
MYTABLE_NOSTATS

Again, as we expected, the table statistics are not collected.

 

Restrictions

---

Keep in mind that only table statistics are gathered and not on any indexes on that table or histograms. Make sure to read the documentation on some of the other restrictions that apply to this new database feature.  While restrictions do exist it’s still a welcome feature for DBAs and Developers alike!