[20210114]toad查看真实执行计划问题.txt

[20210114]toad查看真实执行计划问题.txt --//昨天使用toad优化sql语句,我发现toad查看真实的执行计划出现一些怪异的问题,自己分析看看. 1.环境: SYS@192.168.XX.Y:1521/aaa430> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 2.分析: --//sql语句如下: SELECT /*+  gather_plan_statistics */         MS_CF01.FYCK AS XMDM2         ,COUNT (DISTINCT MS_CF01.CFSB) AS CFS     FROM YF_MZFYMX, MS_CF01    WHERE     (YF_MZFYMX.CFSB = MS_CF01.CFSB)          AND (MS_CF01.YFSB = 166 )          AND (MS_CF01.FYRQ >= TO_DATE ( '2020-12-01 00:00:00' ,'yyyy-mm-dd hh24:mi:ss'))          AND (MS_CF01.FYRQ <= TO_DATE ( '2020-12-31 00:00:00' ,'yyyy-mm-dd hh24:mi:ss'))                    AND EXISTS                 (  SELECT /*+ unnest */ CFSB, YPXH, SUM (YPSL)                      FROM YF_MZFYMX                     WHERE     FYRQ >= TO_DATE ( '2020-12-01 00:00:00' ,'yyyy-mm-dd hh24:mi:ss')                           AND FYRQ <= TO_DATE ( '2020-12-31 00:00:00' ,'yyyy-mm-dd hh24:mi:ss')                           AND YFSB = 166             --            AND YF_MZFYMX.CFSB = MS_CF01.CFSB                  GROUP BY CFSB, YPXH                    HAVING SUM (YF_MZFYMX.YPSL) > 0) GROUP BY MS_CF01.FYCK ORDER BY MS_CF01.FYCK ASC; --//我带入了参数,注意开发少写了AND YF_MZFYMX.CFSB = MS_CF01.CFSB在exists内部.优化问题先放一下. --//在toad下执行,并且使用toad自带的SQL Tracker跟踪sql语句: --//在跟踪界面看到的内容如下: declare   v_ignore raw(100);   v_oldhash number;   v_hash number; begin   v_hash := dbms_utility.get_sql_hash(:SQLText || chr(0), v_ignore, v_oldhash);   :outHash := v_hash; end; SQLText=['SELECT /*+  gather_plan_statistics */         MS_CF01.FYCK AS XMDM2         ,COUNT (DISTINCT MS_CF01.CFSB) AS CFS     FROM YF_MZFYMX, MS_CF01    WHERE     (YF_MZFYMX.CFSB = MS_CF01.CFSB)          AND (MS_CF01.YFSB = 166 )          AND (MS_CF01.FYRQ >= TO] outHash=[0.145428042e+010] ~~~~~~~~~~~~~~~~~~~~~~~~~ Elapsed time: 0.002 -------------------------------------------------------------------------------- Timestamp: 2021/1/14 8:48:02 Select * from v$sql_plan Where hash_value = '1454280429' and child_number =0 order by id sqlhv=['1454280429'] cn=[0] Elapsed time: 0.002 -------------------------------------------------------------------------------- Timestamp: 2021/1/14 8:48:02 alter session set current_schema = PORTAL_HIS Elapsed time: 0.001 -------------------------------------------------------------------------------- Timestamp: 2021/1/14 8:48:02 explain plan set statement_id='Administrator:011421084802' into SYS.PLAN_TABLE$ For SELECT /*+  gather_plan_statistics */         MS_CF01.FYCK AS XMDM2         ,COUNT (DISTINCT MS_CF01.CFSB) AS CFS     FROM YF_MZFYMX, MS_CF01    WHERE     (YF_MZFYMX.CFSB = MS_CF01.CFSB)          AND (MS_CF01.YFSB = 166 )          AND (MS_CF01.FYRQ >= TO_DATE ( '2020-12-01 00:00:00' ,'yyyy-mm-dd hh24:mi:ss'))          AND (MS_CF01.FYRQ <= TO_DATE ( '2020-12-31 00:00:00' ,'yyyy-mm-dd hh24:mi:ss'))                    AND EXISTS                 (  SELECT /*+ unnest */ CFSB, YPXH, SUM (YPSL)                      FROM YF_MZFYMX                     WHERE     FYRQ >= TO_DATE ( '2020-12-01 00:00:00' ,'yyyy-mm-dd hh24:mi:ss')                           AND FYRQ <= TO_DATE ( '2020-12-31 00:00:00' ,'yyyy-mm-dd hh24:mi:ss')                           AND YFSB = 166             --              AND YF_MZFYMX.CFSB = MS_CF01.CFSB                  GROUP BY CFSB, YPXH                    HAVING SUM (YF_MZFYMX.YPSL) > 0) GROUP BY MS_CF01.FYCK ORDER BY MS_CF01.FYCK ASC Elapsed time: 0.093 -------------------------------------------------------------------------------- Timestamp: 2021/1/14 8:48:02 --//很明显跟踪看到的sql语句使用explain plan解析的,自然看不到A-rows信息. --//你可以看下划线内容,可以发现带入的SQLText仅仅是一部分,这样解析就不对了吗? SQLText=['SELECT /*+  gather_plan_statistics */         MS_CF01.FYCK AS XMDM2         ,COUNT (DISTINCT MS_CF01.CFSB) AS CFS     FROM YF_MZFYMX, MS_CF01    WHERE     (YF_MZFYMX.CFSB = MS_CF01.CFSB)          AND (MS_CF01.YFSB = 166 )          AND (MS_CF01.FYRQ >= TO] outHash=[0.145428042e+010] --//我开始以为是截取错误,仔细我想不大可能,我也写一个注解很长的语句,执行计划可以发现A-rows的情况. --//我扫描共享池才发现Hash Value=2826919549,与toad下计算结果不一样. SYS@192.168.XX.Y:1521/aaa430> select sql_text c200 from v$sql where hash_value=2826919549; C200 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT /*+  gather_plan_statistics */         MS_CF01.FYCK AS XMDM2         ,COUNT (DISTINCT MS_CF01.CFSB) AS CFS     FROM YF_MZFYMX, MS_CF01    WHERE     (YF_MZFYMX.CFSB = MS_CF01.CFSB)          AND (MS_CF01.YFSB = :"SYS_B_00" )          AND (MS_CF01.FYRQ >= TO_DATE ( :"SYS_B_01" ,:"SYS_B_02"))          AND (MS_CF01.FYRQ <= TO_DATE ( :"SYS_B_03" ,:"SYS_B_04"))                    AND EXISTS           (  SELECT /*+ unnest */ CFSB, YPXH, SUM (YPSL)                      FROM YF_MZFYMX                     WHERE     FYRQ >= TO_DATE ( :"SYS_B_05" ,:"SYS_B_06")                           AND FYR Q <= TO_DATE ( :"SYS_B_07" ,:"SYS_B_08")                           AND YFSB = :"SYS_B_09"             --              AND YF_MZFYMX.CFSB = MS_CF01.CFSB                  GROUP BY CFSB, YPXH         HAVING SUM (YF_MZFYMX.YPSL) > :"SYS_B_10") GROUP BY MS_CF01.FYCK ORDER BY MS_CF01.FYCK ASC --//噢,会不会我设置cursor_sharing=FORCE的缘故呢.马上在toad下执行: alter session set cursor_sharing=exact; --//然后在执行sql语句就可以获得正确的执行计划了.包含 Starts , A-Rows 信息,也就是使用dbms_xplan.display_cursor解析的执行 --//计划. SYS@192.168.XX.Y:1521/aaa430> show spparameter cursor_sharing SID      NAME                          TYPE       VALUE -------- ----------------------------- ---------- ------------- *        cursor_sharing                string --//嗯,并没有设置参数cursor_sharing在spfile里面,仔细检查系统触发器,发现如下代码: CREATE OR REPLACE TRIGGER SYS.LOGIN_ON_RECORD_IP    AFTER LOGON    ON DATABASE DECLARE    v_client_info   v$session.client_info%TYPE; BEGIN    v_client_info := SYS_CONTEXT ('userenv', 'ip_address');    DBMS_APPLICATION_INFO.set_client_info (v_client_info);    DBMS_SESSION.set_identifier (v_client_info);    EXECUTE IMMEDIATE 'alter session set cursor_sharing =force'; END; / --//顺便修改如下: CREATE OR REPLACE TRIGGER SYS.LOGIN_ON_RECORD_IP    AFTER LOGON    ON DATABASE DECLARE    v_client_info   v$session.client_info%TYPE; BEGIN    v_client_info := NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1');    DBMS_APPLICATION_INFO.set_client_info (v_client_info);    DBMS_SESSION.set_identifier (v_client_info);    EXECUTE IMMEDIATE 'alter session set cursor_sharing =force'; END; / --//很奇怪我发现跟踪文件并没有类似IP地址之类的信息.另外写一篇blog分析. SYS@192.168.XX.Y:1521/aaa430> @ pp TRACEFILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/aaa430/aaa430/trace/aaa430_ora_11757.trc 总结: --//总之造成这样情况的主要原因toad会话cursor_sharing=force,而toad获得hash vale并没有把常量变成:"SYS_B_00"的情况, --//导致计算错误,无法获得真实的执行计划.