ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 实验:cursor count超高的问题分析

实验:cursor count超高的问题分析

原创 Linux操作系统 作者:浪漫双鱼 时间:2011-03-28 16:45:43 0 删除 编辑

        今天发现在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

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


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;

         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;

         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;

         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>

使用如下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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    87
  • 访问量
    237642