[20160407]光标共享问题TOP_LEVEL_RPI_CURSOR.txt
--以前做测试出现,光标不能共享TOP_LEVEL_RPI_CURSOR,不知道什么意思,链接:
-- http://blog.itpub.net/267265/viewspace-765072/
--今天看blog,终于明白表示什么意思?参考链接:
-- http://www.peasland.net/2016/04/05/vsql_shared_cursor-top_level_rpi_cursor/
--为了加强记忆,我重复我原来的测试:
1.环境:
SCOTT@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
$ cat soft_parse.sql
--connect scott/book
begin
for i in 1..10 loop
execute immediate 'select 1234567890 from dual';
end loop;
end;
/
--单独执行:
select 1234567890 from dual;
2.开始测试:
SCOTT@book> @ soft_parse.sql
PL/SQL procedure successfully completed.
SCOTT@book> select 1234567890 from dual;
1234567890
----------
1234567890
SCOTT@book> @ &r/share 0ta1datg212yk
SQL_TEXT = select 1234567890 from dual
SQL_ID = 0ta1datg212yk
ADDRESS = 000000007C4EF010
CHILD_ADDRESS = 000000007C6AFBD0
CHILD_NUMBER = 0
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>7</ID><reason>Top Level RPI Cursor(0)</reason><size>2x4</size><ctxxyfl>1024</ctxxyfl><ispri>0</ispri></ChildNode>
--------------------------------------------------
SQL_TEXT = select 1234567890 from dual
SQL_ID = 0ta1datg212yk
ADDRESS = 000000007C4EF010
CHILD_ADDRESS = 000000007BB942A8
CHILD_NUMBER = 1
TOP_LEVEL_RPI_CURSOR = Y
REASON =
--------------------------------------------------
PL/SQL procedure successfully completed.
--以前真不明白为什么不能共享光标.
SCOTT@book> select child_number,sql_id,executions from v$sql where sql_id='0ta1datg212yk';
CHILD_NUMBER SQL_ID EXECUTIONS
------------ ------------- ----------
0 0ta1datg212yk 10
1 0ta1datg212yk 1
--我转抄作者的blog:http://www.peasland.net/2016/04/05/vsql_shared_cursor-top_level_rpi_cursor/
So what does it mean when TOP_LEVEL_RPI_CURSOR is set to Y for a child? The documentation isn't clear. MOS has very
little on the subject. And all of my Google hits on this column pretty much just regurgitate the documentation. To know
why, it helps to know that RPI stands for Recursive Program Interface. This is part of the Oracle kernel that deals with
recursive SQL. In our case, it deals with the fact that the SQL statement was issued at a different "depth".
What is recursive SQL? It is SQL that is issued on your behalf, which means at a different depth as I will illustrate.
First off, Oracle is performing recursive SQL all the time. At a basic level, when you issue "select * from table_name",
Oracle queries the Data Dictionary to ensure the object exists and that you have permissions on that table. How does
Oracle do that? It uses other SQL statements. The statement you issue is at level 0, the base level. When Oracle issues
a SQL statement to check if the table exists, that will be at the next level, level 1. Sometimes, that will cause other
SQL statements to be issued at the next level, level 2.
The depth of a SQL statement is not limited to just what Oracle is doing in the background, on your behalf. Consider
when you execute a stored procedure. Your call to the stored procedure is at depth 0. Any SQL statement in the stored
procedure is at depth 1. If that stored procedure calls another procedure, the SQL in the other procedure will be at
depth 2.
3. 做一个10046跟踪就很容易明白:
SCOTT@book> @ &r/10046on 12
Session altered.
SCOTT@book> select 1234567890 from dual;
1234567890
----------
1234567890
SCOTT@book> @ soft_parse.sql
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/10046off
Session altered.
$ grep 0ta1datg212yk /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44465.trc
PARSING IN CURSOR #139973607848760 len=27 dep=0 uid=83 oct=3 lid=83 tim=1459994779203747 hv=1579191250 ad='7c4ef010' sqlid='0ta1datg212yk'
PARSING IN CURSOR #139973610685288 len=27 dep=1 uid=83 oct=3 lid=83 tim=1459994782403968 hv=1579191250 ad='7c4ef010' sqlid='0ta1datg212yk'
--注意看dep不一样.注意我的执行顺序,我是先执行select 1234567890 from dual;,再调用soft_parse.sq.
--前者dep=0,后者dep=1.