open cursor

再议OPEN CURSOR与BULK COLLECT

2012/08/01 by admin Leave a Comment

有同学在T.askmac.cn上发帖关于bulk collect与open cursor的问题，帖子的地址在这里。他的疑问在于:

这么说来 OPEN_CURSOR 负责解析SQL语句和生成执行计划.

会不会去执行执行计划?

是不是在第一次提取的时候才会执行执行计划?

test_soruce

create table zengfankun_temp01 as select * from dba_objects;
select count(*) from zengfankun_temp01;–12,6826
analyze table zengfankun_temp01 compute statistics;

create or replace procedure test_open_cursor is
type type_owner is table of zengfankun_temp01.owner%type index by binary_integer;
type type_object_name is table of zengfankun_temp01.object_name%type index by binary_integer;
type type_object_id is table of zengfankun_temp01.object_id%type index by binary_integer;
type type_object_type is table of zengfankun_temp01.object_type%type index by binary_integer;
type type_last_ddl_time is table of zengfankun_temp01.last_ddl_time%type index by binary_integer;

l_ary_owner type_owner;
l_ary_object_name type_object_name;
l_ary_object_id type_object_id;
l_ary_object_type type_object_type;
l_ary_last_ddl_time type_last_ddl_time;

cursor cur_object is
select owner,object_name,object_id,object_type,last_ddl_time
from zengfankun_temp01
order by owner,object_name,object_type,last_ddl_time;
OPEN_START number;
OPEN_END number;
FETCH_START number;
FETCH_END number;
begin
DBMS_OUTPUT.ENABLE (buffer_size=>null) ;
OPEN_START:=dbms_utility.get_time();
open cur_object;
OPEN_END :=dbms_utility.get_time();
dbms_output.put_line(‘OPEN_TIME:’||TO_CHAR(OPEN_END-OPEN_START));
loop
FETCH_START:=dbms_utility.get_time();
fetch cur_object bulk collect into
l_ary_owner,
l_ary_object_name,
l_ary_object_id,
l_ary_object_type,
l_ary_last_ddl_time
limit 10000;
FETCH_END:=dbms_utility.get_time();
dbms_output.put_line(‘FETCH_TIME:’||TO_CHAR(FETCH_END-FETCH_START)||’ ROWCOUNT:’||cur_object%rowCount);

exit when cur_object%notfound or cur_object%notfound is null;
end loop;
end test_open_cursor;

OPEN_TIME:12
FETCH_TIME:21 ROWCOUNT:10000
FETCH_TIME:3 ROWCOUNT:20000
FETCH_TIME:3 ROWCOUNT:30000
FETCH_TIME:3 ROWCOUNT:40000
FETCH_TIME:3 ROWCOUNT:50000
FETCH_TIME:3 ROWCOUNT:60000
FETCH_TIME:3 ROWCOUNT:70000
FETCH_TIME:3 ROWCOUNT:80000
FETCH_TIME:3 ROWCOUNT:90000
FETCH_TIME:3 ROWCOUNT:100000
FETCH_TIME:3 ROWCOUNT:110000
FETCH_TIME:3 ROWCOUNT:120000
FETCH_TIME:1 ROWCOUNT:126826

重复执行

OPEN_TIME:0
FETCH_TIME:18 ROWCOUNT:10000
FETCH_TIME:3 ROWCOUNT:20000
FETCH_TIME:3 ROWCOUNT:30000
FETCH_TIME:3 ROWCOUNT:40000
FETCH_TIME:3 ROWCOUNT:50000
FETCH_TIME:3 ROWCOUNT:60000
FETCH_TIME:3 ROWCOUNT:70000
FETCH_TIME:3 ROWCOUNT:80000
FETCH_TIME:3 ROWCOUNT:90000
FETCH_TIME:3 ROWCOUNT:100000
FETCH_TIME:3 ROWCOUNT:110000
FETCH_TIME:3 ROWCOUNT:120000
FETCH_TIME:2 ROWCOUNT:126826

