[20120327]toad与sqlplus下执行sql语句的一个细节.txt

TOAD是一个很好的图形化oracle管理工具，昨天在解决一个问题时遇到了一些细节问题，实际上我以前就知道，现在把它写下来：

我使用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