以下是Maclean Liu所编写的Oracle 11g新特性的文章汇总列表:


11g新特性:A useful View V$DIAG_INFO
11gR2新特性:LMHB Lock Manager Heart Beat后台进程
给11gR2 RAC添加LISTENER监听器并静态注册
11.2 中Oracle Cluster Registry(OCR)可选的存储设备 实例启动现在提供Large Pages Information大内存页信息了
Does Duplicate Target Database need Pre-existing DB backup?
11g新特性SQL执行计划管理(SQL Plan Management)
11g新特性-在线实施补丁online patching
11g新特性:Rolling Upgrade With Physical Standby
11g新特性-SQL PLUS 错误日志
11g新特性:Note raised when explain plan for create index
11g compression 新特性
Examine 11g automatic block Corruption recovery
Duplicate standby database from active database
11g Database Installation flow
Setup Oracle Direct NFS Client
Recreate failovered primary database using Flashback Database
Oracle 11g中数据库能有多大?
undo backup optimization does not work on
11g新特性之IO校准(IO Calibration)
11g r2中对闪回数据归档的增强
11g Release 2 enhanced Tablespace Point In Time Recovery
Applying online patch on 11gr2
How to recover from root.sh on 11.2 Grid Infrastructure Failed
Uninstall/Remove Grid Infrastructure & Database in Linux
为11.2.0.2 Grid Infrastructure添加节点
Find password cracker in 11g
11g Multi-Column Correlation Stats and Dynamic Sampling
Oracle database 11g release2发布
How to check and disable Adaptive Cursor Sharing in 11g
How does SGA/PGA allocate on 11g AMM?
crsctl status resource -t -init in grid infrastructure
Learning 11g New Background Processes

Slide:11g新特性-在线实施补丁online patching

Slide:Upgrade RAC DB/RDBMS to in Linux By Maclean

Know Current Oracle Database Version Usage

前2周有客户向我咨询现在市面上的Oracle数据库各版本的使用率,考虑到10g将会在2013年超出Extentd Support期,客户希望找到合适的时机升级到11gr2上。

在网上找了一圈,包括IDC似乎都没有公开的调查结果。反而在OTN Database Forum上找到一个”Poll Results: Your production database version”老外搞的生产库版本投票。



CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

客户的一套11.2.0.1 RAC系统采用ASM diskgroup 存放ocr和votedisk,该REG diskgroup中的某个LUN disk由于硬件的原因损坏了,导致冗余的votedisk表决磁盘有一个处于OFFLINE状态,客户希望能删除该OFFLINE的votedisk并新增一个可用的。


crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. OFFLINE  5b3380d6367e4f94bf19e9db5f2f684e ()  []
 2. ONLINE   6802e6d139354fb3bf95725dd01a02fd (/dev/ocr2) [REG]
 3. ONLINE   a433d51ebd2d4facbfc8e95b017f5393 (/dev/asm-disk1) [REG]
 4. ONLINE   3784d344bffa4f6ebff21c4dd3c873bd (/dev/asm-disk2) [REG]
Located 4 voting disk(s).

crsctl delete css votedisk 5b3380d6367e4f94bf19e9db5f2f684e
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

居然无法移除ASM存储上的voting files,太搞笑了。


CRS-4258: Addition and Deletion of Voting Files are not Allowed Because the Voting Files are on ASM in 11gR2 [ID 1060146.1]
Applies to:
Oracle Server - Enterprise Edition - Version: to - Release: 11.2 to 11.2
Information in this document applies to any platform.

CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM in 11gR2.

Stale voting files are seen after accidently dropping one of ASM disks belonging to the ASM diskgroup where voting files are stored.
And CRS-4258 occurs when trying to delete the stale voting files using crsctl delete css votedisk FUID.

[root@grid]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 5b91aad0a2184f3dbfa8f970e8ae4d49 (/dev/oracleasm/disks/ASM10) [PLAY]
2. ONLINE 53b1b40b73164f9ebf3f498f6d460187 (/dev/oracleasm/disks/ASM9) [PLAY]
3. OFFLINE 82dfd04b96f14f6dbf36f5a62b118f61 () []

[root@grid]# crsctl delete css votedisk 82dfd04b96f14f6dbf36f5a62b118f61
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM

1. Seeing stale voting files is due to bug 9024611.

2. "delete" command is not available , only "replace" command is available when voting files are stored on  ASM diskgroup.    

    Please see Oracle Clusterware Administration and Deployment Guide11g Release 2 (11.2)


1. This issue is permanently fixed in

2. Apply patch 9024611. Please contact Oracle support if this patch is not available on your platform.

3. If CSS has stale voting files even after applying patch 9024611, do the following workaround -

Do something to trigger ASM to try to relocate the voting file.

e.g)  $ crsctl replace votedisk  +asm_disk_group   --- Put available ASM diskgroup

        $ crsctl query css votedisk         --- Check if voting files are all online on the new ASM diskgroup
        $ crsctl replace votedisk +PLAY    -- Put the original ASM diskgroup where voting files were 

