[20150803]toad 12版本1个小变化.txt

[20150803]toad 12版本1个小变化.txt

--昨天在使用toad12.0.061时，发现1个小小的变化关于sql_id的。

--可以参考：[20120327]toad与sqlplus下执行sql语句的一个细节.txt
http://blog.itpub.net/267265/viewspace-719592/

--而现在的版本呢?

SCOTT@test> alter system flush shared_pool;
System altered.

--在toad下执行如下：
select /*+ zzzz */ * from dept where deptno=10;
select /*+ zzzz */ * from dept where deptno=10 ;
select /*+ zzzz */ * from dept where deptno=10        ;

SELECT sql_id, sql_text, length(sql_text) n10,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                                                                       N10 CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I I I
------------- ------------------------------------------------------------ --------------------- ------------ --------------- ---------- - - -
96xs9w5bcxzkb select /*+ zzzz */ * from dept where deptno=10                                  46            0      2852011669          3 N N Y

--length(sql_text)=46,不再像9.6.0.27.那样，在结尾处补1个空格。

--可以在sqlplus验证看看:

SCOTT@test> select /*+ zzzz */ * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test> @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 |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
   2 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=10)

--可以发现sql_id= '96xs9w5bcxzkb'.没有变化。

SELECT sql_id, sql_text, length(sql_text) n10,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                                                                       N10 CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I I I
------------- ------------------------------------------------------------ --------------------- ------------ --------------- ---------- - - -
96xs9w5bcxzkb select /*+ zzzz */ * from dept where deptno=10                                  46            0      2852011669          4 N N Y

--EXECUTIONS =4 ,也说明这种变化。