My Oracle SQL Tuning Roadmap PART3

接PART2：My Oracle SQL Tuning Roadmap PART2

三SQL优化案例 

看第一个获取待优化的SQL，如果主动优化，一般从AWR、ASH等里面找到性能差的SQL，然后优化之。

看一个案例，占CPU 72%的SQL来自于同一模块，第一行是存储过程，通过下面绿色框住的SQL与第一行比较，主要通过EXECUTION，基本判断下面的绿色框住的SQL就是那个存储过程中的。也可以和业务确认下，OK，这些SQL的执行频次很高，因为是营销业务，如果要优化，就得搞定这些SQL。

这些SQL，单条SQL的buffer gets也就1000多点，效率还是很高的，但是因为执行的太过于频繁，所以资源消耗极大，因此，得检查下，能不能更优呢？

以第1条SQL：58q183atbusat为例：

SELECT B.ACT_ID,

B.ACT_NAME,

B.TASK_ID,

B.MKT_DICTION,

B.CUST_GROUP_ID,

NVL(B.ATTEST_FLAG, 'N'),

NVL(B.DOUWIN_FLAG, 'N'),

B.CHN_DESC,

NVL(B.SIGN_FLAG, 'N'),

B.MAX_EXECUTE_NUM

FROM (SELECT DISTINCT (ACT_ID)

FROM MK_RULECHN_REL

WHERE CHN_STATUS = '04'

AND CHN_TYPE = :B1) A,

TABLE(CAST(:B2 AS TYPE_MK_ACTIONINFO_TABLE)) B

WHERE A.ACT_ID = B.ACT_ID

SQL其实很简单，一个查询构建的A表，一个TABLE函数构建的B表关联，不知道大家对这个TABLE函数熟悉不熟悉？也就是将一个集合转成表，是PL/SQL里的东西

那个collection部分就是TABLE函数，下面的表走了全表扫描：

按步骤检查，发现不了问题，但是知道，可能是因为HASH JOIN导致全表扫描的问题，是否走NESTED LOOPS+INDEX更好，很显然，要检查TABLE函数大概返回多少行。

经过业务确认，最多也就返回200-300行，最终结果集也是几百行而已。

那么猜测，问题就在于TABLE函数，走了HASH JOIN，上面的执行计划，TABLE函数部分，ROWS为空。

来单独检查一把：返回8168行，返回8000多行，足以导致走HASH JOIN了，而事实，我们至多返回200-300行：

所以每个步骤返回的行，是JOIN方式选择的重要因素，可以谷歌一把，TABLE函数返回8168就是个固定值，block_size=8K的时候就是这么大，可以说，这是ORACLE的一个限制。

只要你用了TABLE函数，就偏向于走HASH JOIN了

http://www.oracle-developer.net/display.php?id=427 有兴趣的可以看这个链接的内容。

解决方式很多了，也就是要走NESTED LOOPS+index, 既然8168很大，那么我们就让优化器知道TABLE函数返回的行少点，才百行左右。

以下些都可以，当然也可以使用hint:use_nl等

CARDINALITY hint (9i+) undocumented;

OPT_ESTIMATE hint (10g+) undocumented;

DYNAMIC_SAMPLING hint (11.1.0.7+);

Extensible Optimiser (10g+).

因为SQL的SELECT部分只访问B，全部来自于TABLE函数，所以改写为子查询就可以了，使用子查询，自然distinct也就没有必要了，因为是semi join(半连接）。

最终改写使用cardinality hint让优化器知道B返回的行只有100行，你给我走NESTED LOOPS+INDEX，然后解决。

原来的sql：

SELECT B.ACT_ID,

       B.ACT_NAME,

       B.TASK_ID,

       B.MKT_DICTION,

       B.CUST_GROUP_ID,

       NVL(B.ATTEST_FLAG, 'N'),

       NVL(B.DOUWIN_FLAG, 'N'),

       B.CHN_DESC,

       NVL(B.SIGN_FLAG, 'N'),

       B.MAX_EXECUTE_NUM

  FROM (SELECT DISTINCT (ACT_ID)

          FROM MK_RULECHN_REL

         WHERE CHN_STATUS = '04'

           AND CHN_TYPE = :B1) A,

 TABLE(CAST(:B2 AS TYPE_MK_ACTIONINFO_TABLE)) B

 WHERE A.ACT_ID = B.ACT_ID

修改后的sql：

SELECT/*+cardinality(b 100)*/  B.ACT_ID, B.ACT_NAME, B.TASK_ID, B.MKT_DICTION, B.CUST_GROUP_ID,NVL(B.ATTEST_FLAG,'N'), NVL(B.DOUWIN_FLAG,'N'), B.CHN_DESC, NVL(B.SIGN_FLAG,'N'),B.MAX_EXECUTE_NUM

FROM  TABLE(CAST(:B2 AS TYPE_MK_ACTIONINFO_TABLE)) B

  WHERE EXISTS(SELECT 1 FROM MK_RULECHN_REL A

   WHERE A.CHN_STATUS = '04'

   AND A.CHN_TYPE = :B1

   AND A.ACT_ID = B.ACT_ID

   );

效率提升几十倍：

一个占72%的应用，我们提升几十倍后，那对系统性能明显是极好的。最终，在执行次数增加50%的情况下，主机CPU使用率由原来的高峰期平均47%的使用率降低为23%。

这个问题能够解决有两个方面：

1、猜测并测试优化器的限制（table函数固定返回行8168)；

2、实际返回的行200-300。

两者缺一不可。如果实际返回的行就是几千上万，那么，单纯通过优化SQL，也是无法取得良好效果的。