开发突然说一条sql执行的巨慢无比，大概要不到20分钟才能出来，需要紧急优化一下。

Sql_id: a491fmrds02ra

处理过的Sql文本：

SELECT
        MESSNAME，
        CASE WHEN ……
    FROM (SELECT
                 T1.SECURITYCODE,
                 T2.SECURITYVARIETYCODE,
                 T4.INDTCODE ,
                 T5.COMPANYCODE AS COMPCODE,
                 NVL(T4.INSTSNAME,T4.INSTNAME) AS INSTSNAME,
              CASE
                   WHEN ……
            FROM (
                 SELECT * FROM NSA.XXX_INMESSDATA WHERE MESSNAME IN ('营业收入','净利润','净资产','净资本')
               AND SECURITYCODE=000728 AND COMPANYCODE=10001043
               ) T1
            JOIN
             (
      SELECT SECURITYCODE, SECURITYVARIETYCODE
        FROM NSA.XXX_PUBLISHSTOCK
       WHERE PUBLISHCODE = '011019001001'
            ) T2
          ON T1.SECURITYCODE = T2.SECURITYCODE
        JOIN NSA.XXX_ORGBASEINFO T4
          ON T1.COMPANYCODE=T4.INDTCODE
        JOIN NSA.XXX_SECUCODE T5
                          ON T2.SECURITYVARIETYCODE=T5.SECURITYVARIETYCODE
         )

简化一下sql以看清逻辑：
SELECT
     ...
            FROM (SELECT
             ...
        FROM (
             SELECT * FROM NSA.XXX_INMESSDATA WHERE MESSNAME IN ('营业收入','净利润','净资产','净资本')
           AND SECURITYCODE='000728' AND COMPANYCODE='10001043' --428条数据
           ) T1
        JOIN
         (
SELECT SECURITYCODE, SECURITYVARIETYCODE
    FROM NSA.XXX_PUBLISHSTOCK
   WHERE PUBLISHCODE = '011019001001'      --37条数据
        ) T2 --申万上市证券公司
      ON T1.SECURITYCODE = T2.SECURITYCODE
    JOIN NSA.XXX_ORGBASEINFO T4
      ON T1.COMPANYCODE=T4.INDTCODE
    JOIN NSA.XXX_SECUCODE T5
      ON T2.SECURITYVARIETYCODE=T5.SECURITYVARIETYCODE
);

可以看到是T1,T2,T4,T5四张表的连接查询。其中T1，T2是子查询。

表	表	关联字段
T1	T2	T1.SECURITYCODE = T2.SECURITYCODE
T1	T4	T1.COMPANYCODE=T4.INDTCODE
T2	T5	T2.SECURITYVARIETYCODE=T5.SECURITYVARIETYCODE

该sql的执行计划：

Plan hash value: 3670362274

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 29658 (100)| |

|* 1 | HASH JOIN | | 1 | 226 | 29658 (1)| 00:08:54 |

|* 2 | HASH JOIN | | 1 | 163 | 21653 (1)| 00:06:30 |

|* 3 | TABLE ACCESS FULL | XXX_PUBLISHSTOCK | 2 | 80 | 15159 (1)| 00:04:33 |

| 4 | MERGE JOIN CARTESIAN | | 1596 | 191K| 6494 (1)| 00:01:57 |

|* 5 | TABLE ACCESS BY INDEX ROWID| XXX_INMESSDATA | 1 | 73 | 4 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | PK_LG_XXX_INMESSDATA | 4 | | 2 (0)| 00:00:01 |

| 7 | BUFFER SORT | | 1350K| 64M| 6490 (1)| 00:01:57 |

| 8 | TABLE ACCESS FULL | XXX_SECUCODE | 1350K| 64M| 6490 (1)| 00:01:57 |

|* 9 | TABLE ACCESS FULL | XXX_ORGBASEINFO | 8761 | 539K| 8004 (1)| 00:02:25 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("XXX_INMESSDATA"."COMPANYCODE"=SYS_OP_C2C("T4"."INDTCODE"))

2 - access("SECURITYVARIETYCODE"=SYS_OP_C2C("T5"."SECURITYVARIETYCODE") AND

"XXX_INMESSDATA"."SECURITYCODE"=SYS_OP_C2C("SECURITYCODE"))

3 - filter(("PUBLISHCODE"='011019001001' AND SYS_OP_C2C("SECURITYCODE")=SYS_OP_C2C(:1)))

5 - filter("SECURITYCODE"=SYS_OP_C2C(:1))

