優化全表掃描
全表掃描的工作是掃描高水位一下所有的資料塊。高水位的標誌存在表頭。
該資料塊以後都是嶄新未格式化的資料塊，
高水位有兩個目的。一是它是全表掃描的終點，二是並行插入的起點！
優化全表掃描的辦法有四種，歸根到底，最終核心目的就是降低高水位！
一、降低高水位；二、緊密碼放資料（壓縮資料）；三、並行查詢；四、修改初始化引數
降低高水位的辦法有三：
一、線上回收空間；二、挪動表空間；三、匯出和匯入。
緊密碼放資料辦法有二：
一、調整pctfree;二、使用壓縮特性。
我們採用具體的實驗來介紹如何優化全表掃描。
實驗如下：
建立大表，50萬左右，分析表，列select * from t1;的計劃，看代價！
SQL> conn scott/tiger
Connected.
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 as select * from emp where 0=9;
Table created.
SQL> insert into t1 select * from emp;
已建立14行。
SQL> insert into t1 select * from t1;
已建立14行。
SQL> /
--一直斜槓，直到
已建立229376行。
SQL> commit;
現在我們就有了45萬行左右的大表！
分析表，獲得統計資訊！
SQL>analyze table T1 compute statistics;
Table analyzed.
SQL> set autot trace expl
SQL> select * from t1;
SQL>select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 458K| 14M| 544 (10)| 00:00:07 |
| 1 | TABLE ACCESS FULL| T1 | 458K| 14M| 544 (10)| 00:00:07 |
--------------------------------------------------------------------------
我們看到代價為544，我們圍繞544進行優化，降低代價！
SQL>set autot off
SQL>delete t1 where deptno=30;--我們刪除了資料，驗證高水位有沒有回收。
SQL>commit;
SQL>analyze table T1 compute statistics;
SQL>set autot trace exp;
SQL>select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';
SQL>select * from t1;
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262K| 8192K| 526 (7)| 00:00:07 |
| 1 | TABLE ACCESS FULL| T1 | 262K| 8192K| 526 (7)| 00:00:07 |
--------------------------------------------------------------------------
我們看到代價為526,比原來小一點，因為cost是根據塊，記憶體，cpu,網路綜合計算的。
行少了一半，但代價沒有少多少！因為這裡高水位沒有變化！
一、線上回收空間；
SQL>alter table t1 enable row movement;
SQL>alter table t1 shrink space;
SQL>analyze table T1 compute statistics;
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
-------- ---------- ------------ ----------
262144 1376 32 21
佔用了1376個資料塊。
SQL>select * from t1;
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262K| 8192K| 275 (11)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T1 | 262K| 8192K| 275 (11)| 00:00:04 |
--------------------------------------------------------------------------
我們看到代價為275,比原來小了接近一半。
二、挪動表空間；
SQL> alter table t1 move tablespace users;
這句話也可以重新碼放資料。
SQL> analyze table T1 compute statistics;
表已分析。
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
---------- ---------- ------------ ----------
262144 1568 96 826
佔用了1568個資料塊，比原來多了192個資料塊，這是因為高水位不是一個一個塊的挪動，
而是一組一組的挪動。
SQL>select * from t1;
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262K| 8192K| 310 (10)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T1 | 262K| 8192K| 310 (10)| 00:00:04 |
--------------------------------------------------------------------------
代價為310，比原來的275大，因為浪費了一些塊，這些塊存在於高水位下，但沒有資料。
但資料庫全表掃描的時候還是檢視了空塊，浪費了！
三、調整pctfree
SQL> alter table t1 pctfree 0;
Table altered.
這句話的目的是使每個資料塊更加緊密的碼放資料，沒有update,或者update行長不變的表，
pctfree應該設定為0.
SQL> alter table t1 move tablespace users;
SQL>analyze table T1 compute statistics;
SQL>select * from t1;
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262K| 8192K| 281 (10)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T1 | 262K| 8192K| 281 (10)| 00:00:04 |
--------------------------------------------------------------------------
代價為281，比310笑了10%，因為pctfree預設為10。
四、使用壓縮儲存的新特性
SQL>alter table t1 compress;
SQL>alter table t1 move tablespace users;
SQL>analyze table T1 compute statistics;
SQL>select * from t1;
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262K| 8192K| 97 (27)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T1 | 262K| 8192K| 97 (27)| 00:00:02 |
--------------------------------------------------------------------------
代價為97，因為壓縮了，資料在同一個資料塊內複用了，減少了儲存空間。
但帶來的負面影響是當我們update的時候，表會暴漲，比不壓縮還大，而且普通的
插入不能壓縮，只有在直接載入的時候，才會有壓縮的特性，參考網站內的壓縮表文章。
五、使用並行查詢來提高全表掃描的效能。
SQL> select /*+ full(t1) parallel(t1 16) */ * from t1;
執行計劃
----------------------------------------------------------
Plan hash value: 2494645258
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262K| 8192K | 7 (29)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)|:TQ10000 |262K|8192K| 7 (29)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 262K| 8192K | 7 (29)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T1 | 262K| 8192K| 7 (29)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------
代價為7，比原來的544小了近百倍。效果明顯。
六、修改db_file_multiblock_read_count引數，使每次的i/o儘量多讀資料塊，也會提高全表掃描效能。
SQL> conn / as sysdba
已連線。
SQL> alter system set db_file_multiblock_read_count=1;
系統已更改。
SQL> startup force
重新啟動資料庫
SQL> conn scott/tiger
已連線。
SQL> set autot trace expl
SQL> select * from t1;
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 262K| 8192K| 419 (7)| 00:00:06 |
| 1 | TABLE ACCESS FULL| T1 | 262K| 8192K| 419 (7)| 00:00:06 |
--------------------------------------------------------------------------
原理97的代價，上升為419，為什麼？因為每次讀的資料塊少了！
-----------------------------------------------------------------------------------------------------------------------
pxboracle@live.com
2014.5.22 14:35
share you knowledge with the world.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12798004/viewspace-1167512/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle優化：避免全表掃描Oracle優化
- 優化Oracle with全表掃描的問題優化Oracle
- 優化Oracle with全表掃描的問題(二)優化Oracle
- oracle sql tuning 8--優化全表掃描OracleSQL優化
- delete 與全表掃描delete
- MySQL中的全表掃描和索引樹掃描MySql索引
- ORACLE全表掃描查詢Oracle
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- 使用索引快速全掃描(Index FFS)避免全表掃描的若干場景索引Index
- oracle 全表掃描,索引範圍掃描與塊的理解Oracle索引
- 查詢全表掃描語句
- 查詢全表掃描的sqlSQL
- Oracle中存取資料掃描Table及索引的方式(全表掃描，索引掃描等)Oracle索引
- (轉)索引掃描還是全表掃描（Index Scan Or Full Table Scan）索引Index
- 轉)索引掃描還是全表掃描（Index Scan Or Full Table Scan）索引Index
- 抓取全表掃描的表，篩選和分析
- 索引全掃描和索引快速全掃描的區別索引
- oracle是如何進行全表掃描的Oracle
- mysql索引覆蓋掃描優化MySql索引優化
- delete 刪除資料 全表掃描還是掃描所有塊的測試delete
- oracle實驗記錄（分割槽全表掃描（全區掃描） FTS 時候的成本計算）Oracle
- 隱形轉換導致全表掃描案例
- 一條全表掃描sql語句的分析SQL
- 一次簡單的效能優化診斷，聚簇因子過高導致全表掃描。優化
- 使用全表掃描快取大表的相關問題快取
- 大表範圍掃描走SORT MERGE JOIN的SQL優化SQL優化
- 有索引卻走全表掃描的實驗分析索引
- noworkload下全表掃描cost的計算
- 【MySQL】全索引掃描的bugMySql索引
- 全表掃描的cost 與 索引掃描Cost的比較 – 無直方圖（10.1.0.3以後）索引直方圖
- 23_Oracle資料庫全表掃描詳解(三)Oracle資料庫
- 22_Oracle資料庫全表掃描詳解(二)Oracle資料庫
- 21_Oracle資料庫全表掃描詳解(一)Oracle資料庫
- 關係型資料庫全表掃描分片詳解資料庫
- 關於分割槽表中的全partition掃描問題
- 使用10046 event trace跟蹤全表掃描操作
- oracle實驗記錄 （全表掃描COST計算方法）Oracle
- Greenplum儲存過程使用分割槽表將進行全表掃描儲存過程