查看有绑定变量的SQL的绑定变量值

最新推荐文章于 2024-09-30 09:47:43 发布

原创最新推荐文章于 2024-09-30 09:47:43 发布 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 11G 同时被 2 个专栏收录

31 篇文章

订阅专栏

日常运维

15 篇文章

订阅专栏

本文详细介绍了如何在Oracle数据库中使用绑定变量执行SQL查询，以及如何查看和解析执行计划。通过实例展示了设置绑定变量、执行SQL语句并获取执行计划的过程，包括使用DBMS_XPLAN包显示执行计划和绑定变量的值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

设置一个绑定变量，执行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 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>

10g

COL NAME FOR A12;
COL DATATYPE_STRING FOR A24;
COL VALUE_STRING FOR A32;
SELECT NAME,
DATATYPE_STRING,
VALUE_STRING,
MAX_LENGTH,
LAST_CAPTURED
FROM V$SQL_BIND_CAPTURE
WHERE SQL_ID = 'fwyf8b45bg5y2';