[20160516]SQL共享游標的測試疑問.txt

[20160516]SQL共享游標的測試疑問.txt

--昨天我看了連結http://blog.itpub.net/17203031/viewspace-754994/,感覺他的測試有問題,不可能相同的sql語句,而sql_id會不一樣
--的.我自己測試看看.

1.環境:

SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--分別以sys,scott使用者建立表T:
create table t as select * from dba_objects;

2.測試

--sys使用者:
select /*+ DEMO */count(*) from t where owner='SCOTT';
--sql_id='0cmtuq6zf22px'

--SCOTT使用者:
select /*+ DEMO */count(*) from t where owner='SCOTT';
--sql_id='0cmtuq6zf22px'

SYS@book> select sql_id, version_count, executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sqlarea where sql_text like 'select /*+ DEMO */%';
SQL_ID        VERSION_COUNT EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------- ---------- --------------- --------------------
0cmtuq6zf22px             2          2               0 SCOTT

SYS@book> select sql_id, child_number , executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sql where sql_text like 'select /*+ DEMO */%';
SQL_ID        CHILD_NUMBER EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------ ---------- --------------- --------------------
0cmtuq6zf22px            0          1               0 SYS
0cmtuq6zf22px            1          1               0 SCOTT

--而作者的測試居然不一樣.感覺非常奇怪.這讓我突然想起toad的老版本會在sql語句上自動補一個空格,感覺作者應該會使用PLSQL
--Developer,我也測試看看:

SYS@book> select sql_id, child_number , executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sql where sql_text like 'select /*+ DEMO */%';
SQL_ID        CHILD_NUMBER EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------ ---------- --------------- ------------------------------
gp5x6kaz2whwu            0          1               0 SCOTT
0cmtuq6zf22px            0          1               0 SYS
0cmtuq6zf22px            1          1               0 SCOTT

--很明顯PLSQL Developer也會"格式化指令碼".

SYS@book> select sql_id, dump(sql_text,16) c100 ,child_number , executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sql where sql_text like 'select /*+ DEMO */%';
SQL_ID        C100                                                                                                 CHILD_NUMBER EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ---------------------------------------------------------------------------------------------------- ------------ ---------- --------------- ------------------------------
gp5x6kaz2whwu Typ=1 Len=54: 73,65,6c,65,63,74,20,2f,2a,2b,20,44,45,4d,4f,20,2a,2f,63,6f,75,6e,74,28,2a,29,20,66,72            0          1               0 SCOTT
              ,6f,6d,20,74,20,77,68,65,72,65,20,6f,77,6e,65,72,3d,27,53,43,4f,54,54,27,20

0cmtuq6zf22px Typ=1 Len=53: 73,65,6c,65,63,74,20,2f,2a,2b,20,44,45,4d,4f,20,2a,2f,63,6f,75,6e,74,28,2a,29,20,66,72            0          1               0 SYS
              ,6f,6d,20,74,20,77,68,65,72,65,20,6f,77,6e,65,72,3d,27,53,43,4f,54,54,27

0cmtuq6zf22px Typ=1 Len=53: 73,65,6c,65,63,74,20,2f,2a,2b,20,44,45,4d,4f,20,2a,2f,63,6f,75,6e,74,28,2a,29,20,66,72            1          1               0 SCOTT
              ,6f,6d,20,74,20,77,68,65,72,65,20,6f,77,6e,65,72,3d,27,53,43,4f,54,54,27

--很明顯PLSQL Developer也會在後面加一個空格.但是我無法得到作者測試5x21uhnky7bnb.
--BTW 我的PLSQL Developer版本是6.0.0.840.

SYS@book> @ &r/share 0cmtuq6zf22px
SQL_TEXT                       = select /*+ DEMO */count(*) from t where owner='SCOTT'
SQL_ID                         = 0cmtuq6zf22px
ADDRESS                        = 000000007D487178
CHILD_ADDRESS                  = 000000007D9B7150
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check
failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>2120444968</original_handle><temp_handle>2101839128</temp_handle><schema>83</schema><synonym_object_number>0</synonym_object_number></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select /*+ DEMO */count(*) from t where owner='SCOTT'
SQL_ID                         = 0cmtuq6zf22px
ADDRESS                        = 000000007D487178
CHILD_ADDRESS                  = 000000007D456048
CHILD_NUMBER                   = 1
AUTH_CHECK_MISMATCH            = Y
TRANSLATION_MISMATCH           = Y
REASON                         =
--------------------------------------------------

PL/SQL procedure successfully completed.