灌入大量数据后手工采集统计信息的重要性 - 数据库编程

-------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 158 | 27 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TBL_STAT_2 | 25 | 1975 | 25 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 158 | 27 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | TBL_STAT | 1 | 79 | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 4 | INDEX RANGE SCAN | IDX_TBL_STAT_2 | 25 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 4 - access("A"."OBJECT_ID"="B"."OBJECT_ID") Note ----- - dynamic sampling used for this statement
20 rows selected. 即使置换两个表的连接顺序，依旧选择TBL_STAT表是全表扫描，TBL_STAT_2是索引范围扫描，但由于插入记录后未采集过统计信息，两张表的预估记录数现在都是和实际相差较多。

9. 手工采集TBL_STAT的统计信息 SQL> exec dbms_stats.gather_table_stats(ownname=>'DCSOPEN',?

tabname=>'TBL_STAT', estimate_percent=>100);
PL/SQL procedure successfully completed.

SQL> explain plan for select a.object_name, b.object_name from tbl_stat_2 a, tbl_stat b where a.object_Id = b.object_id;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------- Plan hash value: 1789047457 -----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29M| 3038M| | 15552 (2)| 00:03:07 |
|* 1 | HASH JOIN | | 29M| 3038M| 47M| 15552 (2)| 00:03:07 |
| 2 | TABLE ACCESS FULL| TBL_STAT | 1190K| 34M| | 3790 (1)| 00:00:46 |
| 3 | TABLE ACCESS FULL| TBL_STAT_2 | 1299K| 97M| | 3645 (1)| 00:00:44 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Note
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
- dynamic sampling used for this statement

19 rows selected. 发现此时TBL_STAT和TBL_STAT_2的预估行数已经不是1了，而且表之间采用的是全表扫描的哈希连接。
10. 手工采集TBL_STAT_2表的统计信息 SQL> exec dbms_stats.gather_table_stats(ownname=>'DCSOPEN', tabname=>'TBL_STAT_2', estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> explain plan for select a.object_name, b.object_name from tbl_stat_2 a, tbl_stat b where a.object_Id = b.object_id;
Explained.

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

灌入大量数据后手工采集统计信息的重要性(四)