Search notes:

Oracle execution plan operation OPTIMIZER STATISTICS GATHERING

The following SQL statements are suppposed to demonstrate online statistics gathering with regards to the execution plan operation OPTIMIZER STATISTICS GATHERING:

First, we need a table:

create table tq84_A(col varchar2(1000));

We let Oracle choose a plan for inserting some random data into the table:

explain plan for
   insert into tq84_A
   select
      dbms_random.string('a', 1000)
   from
      dual connect by level <= 10000;

Nothing special, so far:

select * from table(dbms_xplan.display(format=> 'basic'));
--
-- 
-- ------------------------------------------------
-- | Id  | Operation                     | Name   |
-- ------------------------------------------------
-- |   0 | INSERT STATEMENT              |        |
-- |   1 |  LOAD TABLE CONVENTIONAL      | TQ84_A |
-- |   2 |   CONNECT BY WITHOUT FILTERING|        |
-- |   3 |    FAST DUAL                  |        |
-- ------------------------------------------------

However, when using the append hint …

explain plan for
   insert /*+ append */ into tq84_A
   select
      dbms_random.string('a', 1000)
   from
      dual connect by level <= 10000;

… online statistics will be gathered, indicated by the execution plan operation OPTIMIZER STATISTICS GATHERING:

select * from table(dbms_xplan.display(format=> 'basic'));
-- 
-- ---------------------------------------------------
-- | Id  | Operation                        | Name   |
-- ---------------------------------------------------
-- |   0 | INSERT STATEMENT                 |        |
-- |   1 |  LOAD AS SELECT                  | TQ84_A |
-- |   2 |   OPTIMIZER STATISTICS GATHERING |        |
-- |   3 |    CONNECT BY WITHOUT FILTERING  |        |
-- |   4 |     FAST DUAL                    |        |
-- ---------------------------------------------------

Execute the statement:

insert /*+ append */ into tq84_A
select
   dbms_random.string('a', 1000)
from
   dual connect by level <= 10000;

Prevent ORA-12838: cannot read/modify an object after modifying it in parallel

commit;

Explaining the plan for a create table … as select statement:

explain plan for
   create table  tq84_B as
   select * from tq84_A;

A CTAS also gathers online statistics:

select * from table(dbms_xplan.display(format=> 'basic'));
--
-- ---------------------------------------------------
-- | Id  | Operation                        | Name   |
-- ---------------------------------------------------
-- |   0 | CREATE TABLE STATEMENT           |        |
-- |   1 |  LOAD AS SELECT                  | TQ84_B |
-- |   2 |   OPTIMIZER STATISTICS GATHERING |        |
-- |   3 |    TABLE ACCESS FULL             | TQ84_A |
-- ---------------------------------------------------

Cleaning up:

drop table tq84_A;
drop table tq84_B;

Parent row sources

I've encountered OPTIMIZER STATISTICS GATHERING as only row source for the following plan operations:

Oracle execution plan operation OPTIMIZER STATISTICS GATHERING

Parent row sources

See also