4. If the workaround above cannot be followed for any reason then you can request the patch for unpublished bug 9409327 for your platform.


Hdr: 9294664 PCW ADMUTL PRODID-5 PORTID-226 9024611

crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   9f7f4f7f798d4f69bfe31653894421a2 (ORCL:GRID1) [GRID]
2. OFFLINE  a9b785a59c3c4f67bf15babc67ffb79a () []
3. OFFLINE  29988f37fa794f12bfea3f3672c99609 () []
4. ONLINE   a8b3a040195c4f54bfce8ef21bd4fa07 (ORCL:GRID3) [GRID]
5. ONLINE   a1e4fbd9df6f4f67bf8fc12fe9780721 (ORCL:GRID2) [GRID]
Located 5 voting disk(s).

[root@sdc-drrac01 grid]# crsctl delete css votedisk 
CRS-4258: Addition and deletion of voting files are not allowed because the 
voting files are on ASM

Ct is performing some voting disk failover scenarios in which he has removed 
the 2 votedisk which were on ASM buy drop disk using asmlib and after that 
recreating the disk again and start the cluster in exclusive mode and start 
the ASM and mount the diskgourp So that rebalancing has been done but after 

 crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   9f7f4f7f798d4f69bfe31653894421a2 (ORCL:GRID1) [GRID]
2. OFFLINE  a9b785a59c3c4f67bf15babc67ffb79a () []
3. OFFLINE  29988f37fa794f12bfea3f3672c99609 () []
4. ONLINE   a8b3a040195c4f54bfce8ef21bd4fa07 (ORCL:GRID3) [GRID]
5. ONLINE   a1e4fbd9df6f4f67bf8fc12fe9780721 (ORCL:GRID2) [GRID]
Located 5 voting disk(s).

and not able to drop the vote disk which is offiline 


as per bug 9024611 tried the workaround:

but while running 

crsctl css votedisk delete 

we got syntax error and found that there is no command with crsctl css ...

这个Note说明不能移除ASM存储内voting files的问题在11.上已经解决了,也可以通过安装one-off patch 9024611来修复。

但是实际在11.2.0.2上测试可以发现仍旧无法删除ASM上的voting files:

root@rh2 ~]# crsctl query crs  releaseversion
Oracle High Availability Services release version on the local node is []

[root@rh2 ~]# crsctl query crs  activeversion
Oracle Clusterware active version on the cluster is []

[grid@rh2 ~]$ /s01/grid/OPatch/opatch lsinventory
Invoking OPatch

Oracle Interim Patch Installer version
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /s01/grid
Central Inventory : /s01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    :
OUI version       :
OUI location      : /s01/grid/oui
Log file location : /s01/grid/cfgtoollogs/opatch/opatch2011-08-04_18-50-34PM.log

Patch history file: /s01/grid/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /s01/grid/cfgtoollogs/opatch/lsinv/lsinventory2011-08-04_18-50-34PM.txt

Installed Top-level Products (1): 

Oracle Grid Infrastructure                                 
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

Rac system comprising of multiple nodes
  Local node = rh2
  Remote node = rh3


OPatch succeeded.

[root@rh2 ~]# crsctl delete css votedisk a433d51ebd2d4facbfc8e95b017f5393

CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM



crsctl replace votedisk +DATA
Failed to create voting files on disk group DATA.
Change to configuration failed, but was successfully rolled back.
CRS-4000: Command Replace failed, or completed with errors.

方法四中指出的unpublished bug 9409327(Patch 9409327: OFFLINE VF ENTRY REMAINS AFTER PATCH FOR BUG 9024611),目前仅在IBM AIX on POWER Systems (64-bit)的11.2.0.1上有对应的补丁。

Oracle RAC内部错误:ORA-00600[kjbmprlst:shadow]一例

一套Linux x86-64上的11.2.0.1 4节点RAC系统中LMS GCS服务进程遭遇到内部错误ORA-00600[kjbmprlst:shadow],导致节点实例意外终止,具体日志如下:

Fri Jul 08 02:04:43 2011
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc  (incident=1011732):
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/PROD/PROD1/incident/incdir_1011732/PROD1_lms1_536_i1011732.trc
Fri Jul 08 02:04:44 2011
Trace dumping is performing id=[cdmp_20110708020444]
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc:
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_lms1_536.trc:
ORA-00600: internal error code, arguments: [kjbmprlst:shadow], [], [], [], [], [], [], [], [], [], [], []
LMS1 (ospid: 536): terminating the instance due to error 484
Fri Jul 08 02:04:45 2011
opiodr aborting process unknown ospid (27387) as a result of ORA-1092
System state dump is made for local instance
System State dumped to trace file /u01/app/oracle/diag/rdbms/PROD/PROD1/trace/PROD1_diag_513.trc
Fri Jul 08 02:04:54 2011
Termination issued to instance processes. Waiting for the processes to exit
Fri Jul 08 02:04:58 2011
ORA-1092 : opitsk aborting process

