91麻豆产精品久久久久久,国产精品麻豆99久久久久久,国产精品成人自拍

Oracle 全表掃描及其執行計劃

全表掃描是Oracle訪問數據庫表是較為常見的訪問方式之一。很多朋友一看到SQL語句執行計劃中的全表掃描，就要考慮對其進行修理一番。全表掃描的存在，的確存在可能優化的余地。但事實上很多時候全表掃描也并非是最低效的，完全要看不同的情形與場合，任一方式都是有利有弊的，也就是具體情況要具體分析。本文描述了什么是全表掃描以及何時發生全表掃描，何時全表掃描才低效。

　　本文涉及到的相關鏈接：

　　高水位線和全表掃描

　　啟用 AUTOTRACE 功能

　　Oracle 測試常用表BIG_TABLE

　　Oracle db_file_mulitblock_read_count參數

　　1、什么是全表掃描？

　　全表掃描就是掃表表中所有的行，實際上是掃描表中所有的數據塊，因為Oracle中最小的存儲單位是Oracle block。

　　掃描所有的數據塊就包括高水位線以內的數據塊，即使是空數據塊在沒有被釋放的情形下也會被掃描而導致I/O增加。

　　在全表掃描期間，通常情況下，表上這些相鄰的數據塊被按順序(sequentially)的方式訪問以使得一次I/O可以讀取多個數據塊。

　　一次讀取更多的數據塊有助于全表掃描使用更少的I/O，對于可讀取的數據塊被限制于參數DB_FILE_MULTIBLOCK_READ_COUNT。

　　2、何時發生全表掃描？

　　a、表上的索引失效或無法被使用的情形(如對謂詞使用函數、計算、NULL值、不等運算符、類型轉換)

　　b、查詢條件返回了整個表的大部分數據

　　c、使用了并行方式訪問表

　　d、使用full 提示

　　e、統計信息缺失時使得Oracle認為全表掃描比索引掃描更高效

　　f、表上的數據塊小于DB_FILE_MULTIBLOCK_READ_COUNT值的情形可能產生全表掃描

　　3、演示全表掃描的情形

a、準備演示環境
scott@ORA11G> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

--創建表t
scott@ORA11G> CREATE TABLE t
2 AS
3 SELECT rownum AS n, rpad('*',100,'*') AS pad
4 FROM dual
5 CONNECT BY level <= 1000;

Table created.

--添加索引
scott@ORA11G> create unique index t_pk on t(n);

Index created.

scott@ORA11G> alter table t add constraint t_pk primary key(n) using index t_pk;

Table altered.

--收集統計信息
scott@ORA11G> execute dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);

PL/SQL procedure successfully completed.

scott@ORA11G> set autot trace exp;
scott@ORA11G> select count(*) from t; --->count(*)的時候使用了索引快速掃描

Execution Plan
----------------------------------------------------------
Plan hash value: 454320086
----------------------------------------------------------------------
| Id | Operation             | Name | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T_PK | 1000 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------

scott@ORA11G> set autot off;
scott@ORA11G> alter table t move; --->進行move table

Table altered.

-->move 之后索引失效，如下所示
scott@ORA11G> @idx_info
Enter value for owner: scott
Enter value for table_name: t

Table Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
------------- -------------- -------------------- ------ -------- --------------- ----
T T_PK N 1 UNUSABLE NORMAL ASC

b、索引失效導致全表掃描
scott@ORA11G> set autot trace exp;
scott@ORA11G> select count(*) from t;

scott@ORA11G> set autot off;
scott@ORA11G> alter index t_pk rebuild; -->重建索引

Index altered.

scott@ORA11G> @idx_info
Enter value for owner: scott
Enter value for table_name: t

Table Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
-------------- ---------------- -------------------- ------ -------- --------------- ----
T T_PK N 1 VALID NORMAL ASC

c、返回了整個表的大部分數據使用了全表掃描
scott@ORA11G> select count(pad) from t where n<=990;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   105 |     7   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |   105 |            |          |
|* 2 |   TABLE ACCESS FULL| T    |   991 |   101K|     7   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N"<=990)

--返回小部分數據時，使用的是索引掃描
scott@ORA11G> select count(pad) from t where n<=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   105 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE              |      |     1 |   105 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    10 | 1050 |     3   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | T_PK |    10 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N"<=10)

