再议OPEN CURSOR与BULK COLLECT

有同学在T.askmaclean.com上发帖关于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
--------------------------------------------------------------------------------
了解 Maclean Liu 刘相兵 @诗檀软件
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

在之前的一次讨论中,有同行指出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.askmaclean.com
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';

Script:Diagnostic ORA-01000 maximum open cursors exceeded

以下脚本可以用于诊断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))
/

沪ICP备14014813号

沪公网安备 31010802001379号