ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20210114]toad查看真实执行计划问题.txt

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

原创 Oracle 作者：lfree 时间：2021-01-14 09:24:53 1 删除 编辑

[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"的情况,
--//导致计算错误,无法获得真实的执行计划.

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2749843/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20210113]给PB开发人员的一个建议.txt
下一篇： [20210114]理解DBMS_SESSION.set_identifier.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969647

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593