gather_plan_statistics查看sql的join部分的內存消耗

遇見一個sql語句，感覺驅動表的順序選擇有問題，就倒騰了一會兒，具體的sql語句如下，這里推薦使用gather_plan_statistics來查看具體的每個執行計劃消耗的IO資源、執行時間、預估和實際返回的rows。 SQL_ID dq4pj5cnn0gb8, child number 0 -----------------

遇見一個sql語句，感覺驅動表的順序選擇有問題，就倒騰了一會兒，具體的sql語句如下，這里推薦使用gather_plan_statistics來查看具體的每個執行計劃消耗的IO資源、執行時間、預估和實際返回的rows。

SQL_ID dq4pj5cnn0gb8, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/a.SERVNUMBER, a.REGION from
tbcs.SUBS_USEDTEL a, tbcs.CS_SUBS_SERVNUMBER_TRANS b where a.SUBSID =
b.TRANSIN_SUBSID and a.REGION = b.TRANSIN_REGION and a.INTIME >
sysdate - 90 and a.RECDEFID in ('DropSubs', 'FraudDropSubs') and
a.REGION = 20

Plan hash value: 2146127278

-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:01.08 | 19453 | | | |
|* 1 | HASH JOIN | | 1 | 4749 | 100 |00:00:01.08 | 19453 | 24M| 3319K| 25M (0)|
| 2 | PARTITION RANGE SINGLE| | 1 | 4749 | 374K|00:00:00.83 | 17257 | | | |
|* 3 | TABLE ACCESS FULL | SUBS_USEDTEL | 1 | 4749 | 374K|00:00:00.66 | 17257 | | | |
|* 4 | TABLE ACCESS FULL | CS_SUBS_SERVNUMBER_TRANS | 1 | 13477 | 8795 |00:00:00.05 | 2196 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------

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

1 - access("A"."SUBSID"="B"."TRANSIN_SUBSID" AND "A"."REGION"="B"."TRANSIN_REGION")
3 - filter(("A"."REGION"=20 AND INTERNAL_FUNCTION("A"."RECDEFID") AND "A"."INTIME">SYSDATE@!-90))
4 - filter("B"."TRANSIN_REGION"=20)

這里cbo在執行計劃3中預估SUBS_USEDTEL通過謂詞條件返回的數據只有4749，而實際返回了374K數據，初步來看這個sql應該交換下驅動表的順序，讓CS_SUBS_SERVNUMBER_TRANS去做驅動表。

SQL_ID 8px917y6cub58, child number 0
-------------------------------------
select /*+ gather_plan_statistics leading(b a) */
a.SERVNUMBER, a.REGION
from tbcs.SUBS_USEDTEL a, tbcs.CS_SUBS_SERVNUMBER_TRANS b
where a.SUBSID = b.TRANSIN_SUBSID
and a.REGION = b.TRANSIN_REGION
and a.INTIME > sysdate - 90
and a.RECDEFID in ('DropSubs', 'FraudDropSubs')
and a.REGION = 20

Plan hash value: 2680037744

-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 346 |00:00:00.66 | 20281 | | | |
|* 1 | HASH JOIN | | 1 | 4749 | 346 |00:00:00.66 | 20281 | 1998K| 1998K| 2083K (0)|
|* 2 | TABLE ACCESS FULL | CS_SUBS_SERVNUMBER_TRANS | 1 | 13477 | 14135 |00:00:00.06 | 3024 | | | |
| 3 | PARTITION RANGE SINGLE| | 1 | 4749 | 374K|00:00:00.78 | 17257 | | | |
|* 4 | TABLE ACCESS FULL | SUBS_USEDTEL | 1 | 4749 | 374K|00:00:00.61 | 17257 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------

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

1 - access("A"."SUBSID"="B"."TRANSIN_SUBSID" AND "A"."REGION"="B"."TRANSIN_REGION")
2 - filter("B"."TRANSIN_REGION"=20)
4 - filter(("A"."REGION"=20 AND INTERNAL_FUNCTION("A"."RECDEFID") AND "A"."INTIME">SYSDATE@!-90))

我們添加了hint lleading(b a)強制指定關聯順序，在整個sql消耗的邏輯讀其實是沒多大的變化，其實這里主要需要普及的一個知識點就是hash join的關聯cbo是不會計算到邏輯讀的。

那么這兩個sql好像IO成本每多大的變化啊，但是我們觀察OMem、1Mem、Used-Mem三項是有顯著變化的，這里簡單解釋下這三個指標的信息
OMem為最優執行模式所需的內存評估值
1Mem為one-pass模式所需的內存評估值
Used-Mem則為實際執行時消耗的內存，而且我們還看見25M (0)和2083K (0)都有一個括號0，這個表示該sql是最優執行模式執行的

可以看出制定了正確的驅動表可以大幅度的減輕系統的內存消耗，這里也提供了我們一個思路就是優化sql時不能僅僅去關注IO資源，還要關注下內存的消耗，通過gather_plan_statistics可以很直觀的觀察到sql執行時join關聯部分的內存消耗，

oracle官當對于memstats的解釋（allstats=iostats+memstats的組合）：

?MEMSTATS – Assuming that PGA memory management is enabled (that is,pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators.

這個used-men和v$sql或者v$sqlarea的視圖記錄內存消耗的列是不相同的，used-mem是執行sql部分join消耗的pga內存部分，而v$sql或者v$sqlarea記錄的是cursor的信息

sharable_mem：Amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of all shared memory used by all child cursors.
persistent_mem：Fixed amount of memory used for the lifetime of an open cursor. If multiple child cursors exist, then the fixed sum of memory used for the
lifetime of all the child cursors.
runtime_mem：Fixed amount of memory required during execution of a cursor. If multiple child cursors exist, then the fixed sum of all memory required
during execution of all the child cursors.

這里我們需要注意的時優化sql時不能僅僅只是以邏輯讀去衡量某個sql的性能，對于用戶而言我們肯定是最關注sql的響應時間，我們優化IO、減少內存和cpu消耗等都是為了讓執行sql時做盡可能少的事情，進而提高sql的響應時間。

本文出自：http://www.dbaxiaoyu.com, 原文地址：http://www.dbaxiaoyu.com/archives/2391, 感謝原作者分享。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com