我使用toad版本是9.6.0.27.
1.在sqlplus下执行如下：
SQL> select /*+ zzzz */ * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 96xs9w5bcxzkb, child number 0
-------------------------------------
select /*+ zzzz */ * from dept where deptno=10
Plan hash value: 2852011669
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 0 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
25 rows selected.
--可以知道sql_id=96xs9w5bcxzkb。
SQL> column is_bind_sensitive format a20
SQL> column is_bind_aware format a20
SQL> column is_shareable format a20
SQL> select sql_id,child_number,plan_hash_value,executions,is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id='96xs9w5bcxzkb';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------ --------------- ---------- -------------------- -------------------- --------------------
96xs9w5bcxzkb 0 2852011669 1 N N Y
--查询v$sql可以知道仅仅执行了1次。
2.使用toad登录，执行如下,注意我执行时后面有;(分号)。
select /*+ zzzz */ * from dept where deptno=10;
在回到sqlplus执行：
SQL> select sql_id,child_number,plan_hash_value,executions,is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id='96xs9w5bcxzkb';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------ --------------- ---------- -------------------- -------------------- --------------------
96xs9w5bcxzkb 0 2852011669 1 N N Y
--发现执行一次，不过如何变化，应该这样的sql语句生成的sql_id是一致的。问题到底在那里呢？
SQL> column sql_text format a30
SQL> column x format 999
SQL> SELECT sql_id, sql_text, length(sql_text) x,child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
WHERE sql_text LIKE '%zzzz%' AND sql_text NOT LIKE '%sql_text%';
SQL_ID SQL_TEXT X CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ---------- ------------ --------------- ---------- -------------------- -------------------- --------------------
8hwbj0x1u5kmf select /*+ zzzz */ * from dept 47 0 2852011669 1 N N Y
where deptno=10
96xs9w5bcxzkb select /*+ zzzz */ * from dept 46 0 2852011669 1 N N Y
where deptno=10
--可以发现两条sql语句的sql_text长度不一样，sql_id='96xs9w5bcxzkb' sql语句长度是46，而toad下执行的sql语句长度是47.存在差异。
--仔细对比前面都是一样的，最大的可能在toad在执行前在sql语句分号前加入一个空格后再执行的。
在sqlplus下执行如下：(注意我在分号前加入一个空格)
SQL> select /*+ zzzz */ * from dept where deptno=10 ;
SQL> SELECT sql_id, sql_text, length(sql_text) x,child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
3 WHERE sql_text LIKE '%zzzz%' AND sql_text NOT LIKE '%sql_text%';
SQL_ID SQL_TEXT X CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ---------- ------------ --------------- ---------- -------------------- -------------------- --------------------
8hwbj0x1u5kmf select /*+ zzzz */ * from dept 47 0 2852011669 2 N N Y
where deptno=10
96xs9w5bcxzkb select /*+ zzzz */ * from dept 46 0 2852011669 1 N N Y
where deptno=10
--可以发现sql_id=8hwbj0x1u5kmf,执行次数变成了2次。再次证明了toad"格式化"sql语句，在分号前加入了空格。
--而且如果你在toad下分号前加入空格或者tab效果都一样。执行都是如下sql语句select /*+ zzzz */ * from dept where deptno=10 ;
SQL> SELECT sql_id, sql_text, length(sql_text) x,child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
3 WHERE sql_text LIKE '%zzzz%' AND sql_text NOT LIKE '%sql_text%';
SQL_ID SQL_TEXT X CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------------------------ ---------- ------------ --------------- ---------- -------------------- -------------------- --------------------
8hwbj0x1u5kmf select /*+ zzzz */ * from dept 47 0 2852011669 9 N N Y
where deptno=10
96xs9w5bcxzkb select /*+ zzzz */ * from dept 46 0 2852011669 1 N N Y
where deptno=10