[20130104]oracle能有多少子游標.txt

[20121019]oracle能有多少子游標.txt

原連結：
http://www.antognini.ch/2012/10/how-many-children-can-a-parent-cursor-have-1000000/

看看oracle可能有多少子游標，也就是最大是多少？重複原作者的測試看看。

我的測試環境:

SQL> select * from v$version where rownum<=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

CREATE TABLE t (n NUMBER);

INSERT INTO t VALUES (1);

COMMIT;

execute dbms_stats.gather_table_stats(user,'t')

1.測試

DECLARE

    l_count PLS_INTEGER;

BEGIN

    FOR i IN 1..100

    LOOP

    EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||i;

    FOR j IN 1..10000

    LOOP

        EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||j;

        EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into l_count;

    END LOOP;

    END LOOP;

END;

/

--從top看CPU很忙。

Tasks: 123 total,   2 running, 121 sleeping,   0 stopped,   0 zombie
Cpu(s): 25.0% us,  0.2% sy,  0.0% ni, 74.7% id,  0.2% wa,  0.0% hi,  0.0% si
Mem:   4045276k total,  3892552k used,   152724k free,   118700k buffers
Swap:  3911788k total,     1428k used,  3910360k free,  3149860k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8957 oracle11  25   0 1755m 119m 116m R 99.9  3.0   4:25.10 oracle

--檢視等待事件.

SQL> select sid,event from v$session_wait where sid=191

      SID EVENT                                                           

---------- ------------------------

       191 library cache: mutex X                                          

1 row selected.

SQL> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';

  COUNT(*)

----------

      2349

--最大就是這個，估計我共享池設定太小，無法達到作者的測試要求。
SQL> show sga

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size            1174407280 bytes
Database Buffers          419430400 bytes
Redo Buffers                7360512 bytes

摘抄一段:
http://www.antognini.ch/2012/10/how-many-children-can-a-parent-cursor-have-1000000/
    The most interesting part is the one providing the number of child cursors: 65536.

    I might be wrong but to me that means that the child number is stored as an unsigned integer taking 16 bits. And, when the maximum is exceeded, an ORA-00600 is raised.

    According to this test the maximum number of child cursors for a given parent cursor is 65536. But, as already written,the fix for bug# 10187168 artificially limits it to 100.