SQL已经处于软件解析了, 块大部分缓存到了内存当中.
因此OPEN CURSOR 时间接近0
第一次提取时间也降低了3个点.
而第N次时间并没改变.

怎么说了第N次的时间只有提取到数组所花费的时间.

很显然必有在某个地方存放结果集游标指针指向该结果集. 否则如何知道要提取10000条呢?

实际我没有完全理解这位同学想表达的完全clear的观点，我想着重和重复说明的是：

当OPEN CURSOR 操作发生时， PL/SQL引擎转到SQL引擎负责PARSE SQL语句获得执行计划，同时它会记录OPEN CURSOR这一刻的SNAPSHOT SCN 快照SCN，但是Oracle并不会实际FETCH相关的数据，也不会将这些数据复制到某个地方。

直到实际FETCH 数据时才会去访问实际的数据块，这些块一般都是Current Block， The most recent version of block ，这样的块的SCN >> Snapshot scn，需要通过UNDO数据构建出一个SCN 合适的Best Block ，以满足Read Consistentcy；如果此时存在的UNDO SNAPSHOT不足以构造出这样一个很久之前的Best Block的话，那么就可能出现ORA-1555错误。

为了证明我的观点，我会创建一个环境测试，这个环境会利用一张小表但是有这char(2000)这样的列，这导致一条记录将占用一个数据块，我会使用bulk collect fetch一次fetch 10 条记录，如果实验理想那么OPEN CURSOR时将只完成PARSE解析SQL和开始执行的操作，之后当每需要完成一次fetch bulk collect一次都需要去逻辑读取10个数据块，通过”_trace_pin_time”可以捕获Server Process去pin CR block的行为，换句话说可以看到一次Fetch Bulk Collect limit 10触发10个buffer被pin。

