[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