[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