[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也與前面不同。

[20150616]關於sql_id.txt

相關文章