Oracle+高效能SQL引擎剖析：SQL優化與調優機制詳解-筆記之執行計劃（二）

nathanzhn發表於2014-10-29
OracleSQL優化筆記
第2章 解讀執行計劃
執行計劃中的TempSpc/E-Temp，OMem，1Mem, Used-Mem, Used-Tmp之間有什麼關係嗎？我覺得看文字解釋不太好理解，我做了個實驗：
SQL> exec sql_explain('select * from t_objects order by 1,2,3,4,5,6,7,8', 'ADVANCED ALLSTATS LAST', FALSE);
before parse: select * from t_objects order by 1,2,3,4,5,6,7,8
SQL_ID  d1mc4vkc35231, child number 0
-------------------------------------
select * from t_objects order by 1,2,3,4,5,6,7,8
Plan hash value: 2389590244
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |        |       |       |  1937 (100)|          |       |    |     |
|   1 |  SORT ORDER BY     |           |  73993 |  7009K|  9712K|  1937   (1)| 00:00:24 |    10M|  1275K| 9874K (0)|
|   2 |   TABLE ACCESS FULL| T_OBJECTS |  73993 |  7009K|       |   296   (1)| 00:00:04 |       |    |     |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T_OBJECTS@SEL$1
理解其中的E-Temp, OMem, 1Mem, Used-Mem

E-row是CBO用統計資訊估計的行數， 差不多就是cardinality. A-row --是這一步真正返回的行數 --row source
E-TEMP--是估計要使用的臨時表空間。
The size of a work area can be controlled and tuned. Generally, bigger work areas  
can significantly improve the performance of a particular operator at the cost of 
higher memory consumption. Ideally, the size of a work area is big enough that it 
can accommodate the input data and auxiliary memory structures allocated by its 
associated SQL operator. This is known as the optimal size of a work area (e.g. 
a memory sort). When the size of the work area is smaller than optimal  
(e.g. a disk sort), the response time increases, because an extra pass is performed  
over part of the input data. This is known as the one-pass size of the work area.  
Under the one-pass threshold, when the size of a work area is far too small compared  
to the input data size, multiple passes over the input data are needed. This could  
dramatically increase the response time of the operator. This is known as the multi-pass  
size of the work area. 
optimal尺寸：SQL語句能夠完全在所分配的SQL工作區內完成所有的操作。這時的效能最佳。
onepass尺寸：SQL語句需要與磁碟上的臨時表空間互動一次才能夠在所分配的SQL工作區中完成所有的操作。
multipass尺寸：由於SQL工作區過小，從而導致SQL語句需要與磁碟上的臨時表空間互動多次才能完成所有的操作。這個時候的效能將急劇下降。
optimal -- OMem, 1-- one pass

我理解這個執行計劃完成排序需要使用的空間一共是Used-Mem+TempSpc。
那接下來要做的實驗就是把PGA大小擴大10M，再看看該執行計劃是否還會有TempSpc的值。

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

相關文章