关于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';

滚动游标失效(Rolling Cursor Invalidations)

在Oracle 10g中DBMS_STATS包针对GATHER_TABLE/INDEX_STATS和DELETE_TABLE/INDEX_STATS等收集统计信息的存储过程提供了AUTO_INVALIDATE选项;
该参数允许用户指定是否让那些对统计信息有依存关系的游标失效,举例来说如果SQL游标涉及到的表,索引,列或固有对象的统计信息收到以上存储过程修改时,使用NO_INVALIDATE选项可以指定是否让这些受到影响的游标失效,何时失效。
NO_INVALIDATE选项可以有以下三种值:

  • TRUE : 不让相关游标失效
  • FALSE: 立即让相关游标失效
  • AUTO_INVALIDATE(default):让Oracle自己决定何时让游标失效。
--   no_invalidate - Do not invalide the dependent cursors if set to TRUE.
--      The procedure invalidates the dependent cursors immediately
--      if set to FALSE.
--      Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
--      invalidate dependend cursors. This is the default. The default
--      can be changed using set_param procedure.

当统计信息为DBMS_STATS包所修改,新的尚未在共享池中缓存的游标将直接使用这些统计信息; 对于已经存在的共享池中游标缓存,我们无法在原始子游标的基础上更新它们的执行计划;这些旧的子游标将被新的参考最新统计信息的子游标替代,这个过程包含一次硬解析以便获得新的优化树和执行计划;换而言之传统的立即游标失效(Immediate Cursor Invalidation)就是在统计信息更新后立即导致原始子游标的失效,而我们所说的滚动游标失效(Rolling Cursor Invalidations)是在统计信息成功更新的前提下保证原始子游标不立即失效;设想如果系统中有一张业务相关表,一旦我们更新了该表的统计信息可能导致大量共享失效,短期内硬解析将十分频繁并占用大量cpu,而且很多时候我们并不期望执行计划有显著变化;为了防止dbms_stats包统计信息时不要越帮越忙,就可以考虑到使用NO_INVALIDATE选项。

我们来看看RCI的具体表现:
[Read more…]

How to check and disable Adaptive Cursor Sharing in 11g

_optimizer_adaptive_cursor_sharing=false disables the feature.

There are 2 new columns in V$sql , IS_BIND_SENSITIVE and IS_BIND_AWARE that indicate the status for individual cursors.

1.) The parameter “_optimizer_adaptive_cursor_sharing” can be changed “on the fly”. This means if you issue an ‘alter system set “_optimizer_adaptive_cursor_sharing” = false |true; ‘ will be reflected in any existing session.

Remember, to disable ACS in 11g ,you should also set alter  system set “_optimizer_extended_cursor_sharing_rel”=’NONE’;

The parameter can be set at session or system level.
When set to NONE it stops the code from maintaining the internal statistical data about the binds.

 

And I advise you set “_optimizer_extended_cursor_sharing” = NONE .

 

2.) show parameter will always retrieve non-default settings also for hidden parameters:

sho parameter adapt
_optimizer_adaptive_cursor_sharing boolean FALSE

1.) non-default hidden (=underscore) parameters are shown with “show parameter ”
2.) the setting of hidden (=underscore) parameters are not supposed to be queried by end users.
3.) You may use 10053 tracing for obtaining the information for optimizer related parameters

sqlplus
set lines 200
set null null
set pages 99
set timi on
set time on

alter session set max_dump_file_size=unlimited;
alter session set events ‘10053 trace name context forever, level 1’;

— execute a statement causing a hardparse:

select /* a new comment */ * from dual;

exit

-> Use an editor or an unix command ( ie grep) and search for the _optimizer_adaptive_cursor_sharing parameter in the tracefile.

If you want to restore Optimizer_enabled_features from 11.2.0.1 to 10.2.0.4 , then you set:

alter session set "_optimizer_undo_cost_change" = '10.2.0.4'; -- 11.2.0.1
alter session set "_optimizer_null_aware_antijoin" = false; -- true
alter session set "_optimizer_extend_jppd_view_types" = false; -- true
alter session set "_replace_virtual_columns" = false; -- true
alter session set "_first_k_rows_dynamic_proration" = false; -- true
alter session set "_bloom_pruning_enabled" = false; -- true
alter session set "_optimizer_multi_level_push_pred" = false; -- true
alter session set "_optimizer_group_by_placement" = false; -- true
alter session set "_optimizer_extended_cursor_sharing_rel" = none; -- simple
alter session set "_optimizer_adaptive_cursor_sharing" = false; -- true
alter session set "_optimizer_improve_selectivity" = false ; -- true
alter session set "_optimizer_enable_density_improvements" = false; -- true
alter session set "_optimizer_native_full_outer_join" = off; -- force
alter session set "_optimizer_enable_extended_stats" = false; -- true
alter session set "_nlj_batching_enabled" = 0; -- 1
alter session set "_optimizer_extended_stats_usage_control" = 255; -- 224
alter session set "_bloom_folding_enabled" = false; -- true
alter session set "_optimizer_coalesce_subqueries" = false; -- true
alter session set "_optimizer_fast_pred_transitivity" = false; -- true
alter session set "_optimizer_fast_access_pred_analysis" = false; -- true
alter session set "_optimizer_unnest_disjunctive_subq" = false; -- true
alter session set "_optimizer_unnest_corr_set_subq" = false; -- true
alter session set "_optimizer_distinct_agg_transform" = false; -- true
alter session set "_aggregation_optimization_settings" = 32; -- 0
alter session set "_optimizer_connect_by_elim_dups" = false; -- true
alter session set "_optimizer_eliminate_filtering_join" = false; -- true
alter session set "_connect_by_use_union_all" = old_plan_mode; --true
alter session set "_optimizer_join_factorization" = false; -- true
alter session set "_optimizer_use_cbqt_star_transformation" = false; -- true
alter session set "_optimizer_table_expansion" = false ; -- true
alter session set "_and_pruning_enabled" = false ; -- true
alter session set "_optimizer_distinct_placement" = false ; -- true
alter session set "_optimizer_use_feedback" = false ; -- true
alter session set "_optimizer_try_st_before_jppd" = false ; -- true

脚本:格式化的V$SQL_SHARED_CURSOR报告

The function generates a summary report of the v$sql_shared_cursor view and additional diagnostic information depending on the reason code.
Counts all the versions that have ‘Y’ in any of the columns and if any have all ‘N’ too.

This script may be useful to diagnose ORA-600 [17059] errors

Running the Script
— Generate reports for all cursors with more than 100 versions using SQL_ID (10g and up)
select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=100;

— Generate reports for all cursors with more than 100 versions using HASH_VALUE
select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=100;

— Generate the report for cursor with sql_id cyzznbykb509s
select * from table(version_rpt(‘cyzznbykb509s’));

version_rpt3_12.sql

沪ICP备14014813号

沪公网安备 31010802001379号