测试例：子游标个数限定的功能使用例

SQLplusDB

发布于 2020-03-26 10:26:13

6190

发布于 2020-03-26 10:26:13

概述

我们知道Oracle在以下的版本中，为了防止产生过多的子游标，增加了子游标个数限定的功能。

UNIX/Linux 平台:  
o PSU 11.2.0.2.2 以后
o PSU 11.1.0.7.7 以后Windows 平台 :    
o 11.2.0.2 Patch 3 以后
o 11.1.0.7 Patch 38 以后

即：当子游标个数超过限定数时，该功能会把父游标无效化，重新生成一个父游标。

子游标个数限定的功能，在各个版本上的设置方法如下：

- 11.1.0.7
_cursor_features_enabled=18
event =  "106001 trace name context forever, level XXX  "- 11.2.0.1
_cursor_features_enabled=34
event = "106001 trace name context forever, level XXX "- 11.2.0.2
_cursor_features_enabled=1026
event =  "106001 trace name context forever, level XXX "- 11.2.0.3以后版本
_cursor_obsolete_threshold=XXX* 其中XXX 为限定的子游标个数

11.2.0.3以后的版本限定功能默认有效并且默认值如下：

11.2.0.3: 100
11.2.0.4以后: 1024

本测试例基于11.2.0.2.2数据库版本，验证该子游标个数限定的功能。读者也可以参考测试方法和过程，对其他任意版本进行验证。

测试

测试内容主要包括如下：

1.未开启子游标个数限定的功能时的数据库动作。
2.开启子游标个数限定的功能时的数据库动作

准备测试数据

本次测试通过系统提供的Scott Schema进行数据准备。

---建立测试用户
SQL> create user testc1 identified by testc1;
SQL> grant dba to testc1;SQL> create user testc2 identified by testc2;
SQL> grant dba to testc2;SQL> create user testc3 identified by testc3;
SQL> grant dba to testc3;SQL> create user testc4 identified by testc4;
SQL> grant dba to testc4;SQL> create user testc5 identified by testc5;
SQL> grant dba to testc5;---为每个测试用户创建同名的表
SQL> conn testc1/testc1
SQL> create table emp as select * from scott.emp;SQL> conn testc2/testc2
SQL> create table emp as select * from scott.emp;SQL> conn testc3/testc3
SQL> create table emp as select * from scott.emp;SQL> conn testc4/testc4
SQL> create table emp as select * from scott.emp;SQL> conn testc5/testc5
SQL> create table emp as select * from scott.emp;

未开启子游标个数限定的功能时的数据库动作

不同的用户执行相同的SQL语句时，Oracle会进行语义检查，由于各个用户的权限以及查询对象所在的Schema不同，相同的父游标（SQL文）下会生成多个子游标。

----不同的用户执行相同的SQL语句
SQL> conn scott/tiger
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc1/testc1
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc2/testc2
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc3/testc3
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc4/testc4
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc5/testc5
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';----通过v$sql_shared_cursor查看子游标的生成情况
SQL> conn /as sysdba
----查看SQL ID
SQL> select SQL_ID,SQL_FULLTEXT from V$SQL where upper(SQL_FULLTEXT) like upper('%Cursor TEST %');SQL_ID
-------------
SQL_FULLTEXT
-------------------------------------------------------------------------------
9kma2mah0qmbj
select /* Cursor TEST */ * from emp where ENAME= 'WARD'
...---子游标的生成情况
SQL> set linesize 500
SQL> select * from v$sql_shared_cursor where sql_id='9kma2mah0qmbj';SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --------------------------------------------------------------------------------
9kma2mah0qmbj 0000000089399910 000000008915C338            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N <ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check fa
9kma2mah0qmbj 0000000089399910 00000000891E4B68            1 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N <ChildNode><ChildNumber>1</ChildNumber><ID>37</ID><reason>Authorization Check fa
9kma2mah0qmbj 0000000089399910 00000000891FB5F8            2 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N <ChildNode><ChildNumber>2</ChildNumber><ID>37</ID><reason>Authorization Check fa
9kma2mah0qmbj 0000000089399910 000000008B8D22A0            3 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N <ChildNode><ChildNumber>3</ChildNumber><ID>37</ID><reason>Authorization Check fa
9kma2mah0qmbj 0000000089399910 000000008A948C48            4 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N <ChildNode><ChildNumber>4</ChildNumber><ID>37</ID><reason>Authorization Check fa
9kma2mah0qmbj 0000000089399910 000000008921D318            5 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N6 rows selected.

