设置一个绑定变量，执行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>
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责，本站只提供参考并不构成任何投资及应用建议。本站是一个个人学习交流的平台，网站上部分文章为转载，并不用于任何商业目的，我们已经尽可能的对作者和来源进行了通告，但是能力有限或疏忽，造成漏登，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。
