測試例：子游標個數限定的功能使用例

  • 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