日本搞逼视频_黄色一级片免费在线观看_色99久久_性明星video另类hd_欧美77_综合在线视频

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

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

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

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關注
欄目熱點
關閉
程序員人生
主站蜘蛛池模板： 日产精品久久久一区二区开放时间 | av在线激情 | 日韩欧美国产视频 | 精品国产一区二区三区免费 | 欧美精品一区二区三区蜜臀 | 亚洲精品乱码久久久久久 | 色视在线 | 色婷婷综合久久久中文字幕 | 日韩在线h| 国产精品1区2区 | 久久这里有精品 | 国产日韩一区二区 | 国产精品久久久久久久午夜片 | 亚洲欧美中文日韩在线v日本 | 精品视频久久久久久 | 成人国产精品久久久 | 亚洲精品久久久久久久久久久久久 | 最新国产在线视频 | 中文字幕精品一区久久久久 | 精品一区二区三区在线观看国产 | 99久热| 久久成年人视频 | 久久视频一区 | 可以在线看的av | 9久久精品| 日韩在线视频一区二区三区 | 国产精品美女久久 | 91久久国产综合久久 | 国产精品无码久久久久 | 韩日精品一区二区 | 淫影视 | 成人h视频在线观看 | 国产欧美日韩中文字幕 | 亚洲精品一区二区网址 | 久久精品不卡 | 亚洲高清视频在线观看 | 91精品久久久久久久久久入口 | 美女av一区二区 | 能看av的网站 | 国产一区高清 | 精品欧美一区二区三区免费观看 |