[20231210]执行计划与绑定变量.txt

[20231210]执行计划与绑定变量.txt --//以前看执行计划忽略一个细节,就是在cursor_sharing=force的情况下,执行计划看不到以常量带入的绑定变量值. --//通过测试说明问题. 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试: SCOTT@test01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@test01p> alter session set cursor_sharing=force ; Session altered. SCOTT@test01p> variable a varchar2(32); SCOTT@test01p> exec :a := 'aaa'; PL/SQL procedure successfully completed. SCOTT@test01p> select * from dept where deptno= 10 and dname=:a; no rows selected SCOTT@test01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  a83u3aa69m4n9, child number 0 ------------------------------------- select * from dept where deptno=:"SYS_B_0" and dname=:a Plan hash value: 2852011669 --------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |      1 |        |       |     1 (100)|          |      0 |00:00:00.01 |       2 | |*  1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |    20 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 | |*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 | --------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1    2 - SEL$1 / DEPT@SEL$1 Peeked Binds (identified by position): --------------------------------------    2 - :2 (VARCHAR2(30), CSID=852): 'aaa' Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("DNAME"=:A)    2 - access("DEPTNO"=:SYS_B_0) 31 rows selected. --//注意Peeked Binds (identified by position):,仅仅抓取到:a变量的值,deptno =10转换为deptno=:"SYS_B_0"值并没有抓取到. --//要查看可以执行如下,查询v$sql_bind_capture视图: SCOTT@test01p> @bind_cap a83u3aa69m4n9 '' SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING ------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- --------------- a83u3aa69m4n9            0 YES :SYS_B_0                      1         22 2023-12-10 21:28:57 NUMBER          10                            YES :A                            2         32 2023-12-10 21:28:57 VARCHAR2(32)    aaa 3.继续： --//上班补充一些测试： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> alter session set cursor_sharing = force; Session altered. SCOTT@book> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book> variable a varchar2(30) SCOTT@book> exec :a := 'aaa'; PL/SQL procedure successfully completed. SCOTT@book> select 10,:a from dual ;         10 :A ---------- --------------------------------         10 aaa SCOTT@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  4qtudv4zxz44w, child number 0 ------------------------------------- select :"SYS_B_0",:a from dual Plan hash value: 1388734953 ------------------------------------------------------------------------------------------------- | Id  | Operation        | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |      |      1 |        |     2 (100)|          |      1 |00:00:00.01 | |   1 |  FAST DUAL       |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 | ------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DUAL@SEL$1 18 rows selected. --//select部分涉及到绑定变量也无法抓取。 SCOTT@book> @ bind_cap 4qtudv4zxz44w '' no rows selected SELECT sql_id         ,child_number         ,was_captured         ,name         ,position         ,max_length         ,last_captured         ,datatype_string         ,DECODE          (             datatype_string            ,'DATE', TO_CHAR ( TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss') ,'yyyy/mm/dd hh24:mi:ss')            ,'TIMESTAMP', TO_CHAR ( ANYDATA.accesstimestamp (value_anydata) ,'yyyy/mm/dd hh24:mi:ss.ff9')            ,value_string          )             value_string     --        decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30     FROM v$sql_bind_capture    WHERE     sql_id = '4qtudv4zxz44w' --         AND was_captured = 'YES'          AND DUP_POSITION IS NULL          AND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%') ORDER BY child_number, was_captured, position; SQL_ID        CHILD_NUMBER WAS NAME       POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING ------------- ------------ --- -------- ---------- ---------- ------------------- --------------- ------------ 4qtudv4zxz44w            0 NO  :SYS_B_0          1         22                     NUMBER 4qtudv4zxz44w            0 NO  :A                2         32                     VARCHAR2(32) --//was_captured = 'NO' 4.简单总结： --//select 部分也不会抓取,使用绑定变量或者常量都不会. --//在cursor_sharing=force的情况下.在谓词的常量转换为SYS_B_N的不会抓取显示在执行计划,但是v$sql_bind_capture可以查询到。 5.附上bind_cap脚本: set verify off column value_string format a50 column datatype_string format a15 break on sql_id on child_number  skip 1 select  replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1; SELECT sql_id         ,child_number         ,was_captured         ,name         ,position         ,max_length         ,last_captured         ,datatype_string         ,DECODE          (             datatype_string            ,'DATE', TO_CHAR ( TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss') ,'yyyy/mm/dd hh24:mi:ss')            ,'TIMESTAMP', TO_CHAR ( ANYDATA.accesstimestamp (value_anydata) ,'yyyy/mm/dd hh24:mi:ss.ff9')            ,value_string          )             value_string     --        decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30     FROM v$sql_bind_capture    WHERE     sql_id = '&1'          AND was_captured = 'YES'          AND DUP_POSITION IS NULL          AND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%') ORDER BY child_number, was_captured, position; clear break