该ORA-00600[kjbmprlst:shadow]错误定位为11.2.0.1上的Bug 10121589或Bug 9458781:

Bug 10121589  ORA-600 [kjbmprlst:shadow] can occur in RAC

    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected 	Versions BELOW 12.1
    Versions confirmed as being affected 	 

    Platforms affected	Generic (all / most platforms affected)


    This issue is fixed in	

        12.1 (Future Release) Bundle Patch 2 for Exadata Database Bundle Patch 7 for Exadata Database 


Related To:

    Internal Error May Occur (ORA-600)
    ORA-600 [kjbmprlst:shadow] 

    RAC (Real Application Clusters) / OPS 


    An ORA-600 [kjbmprlst:shadow] can occur if the fix for bug 9979039
    is present.

     One off patches for 10200390 should also include this fix.

Bug 9458781  Missing close message to master leaves closed lock dangling crashing the instance with assorted Internal error


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

    Platforms affected	Generic (all / most platforms affected)


    This issue is fixed in	 (Server Patch Set) Bundle Patch 4 for Exadata Database 


Related To:

    Instance May Crash
    Internal Error May Occur (ORA-600)

    RAC (Real Application Clusters) / OPS 


    A lock is closed without sending a message to the master.
    This causes closed lock dangling at the master crashing the instance with different internal errors.

    Reported internal errors so far are :

该kjbmprlst:shadow内部函数用以管理kjbm shadow锁(/libserver10.a/kjbm.o )信息,存在某个已关闭的lock没有及时message给master node的代码漏洞,目前除了安装补丁外没有已验证的workaround办法(disable drm似乎是无效的):

oradebug lkdebug (track resources, take dumps)
KCL history
KJBL history
KJL history

PCM (GCS) and non-PCM (GES) resources are kept separate and use separate code paths.
Resource table: kjr and kjrt
Lock table: kjlt
Processes: kjpt
Resource table: kjbr
Lock table: kjbl

DLM Structures (continued)
/* PCM resource structure */
typedef struct kjbr {                                /* 68 bytes on sun4u */
  kjsolk       hash_q_kjbr;                             /* hash list : hp */
  ub4          resname_kjbr[2];	                     /* the resource name */
  kjsolk       scan_q_kjbr; /* chain to lmd scan q of grantable resources */
  kjsolk       grant_q_kjbr;                 /* list of granted resources */
  kjsolk       convert_q_kjbr;       /* list of resources being converted */
  ub4          diskscn_bas_kjbr;         /* scn(base) known to be on disk */
  ub2          diskscn_wrap_kjbr;        /* scn(wrap) known to be on disk */
  ub2          writereqscn_wrap_kjbr;    /* scn(wrap) requested for write */
  ub4          writereqscn_bas_kjbr;     /* scn(base) requested for write */
  struct kjbl *sender_kjbr;                 /* lock elected to send block */
  ub2          senderver_kjbr;                  /* version# of above lock */
  ub2          writerver_kjbr;                  /* version# of lock below */
  struct kjbl *writer_kjbr;                /* lock elected to write block */
  ub1          mode_role_kjbr; /* one of 'n', 's', 'x' && one of 'l' or 'g' */
  ub1          flags_kjbr;                        /* ignorewip, free etc. */
  ub1          rfpcount_kjbr;                      /* refuse ping counter */
  ub1          history_kjbr;                /* resource operation history */
  kxid         xid_kjbr;                          /* split transaction ID */
} kjbr ;

/* kjbl - PCM lock structure
** Clients and most of the DLM will use the KJUSER* or KJ_* modes and kscns  */

typedef struct kjbl {                                /* 52 bytes on sun4u */
  union {                     /* discriminate lock@master and lock@client */
    struct {                                           /* for lock@master */
      kgglk        state_q_kjbl;             /* link to chain to resource */
      kjbopqi     *rqinfo_kjbl;                             /* target bid */
      struct kjbr *resp_kjbl;                   /* pointer to my resource */
    } kjbllam;                                 /* KJB Lock Lock At Master */
    struct {                                           /* for lock@client */
      ub4         disk_base_kjbl;        /* disk version(base) for replay */
      ub2         disk_wrap_kjbl;        /* disk version(wrap) for replay */
      ub1         master_node_kjbl;                   /* master instance# */
      ub1         client_flag_kjbl;     /* flags specific to client locks */
      ub2         update_seq_kjbl;               /* last update to master */
    } kjbllac;                                 /* KJB Lock Lock At Client */
  } kjblmcd;                        /* KJB Lock Master Client Discrimnant */
  void  *remote_lockp_kjbl;           /* pointer to client lock or shadow */
  ub2    remote_ver_kjbl;                         /* remote lock version# */
  ub2        ver_kjbl;                                     /* my version# */
  ub2        msg_seq_kjbl;                         /* client->master seq# */
  ub2        reqid_kjbl;                         /* requestid for convert */
  ub2        creqid_kjbl; /* requestid for convert that has been cancelled */
  ub2        pi_wrap_kjbl;                     /* scn(wrap) of highest pi */
  ub4        pi_base_kjbl;                     /* scn(base) of highest pi */
  ub1        mode_role_kjbl; /* one of 'n', 's', 'x' && one of 'l' or 'g' */
  ub1        state_kjbl;       /* _L|_R|_W|_S, notify, which q, lock type */
  ub1        node_kjbl;                       /* instance lock belongs to */
  ub1        flags_kjbl;                                /* lock flag bits */
  ub2        rreqid_kjbl;                               /* save the reqid */
  ub2         write_wrap_kjbl;        /* last write request version(wrap) */
  ub4         write_base_kjbl;        /* last write request version(base) */
  ub4         history_kjbl;                     /* lock operation history */
} kjbl;

