當前位置：
首頁 > 知識 > 查看有綁定變數的SQL的綁定變數值

查看有綁定變數的SQL的綁定變數值

知識 01-09

設置一個綁定變數，執行SQL，查看執行計劃

SQL> var a varchar2(10);

SQL> exec :a:="TAB$";

PL/SQL procedure successfully completed.

SQL> select owner from dba_objects where object_name =:a;

OWNER

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

SYS

SQL> select * from table(dbms_xplan.display_cursor(null,null,"ALLSTATS"));

PLAN_TABLE_OUTPUT

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

SQL_ID dj138p4rmg730, child number 0

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

select owner from dba_objects where object_name =:a

Plan hash value: 300169427

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

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |

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

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 36 | | | |

| 1 | VIEW | DBA_OBJECTS | 1 | 3 | 1 |00:00:00.01 | 36 | | | |

| 2 | UNION-ALL | | 1 | | 1 |00:00:00.01 | 36 | | | |

|* 3 | FILTER | | 1 | | 1 |00:00:00.01 | 35 | | | |

|* 4 | FILTER | | 1 | | 1 |00:00:00.01 | 35 | | | |

| 5 | NESTED LOOPS | | 1 | 2 | 1 |00:00:00.01 | 35 | | | |

|* 6 | HASH JOIN | | 1 | 2 | 1 |00:00:00.01 | 32 | 1393K| 1393K| 1/0/0|

|* 7 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 2 | 1 |00:00:00.01 | 30 | | | |

|* 8 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 1 |00:00:00.01 | 29 | | | |

| 9 | INDEX FULL SCAN | I_USER2 | 1 | 89 | 90 |00:00:00.01 | 2 | | | |

| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 1 | 1 |00:00:00.01 | 3 | | | |

|* 11 | INDEX UNIQUE SCAN | I_USER# | 1 | 1 | 1 |00:00:00.01 | 1 | | | |

| 12 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |

|* 13 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |

|* 14 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |

| 15 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 1 | | | |

|* 16 | INDEX SKIP SCAN | I_LINK1 | 1 | 1 | 0 |00:00:00.01 | 1 | | | |

| 17 | TABLE ACCESS CLUSTER | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |

|* 18 | INDEX UNIQUE SCAN | I_USER# | 0 | 1 | 0 |00:00:00.01 | 0 | | | |

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

Predicate Information (identified by operation id):

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

3 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND

"O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND

"O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND

((SYS_CONTEXT("userenv","current_edition_name")="ORA$BASE" AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND

"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT("userenv","current_edition_id"))) OR IS NOT NULL))))

4 - filter((:A<>"_default_auditing_options_" AND :A<>"_NEXT_OBJECT"))

6 - access("O"."OWNER#"="U"."USER#")

7 - filter(BITAND("O"."FLAGS",128)=0)

8 - access("O"."NAME"=:A AND "O"."LINKNAME" IS NULL)

filter(("O"."NAME"=:A AND "O"."TYPE#"<>10 AND "O"."NAME"<>"_NEXT_OBJECT" AND "O"."NAME"<>"_default_auditing_options_"

AND "O"."LINKNAME" IS NULL))

11 - access("O"."SPARE3"="U"."USER#")

13 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT("userenv","current_edition_id")))

filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT("userenv","current_edition_id"))))

14 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

16 - access("L"."NAME"=:A)

filter("L"."NAME"=:A)

18 - access("L"."OWNER#"="U"."USER#")

52 rows selected.

SQL>

查看綁定變數值，並查看執行計劃

SQL> select * from table(dbms_xplan.display_cursor("dj138p4rmg730",format=>"PEEKED_BINDS"));

PLAN_TABLE_OUTPUT

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

SQL_ID dj138p4rmg730, child number 0

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

select owner from dba_objects where object_name =:a

Plan hash value: 300169427

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | 33 (100)| |

| 1 | VIEW | DBA_OBJECTS | 3 | 249 | 33 (0)| 00:00:01 |

| 2 | UNION-ALL | | | | | |

|* 3 | FILTER | | | | | |

|* 4 | FILTER | | | | | |

| 5 | NESTED LOOPS | | 2 | 248 | 32 (0)| 00:00:01 |

|* 6 | HASH JOIN | | 2 | 212 | 30 (0)| 00:00:01 |

|* 7 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 2 | 168 | 29 (0)| 00:00:01 |

|* 8 | INDEX SKIP SCAN | I_OBJ2 | 2 | | 27 (0)| 00:00:01 |

| 9 | INDEX FULL SCAN | I_USER2 | 89 | 1958 | 1 (0)| 00:00:01 |

| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 18 | 1 (0)| 00:00:01 |

|* 11 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |

| 12 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |

|* 13 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |

|* 14 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |

| 15 | NESTED LOOPS | | 1 | 97 | 1 (0)| 00:00:01 |

|* 16 | INDEX SKIP SCAN | I_LINK1 | 1 | 79 | 1 (0)| 00:00:01 |

| 17 | TABLE ACCESS CLUSTER | USER$ | 1 | 18 | 0 (0)| |

|* 18 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |

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

Peeked Binds (identified by position):

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

1 - :A (VARCHAR2(30), CSID=852): "TAB$"

Predicate Information (identified by operation id):

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

3 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8

AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND

"O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR

BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND

((SYS_CONTEXT("userenv","current_edition_name")="ORA$BASE" AND "U"."TYPE#"<>2) OR

("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT("userenv","current_edition_id")))

OR IS NOT NULL))))

4 - filter((:A<>"_default_auditing_options_" AND :A<>"_NEXT_OBJECT"))

6 - access("O"."OWNER#"="U"."USER#")

7 - filter(BITAND("O"."FLAGS",128)=0)

8 - access("O"."NAME"=:A AND "O"."LINKNAME" IS NULL)

filter(("O"."NAME"=:A AND "O"."TYPE#"<>10 AND "O"."NAME"<>"_NEXT_OBJECT" AND

"O"."NAME"<>"_default_auditing_options_" AND "O"."LINKNAME" IS NULL))

11 - access("O"."SPARE3"="U"."USER#")

13 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT("userenv","current_e

dition_id")))

filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT("userenv","current_

edition_id"))))

14 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

16 - access("L"."NAME"=:A)

filter("L"."NAME"=:A)

18 - access("L"."OWNER#"="U"."USER#")

61 rows selected.

SQL>

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

作者：Liyuper

原文：https://blog.csdn.net/lyp13696402570/article/details/85709583

版權聲明：本文為博主原創文章，轉載請附上博文鏈接！

查看有綁定變數的SQL的綁定變數值

打開今日頭條，查看更多圖片
喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！


請您繼續閱讀更多來自 程序員小新人學習 的精彩文章:

※SpringMVC異常處理的三種方式
※用R語言實現對不平衡數據的四種處理方法

TAG:程序員小新人學習 |