[20160407]游標共享TOP_LEVEL_RPI_CURSOR

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