[oracle@nas ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 1 11:36:52 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
https://www.askmac.cn

SQL> create table maclean (t1 char(2000)) tablespace users pctfree 99;   

Table created.

SQL> begin       
  2  for i in 1..200 loop
  3  insert into maclean values('MACLEAN');
  4  commit ;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','MACLEAN');

PL/SQL procedure successfully completed.

SQL> select count(*) from maclean;

  COUNT(*)
----------
       200

SQL> select blocks,num_rows from dba_tables where table_name='MACLEAN';

    BLOCKS   NUM_ROWS
---------- ----------
       244        200

SQL> alter system set "_trace_pin_time"=1 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2232472 bytes
Variable Size            1795166056 bytes
Database Buffers         1325400064 bytes
Redo Buffers               17227776 bytes
Database mounted.
Database opened.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> 
SQL> 
SQL> declare
  2    cursor v_cursor is
  3      select * from sys.maclean;
  4    type v_type is table of sys.maclean%rowtype index by binary_integer;
  5    rec_tab v_type;
  6  begin
  7    open v_cursor;
  8    dbms_lock.sleep(30);
  9    loop
 10      fetch v_cursor bulk collect
 11        into rec_tab limit 10;
 12      dbms_lock.sleep(10);
 13      exit when v_cursor%notfound;
 14    end loop;
 15  end;
 16  /

 看一下它的10046 trace+ pin trace:

 PARSING IN CURSOR #47499559136872 len=337 dep=0 uid=0 oct=47 lid=0 tim=1343836146412056 hv=496860239 ad='11a11dbb0' sqlid='4zh7954ftuz2g'
declare
  cursor v_cursor is
    select * from sys.maclean;
  type v_type is table of sys.maclean%rowtype index by binary_integer;
  rec_tab v_type;
begin
  open v_cursor;
  dbms_lock.sleep(30);
  loop
    fetch v_cursor bulk collect
      into rec_tab limit 10;
    dbms_lock.sleep(10);
    exit when v_cursor%notfound;
  end loop;
end;
END OF STMT
PARSE #47499559136872:c=0,e=346,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1343836146412051
=====================
PARSING IN CURSOR #47499559126280 len=25 dep=1 uid=0 oct=3 lid=0 tim=1343836146414939 hv=3296884535 ad='11a11d250' sqlid='2mb1493284xtr'
SELECT * FROM SYS.MACLEAN
END OF STMT
PARSE #47499559126280:c=1999,e=2427,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=2568761675,tim=1343836146414937
EXEC #47499559126280:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2568761675,tim=1343836146415104

上面完成了 对 SELECT * FROM SYS.MACLEAN的 PARSE 并开始执行 ， 但是没有FETCH任何记录也没有pin 逻辑读任何数据块， 这说明了OPEN CURSOR操作的本质

*** 2012-08-01 11:49:36.424
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 30009361 duration=0 p2=0 p3=0 obj#=-1 tim=1343836176424782

等待了30s 

pin ktewh26: kteinpscan dba 0x10a6202:4 time 1039048805
pin ktewh27: kteinmap dba 0x10a6202:4 time 1039048847
pin kdswh11: kdst_fetch dba 0x10a6203:1 time 1039048898
pin kdswh11: kdst_fetch dba 0x10a6204:1 time 1039048961
pin kdswh11: kdst_fetch dba 0x10a6205:1 time 1039049004
pin kdswh11: kdst_fetch dba 0x10a6206:1 time 1039049042
pin kdswh11: kdst_fetch dba 0x10a6207:1 time 1039049089
pin kdswh11: kdst_fetch dba 0x10a6208:1 time 1039049123
pin kdswh11: kdst_fetch dba 0x10a6209:1 time 1039049159
pin kdswh11: kdst_fetch dba 0x10a620a:1 time 1039049191
pin kdswh11: kdst_fetch dba 0x10a620b:1 time 1039049225
pin kdswh11: kdst_fetch dba 0x10a620c:1 time 1039049260

kdst_fetch是实际fetch块中记录的函数 ， 这里fetch了10个块

完成一次实际的FETCH 

FETCH #47499559126280:c=0,e=536,p=0,cr=12,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836176425542

*** 2012-08-01 11:49:46.428
WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002694 duration=0 p2=0 p3=0 obj#=-1 tim=134383618642829

再次休眠10s 

pin kdswh11: kdst_fetch dba 0x10a620d:1 time 1049052211
pin kdswh11: kdst_fetch dba 0x10a620e:1 time 1049052264
pin kdswh11: kdst_fetch dba 0x10a620f:1 time 1049052299
pin kdswh11: kdst_fetch dba 0x10a6211:1 time 1049052332
pin kdswh11: kdst_fetch dba 0x10a6212:1 time 1049052364
pin kdswh11: kdst_fetch dba 0x10a6213:1 time 1049052398
pin kdswh11: kdst_fetch dba 0x10a6214:1 time 1049052430
pin kdswh11: kdst_fetch dba 0x10a6215:1 time 1049052462
pin kdswh11: kdst_fetch dba 0x10a6216:1 time 1049052494
pin kdswh11: kdst_fetch dba 0x10a6217:1 time 1049052525
FETCH #47499559126280:c=0,e=371,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836186428807

接着pin 10个数据块， 并实际fetch 一次

WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002864 duration=0 p2=0 p3=0 obj#=-1 tim=1343836196431754
pin kdswh11: kdst_fetch dba 0x10a6218:1 time 1059055662
pin kdswh11: kdst_fetch dba 0x10a6219:1 time 1059055714
pin kdswh11: kdst_fetch dba 0x10a621a:1 time 1059055748
pin kdswh11: kdst_fetch dba 0x10a621b:1 time 1059055781
pin kdswh11: kdst_fetch dba 0x10a621c:1 time 1059055815
pin kdswh11: kdst_fetch dba 0x10a621d:1 time 1059055848
pin kdswh11: kdst_fetch dba 0x10a621e:1 time 1059055883
pin kdswh11: kdst_fetch dba 0x10a621f:1 time 1059055915
pin kdswh11: kdst_fetch dba 0x10a6221:1 time 1059055953
pin kdswh11: kdst_fetch dba 0x10a6222:1 time 1059055992
FETCH #47499559126280:c=0,e=385,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836196432274

以下类似

可以看到上面的 DBA都是连续的   

............................

末尾部分

WAIT #47499559136872: nam='PL/SQL lock timer' ela= 10002933 duration=0 p2=0 p3=0 obj#=-1 tim=1343836366495589
pin kdswh11: kdst_fetch dba 0x10a62f6:1 time 1229119497
pin kdswh11: kdst_fetch dba 0x10a62f7:1 time 1229119545
pin kdswh11: kdst_fetch dba 0x10a62f8:1 time 1229119576
pin kdswh11: kdst_fetch dba 0x10a62f9:1 time 1229119610
pin kdswh11: kdst_fetch dba 0x10a62fa:1 time 1229119644
pin kdswh11: kdst_fetch dba 0x10a62fb:1 time 1229119671
pin kdswh11: kdst_fetch dba 0x10a62fc:1 time 1229119703
pin kdswh11: kdst_fetch dba 0x10a62fd:1 time 1229119730
pin kdswh11: kdst_fetch dba 0x10a62fe:1 time 1229119760
pin kdswh11: kdst_fetch dba 0x10a62ff:1 time 1229119787
FETCH #47499559126280:c=0,e=340,p=0,cr=10,cu=0,mis=0,r=10,dep=1,og=1,plh=2568761675,tim=1343836366496067

可以看到起始DBA是 0x10a6203 ， 末尾DBA 是 0x10a62ff

以下验证了起始DBA正是MACLEAN表的第一个数据块，而末尾DBA也正是Maclean表高水位块


getbfno函数用于将dba转换为数据文件号和块号

CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_str   VARCHAR2 (255) DEFAULT NULL;
   l_fno   VARCHAR2 (15);
   l_bno   VARCHAR2 (15);
BEGIN
   l_fno :=
      DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),
                                                       'xxxxxxxx'
                                                      )
                                           );
   l_bno :=
      DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),
                                                        'xxxxxxxx'
                                                       )
                                            );
   l_str :=
         'datafile# is:'
      || l_fno
      || CHR (10)
      || 'datablock is:'
      || l_bno
      || CHR (10)
      || 'dump command:alter system dump datafile '
      || l_fno
      || ' block '
      || l_bno
      || ';';
   RETURN l_str;
