[20150616]关于sql_id.txt

--我曾经提到PLSQL中使用绑定变量，oracle会格式化处理，转化为特定的格式。可以参考我以前的例子：
--[20121102]PLSQL中的绑定变量.txt
--http://blog.itpub.net/267265/viewspace-748190/

--我也曾经写过一篇exact_matching_signature,force_matching。
--http://blog.itpub.net/267265/viewspace-1220996/
--计算exact_matching_signature,force_matching时也经过了"格式化"处理。

--今天讲一下sql语句，我记得以前讲sql关于绑定变量时，要求开发写sql语句遵循一定的规范，比如select,where等关键字大写，字段小写等等。
--实际上现在想想意义不大，国内大部分应用都没有使用绑定变量.要求这些规范没用。

1.看看sql语句。

SCOTT@test> @ver1

PORT_STRING          VERSION        BANNER
-------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx  11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

$ cat -eT a1.sql
select *$
from dept$
where deptno=10;$
--这样可以看出文件的一些特殊符号。

$ cp a1.sql a2.sql

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

SCOTT@test> @a1
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID  cv9sf48f5ka60, child number 0
-------------------------------------
select * from dept where deptno=10

--sql_id=cv9sf48f5ka60.

2.使用unix2dos转换a2.sql
$ unix2dos a2.sql
unix2dos: converting file a2.sql to DOS format ...

$ file a1.sql  a2.sql
a1.sql: ASCII text
a2.sql: ASCII text, with CRLF line terminators

SCOTT@test> @a2
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test> select length ( SQL_FULLTEXT),sql_fulltext c40 ,executions , sys.dbms_sqltune_util0.sqltext_to_sqlid(sql_fulltext||chr(0)) c20 from v$sql where sql_id='cv9sf48f5ka60';
LENGTH(SQL_FULLTEXT) C40                                      EXECUTIONS C20
-------------------- ---------------------------------------- ---------- --------------------
                  34 select *                                          2 cv9sf48f5ka60
                     from dept
                     where deptno=10

--执行了2次。说明a2.sql执行的sql_id 与执行a1.sql的sql_id一样。

$ ls -l a1.sql
-rw-r--r--  1 oracle11g oinstall 36 2015-06-16 22:53:14 a1.sql

$ xxd -c 16 a1.sql
0000000: 7365 6c65 6374 202a 0a66 726f 6d20 6465  select *.from de
0000010: 7074 0a77 6865 7265 2064 6570 746e 6f3d  pt.where deptno=
0000020: 3130 3b0a                                10;.

--a1.sql文件大小36，扣除最后的分号以及0x0a，长度正好是34.

3.修改a2.sql文件，在后面加入一些tab或者空格：

$ cat -eT a2.sql
select *      ^I^I^M$
from dept ^I^I    ^M$
where deptno=10;^M$

$ ls -l a2.sql
-rw-r--r--  1 oracle11g oinstall 54 2015-06-16 23:02:24 a2.sql

--^I 表示tab。a2.sql文件长度变成了54.

SCOTT@test> @a2
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test> select length ( SQL_FULLTEXT),sql_fulltext c40 ,executions , sys.dbms_sqltune_util0.sqltext_to_sqlid(sql_fulltext||chr(0)) c20 from v$sql where sql_id='cv9sf48f5ka60';
LENGTH(SQL_FULLTEXT) C40                                      EXECUTIONS C20
-------------------- ---------------------------------------- ---------- --------------------
                  34 select *                                          3 cv9sf48f5ka60
                     from dept
                     where deptno=10

--a2.sql执行后sql_id依旧没有变化。
--说明：执行的sql语句计算sql_id实际上还是经历了一个"格式化"的过程。

4.覆盖a2.sql:
$ cp a1.sql a2.sql

--再修改如下：
$ cat -eT a2.sql
select *    ^M       $
from dept$
where deptno=10;$
--^M表示回车0x0d。

SCOTT@test> @a2
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test> select length ( SQL_FULLTEXT),sql_fulltext c40 ,executions , sys.dbms_sqltune_util0.sqltext_to_sqlid(sql_fulltext||chr(0)) c20 from v$sql where sql_id='cv9sf48f5ka60';
LENGTH(SQL_FULLTEXT) C40                                      EXECUTIONS C20
-------------------- ---------------------------------------- ---------- --------------------
                  34 select *                                          4 cv9sf48f5ka60
                     from dept
                     where deptno=10

--执行次数加1.如果在select前面加入空格sql_id会变(这个测试不做了).

5.做1次刷新：
SCOTT@test> alter system flush shared_pool;
System altered.

SCOTT@test> @a2
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test> select length ( SQL_FULLTEXT),sql_fulltext c40 ,executions , sys.dbms_sqltune_util0.sqltext_to_sqlid(sql_fulltext||chr(0)) c20 from v$sql where sql_id='cv9sf48f5ka60';
LENGTH(SQL_FULLTEXT) C40                                      EXECUTIONS C20
-------------------- ---------------------------------------- ---------- --------------------
                  34 select *                                          1 cv9sf48f5ka60
                     from dept
                     where deptno=10

总结：

--sql语句计算sql_id也是经历"格式化"过程，删除0x0d以及每行结尾的tab空格(除了分号这行)。是否有其它情况不得而知。

--补充测试，如果在分号前加入空格：

SCOTT@test> select sql_text,length ( SQL_FULLTEXT),sql_fulltext c40 ,executions , sys.dbms_sqltune_util0.sqltext_to_sqlid(sql_fulltext||chr(0)) c20 from v$sql where sql_id='1p8afzt6x1mhd';
SQL_TEXT                                                     LENGTH(SQL_FULLTEXT) C40                                      EXECUTIONS C20
------------------------------------------------------------ -------------------- ---------------------------------------- ---------- --------------------
select * from dept where deptno=10                                             35 select *                                          1 1p8afzt6x1mhd
                                                                                  from dept
                                                                                  where deptno=10

--长度变了，sql_id也与前面不同。