Bulk load statistics gathering

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Thu, 3 Oct 2019 18:52:10 +0000
Message-ID: <BL0PR11MB3169DB00276895A6C0249E9BF49F0_at_BL0PR11MB3169.namprd11.prod.outlook.com>

Hi folks,
I understand that bulk load statistics on existing tables are gathered only when direct load is used (APPEND hint) and the table has empty segments as a result of TRUNCATE TABLE command.

When I run the following command the first time, the explain plan shows OPTIMIZER STATISTICS GATHERING in the output. The DBA_TAB_STATISTICS also shows that statistics were gathered: insert /*+ APPEND */ into A select * from B where rownum <= 100000 ;

100000 rows created.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));

PLAN_TABLE_OUTPUT

---

SQL_ID dfvfr45twdfc7, child number 0

---

insert /*+ APPEND */ into A select * from B where rownum <= 100000

Plan hash value: 1452859942

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | INSERT STATEMENT | | | | 874K(100)| |
| 1 | LOAD AS SELECT | A | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 30M| 15G| 874K (1)| 00:00:35 |

|*  3 |    COUNT STOPKEY                 |                    |       |       |            |          |


|   4 |     TABLE ACCESS FULL            | B                  |    30M|    15G|   874K  (1)| 00:00:35 |


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

Predicate Information (identified by operation id):

---

   3 - filter(ROWNUM<=100000)

When I run the command the second time, the explain plan again shows OPTIMIZER STATISTICS GATHERING in the output but the statistics are not gathered which is the expected behavior: SQL> insert /*+ APPEND */ into A select * from B where rownum <= 1000 ; 1000 rows created.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));

PLAN_TABLE_OUTPUT

---

SQL_ID 9q06jrn540dsf, child number 0

---

insert /*+ APPEND */ into A select * from B where rownum <= 1000

Plan hash value: 1452859942

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | INSERT STATEMENT | | | | 874K(100)| |
| 1 | LOAD AS SELECT | A | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 30M| 15G| 874K (1)| 00:00:35 |

|*  3 |    COUNT STOPKEY                 |                    |       |       |            |          |


|   4 |     TABLE ACCESS FULL            | B                  |    30M|    15G|   874K  (1)| 00:00:35 |


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

Predicate Information (identified by operation id):

---

   3 - filter(ROWNUM<=1000)

22 rows selected.

Shouldn't the optimizer detect that the table is not empty and not display OPTIMIZER STATISTICS GATHERING in the explain plan or show something in the Notes section that OPTIMIZER STATISTICS GATHERING were skipped?

Thanks,
Amir

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 03 2019 - 20:52:10 CEST