6 - access("COMPANYCODE"=SYS_OP_C2C(:2))

filter(("MESSNAME"=U'\51C0\5229\6DA6' OR "MESSNAME"=U'\51C0\8D44\4EA7' OR

"MESSNAME"=U'\51C0\8D44\672C' OR "MESSNAME"=U'\8425\4E1A\6536\5165'))

9 - filter(SYS_OP_C2C("T4"."INDTCODE")=SYS_OP_C2C(:2))

从执行计划中可以看到XXX_INMESSDATA和XXX_SECUCODE走排序合并连接，结果集再与XXX_PUBLISHSTOCK走哈希连接，最后结果集与XXX_ORGBASEINFO走哈希连接。

这里最大的问题就是因为统计信息不准使得T1和T5走排序合并连接，根据后面查询的数据量和sql逻辑来看，猜测最好的执行计划应该是T4和T5相关的表连接走哈希连接。

也就是T1，T2排序合并连接或者哈希连接之后的结果集与T4走哈希连接，结果集再与T5走哈希连接。

连接顺序为T1àT2àT4àT5或者T2àT1àT4àT5。

所以如果执行计划不遂人愿的话，可以选择leading(T1 T2)这个hint，当然目前只是猜测。

先解决NSA.XXX_PUBLISHSTOCK全表扫描：

create index NSA.XXX_PUBLISHSTOCK_IDX_01 ON NSA.XXX_PUBLISHSTOCK(PUBLISHCODE) ONLINE PARALLEL 4;

ALTER INDEX NSA.XXX_PUBLISHSTOCK_IDX_01 NOPARALLEL;

查询T1和T2的结果集：

SQL> SELECT count(*)

2 FROM NSA.XXX_PUBLISHSTOCK

3 WHERE PUBLISHCODE = '011019001001';

COUNT(*)

----------

37

SELECT count(*) FROM NSA.XXX_INMESSDATA WHERE MESSNAME IN ('营业收入','净利润','净资产','净资本')

AND SECURITYCODE=000728 AND COMPANYCODE=10001043

COUNT(*)

----------

428

SQL> select count(*) from NSA. XXX _ORGBASEINFO;

COUNT(*)

----------

8785461

SQL> select count(*) from NSA. XXX _SECUCODE;

COUNT(*)

----------

1361088

对比执行计划可以看到caidinality值偏低，所以检查统计信息是否准确：

NSA.XXX_INMESSDATA表：

OWNER PARTNAME NROWS BLOCKS AVGSPC CCNT ROWLEN SSIZE ANADATE

---------- ------------------------------ ---------- ---------- ------ ---- ------ -------- -------------------

NSA 228757 1210 0 0 144 228757 2018-03-15 22:03:14

SQL> select count(*) from NSA.XXX_INMESSDATA;

COUNT(*)

----------

266492

NSA.XXX_PUBLISHSTOCK表：

OWNER PARTNAME NROWS BLOCKS AVGSPC CCNT ROWLEN SSIZE ANADATE

---------- ------------------------------ ---------- ---------- ------ ---- ------ -------- -------------------

NSA 3985126 29362 0 0 190 3985126 2018-06-08 22:42:31

SQL> select count(*) from NSA.XXX_PUBLISHSTOCK;

COUNT(*)

----------

3994198

可以看到NSA.XXX_INMESSDATA表统计信息较旧。收集统计信息：

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'NSA',tabname=>'XXX_INMESSDATA',estimate_percent=>10,no_invalidate=>false,cascade=>true,degree => 10);

Can you confirm?[y/n]

y

BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname=>'NSA',tabname=>'XXX_INMESSDATA',estimate_percent=>10,no_invalidate=>false,cascade=>true,degree => 10); END;

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 24281

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 1

统计信息竟然被锁住了。这种历史遗留问题就暂且不去追究了，先解决当下sql慢的问题。为了尽量减小未知影响，暂时不解锁统计信息。

在上一层查询中加hint纠正cardinality值：

/*+cardinality(T1 425) cardinality(T2 35)*/

此时的执行计划：

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1617115992

--------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 23 | 5198 | 14603 (1)| 00:04:23 |

|* 1 | HASH JOIN | | 23 | 5198 | 14603 (1)| 00:04:23 |

|* 2 | HASH JOIN | | 10 | 1760 | 8106 (1)| 00:02:26 |

|* 3 | HASH JOIN | | 4 | 452 | 112 (0)| 00:00:03 |

|* 4 | TABLE ACCESS BY INDEX ROWID| XXX_PUBLISHSTOCK | 35 | 1400 | 108 (0)| 00:00:02 |

