实验：cursor count超高的问题分析-优快云博客

今天发现在9.2.0.1上面oracle的如下写法会造成语句的版本超高select /*+ first_rows(10)*/，我的生产库已经达到了1000多，而相同的语句在9201以上版本是没有任何问题的。测试如下：

oracle: /oracle $ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Mar 23 10:50:09 2011

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.1.0 - Production

SQL> create table test_0323 (a int);

Table created.

SQL> insert into test_0323 values(1);

1 row created.

SQL> insert into test_0323 values(2);

1 row created.

SQL> insert into test_0323 values(3);

1 row created.

SQL> insert into test_0323 values(4);

1 row created.

SQL> insert into test_0323 values(5);

1 row created.

SQL> insert into test_0323 values(6);

1 row created.

SQL> insert into test_0323 values(7);

1 row created.

SQL> insert into test_0323 values(8);

1 row created.

SQL> insert into test_0323 values(9);

1 row created.

SQL> insert into test_0323 values(10);

1 row created.

SQL> insert into test_0323 values(11);

1 row created.

SQL> insert into test_0323 values(12);

1 row created.

SQL> insert into test_0323 values(13);

1 row created.

SQL> insert into test_0323 values(14);

1 row created.

SQL> insert into test_0323 values(15);

1 row created.

SQL> insert into test_0323 values(16);

1 row created.

SQL> insert into test_0323 values(17);

1 row created.

SQL> insert into test_0323 values(18);

1 row created.

SQL> insert into test_0323 values(19);

1 row created.

SQL> insert into test_0323 values(20);

1 row created.

SQL> insert into test_0323 values(21);

1 row created.

SQL> insert into test_0323 values(22);

1 row created.

SQL> insert into test_0323 values(23);

1 row created.

SQL> insert into test_0323 values(24);

1 row created.

SQL> insert into test_0323 values(25);

1 row created.

SQL> insert into test_0323 values(26);

1 row created.

SQL> insert into test_0323 values(27);

1 row created.

SQL> insert into test_0323 values(28);

1 row created.

SQL> insert into test_0323 values(29);

1 row created.

SQL> insert into test_0323 values(30);

1 row created.

SQL> insert into test_0323 values(31);

1 row created.

SQL> insert into test_0323 values(32);

1 row created.

SQL> insert into test_0323 values(33);

1 row created.

SQL> insert into test_0323 values(34);
insert into test_0323 values(35);

1 row created.

SQL>
1 row created.

SQL> insert into test_0323 values(36);

1 row created.

SQL>
SQL>
SQL> commit
2 ;

Commit complete.

SQL> select /*+ first_rows(10)*/ from test_0323 where rownum<=300;
select /*+ first_rows(10)*/ from test_0323 where rownum<=300
*
ERROR at line 1:
ORA-00936: missing expression

SQL> select /*+ first_rows(10)*/* from test_0323 where rownum<=300;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        36

36 rows selected.

SQL> select /*+ first_rows(10)*/* from test_0323 where rownum<=300;

36 rows selected.

SQL> select /*+ first_rows(10)*/* from test_0323 where rownum<=300;

36 rows selected.

SQL> select /*+ first_rows(10)*/* from test_0323 where rownum<=300;

36 rows selected.

SQL>

使用如下sql查询共享的cursor数量及多个cursor产生的原因
SQL> select kglhdpar, address,auth_check_mismatch, translation_mismatch,OPTIMIZER_MISMATCH
2 from v$sql_shared_cursor
3 where kglhdpar in
( select address
from v$sql
4 5 6 where sql_text like 'select /*+ first_rows(10)*/* from test_0323 where%');

KGLHDPAR ADDRESS A T O
---------------- ---------------- - - -
C000000054A893E8 C00000006CE52A78 N N N
C000000054A893E8 C000000046A9FEC0 N N Y
C000000054A893E8 C00000005EBEC9E0 N N Y
C000000054A893E8 C000000056A5DA80 N N Y

看到我这个语句有4个不同的cursor，因为我执行了4次，就生成了4个。而OPTIMIZER_MISMATCH列显示为Y,表明这是由于optimizer不匹配造成的子游标过高。很是郁闷，我的语句一成不变，也并未不停的改变optimizer，所以应该是一个bug。

在9201以上版本做相同测试，都未发现有如此情况，可见应该是此版本的bug，不过我并未对应到bug号。

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

转载于:http://blog.itpub.net/11088128/viewspace-691149/