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, 感谢原作者分享。