在之前的一次討論中，有同行指出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 - ProductionSQL> select * from global_name;GLOBAL_NAME -------------------------------------------------------------------------------- www.oracledatabase12g.comselect sql_text, cursor_type, count(*)from v$open_cursorwhere sid = 392group by sql_text, cursor_typeorder 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_definition2 from v$fixed_view_definition3 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$kgllkwhere kglhdnsp = 0and kglhdpar != kgllkhdlKGLHDPAR!=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$KGLLK3 where KGLHDNSP = 04 group by kgllkmod, kgllkctp;KGLLKMOD KGLLKCTP COUNT(*) ---------- ---------------------------------------------------------------- ----------1 SESSION CURSOR CACHED 321 DICTIONARY LOOKUP CURSOR CACHED 961 OPEN-RECURSIVE 421 PL/SQL CURSOR CACHED 191 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 cursorsThis note gives a brief overview of bug 7375227.The content was last updated on: 10-JUL-2009Click 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.2Versions 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 DescriptionView V$OPEN_CURSOR contains both open cursors and session cached cursors andbefore this fix there was no way to distinguish them.This fix adds new column CURSOR_TYPE to V$OPEN_CURSOR which distinguishesbetween open cursors and cursors in the session cache. Values are:SYSTEM - recursive sql cursorsPL/SQL - for open pl/sql cursorsKNT 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';

轉載于:https://www.cnblogs.com/macleanoracle/archive/2013/03/19/2967818.html

總結

以上是生活随笔為你收集整理的关于V$OPEN_CURSOR的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。