PCM DLM locks that are owned by the local instance are allocated and embedded in an LE structure.
PCM DLM locks that are owned by remote instances and mastered by the local instance are allocated in SHARED_POOL.

PCM Locks and Resources
Fields of interest in the kclle structure: kcllerls or releasing; kcllelnm or name(id1,id2);
kcllemode or held-mode; kclleacq or acquiring; kcllelck or DLM lock.

Fields of interest in the kjbr structure: resname_kjbr[2] or resource name; grant_q_kjbr or grant queue;
convert_q_kjbr or convert queue; mode_role_kjbr, which is a bitwise merge of grant mode and
role-interpreted NULL(0x00), S(0x01), X(0x02), L0 Local (0x00), G0 Global without PI (0x08), G1 Global with PI (0x018).

The field mode_role_kjbl in kjbl is a bitwise merge of grant, request, and lock mode: 0x00 if grant NULL;
0x01 if grant S; 0x02 if grant X; 0x04 lock has been opened at master; 0x08 if global role (otherwise local);
0x10 has one or more PI; 0x20 if request CR; 0x40 if request S; 0x80 if request X.

Someone has to keep a list of all buffers and where they are mastered
This is called Global Resource Directory (GRD)
GRD is present on all the instances of the cluster
To find out the master:
select  b.dbablk, r.kjblmaster master_node
from x$le l, x$kjbl r, x$bh b
where b.obj =
and b.le_addr = l.le_addr
and l.le_kjbl = r.kjbllockp

Oracle Support宣称可以通过11.2.0.2 (Server Patch Set) Bundle Patch 4 for Exadata Database修复该bug,但是有迹象表明在11.2.0.2上仍可能发生该ORA-00600[kjbmprlst:shadow]内部错误,同时该bug更多地发生在超过2个节点的RAC系统中。



根据metalink文档<ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]>在11g中将逐渐废弃cursor_sharing参数的SIMILAR选项,原因是在今后的版本中Exact和Force选项可以满足游标共享的需求了,使用SIMILAR选项可能引发额外的version_count过多或cursor pin s on X等待事件。

We recommend that customers discontinue setting cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g. A number of customers have seen an increase in the number of child cursors since migrating to Oracle Database 11g Release 2. This can lead to many problems including complete CPU saturation of a machine requiring a database instance bounce or general database performance issues in the form of waits on mutexes and ‘library cache lock’.
From Oracle versions 9.0 through 11.1, an oracle instance would limit the number child cursors in the shared pool associated with a single parent to 1024 before it would mark the parent OBSOLETE effectively invalidating it and all the children. Then a new parent with one child would be created and used going forward. But this would only limit the degradation of performance for some environments rather than fix something that could be addressed more effectively through improved application coding. (The attempt to address this from the database side also introduced other issues like bug 5177766). The child limit was removed by Oracle development because it was only masking an application problem at the expense of database performance for better designed applications. In addition, the obsolete code would not work in cases when SQL was wrapped within PL/SQL. The fundamental problem that obsolete code was masking is application code that was written incorrectly with regards to the ability to be shared. For example,  it is not written with user binds or the literal characteristics differ to a high degree.
Therefore, setting cursor_sharing = SIMILAR is highly discouraged in Oracle Database 11g Release 2 (and generally has not been recommended for most environments even in earlier versions) for several reasons:
1) This parameter is generally overly restrictive in what it actually allows to be shared. SIMILAR tells oracle to try and share cursors by replacing all literals with binds for legacy applications, but directs that sharing only be performed when all the replaced literal values were exactly the same (in the case of predicates referencing columns with histograms or using inequality operators such as BETWEEN, <, and !=)
2) This parameter seems to bypass a lot of the improvements made with Oracle Database 11g’s Adaptive Cursor Sharing feature and other abilities in the Cost Based Optimizer code to make better decisions on what execution plans should and should not be shared.
3) Having many child cursors all associated with 1 parent cursor could perform much worse than having many parent cursors that would be seen with having the default setting of cursor_sharing = EXACT (or FORCE). The scenario of many thousands of child cursors associated with 1 parent results in a potential bottleneck for searches for matching cursors (soft parsing) within the library cache.
The cursor_sharing parameter was introduced as a workaround for legacy applications that could not scale because they had not yet been redesigned to use bind variables. It has been presumed that most applications have been redesigned since then. If you are still using such an application, our recommendation is to set cursor_sharing = FORCE. This setting maximizes cursor sharing while leveraging the Adaptive Cursor Sharing framework to generate multiple execution plans based on different literal value ranges if necessary.