d、使用并行方式訪問表時使用了全表掃描
scott@ORA11G> select /*+ parallel(3) */ count(pad) from t where n<=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3126468333
----------------------------------------------------------------------------------------------------------------
| Id | Operation              | Name     | Rows | Bytes | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |   105 |     3   (0)| 00:00:01 |        |      |            |
|   1 | SORT AGGREGATE        |          |     1 |   105 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |   105 |            |          | Q1,00 | P->S | QC (RAND) |
|   4 |     SORT AGGREGATE     |          |     1 |   105 |            |          | Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    10 | 1050 |     3   (0)| 00:00:01 | Q1,00 | PCWC |            |
|* 6 |       TABLE ACCESS FULL| T        |    10 | 1050 |     3   (0)| 00:00:01 | Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("N"<=10)
Note
-----
   - Degree of Parallelism is 3 because of hint
--Author : Robinson
--Blog   :http://blog.csdn.net/robinson_0612

e、使用full提示時使用了全表掃描
scott@ORA11G> select /*+ full(t) */ count(pad) from t where n<=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   105 |     7   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |   105 |            |          |
|* 2 |   TABLE ACCESS FULL| T    |    10 | 1050 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N"<=10)

f、統計信息缺失導致全表掃描的情形
scott@ORA11G> exec dbms_stats.delete_table_stats('SCOTT','T');

PL/SQL procedure successfully completed.

scott@ORA11G> select count(pad) from t where n<=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    65 |     7   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |    65 |            |          |
|* 2 |   TABLE ACCESS FULL| T    |    10 |   650 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N"<=10)
Note
-----
   - dynamic sampling used for this statement (level=2)

--上面的執行計劃使用了全表掃描，而且提示使用了動態采樣，也就是缺乏統計信息
--表上的數據塊小于DB_FILE_MULTIBLOCK_READ_COUNT值的情形可能產生全表掃描的情形不演示

　4、全表掃描何時低效？

--先來做幾個實驗
a、演示表上的相關信息
scott@ORA11G> @idx_info
Enter value for owner: scott
Enter value for table_name: big_table

Table Name Index Name CL_NAM CL_POS Status IDX_TYP DSCD
------------------------- ------------------------- --------- ------ -------- --------------- ----
BIG_TABLE BIG_TABLE_PK ID 1 VALID NORMAL ASC

scott@ORA11G> @idx_stat
Enter value for input_table_name: big_table
Enter value for owner: scott

                                                     AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS
---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------
   1 BIG_TABLE_PK          208     100000             1             1       1483 20130524 10:45:51        1515     100000

--數據庫參數設置
scott@ORA11G> show parameter optimizer_index_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
scott@ORA11G> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS

b、查詢返回20%數據行的情形
scott@ORA11G> alter system flush buffer_cache;
scott@ORA11G> select sum(object_id),avg(object_id) from big_table where id between 20000 and 40000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282                             -- 執行計劃中，使用了索引范圍掃描
---------------------------------------------------------------------------------------------
| Id | Operation                    | Name         | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    18 |   341   (0)| 00:00:05 |
|   1 | SORT AGGREGATE              |              |     1 |    18 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE    | 20046 |   352K|   341   (0)| 00:00:05 |
|* 3 |    INDEX RANGE SCAN          | BIG_TABLE_PK | 20046 |       |    43   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID">=20000 AND "ID"<=40000)
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        351 consistent gets
        351 physical reads
          0 redo size
        427 bytes sent via SQL*Net to client
        349 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

scott@ORA11G> alter system flush buffer_cache;
scott@ORA11G> select /*+ full(big_table) */ sum(object_id),avg(object_id) from big_table where id between 20000 and 40000;

