灌入大量資料後手工採集統計資訊的重要性，採集統計資訊重要性
1. 建立測試表TBL_STAT，及索引，但不插入記錄
SQL> create table TBL_STAT as select * from dba_objects where 1<>1;
Table created.
SQL> create index idx_tbl_stat on tbl_stat (object_id);
Index created.
SQL> select count(*) from tbl_stat;
COUNT(*)
----------
0
2. 檢索TBL_STAT的執行計畫SQL> explain plan for select object_name from tbl_stat where object_id = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2448091186
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
|* 1 |
TABLE ACCESS FULL| TBL_STAT | 1 | 79 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
發現按照索引欄位查詢使用的是全表掃描。
3. 手工收集TBL_STAT表的統計資訊SQL> exec dbms_stats.gather_table_stats(ownname=>'DCSOPEN', tabname=>'TBL_STAT', estimate_percent=>100);
PL/SQL procedure successfully completed.
4. 再次檢索TBL_STAT表
SQL> explain plan for select object_name from tbl_stat where object_id = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3529113932
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBL_STAT | 1 | 79 | 1 (0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN | IDX_TBL_STAT | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
14 rows selected.
發現這次用到了索引範圍掃描，說明收集統計資訊讓Oracle可以選擇正確的執行計畫路徑。
5. 插入100萬的測試記錄SQL> begin
2 for i in 1 .. 10 loop
3 insert into tbl_stat select * from dba_objects;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from tbl_stat;
COUNT(*)
----------
1190725
6. 查看檢索TBL_STAT表的執行計畫SQL> explain plan for select object_name from tbl_stat where object_id = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3529113932
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBL_STAT | 1 | 79 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TBL_STAT | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
14 rows selected.
插入100萬記錄後，發現仍是索引範圍掃描。
7. 建立第二個測試表TBL_STAT_2，以及索引SQL> create table tbl_stat_2 as select * from tbl_stat;
Table created.
SQL> create index idx_tbl_stat_2 on tbl_stat_2 (object_id);
Index created.
SQL> select count(*) from tbl_stat_2;
COUNT(*)
----------
1190725
8. 檢索TBL_STAT和TBL_STAT_2關聯查詢的執行計畫SQL> explain plan for select a.object_name, b.object_name from tbl_stat a, tbl_stat_2 b where a.object_Id = b.object_id;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 752230886
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
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使用的是索引掃描，表之間是嵌套迴圈串連。
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: 752230886
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------Plan hash value: 2620555949
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29M| 1703M| | 12327 (2)| 00:02:28 |
|* 1 | HASH JOIN | | 29M| 1703M| 47M| 12327 (2)| 00:02:28 |
| 2 | TABLE ACCESS FULL| TBL_STAT_2 | 1190K| 34M| | 3644 (1)| 00:00:44 |
| 3 | TABLE ACCESS FULL| TBL_STAT | 1190K| 34M| | 3790 (1)| 00:00:46 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
15 rows selected.
此時TBL_STAT_2表的記錄也趨於和實際一致，兩表的串連仍是雜湊串連。
總結：1. 表的統計資訊收集還是比較重要的一項工作，除了Oracle 10g以後會有自動收集的作業外，也可以手工進行統計資訊的收集。2. 本例中，由於TBL_STAT表灌入100萬資料後，未收集統計資訊，和TBL_STAT_2表串連採用的是嵌套迴圈串連，這種串連適用於大表和小表的關聯情境，但實際這的兩張表資料量相當，且都超過了100萬，這樣相當於100萬*100萬次關聯，當收集統計資訊後，兩表串連改為了雜湊串連，說明此時Oracle已經知道了表的實際資料量，執行計畫也是依據表的實際資料量來做的判斷，因此當表灌入大量資料後，建議手工採集統計資訊，否則在系統自動採集統計資訊之前，可能得到的執行計畫就是錯的。