END;
/

Function created.

SQL> select getbfno('0x10a6203') from dual;

GETBFNO('0X10A6203')
--------------------------------------------------------------------------------
datafile# is:4
datablock is:680451
dump command:alter system dump datafile 4 block 680451;

SQL> select getbfno('0x10a62ff') from dual;

GETBFNO('0X10A62FF')
--------------------------------------------------------------------------------
datafile# is:4
datablock is:680703
dump command:alter system dump datafile 4 block 680703;

SQL> select dbms_rowid.rowid_block_number(min(rowid)),dbms_rowid.rowid_relative_fno(min(rowid)) from maclean;

DBMS_ROWID.ROWID_BLOCK_NUMBER(MIN(ROWID))
-----------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(MIN(ROWID))
-----------------------------------------
                                   680451
                                        4



SQL> select dbms_rowid.rowid_block_number(max(rowid)),dbms_rowid.rowid_relative_fno(max(rowid)) from maclean;

DBMS_ROWID.ROWID_BLOCK_NUMBER(MAX(ROWID))
-----------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(MAX(ROWID))
-----------------------------------------
                                   680703
                                        4

以上演示验证了3个观点：

1.当OPEN CURSOR 操作发生时， PL/SQL引擎转到SQL引擎负责PARSE SQL语句获得执行计划，同时它会记录OPEN CURSOR这一刻的SNAPSHOT SCN 快照SCN，但是Oracle并不会实际FETCH相关的数据，也不会将这些数据复制到某个地方。

2.直到实际FETCH 数据时才会去访问实际的数据块

