父游标可以拥有的子cursor数量

11GR1以前，经常可能会遭遇父游标下挂载子游标过多的问题，AWR报告的

SQL Statistics

• SQL ordered by Version Count

也给出了按照子游标数量排序的SQL，某些情况下，这个数量可能会成千上万，一定程度上造成了共享池内存的浪费和定位游标过程中消耗CPU时间长，持有LATCH时间长的问题。

11.2.0.3后，ORACLE改善了这一点，通过设置隐含参数_cursor_obsolete_threshold

 来控制一个父游标最多能拥有子游标的数量。此参数的默认值为100。一个父游标下一旦子游标的个数超过这个个数，此父游标将会被废弃，重新生成一个新的游标。

这个过程可以通过如下实验模拟：

SQL> CREATE TABLE t (n NUMBER);

Table created.

SQL> execute dbms_stats.gather_table_stats(user,'t')

PL/SQL procedure successfully completed.

SQL>  DECLARE

  2      a number;

  3    BEGIN

  4        FOR c IN 1..10000

  5        LOOP

  6          EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||c;

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

  8        END LOOP;

  9    END;

 10    /

通过设置optimizer_index_cost_adj 来改变SQL的执行环境，这个值的取值范围为1到10000，因此如果父游标不被废弃的话，会产生10000个子游标。看看实际情况呢？

在PL/SQL执行过程中，我们来查看V$SQL的情况：

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

  COUNT(*)

----------

      1427

SQL> /

  COUNT(*)

----------

      1703

SQL> /

  COUNT(*)

----------

      1898

SQL> /

  COUNT(*)

----------

      2066

SQL> /

  COUNT(*)

----------

      2254

刚看到这个结果，有点出乎我的意料，子游标的数量大大的超出了100.不要急，我们接着看。

SQL> SELECT sql_id,is_obsolete FROM v$sql WHERE sql_id='5tjqf7sx5dzmj' AND child_number=0 ORDER BY 2;

SQL_ID          IS

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

5tjqf7sx5dzmj   N

5tjqf7sx5dzmj   Y

5tjqf7sx5dzmj   Y

5tjqf7sx5dzmj   Y

5tjqf7sx5dzmj   Y

5tjqf7sx5dzmj   Y

5tjqf7sx5dzmj   Y

5tjqf7sx5dzmj   Y

5tjqf7sx5dzmj   Y

5tjqf7sx5dzmj   Y

省略..........

我们看到sql_id为5tjqf7sx5dzmj，child_number为0的记录有很多，但是is_obsolete值为n的只有一个，也就是其他的都是已经被废弃的。

在11GR2版本前，子游标的最大数量为65535，不再用实验证实，大家有兴趣可以自己测试。有一点需要提醒，测试前，请保证你的共享池足够大，否则还么没等测试到最大值，共享池吃紧，释放某些子游标，重用这些内容放置新的子游标。

参考：

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-773603/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-773603/