开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

[20160407]光标共享TOP_LEVEL_RPI_CURSOR

简介: [20160407]光标共享问题TOP_LEVEL_RPI_CURSOR.txt --以前做测试出现,光标不能共享TOP_LEVEL_RPI_CURSOR,不知道什么意思,链接: -- http://blog.
+关注继续查看

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
[20170904]11Gr2 查询光标为什么不共享脚本
[20170904]11Gr2 查询光标为什么不共享脚本.txt --//参考链接下面的注解脚本: https://carlos-sierra.net/2017/09/01/poors-man-script-to-summarize-reasons-why-cursors-are-not-shared/ --//做一个记录.
829 0
【内含分享PPT/视频/文章】阿里云MVP学院MaxCompute技术闭门会线上首播 | 2019大数据技术公开课第二季
数据的价值是解释业务还是预测业务?是支撑业务还是驱动业务?企业级计算服务的核心问题是什么?企业级计算平台要解决的核心问题是什么?商业和技术的平衡点在哪里? 一起直播学习,让数据真正驱动业务。
12054 0
Bootstrap响应式前端框架笔记八——按钮组
Bootstrap响应式前端框架笔记八——按钮组
0 0
阿里云市场读光OCR印刷文字识别-身份证识别API调用Java 示例参考
读光是一款由阿里巴巴集团达摩院团队打造的OCR云产品,多年来, 不断整合前沿技术和行业经验,打磨出了能够承载跨行业应用的技术架构,形成了完备的图像文字定位、文字识别和文字理解的技术体系。经过多种数据类型的洗礼和实战经验,读光OCR的识别准确率和处理性能稳居业界领先水平。读光身份证识别支持二代身份证正反面所有字段的识别。支持实拍复印件判断和人脸位置检测。基于达摩院强大的深度学习算法和OCR技术,各字段精度均处于业界领先水平,身份证号码识别准确率达到99.9%以上。本文将介绍身份证识别的快速调试和基于Java的调用。
0 0
阿里云市场读光OCR印刷文字识别-身份证识别API调用Java 示例参考
读光是一款由阿里巴巴集团达摩院团队打造的OCR云产品,多年来, 不断整合前沿技术和行业经验,打磨出了能够承载跨行业应用的技术架构,形成了完备的图像文字定位、文字识别和文字理解的技术体系。经过多种数据类型的洗礼和实战经验,读光OCR的识别准确率和处理性能稳居业界领先水平。读光身份证识别支持二代身份证正反面所有字段的识别。支持实拍复印件判断和人脸位置检测。基于达摩院强大的深度学习算法和OCR技术,各字段精度均处于业界领先水平,身份证号码识别准确率达到99.9%以上。本文将介绍身份证识别的快速调试和基于Java的调用。
0 0
【前端第四课】CSS值和单位;CSS文字排版;CSS变换,过渡,动画;CSS定位
【前端第四课】CSS值和单位;CSS文字排版;CSS变换,过渡,动画;CSS定位
0 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载