多多色-多人伦交性欧美在线观看-多人伦精品一区二区三区视频-多色视频-免费黄色视屏网站-免费黄色在线

國內最全IT社區平臺 聯系我們 | 收藏本站
阿里云優惠2
您當前位置：首頁 > 數據庫 > 數據庫應用 > 灌入大量數據后手工采集統計信息的重要性

灌入大量數據后手工采集統計信息的重要性

來源：程序員人生   發布時間：2015-01-06 08:47:40 閱讀次數：3011次

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. 創建第2個測試表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致，兩表的連接還是哈希連接。

總結：
1. 表的統計信息搜集還是比較重要的1項工作，除Oracle 10g以后會有自動搜集的作業外，也能夠手工進行統計信息的搜集。
2. 本例中，由于TBL_STAT表灌入100萬數據后，未搜集統計信息，和TBL_STAT_2表連接采取的是嵌套循環連接，這類連接適用于大表和小表的關聯場景，但實際這的兩張表數據量相當，且都超過了100萬，這樣相當于100萬*100萬次關聯，當搜集統計信息后，兩表連接改成了哈希連接，說明此時Oracle已知道了表的實際數據量，履行計劃也是根據表的實際數據量來做的判斷，因此當表灌入大量數據后，建議手工收集統計信息，否則在系統自動收集統計信息之前，可能得到的履行計劃就是錯的。

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
程序員人生
------分隔線----------------------------
分享到:
------分隔線----------------------------
為碼而活
積分：4237
15粉絲
7關注
欄目熱點
關閉
程序員人生
主站蜘蛛池模板： 激情视频在线观看 | 欧美一区二区在线免费观看 | 18在线 | 亚洲综合亚洲综合网成人 | 欧美日韩视频二区三区 | 成人欧美视频在线观看播放 | 一区二区三区四区在线播放 | 日本不卡网 | 国产亚洲欧美在线观看的 | 亚洲欧美大片 | 中文字幕乱码文字醉 | 欧美双插 | 亚洲免费黄网 | 日韩精品一区二区三区中文在线 | 91久久九九精品国产综合 | 中文字幕成人在线观看 | 免费观看h | 国产h视频免费观看 | 国产婷婷一区二区在线观看 | 国产精品自产拍在线观看 | 国产啪视频1000部免费视频 | 高清无遮挡在线观看 | 欧美成人三级一区二区在线观看 | 一区二区在线看 | 中文字幕乱码在线观看 | 国产精品三区四区 | 澳门特级α片免费观看视频 | 一区二区三区免费视频播放器 | 嫩草影院在线观看精品视频 | 久久精品隔壁老王影院 | 天堂在线xw| 欧美久久久久久久一区二区三区 | 成年ssswww日本| 一级做a爱片性色毛片武则天五则 | 午夜影院小视频 | 在线观看国产亚洲 | v天堂在线 | 亚洲精品日韩中文字幕久久久 | 又大又硬又黄又刺激的免费视频 | 99久久精品男女性高爱 | 欧美成视频在线观看 |