SQL調優公式T＝S/V (zt)

tolywang發表於2008-01-24
SQL公式

http://www.itpub.net/viewthread.php?tid=931723&extra=&page=2

S指sql所需訪問的資源總量，V指sql單位時間所能訪問的資源量，T自然就是SQL執行所需時間了

這個公式很簡單，調優的目標是減少T，T＝S/V，那麼我們要調的物件自然就要放到這裡的S和V上，調優的方法就是圍繞減少S和增大V

增大V（單位時間所能訪問的資源量），在硬體裝置不變的情況下所能做的只有充分利用現有資源，如：透過調整SGA充分利用物理MEMORY，透過並行處理充分利用CPU，透過調整IO分佈充分利用硬碟處理能力，還有。。。。升級裝置？

[@more@]

減少S（sql所需訪問的資源總量），這通常是調優工作的重中之重，SQL調優的主要目的就是圍繞著如何減少S在進行，在ORACLE中，所需訪問的資源以block記，一條SQL執行所讀寫的block數直接影響到SQL的執行時間，如何知道SQL執行所讀寫的block數呢，簡單的方法如下：
set autotrace traceonly
SQL> select count(*) from test;

已用時間: 00: 00: 10.01

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

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19342 (1)| 00:03:53 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 5285K| 19342 (1)| 00:03:53 |
-------------------------------------------------------------------


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

透過上面的資訊我們可以看到此SQL訪問的block數為70655，並且基本上是物理讀，其執行時間為00: 00: 10.01

大家可能一眼就看出來了，這條SQL執行了全表掃描，加索引最佳化就可以了，沒錯，索引訪問正是減少SQL所需訪問資源的一個主要途徑
其效果也很明顯
已用時間: 00: 00: 01.89

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

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST1 | 5285K| 3089 (2)| 00:00:38 |
-------------------------------------------------------------------------


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

其訪問的block數變為11218，其執行時間隨之變為00: 00: 01.89

透過減少S，我們可以看到T得到明顯的減小

上面情況是在V不變的情況下（都是物理讀）的差別

再看看V最大化的結果

SQL> select count(*) from test;

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

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST1 | 5285K| 3089 (2)| 00:00:38 |
-------------------------------------------------------------------------

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

已用時間: 00: 00: 00.79

SQL> select /*+parallel(test 2)*/ count(*) from test;



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

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST1 | 5285K| 3089 (2)| 00:00:38 |
-------------------------------------------------------------------------


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

已用時間: 00: 00: 00.68

T被控制到了秒以下。

可是對於這樣的一個業務11218個block的資源需求量是否也不是很合理呢，如果這個業務發生的很頻繁，每次執行都需要重複掃描到相同資源並且這部分資源佔總資源的絕大部分，這肯定也是很不合理的。
既然是減少S，那麼減少這些重複訪問的絕大部分資源理應得到更好的效果。
以上面的業務為例：
本業務的需求是實時統計表內的行數，資料都是具有生命週期的，通常情況下一個業務表內的資料大多是處於不活動狀態，如此以來，預先統計好這部分資料並形成結果，每次需要統計這部分資料時直接呼叫結果必將大幅減少業務所需訪問的資源
如本例，已知object_id小於等於13000的資料處於不活動狀態
構建分析結果表
create table test_analyzed as select count(*) o_count，13000 as o_data from test where object_id<=13000;

SQL> select o_count from test_analyzed;

O_COUNT
----------
5242624

已用時間: 00: 00: 00.00

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

SQL> select count(*) from test where object_id>13000;

COUNT(*)
----------
42624

已用時間: 00: 00: 00.01


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

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 159 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| I_TEST1 | 73774 | 360K| 159 (1)| 00:00:02 |
-----------------------------------------------------------------------------

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

2 - access("OBJECT_ID">13000)


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

S變成了3＋98＝101，T自然也就變成不到10毫秒了

當然現實工作中，業務需求不可能就可以如此簡單的加以分解，這裡只是提供一個思路

效能調優是產品全生命週期的工作，一條sql的調優很可能會追溯到產品需求的定義及產品設計等環節
而在調優工作中V的提升通常是不可持續的，一定程度的提升後再想有所提高是需要付出財力的，S的提升卻是很有潛力可挖的。

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

相關文章