Execution Plan
----------------------------------------------------------
Plan hash value: 599409829                ---- 使用了提示執行為全表掃描
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    18 |   413   (1)| 00:00:05 |
|   1 | SORT AGGREGATE    |           |     1 |    18 |            |          |
|* 2 |   TABLE ACCESS FULL| BIG_TABLE | 20046 |   352K|   413   (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"<=40000 AND "ID">=20000)
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1486 consistent gets
       1484 physical reads
          0 redo size
        427 bytes sent via SQL*Net to client
        349 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

--注意對比上面兩次操作中的consistent gets與physical reads

c、查詢返回30%數據行的情形
scott@ORA11G> alter system flush buffer_cache;
scott@ORA11G> select sum(object_id),avg(object_id) from big_table where id between 20000 and 50000;

Execution Plan
----------------------------------------------------------
Plan hash value: 599409829             --->盡管返回數據的總行數為30%，而此時優化器使用了全表掃描
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    18 |   413   (1)| 00:00:05 |
|   1 | SORT AGGREGATE    |           |     1 |    18 |            |          |
|* 2 |   TABLE ACCESS FULL| BIG_TABLE | 30012 |   527K|   413   (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"<=50000 AND "ID">=20000)
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1486 consistent gets
       1484 physical reads
          0 redo size
        427 bytes sent via SQL*Net to client
        349 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

--下面使用提示來強制優化器走索引掃描
scott@ORA11G> alter system flush buffer_cache;
scott@ORA11G> select /*+ index(big_table big_table_pk) */ sum(object_id),avg(object_id)
2 from big_table where id between 20000 and 50000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282
---------------------------------------------------------------------------------------------
| Id | Operation                    | Name         | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    18 |   511   (1)| 00:00:07 |
|   1 | SORT AGGREGATE              |              |     1 |    18 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE    | 30012 |   527K|   511   (1)| 00:00:07 |
|* 3 |    INDEX RANGE SCAN          | BIG_TABLE_PK | 30012 |       |    64   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID">=20000 AND "ID"<=50000)
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        526 consistent gets
        526 physical reads
          0 redo size
        427 bytes sent via SQL*Net to client
        349 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

--注意觀察每一次測試時所耗用的物理讀與邏輯讀
--從上面的測試可以看出，當表上所返回的數據行數接近于表上的30%時，Oracle 傾向于使用全表掃描
--而對于表上所返回的數據行數接近于表上的30%的情形，我們給與索引提示，此時比全表掃描更高效，即全表掃描是低效的
--筆者同時測試了數據返回總行數接近80%的情形以及創建了一個百萬記錄的進行對比測試
--大致結論，如果查詢所返回的數據的總行數僅僅是表上數據的百分之八十以下，而使用了全表掃描，即可認為該全表掃描是低效的
--注：
--具體情況需要具體分析，如果你的表是千萬級的，返回總數據的百分之零點幾都會導致很大的差異
--其次，表上的索引應具有良好的聚簇因子，如不然，測試的結果可能有天壤之別
--最后，上面所描述的返回總行數應與執行結果返回的行數有差異，是指多少行參與了sum(object_id)

　5、小表的全表掃描是否高效？

--使用scott下dept表，僅有4行數據
scott@ORA11G> select * from dept where deptno>10;

3 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2985873453            --->執行計劃選擇了索引掃描
---------------------------------------------------------------------------------------
| Id | Operation                   | Name    | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |    60 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| DEPT    |     3 |    60 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | PK_DEPT |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO">10)
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets                      -->使用了4次邏輯讀
          0 physical reads
          0 redo size
        515 bytes sent via SQL*Net to client
        349 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          3 rows processed

-->下面強制使用全表掃描
scott@ORA11G> select /*+ full(dept) */ * from dept where deptno>10;

3 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |    60 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT |     3 |    60 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO">10)
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          4 consistent gets         -->此時的邏輯讀同樣為4次
          0 physical reads
          0 redo size
        515 bytes sent via SQL*Net to client
        349 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          3 rows processed

--下面來看看count(*)的情形
scott@ORA11G> select count(*) from dept;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3051237957               --->執行計劃選擇了索引全掃描
--------------------------------------------------------------------
| Id | Operation        | Name    | Rows | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |     1   (0)| 00:00:01 |
|   1 | SORT AGGREGATE |         |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_DEPT |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          1 consistent gets            -->邏輯讀僅為1次
          0 physical reads
          0 redo size
        335 bytes sent via SQL*Net to client
        349 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

-->下面強制使用全表掃描
scott@ORA11G> select /*+ full(dept) */ count(*) from dept;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 315352865
-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          3 consistent gets     -->使用了3次邏輯讀
          0 physical reads
          0 redo size
        335 bytes sent via SQL*Net to client
        349 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

--對于小表，從上面的情形可以看出，使用索引掃描也是比全表掃描高效
--因此，建議始終為小表建立索引

posted on 2013-06-07 10:10 順其自然EVO 閱讀(285) 評論(0) 編輯收藏所屬分類: 數據庫

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: MySQL數字輔助表 PLSQL Developer鮮為人知的快捷鍵淘寶分布式配置管理服務Diamond iOS開發之FMDB AlwaysOn可用性組功能測試(2)-SQL Server群集故障轉移 SQL Server 2008 (R2) 單機版安裝的先決條件 AlwaysOn可用性組功能測試(2)-SQL Server群集故障轉移聊聊Oracle外鍵約束的幾個操作選項 SQL優化中索引列使用函數之靈異事件利用binlog進行數據庫的還原

qileilove

Oracle 全表掃描及其執行計劃

導航

統計

常用鏈接

留言簿(55)

隨筆分類

隨筆檔案

文章分類

文章檔案

搜索

最新評論

閱讀排行榜

評論排行榜