3. 单纯的open cursor+ fetch bulk collect不会在”某个地方存放结果集”

关于V$OPEN_CURSOR

2011/07/01 by admin 6 Comments

在之前的一次讨论中，有同行指出V$OPEN_CURSOR中列出的不是OPEN CURSOR而是SESSION CACHED CURSOR，原因是在一次ORA-01000(maximum open cursors exceeded)事故中他没有从V$OPEN_CURSOR中找到大量的打开游标。

对于这个问题，我们可以利用JAVA程序做一个演示来说明，以下为JAVA代码：

package javaapplication2;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.*;
import java.sql.*;

public class Main {

    public static void main(String[] args) throws SQLException {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch(Exception e ){}
   Connection cnn1=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:G11R2", "maclean", "maclean");

  // Connection m[]=new Connection[2000];
   Connection myconn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:G11R2", "maclean", "maclean");

    Statement stat1=myconn.createStatement();
   ResultSet rst1=stat1.executeQuery("select * from v$version");
   while(rst1.next())
   {
       System.out.println(rst1.getString(1));
   }
   rst1=stat1.executeQuery("select distinct sid from v$mystat");

   while (rst1.next()){
   System.out.println("MY SID IS "+rst1.getString(1));
        }

   PreparedStatement s[]=new PreparedStatement[2000];
   PreparedStatement p;
   //ResultSet r[]=new ResultSet[2000];
   int i=0;
   while(i<2000){
    //  m[i]=DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.121:1521:G10R2", "maclean", "maclean");
      //s[i]=m[i].createStatement();
      //m[i].setAutoCommit(false);
      //s[i].execute("insert into testjava values(1)");
       p=myconn.prepareStatement("select /* FIND_ME_OPPO */ * from dual");
       p.execute();

            try {
                Thread.sleep(200);
            } catch (InterruptedException ex) {
                Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
            }

      i++;
      System.out.println(i+" cursor is ok !");
   }
   }
}

以上JAVA代码会打个一个数据库会话，并在循环中不断以prepareStatement对象执行SQL语句，且我们不使用close()方法关闭prepareStatement所打开的游标，实际上这是很多JDBC应用产生ORA-01000问题的主要原因，开发人员在使用prepareStatement时不知道要使用close()方法以回收OPEN CURSOR资源。

注意这里在使用JDBC API时的表现(可能是目前最流行应用形式)和PL/SQL中的游标是存在区别的，在PL/SQL使用close cursor语句并不会真正意义上关闭游标。出于性能的考量，PL/SQL中的游标将被缓存以备将来使用，同时Oracle会维护一张cursor的LRU列表，但如果当本会话的游标数量即将达到open_cursors参数所定义的上限数量时，老的游标将被真正意义上close，以便open后来者。

The following is a technical explanation provided by Oracle Development:

The server caches cursors opened by the PL/SQL engine. Each time

a cursor is closed, it is really moved to an Least Recently Used

(LRU) list of open cursors and left open. This is done as a

performance improvement by saving a ’round_trip’ from Client to Server

each time a cursor is opened.

Note that no optimization occurs. In other words, the cursor caching

code does not check to see if a particular query already has a cursor

open; it simply creates another one. This is done until OPEN_CURSORS

is reached. If you have OPEN_CURSORS cached as open, however, then if

an identical query occurs, the server reuses the cached cursor.

可以通过以下语句来了解系统中真正意义上的打开着的游标:

select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by b.name;

编译并尝试运行以上程序：

compile：
Created dir: C:\Users\Maclean Liu\Documents\NetBeansProjects\JavaApplication2\dist
Copy libraries to C:\Users\Maclean Liu\Documents\NetBeansProjects\JavaApplication2\dist\lib.
Building jar: C:\Users\Maclean Liu\Documents\NetBeansProjects\JavaApplication2\dist\JavaApplication2.jar
To run this application from the command line without Ant, try:
java -jar "C:\Users\Maclean Liu\Documents\NetBeansProjects\JavaApplication2\dist\JavaApplication2.jar"
jar：
成功生成（总时间：0 秒）