What do you need to do?

Change the cursor_sharing to either FORCE or EXACT, keeping in mind the effects of either.

this is due to the setting in the init.ora:

Please set immediately
alter system set cursor_sharing=’FORCE’ scope=both;

to reduce the number of versions for a single sql statement whic is overloading your shared_pool.

377847.1 for SIMILA not FORCE are not the same

When bind variables are peeked. The parse engine makes a decision as to the ‘safety’ of these peeked values for creating plans based upon whether it is felt that different values could produce different plans.

The usual (but not the only) reason for such different plans is the use of CURSOR_SHARING=SIMILAR and the presence of histogram column statistics on the column that the bind is being compared with when using the Cost Based Optimizer (CBO). If there are histograms on the column, then the bind value may be deemed to be ‘unsafe’ because there is the potential chance that the different values could produce a different explain plan and the selection of a single plan for all values may not be ‘safe’ in terms of performance. If the bind is deemed ‘unsafe’ then multiple children are created for each set of different bound values so that different plans can be associated with them. This occurs in a few scenarios but the most common is with histogram stats on an equality predicate.


With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

Is the operator NEITHER of   ‘=’ or ‘!=’
Are there Column Histograms present on the column.

If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, <, >=, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see:

Note:261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE

With histogram stats on an equality predicate, this can cause severe problems (in terms of sharing) if there is, for example, a histogram on a main key column.eg:

select … from orders where orderid=’literal’;

If  there is a histogram on “orderid” then the bind will likely get marked unsafe and a new child will be produced for every single value of ‘literal’. The SQL would only be shared if the value of ‘literal’ matches exactly to a value used already.

Remember that if literals are converted to binds due to CURSOR_SHARING then they are subject to this checking, though unsafe binds are really only an issue if CURSOR_SHARING is SIMILAR.

In an OLTP type environment it would be sensible to only use histogram statistics on columns that need it (i.e. where there are only a few distinct values with heavy skew and where different plans are needed for different predicate values). This way most SQL is shared and the absence of histograms keeps predicates safe except where needed.


If CURSOR_SHARING = FORCE binds can still be “unsafe” (in terms of performance) if used by the optimizer in decisions but this should not then affect shareability of the SQL since CURSOR_SHARING=FORCE does not care about unsafe literals, but the cursor should stil lbe shared. e.g.: In the above example with orderid=’literal’, without histograms, the CBO does not need to look at ‘literal’ to determine the selectivity of the predicate and so the bind does not get marked unsafe.
If there is histograms, the predicate is marked as unsafe, but since FORCE uses the same plan whatever the circumstance, this does not matter. Only where non data literals for whom different values alter the actual meaning of the SQL (e.g. order by 1 versus order by 2) will an unsafe predicate have an affect on plans.

Note that, prior to 11g, unsafe literals are NOT covered by ‘bind mismatch’ in V$SQL_SHARED_CURSOR  as this is for user bind metadata mismatches. ie: different max bind lengths or bind type mismatches.
In 11g R2 (and Patchset) a new column has been added to V$SQL_SHARED_CURSOR to check if literal replacement is used with CURSOR_SHARING=SIMILAR. The new column HASH_MATCH_FAILED  is set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement. The unshared child cursors may have histogram data
on key columns used in equality predicates, or range predicates with literals which the optimizer has marked
as unsafe.

From the optimizer point of view, these additional plans for ‘unsafe’ bind variables explain why multiple good plans may occur for peeked bind variables, even though the standard behavior for binds is to use peeked binds for the initial parse and then use the resultant plan for all other iterations. With unsafe binds, different plans for different peeked values can occur.

Background Information

This issue has been coming more in to focus with dynamic sampling in 10.2 since the default was changed from 1 to 2. When optimizer_dynamic_sampling is greater than 1 then Dynamic sampling emulates statistics + histograms. If histograms are created, then binds may be marked as unsafe and produce different plans for different values. With optimizer_dynamic_sampling > 1 a predicate can create a new version of a sql statement for each different value, even if there are no histograms (or even statistics) on a table (since dynamic sampling may create these in the background).





Note #1: Forcing cursor sharing among similar (but not identical)
statements can have unexpected results in some DSS applications
and in applications using stored outlines.

Note #2: Setting CURSOR_SHARING to FORCE causes an increase in the
maximum lengths (as returned by DESCRIBE) of any selected
expressions that contain literals (in a SELECT statement).
However, the actual length of the data returned will not change.

Cursor_sharing 相关的BUG 列表:


