[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.