對一條基於分割槽的簡單SQL的優化

space6212發表於2019-03-01
SQL優化


今天遇到一個很奇怪的問題，在同一個資料庫上，一條簡單SQL訪問同樣的表，一個用常量，一個用變數，但執行計劃看起來一樣，返回相同的資料，效能差別很大，經過一番研究，終於發現原因。
下面通過實驗模擬這個問題。

[@more@]由於我遇到問題的表是分割槽表，故在實驗中也用分割槽表模擬。

drop table test purge;
create table test(id int, big_col char(2000))
partition by range(id)
(
partition p0 values less than(1),
partition p1 values less than(2),
partition p2 values less than(3),
partition p3 values less than(4),
partition p4 values less than(5),
partition p5 values less than(6),
partition p6 values less than(7),
partition p7 values less than(8),
partition p8 values less than(9),
partition p9 values less than(10)
)
;
insert into test select mod(rownum,10),'1' from dual connect by level <=10000;
commit;
analyze table test compute statistics for table;

以上SQL模擬10個分割槽的表，下面我們重現問題：
suk@ORA10G> alter system flush shared_pool;

系統已更改。

suk@ORA10G> var v number
suk@ORA10G> exec :v:=9

suk@ORA10G> select count(1) from test where id>1 and id>:v;


執行計劃
----------------------------------------------------------
Plan hash value: 3670302319

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 732 (1)| 00:00:09 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 25 | 325 | 732 (1)| 00:00:09 | 2 | 10 |
|* 3 | TABLE ACCESS FULL | TEST | 25 | 325 | 732 (1)| 00:00:09 | 2 | 10 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("ID">1 AND "ID">TO_NUMBER(:V))


統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
3502 consistent gets
0 physical reads
9836 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

suk@ORA10G> select count(1) from test where id>:v;


執行計劃
----------------------------------------------------------
Plan hash value: 3670302319

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 813 (1)| 00:00:10 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 500 | 6500 | 813 (1)| 00:00:10 | KEY | 10 |
|* 3 | TABLE ACCESS FULL | TEST | 500 | 6500 | 813 (1)| 00:00:10 | KEY | 10 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("ID">TO_NUMBER(:V))


統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
374 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

suk@ORA10G> select count(1) from test where id>:v and id>1 ;


執行計劃
----------------------------------------------------------
Plan hash value: 3670302319

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 813 (1)| 00:00:10 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 25 | 325 | 813 (1)| 00:00:10 | KEY | 10 |
|* 3 | TABLE ACCESS FULL | TEST | 25 | 325 | 813 (1)| 00:00:10 | KEY | 10 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("ID">1 AND "ID">TO_NUMBER(:V))


統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
374 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

這個SQL非常簡單，甚至連表關聯都沒有，但是它卻引發了很嚴重的效能問題。它之所以加上條件id>1是因為業務邏輯要求這個sql的id必須大於1，雖然絕大部分情況下輸入的變數值也會大於1，但開發人員擔心使用者輸入小於1的值，導致資料錯誤。
我剛拿到SQL的時候，直接在PL/SQL DEV中檢視執行計劃，計劃看起來一模一樣，想不通為什麼效能會這麼差，後來在SQLPLUS中檢視執行計劃，才發現其中的端倪。

首先我們看select count(1) from test where id>1 and id>:v，它的執行計劃是範圍分割槽掃描，但是我們看到pstart=2，pstop=10，那意味著雖然我們輸入的變數是9，但這個SQL仍然要掃描9個分割槽。
也就是在生成執行計劃的時候，用的是id>1這個條件而忽略id>:v這個更好的過濾條件。掃描太多不必要的分割槽從而導致效能很差。
從第一個SQL看，繫結變數窺視並不起作用。

再看第二個SQL：select count(1) from test where id>:v，這個SQL對應的pstart=key，pstop=10，這說明掃描的分割槽個數不確定，依輸入的變數值而定。

最後看第三個SQL：select count(1) from test where id>:v and id>1，神奇發生了，它和第二個SQL只是條件的順序發生了變化，但是它的效能要好很多，主要原因是pstart=key，pstop=10，我們這裡輸入的變數值是9，意味著它只需要掃描2個分割槽，效率和第二種情況一樣。

現象貌似很奇怪，但實際上是可以解釋的。首先這個表很簡單，沒有索引，所以優化器在選擇執行計劃時可選計劃並不多。優化器認為where中的兩個條件是平等的，這種情況下，它生成執行計劃時並不考慮變數值(沒有使用繫結變數窺視)，在RBO下，兩個平等的條件會優先考慮靠後的條件，而在CBO下更好相反，會優先考慮更靠近where關鍵字的條件。
這些SQL都是基於CBO的，
第一個SQL，id>1靠近where關鍵字，因此優先考慮的是id>1，所以pstart=1
第三個SQL，id>:v靠近where關鍵字，因此優先考慮的是id>:v，所以pstart=key。

從這個結論可以推測，如果變數:v<=1，則第一個SQL比第三個SQL更高效，因為這種情況下它掃描更少分割槽，大家有興趣的可以去驗證一下。


這個文章重點是為了說明如何去分析這些問題，當你對一些優化的基本知識都有所瞭解時，看似很詭異的問題也就豁然開朗了。

最後，這個SQL的解決辦法是把把條件id>:v放在靠近where的位置，而把id>1放在更後面的位置。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-1035616/，如需轉載，請註明出處，否則將追究法律責任。

相關文章