java -jar "C:\Users\Maclean Liu\Documents\NetBeansProjects\JavaApplication2\dist\JavaApplication2.jar"
MY SID IS 392
1 cursor is ok !
2 cursor is ok !
......................

以上程序打开的会话为392，我们来观察392会话的CURSOR情况:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

select sql_text, cursor_type, count(*)
  from v$open_cursor
 where sid = 392
 group by sql_text, cursor_type
 order by 3 desc
/

SQL_TEXT                                                     CURSOR_TYPE                                          COUNT(*)
------------------------------------------------------------ -------------------------------------------------- ----------
select /* FIND_ME_OPPO */ * from dual                        OPEN                                                     1279
select * from v$version                                      OPEN                                                        1
select privilege# from sysauth$ where (grantee#=:1 or grante DICTIONARY LOOKUP CURSOR CACHED                             1
select distinct sid from v$mystat                            OPEN                                                        1
insert into sys.aud$( sessionid,entryid,statement,ntimestamp OPEN-RECURSIVE                                              1
select /*+ connect_by_filtering */ privilege#,level from sys DICTIONARY LOOKUP CURSOR CACHED                             1

可以看到"select /* FIND_ME_OPPO */ * from dual"语句在V$OPEN_CURSOR中存在1279条记录，
且CURSOR TYPE均为OPEN

注意V$OPEN_CURSOR视图中的CURSOR_TYPE列直到11g release 2中才出现，如果该列在9i/10g中就有的话那么可能就不会造成那么多misunderstand了，实际上V$OPEN_CURSOR中的记录来源于X$KGLLK:

SQL> select view_definition
  2    from v$fixed_view_definition
  3   where view_name = 'GV$OPEN_CURSOR';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,
       kgllkuse,
       kgllksnm,
       user_name,
       kglhdpar,
       kglnahsh,
       kgllksqlid,
       kglnaobj,
       kgllkest,
       decode(kgllkexc, 0, to_number(NULL), kgllkexc),
       kgllkctp                                              -- CURSOR_TYPE
  from x$kgllk
 where kglhdnsp = 0
   and kglhdpar != kgllkhdl

KGLHDPAR!=KGLLKHDL 该条件排除了父游标，所以V$OPEN_CURSOR列出的均是child cursor
KGLHDNSP ==> NAME SPACE =0 

SQL> select distinct kgllkctp from X$kgllk;

KGLLKCTP
----------------------------------------------------------------
SESSION CURSOR CACHED
PL/SQL CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED
BUNDLE DICTIONARY LOOKUP CACHED

以上是六种CURSOR_TYPE，可以看到SESSION CURSOR CACHED和PL/SQL CURSOR CACHED也在其中：

SQL> select kgllkmod, kgllkctp, count(*)
  2    from X$KGLLK
  3   where KGLHDNSP = 0
  4   group by kgllkmod, kgllkctp;

  KGLLKMOD KGLLKCTP                                                           COUNT(*)
---------- ---------------------------------------------------------------- ----------
         1 SESSION CURSOR CACHED                                                    32
         1 DICTIONARY LOOKUP CURSOR CACHED                                          96
         1 OPEN-RECURSIVE                                                           42
         1 PL/SQL CURSOR CACHED                                                     19
         1 OPEN                                                                    216

注意不管是何种CURSOR_TYPE，本质上都是child cursor上的library cache lock，KGLLKMOD=1即这些library cache lock的lock mode均是NULL,也就是breakable parse lock。

Null (N): special for session persistency

Null locks are special and are only acquired on executable objects (child cursors, procedures, functions, and packages)

A broken null lock means the meta-data has changed.

Null locks are special. They are acquired on objects that are to be executed (child cursor, procedure, function, package, or type body) and they are used to maintain an interest on an object for a long period of time (session persistency), and to detect if the object becomes invalid. Null locks can be broken at any time. This is used as a mechanism to notify a session that an executable object is no longer valid. If a null lock is broken, and thus the object is invalidated, it is an indication to the user who was holding the null lock that the object needs to be recompiled.

A Null lock is acquired during the parse phase of SQL statement execution and is held as long as the shared SQL area for that statement remains in the shared pool. A null lock does not prevent any DDL operation, and can be broken to allow conflicting DDL operations, hence the term breakable parse lock.

A Null lock on an object is broken when there is an exclusive pin on the object. A null lock on a read-only object is broken where there is an exclusive pin on any of the parent objects it depends on.

因为CURSOR_TYPE(kgllkctp)列的出现我们在11.2中能够很方便地分辨OPEN CUROSR和SESSION CURSOR CACHED,但如果是在9i/10g/11gr1中则无法通过V$OPEN_CURSOR或X$KGLLK找出哪些是打开游标，另一些是会话缓存游标？

实际上Oracle Support在10g中已经意识到了这个问题，Metalink Note<Bug 7375227 – V$OPEN_CURSOR contains both open cursors and session cached cursors [ID 7375227.8]>说明了该问题：

Bug 7375227  V$OPEN_CURSOR contains both open cursors and session cached cursors
 This note gives a brief overview of bug 7375227.
 The content was last updated on: 10-JUL-2009
 Click here for details of each of the sections below.
Affects:

    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected	Versions >= 10.2.0.1 but < 11.2
    Versions confirmed as being affected	

        10.2.0.3 

    Platforms affected	Generic (all / most platforms affected)

Fixed:

    This issue is fixed in	

        11.2 (Future Release) 

Symptoms:

Related To:
    Code Improvement
    (None Specified)
    V$OPEN_CURSOR 

Description

    View V$OPEN_CURSOR contains both open cursors and session cached cursors and
    before this fix there was no way to distinguish them.
    This fix adds new column CURSOR_TYPE to V$OPEN_CURSOR which distinguishes
    between open cursors and cursors in the session cache. 

    Values are:
     SYSTEM - recursive sql cursors
     PL/SQL - for open pl/sql cursors
     KNT CACHED, KKS CACHED, PL/SQL CACHED, KQD CACHED, KQD BUNDLE CACHED
     , KXCC CACHED - for cached cursors

Cursor Sharing的相关诊断事件:

[oracle@rh2 ~]$ oerr ora 10270
10270, 00000, "Debug shared cursors"
// *Cause: Enables debugging code in shared cursor management modules
// *Action:

alter session set events '10270 trace name context forever, level 10';

[oracle@rh2 ~]$ oerr ora 10277
10277, 00000, "Cursor sharing (or not) related event (used for testing)"
// *Cause:
// *Action:

 alter session set events '10277 trace name context forever, level 1';

alter session set events  'immediate trace name library_cache level 10';

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: cursor sharing, library cache lock, open cursor, session_cached_cursors

Script:Diagnostic ORA-01000 maximum open cursors exceeded

2009/07/17 by admin Leave a Comment

以下脚本可以用于诊断ORA-01000打开游标过多错误:

set linesize 140 pagesize 1400

select
to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' )
/

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%cursor ca%'
/

select sum(a.value), b.name,a.sid
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by rollup (b.name,a.sid)
order by 1
/

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' 
order by 1 
/

select sid, count(*) from v$open_cursor group by sid
order by 2 
/

Exec   DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 

exec dbms_lock.sleep(300);

Exec   DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 


@?/rdbms/admin/awrrpt
upload the awr report

or 
select dbms_workload_repository.awr_report_text(l_dbid     => dbid,
                                                l_inst_num => instance_number,
                                                l_bid      => mid - 1,
                                                l_eid      => mid)
  from (select vd.dbid, vi.instance_number, mid
          from v$database vd,
               v$instance vi,
               (select max(snap_id) mid from dba_hist_snapshot dhs))
/

Filed Under: Oracle, Oracle脚本script Tagged With: open cursor, ORA-01000, Oracle脚本script