我们可以看到未开启子游标个数限定的功能时，生成了包括ChildNumber：0~5一共6个子游标。

2.开启子游标个数限定的功能时的数据库动作

下面我们测试一下开启子游标个数限定的功能时的数据库动作，

--查看数据库的设定(optimizer_features_enable=11.2.0.2)SQL> conn /as sysdba
SQL> show parameter optimizer_features_enableNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      11.2.0.2--开启子游标个数限定的功能,设置子游标上限为3    
SQL> alter system set "_cursor_features_enabled"=1026 scope=spfile;System altered.SQL> alter system set event =  "106001 trace name context forever, level 3 " scope=spfile;System altered.SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area  839282688 bytes
Fixed Size                  2231128 bytes
Variable Size             666895528 bytes
Database Buffers          163577856 bytes
Redo Buffers                6578176 bytes
Database mounted.
Database opened.---重启数据库查看设定状况
SQL> set pagesize 100
set linesize 200
col Parameter format a40
col Description format a50
col Value format a40SQL> SQL> SQL> SQL>
SQL> select a.ksppinm "Parameter",a.KSPPDESC "Description",b.ksppstvl "Value"
from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '_cursor_features_enabled';
2  
Parameter                   Description                            Value
--------------------------- -------------------------------------- ----------
_cursor_features_enabled    Shared cursor features enabled bits.   1026　★SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump system
106001 trace name context forever, level 3　★
SQL>----不同的用户执行相同的SQL语句
SQL> conn scott/tiger
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc1/testc1
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc2/testc2
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc3/testc3
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc4/testc4
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';
SQL> conn testc5/testc5
SQL> select /* Cursor TEST */ * from emp where ENAME= 'WARD';----通过v$sql查看各个游标的情况SQL> select SQL_ID,SQL_FULLTEXT,IS_OBSOLETE,IS_SHAREABLE from V$SQL
where upper(SQL_FULLTEXT) like upper('%Cursor TEST %');  2  SQL_ID        SQL_FULLTEXT                                                                     I I
------------- -------------------------------------------------------------------------------- - -
9kma2mah0qmbj select /* Cursor TEST */ * from emp where ENAME= 'WARD'                          Y Y★IS_OBSOLETE为Y,游标被废弃
9kma2mah0qmbj select /* Cursor TEST */ * from emp where ENAME= 'WARD'                          Y Y★IS_OBSOLETE为Y,游标被废弃
9kma2mah0qmbj select /* Cursor TEST */ * from emp where ENAME= 'WARD'                          Y Y★IS_OBSOLETE为Y,游标被废弃
9kma2mah0qmbj select /* Cursor TEST */ * from emp where ENAME= 'WARD'                          N Y
9kma2mah0qmbj select /* Cursor TEST */ * from emp where ENAME= 'WARD'                          N Y
9kma2mah0qmbj select /* Cursor TEST */ * from emp where ENAME= 'WARD'                          N Y
a1t30rf4cay7b select SQL_ID,SQL_FULLTEXT,IS_OBSOLETE,IS_SHAREABLE from V$SQL                   N Y
            where upper(SQL_F7 rows selected.----通过v$sql_shared_cursor查看子游标的生成情况
SQL>  select * from v$sql_shared_cursor where sql_id='9kma2mah0qmbj';SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
REASON
--------------------------------------------------------------------------------
9kma2mah0qmbj 000000008AD768F8 000000008AD76498            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
<ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check fa9kma2mah0qmbj 000000008AD768F8 000000008AFFEA98            1 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
<ChildNode><ChildNumber>1</ChildNumber><ID>37</ID><reason>Authorization Check fa9kma2mah0qmbj 000000008AD768F8 0000000091EBC028            2 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
<ChildNode><ChildNumber>2</ChildNumber><ID>37</ID><reason>Authorization Check fa9kma2mah0qmbj 000000008BCA58A8 000000008BCAA430            0 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
<ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check fa9kma2mah0qmbj 000000008BCA58A8 000000008BCA5008            1 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
<ChildNode><ChildNumber>1</ChildNumber><ID>37</ID><reason>Authorization Check fa9kma2mah0qmbj 000000008BCA58A8 000000008AD5A678            2 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N6 rows selected.

我们可以看到在开启子游标个数限定的功能后，当子游标个数超过限定数，该功能会把父游标无效，重新生成一个父游标。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2016-11-19，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库