NB Bug Fixed Description
14456124 Predicate push may not occur with cursor sharing
14053457, ORA-917 parsing SQL with indicator binds with CURSOR_SHARING
12723295, ORA-600 [qerixGetKey:optdesc] with function based index and CURSOR_SHARING
9877960, ORA-600 or similar using CURSOR_SHARING with HS connections
14087914 Wrong results from ExistsNode with CURSOR_SHARING
+ 13550185,,, Hang / SGA memory corruption / ORA-7445 [kglic0] when using multiple shared pool subpools
13023854, Long parse time / hang for SQL with nested CASE expressions with CURSOR_SHARING enabled
12862170 INSERT ALL fails with ORA-600[kkslhsh1] with CURSOR_SHARING enabled / High Version Count on HASH_MATCH_FAILED
12797420,, “library cache: mutex X” waits on DB instance handle with CURSOR_SHARING
12649442,,, ORA-7445 [kxscod] with CURSOR_SHARING=FORCE or SIMILAR
12596444,,, Cursor not shared with CURSOR_SHARING if SQL has a CASE expression or set operation (UNION)
12534597 Bind Peeking is disabled for remote queries
12374212,,, Assorted dump , internal errors, memory corruptions with cursor_sharing = force
12345980, high parse time with cursor_sharing=force when session_cached_cursors set
11858021, ORA-600 [kpoal8-1] using DG4DRDA with CURSOR_SHARING=force
11806961, ORA-600 [kkspsc0: basehd] using CURSOR_SHARING
11738259,,, ORA-600 [kghssgfr2] using CURSOR_SHARING=FORCE
11714159, ORA-917 occurs with CURSOR_SHARING even if patch:9877980 is applied – superseded
11076030, Wrong results for XDB when CURSOR_SHARING enabled
11069199,,, ORA-600 [kksObsoleteCursor:invalid stub] with CURSOR_SHARING = SIMILAR | FORCE if fix 10187168 present
11063191,,,, ORA-4031 with hint /*+ CURSOR_SHARING_EXACT */ – excessive “KKSSP^nn” memory
10126094,,, ORA-600 [kglLockOwnersListAppend-ovf] from literal replacement on SQL issued from PLSQL
10013170, ORA-600 [736] from literal replacement with a “WAIT n” clause
* 9877980,,, ORA-7445[kkslMarkLiteralBinds] / Assorted Errors on if cursor sharing is enabled – Affects RMAN
9877964, ORA-600 [19003] raised by LIKE :BIND in query
9680430, High version count with CURSOR_SHARING = FORCE due to CBO transformation
9548104, OERI [qcsfbdnp:1] instead of ORA-1788 with cursor sharing
9413962, Many child cursors / ORA-600 [opixrb-3.0] [2005] [ORA-02005] binding literal for remote RPI
9411496, ORA-979 on GROUP BY query with CURSOR_SHARING set
9362218, Literals replaced by binds when CURSOR_SHARING=EXACT
9348402 OERI [kks-hash-collision] can occur with CURSOR_SHARING=FORCE|SIMILAR
9223586, Problems with variable length NCHAR literals with cursor sharing
9031183, ORA-1722 with CURSOR_SHARING=SIMILAR and with NCHAR
9008904, Dump (audTransFro) with CURSOR_SHARING
8545377, ORA-1780 with CURSOR_SHARING on XML queries
8246445, Query rewrite not working for multi-MV rewrite with literal replacement
5751866 Wrong Results with CASE and CURSOR_SHARING
9767674 Dump [kkslmtl] using CURSOR_SHARING – superceded
8794693 Dump [kkscsmtl] using literal replacement (CURSOR_SHARING)
8491399 Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype
8453245 Many child cursors with CURSOR_SHARING = FORCE
8264642 ORA-600 [kkexbindopn0] with CURSOR_SHARING = SIMILAR
7651092 ORA-1000 with Literal Replacement, EXECUTE IMMEDIATE and CURSOR_SHARING_EXACT hint (affects DBMS_STATS)
7516867,, Intermittent Wrong results from literal replacement with fix for bug 6163785
7272297,,, Memory corruption / OERI[17114] / OERI[17125] with literal replacement
7212120, Session cursor cache not used properly when CURSOR_SHARING=force/similar
6337716,, Wrong max column size for NULL strings with literal replacement
5757106,, OERI[15851] selecting aggregate of a constant with literal replacement
4071519,, GROUP BY query with CURSOR_SHARING fails with ORA-1802
3461251, V$SQL_SHARED_CURSOR shows all N with CURSOR_SHARING
7296258, Intermittent Wrong results from literal replacement and remote objects
6163785, Intermittent Wrong Results with dblink and cursor_sharing
8202234 Intermittent Wrong Results with dblink and cursor_sharing
5863277, ORA-1008 from SQL on second run when cursor_sharing=similar/force
5762750, ORA-907 when cursor_sharing is enabled
5476507, OERI[15868] / OERI[15160] can occur with cursor sharing
5364819, OERI[kkslpbp:1] when using literal replacement
5254759, ORA-12801/ORA-1008 occurs on a parallel query with bind variables
5177766, OERI[17059] with SESSION_CACHED_CURSORS
5155885, OERI[kkslgbv0] with CURSOR_SHARING=similar
+ 5146740, Wrong results with bind variables/CURSOR_SHARING
5082178, Bind peeking may occur when it should not
5055175, Dump [kkslpkp] using literal replacement with timezone literals – superceded
4867724, Literal replacement limits column names to 30 characters
4698156, ORA-12850 querying GV$ views when CURSOR_SHARING=FORCE
4607460,, Dump [opipls] when CURSOR_SHARING = SIMILAR | FORCE
4513695, Poor performance for SELECT with ROWNUM=1 with literal replacement
4458226,,, High version count with cursor_sharing=force
4456646, Dump (kxscod/memcmp) using literal replacement
4451881,, OERI[kkslhsh1] from Insert as SELECT
4436832, False ORA-979 “not a group by expression” with literal replacement
4359367,, High version_count with cursor sharing
4254094, OERI[qerrmObnd1][932] possible for queries over DB links
4202503, Parse errors possible using CONTAINS with cursor_sharing=similar/force
4200541, Parse error when CURSOR_SHARING=FORCE|SIMILAR with user binds
3880881,, Dump (kkslMarkLiteralBinds) using cursor sharing
2837580, Dump if SQL has > ~35000 literals
13349665 ORA-600 [kkslmtl-valnotfound] with fix for bug 9767674 (eg After applying PSU
3980673,, Literal replacement may dump (kkslpbp) if user binds have names like SYS_B_xx
3842253,, Dump (kgghash) using literal replacement
3819834,, Dump (kkslflb) with literal replacement
3818541,, CURSOR_SHARING=force does not work if SQL has an NVL or DECODE predicate
3737955,, Long parse times for long inlists / many AND/OR terms
3668572,, ORA-979 when CURSOR_SHARING = force|similar with inline view and GROUP BY
3645694, Poor plan from SQL with literal replacement and LIKE .. ESCAPE ..
3406977,, High version count in V$SQL due to binds marked as non-data with CURSOR_SHARING=FORCE
3148830,, Select over database link fails with OERI[opixmp-7] if CURSOR_SHARING=similar
3128363, OCIAttrGet returns wrong size when CURSOR_SHARING=SIMILAR or FORCE
3007574 OERI:15212 using CURSOR_SHARING=FORCE with database links
5526018 Dump [kkslpbp] / OERI[kkslpbp:1] with materialized view and literal replacement
3132071, Wrong results possible with CURSOR_SHARING=FORCE|SIMILAR
3045623, OERI[kkslhsh1] possible with CURSOR_SHARING=FORCE
2958096, Using XMLType / objects over DBLINKS with CURSOR_SHARING may dump
2843601, DML across a dblink fails with ORA-24370 if cursor_sharing is enabled
2643579, ORA-7445 [kxspoac] using CURSOR_SHARING=FORCE (or similar)
2620541, ORA-7445 [KXSPOAC] can occur on DML using CURSOR_SHARING
2508702, ORA-7445 [KKSLHIB] with CURSOR_SHARING=FORCE with INTO clause
2442125, Wrong results from PQ which compares a CHAR column to a BIND variable
2308292,, ORA-7445 [kkslhib] for INTO <bind> using CURSOR_SHARING and SESSION_CACHED_CURSORS
2273604,, CURSOR_SHARING=FORCE may not share cursors using a BETWEEN clause with CBO
2052836,,, Client dump possible using CALL statement with CURSOR_SHARING=FORCE
4197915 CAST( x as number(n)) fails in PQ slaves with ORA-1727 with cursor_sharing
3614299 Dump adding a subscriber with a rule containing literals with CURSOR_SHARING = FORCE|SIMILAR
2442097 INSERTS into USER-DEFINED TYPES with CURSOR_SHARING=FORCE does not share cursors
2237610 OERI:[kgskbwt1] / OERI:[kskbind1] possible using CURSOR_SHARING
2300719, Spin possible in PRSGNT when CURSOR_SHARING=SIMILAR
2262665,, ORA-7445 [kkslpbp] possible with CURSOR_SHARING=FORCE
2259787, Dump possible in EXPCMPBND() with CURSOR_SHARING and GROUPING SETS
2154645, Spin in KKSFBC possible when CURSOR_SHARING=FORCE
1947974, False ORA-1008 possible using EXECUTE IMMEDIATE with CURSOR_SHARING=SIMILAR
1840199, OERI:EVAGGSID-1 possible using user defined aggregates (UDAG) with CURSOR_SHARING=FORCE|SIMILAR
1782025, Client may dump/error using DML RETURNING with CURSOR_SHARING=FORCE|SIMILAR
1777504,, Hang possible with CURSOR_SHARING=FORCE (or SIMILAR in 9i)
1764925, OERI:QCTSTC2O1 possible using CURSOR_SHARING = SIMILAR
2159152, Cursors not shared with CURSOR_SHARING=FORCE if it has a transitive predicate
1538450, Dump in KXSPOAC possible CURSOR_SHARING=FORCE set
1365873, OERI:17182 / CGA corruption with CURSOR_SHARING=FORCE
1358871, CURSOR_SHARING=FORCE may dump if first bind is a literal
984251, CURSOR_SHARING=FORCE can cause ORA-29909 when using an ANCILLARY OPERATOR
2485018 OERI:[KKSLGOP1] from SQL issued over a DBLINK with CURSOR_SHARING
2221407 ORA-3106 / OERI:15212 using BINDS over DBLINK with CURSOR_SHARING=FORCE
1545473, ORA-979 possible using CURSOR_SHARING=FORCE with GROUP BY & ORDER BY & LITERALS
1111796 Dump possible from CURSOR_SHARING=FORCE
984132 Literal in cursor expression can CORE DUMP with CURSOR_SHARING=FORCE


11g默认启用强大的审计选项,AUDIT_TRAIL参数的缺省值为DB,这意为着审计数据将记录在数据库中的AUD$审计字典基表上。Oracle官方宣称默认启用的审计日志不会对绝大多数产品数据库的性能带来过大的负面影响,同时Oracle公司还推荐使用基于OS文件的审计日志记录方式(OS audit trail files)。

注意因为在11g中CREATE SESSION将被作为受审计的权限来被记录,因此当SYSTEM表空间因磁盘空间而无法扩展时将导致这部分审计记录无法生成,这将最终导致普通用户的新会话将无法正常创建,普通用户将无法登陆数据库。在这种场景中仍可以使用SYSDBA身份的用户创建会话,在将审计数据合适备份后删除一部分记录,或者干脆TRUNCATE AUD$都可以解决上述问题。


注意在默认情况下会以AUTOEXTEND ON自动扩展选项创建SYSTEM表空间,因此系统表空间在必要情况下还是会自动增长的,我们所需注意的是磁盘上的剩余空间是否能够满足其增长需求,以及数据文件扩展的上限,对于普通的8k smallfile表空间而言单个数据文件的最大尺寸是32G。


SQL> select * from v$version;

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

SQL> select * from global_name;


SQL> select privilege,success,failure from dba_priv_audit_opts;

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
CREATE ANY JOB                           BY ACCESS  BY ACCESS
DROP PROFILE                             BY ACCESS  BY ACCESS
ALTER PROFILE                            BY ACCESS  BY ACCESS

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER DATABASE                           BY ACCESS  BY ACCESS
GRANT ANY ROLE                           BY ACCESS  BY ACCESS
DROP ANY TABLE                           BY ACCESS  BY ACCESS
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
CREATE ANY TABLE                         BY ACCESS  BY ACCESS
DROP USER                                BY ACCESS  BY ACCESS
ALTER USER                               BY ACCESS  BY ACCESS
CREATE USER                              BY ACCESS  BY ACCESS
CREATE SESSION                           BY ACCESS  BY ACCESS
AUDIT SYSTEM                             BY ACCESS  BY ACCESS

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS

23 rows selected.


SQL> select audit_option,success,failure from dba_stmt_audit_opts;

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS
SYSTEM AUDIT                             BY ACCESS  BY ACCESS
CREATE SESSION                           BY ACCESS  BY ACCESS
CREATE USER                              BY ACCESS  BY ACCESS
ALTER USER                               BY ACCESS  BY ACCESS
DROP USER                                BY ACCESS  BY ACCESS
PUBLIC SYNONYM                           BY ACCESS  BY ACCESS
DATABASE LINK                            BY ACCESS  BY ACCESS
ROLE                                     BY ACCESS  BY ACCESS
PROFILE                                  BY ACCESS  BY ACCESS
CREATE ANY TABLE                         BY ACCESS  BY ACCESS

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
DROP ANY TABLE                           BY ACCESS  BY ACCESS
GRANT ANY ROLE                           BY ACCESS  BY ACCESS
SYSTEM GRANT                             BY ACCESS  BY ACCESS
ALTER DATABASE                           BY ACCESS  BY ACCESS
ALTER PROFILE                            BY ACCESS  BY ACCESS
DROP PROFILE                             BY ACCESS  BY ACCESS

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
CREATE ANY JOB                           BY ACCESS  BY ACCESS

28 rows selected.


SQL> select action_name,count(*) from dba_audit_trail group by action_name;

ACTION_NAME                    COUNT(*)
---------------------------- ----------
LOGOFF BY CLEANUP                    40
LOGON                               460
LOGOFF                              377
ALTER USER                            2
SYSTEM GRANT                         12
ALTER SYSTEM                         10
CREATE PUBLIC SYNONYM                 5
ALTER DATABASE                        2
CREATE DATABASE LINK                  1
DROP PUBLIC SYNONYM                   5

10 rows selected.


沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569