實驗：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;

         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/，如需轉載，請註明出處，否則將追究法律責任。

實驗：cursor count超高的問題分析

相關文章