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

  • 2020 年 3 月 26 日
  • 筆記

概述

我们知道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.

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

VirMach 便宜 VPS

QNews

QNews