|* 5 | INDEX RANGE SCAN | XXX_PUBLISHSTOCK_IDX_01 | 236 | | 3 (0)| 00:00:01 |

|* 6 | TABLE ACCESS BY INDEX ROWID| XXX_INMESSDATA | 425 | 31025 | 4 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN | PK_LG_XXX_INMESSDATA | 4 | | 2 (0)| 00:00:01 |

|* 8 | TABLE ACCESS FULL | XXX_ORGBASEINFO | 8770 | 539K| 7994 (1)| 00:02:24 |

| 9 | TABLE ACCESS FULL | XXX_SECUCODE | 1356K| 64M| 6490 (1)| 00:01:57 |

--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("SECURITYVARIETYCODE"=SYS_OP_C2C("T5"."SECURITYVARIETYCODE"))

2 - access("XXX_INMESSDATA"."COMPANYCODE"=SYS_OP_C2C("T4"."INDTCODE"))

3 - access("XXX_INMESSDATA"."SECURITYCODE"=SYS_OP_C2C("SECURITYCODE"))

4 - filter(SYS_OP_C2C("SECURITYCODE")=U'000728')

5 - access("PUBLISHCODE"='011019001001')

6 - filter("SECURITYCODE"=U'000728')

7 - access("COMPANYCODE"=U'10001043')

filter("MESSNAME"=U'\51C0\5229\6DA6' OR "MESSNAME"=U'\51C0\8D44\4EA7' OR

"MESSNAME"=U'\51C0\8D44\672C' OR "MESSNAME"=U'\8425\4E1A\6536\5165')

8 - filter(SYS_OP_C2C("T4"."INDTCODE")=U'10001043')

此时的sql执行已经秒出了。

本以为到这里就结束了，但是没想到随手又执行了一遍发现再次变得非常慢了。

然后我将优化后的sql多加了个空格后执行，秒出。再次执行，又无比缓慢了。

开启10046追踪sql执行：

开启Trace

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

关闭Trace

ALTER SESSION SET EVENTS '10046 trace name context off';

Trace文件：

很多的direct path read temp;

Tkprof处理过的trace文件：

第二次还是硬解析，执行计划也变了：

将上面的sql id取出，查询v$sql_shard_cursor视图：

两次执行时确实生成了两个child cursor，不同之处就在于标红处的use_feedback_stats，第一次时是Y，第二次是N。

Cardinality Feedback基数反馈是版本11.2中引入的新特性。Cardinality feedback基数反馈是11gR2版本引入的的特性，在12c版本的被改名为Statistics Feedback统计信息反馈。对于CBO而言，统计信息的准确性对CBO选择执行计划的影响几乎是决定性的。统计信息对cardinality的计算值又有很大关系，这个值直接影响成本估算。而统计信息陈旧，数据分布不均匀，直方图不恰当等原因，都可能导致计算出的cardinality值有误差，进而影响执行计划。这个特性就是为了解决这个问题引入的。它可以根据比较上一次sql执行时实际的cardinality值和估算值，如果误差大的话会再下一次执行sql时做出调整。调整的结果，很可能会使执行计划改变。这个特性在生产系统上往往是不友好的，而且MOS上一堆BUG。可以关闭这个特性。

启用该特性后，在执行计划note部分会有：

Note

-----

- cardinality feedback used for this statement

使用hint强制关闭该特性：opt_param(‘_optimizer_use_feedback’ ‘false’) HINT

之后一切正常。

优化后的sql：

SELECT /*+ opt_param('_optimizer_use_feedback' 'false') */

……

FROM (SELECT /*+cardinality(T1 425) cardinality(T2 35)*/

……

FROM (

SELECT * FROM NSA.XXX_INMESSDATA WHERE MESSNAME IN ('营业收入','净利润','净资产','净资本')

AND SECURITYCODE='000728' AND COMPANYCODE='10001043'

) T1

JOIN

(

SELECT SECURITYCODE, SECURITYVARIETYCODE

FROM NSA.XXX_PUBLISHSTOCK

WHERE PUBLISHCODE = '011019001001'

) T2

ON T1.SECURITYCODE = T2.SECURITYCODE

JOIN NSA.XXX_ORGBASEINFO T4

ON T1.COMPANYCODE=T4.INDTCODE

JOIN NSA.XXX_SECUCODE T5

ON T2.SECURITYVARIETYCODE=T5.SECURITYVARIETYCODE

) ;

ITPub博客

oracle sql优化之基数反馈（Cardinality feedback）问题

最新文章