[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
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.