[20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
--//记录自己测试遇到的问题，我以为不会出现TOP_LEVEL_RPI_CURSOR不共享，结果还是出现。
1.环境：
SYS@book> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试脚本以及测试环境：
--//分别在cdb$root,book01p下建立表t.
--//cdb$root下以sys用户建立如下表。
SYS@book> create table t as select rownum id ,cast('test' as varchar2(10)) name from dual ;
Table created.
SYS@book> alter table t modify ( id not null );
Table altered.
SYS@book> create unique index i_t_id on t(id);
Index created.
--//pdb=book01p下以scott用户建立表以及索引过程略。
$ cat m14.txt
--//alter session set session_cached_cursors=0;
set verify off
variable vmethod varchar2(20);
exec :vmethod := '&&2';
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
SELECT COUNT(NAME) INTO v_id FROM T WHERE ID=1;
end loop;
dbms_lock.sleep(2);
end ;
/
quit
$ cat aa5.txt
alter session set session_cached_cursors=0;
alter session set optimizer_index_cost_adj = &&1;
SELECT COUNT(NAME) FROM T WHERE ID=1;
alter session set optimizer_index_cost_adj = 100;
--//同时执行如下：
seq 5 | xargs -IQ sqlplus -s -l scott/book@book01p @m14.txt 6 xx Q > /dev/null &
seq 5 | xargs -IQ sqlplus -s -l / as sysdba @m14.txt 6 xx Q > /dev/null
--//这样相当于建立10个子光标，相互交错。
SYS@book> select con_id,sql_id,child_number,hash_value,sql_text,LAST_ACTIVE_TIME from v$sql where sql_id='5zfc9khsnyp90' order by LAST_ACTIVE_TIME;
CON_ID SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LAST_ACTIVE_TIME
---------- ------------- ------------ ---------- ------------------------------------------------------------ -------------------
1 5zfc9khsnyp90 0 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:37
3 5zfc9khsnyp90 1 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:37
1 5zfc9khsnyp90 2 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:39
3 5zfc9khsnyp90 3 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:39
1 5zfc9khsnyp90 4 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:41
3 5zfc9khsnyp90 5 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:41
1 5zfc9khsnyp90 6 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:44
3 5zfc9khsnyp90 7 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:44
1 5zfc9khsnyp90 8 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:46
3 5zfc9khsnyp90 9 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 10:27:46
10 rows selected.
SYS@book> @ aa5.txt 1
Session altered.
Session altered.
COUNT(NAME)
-----------
1
Session altered.
SYS@book> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
2974766368 5zfc9khsnyp90 10 87328 b14f5520 2026-11-02 10:28:46 16777276
--//在sqlplus下执行aa5.txt脚本，建立新的子光标CHILD_NUMBER=10.
SYS@book> @ gunshare 5zfc9khsnyp90
REASON_NOT_SHARED CURSORS SQL_IDS
----------------------------- ---------- ----------
OPTIMIZER_MISMATCH 9 1
HASH_MATCH_FAILED 1 1
TOP_LEVEL_RPI_CURSOR 1 1
--- select sql_id,child_number,HASH_MATCH_FAILED,OPTIMIZER_MISMATCH,TOP_LEVEL_RPI_CURSOR,reason from v$sql_shared_cursor where sql_id='5zfc9khsnyp90' order by 1,2;
SYS@book> select sql_id,child_number,HASH_MATCH_FAILED,OPTIMIZER_MISMATCH,TOP_LEVEL_RPI_CURSOR,reason from v$sql_shared_cursor where sql_id='5zfc9khsnyp90' order by 1,2;
SQL_ID CHILD_NUMBER HASH_MATCH_FAILED OPTIMIZER_MISMATCH TOP_LEVEL_RPI_CURSOR REASON
------------- ------------ ----------------- ------------------ -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
5zfc9khsnyp90 0 N N N <ChildNode><ChildNumber>0</ChildNumber><ID>7</ID><reason>Top Level RPI Cursor(0)</reason><size>2x4</size><ctxxyfl>16780288</ctxxyfl><ispri>0</ispri></
ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 1
2 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 1 Y N N <ChildNode><ChildNumber>1</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 1
2 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 2 N Y N <ChildNode><ChildNumber>2</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 2
3 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 3 N Y N <ChildNode><ChildNumber>3</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 2
3 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 4 N Y N <ChildNode><ChildNumber>4</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 3
4 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 5 N Y N <ChildNode><ChildNumber>5</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 3
4 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 6 N Y N <ChildNode><ChildNumber>6</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 4
5 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 7 N Y N <ChildNode><ChildNumber>7</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 4
5 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 8 N Y N <ChildNode><ChildNumber>8</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x508</size><optimizer_index_cost_adj> 5
1 </optimizer_index_cost_adj></ChildNode>
5zfc9khsnyp90 9 N Y N
5zfc9khsnyp90 10 N Y Y
11 rows selected.
--//注意看还出现HASH_MATCH_FAILED=Y在CHILD_NUMBER=1，现在明白了con_id不同，会出现HASH_MATCH_FAILED不共享的情况,以前不理解。
--//在CHILD_NUMBER=10子光标上出现TOP_LEVEL_RPI_CURSOR=Y。
--//换一句话讲在pl/sql下执行的sql语句与其他情况下执行的sql语句会出现不共享的情况.
--//以前一直认为PL/sql里面使用execute immediate的执行方式与sqlplus下执行会出现TOP_LEVEL_RPI_CURSOR的情况。
-- Script: create_children.sql
-- Create a parent cursor with &childno number of child cursors
define &childno=&1
declare
l_count pls_integer;
begin
for oica in 1..&childno
loop
execute immediate 'alter session set optimizer_index_cost_adj = '||oica;
execute immediate 'select count(*) from t' into l_count;
end loop;
end;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3187397/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
- [20261013]光标跟踪事件.txt
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
