from http://www.eygle.com/archives/2011/03/cursor_sharing_exact.html
在使用了cursor_sharing参数后,可能会导致某些SQLChildren Cursor过高,引起竞争,这些竞争可能包括library cache lock的各类Child Cursor的分配、释放、扫描和锁定:
library cache lock | kgllkal: child: multiinstance | 0 | 588,722 | 400,470 |
library cache lock | kgllkdl: child: cleanup | 0 | 353,668 | 145,020 |
library cache lock | kgllkdl: child: no lock handle | 0 | 106,784 | 76,528 |
library cache lock | kglhdbrnl: child | 0 | 1,899 | 1,752 |
library cache pin | kglpnal: child: alloc space | 0 | 332 | 541 |
针对特定的SQL可以通过cursor_sharing_exact提示,强制SQL硬解析,避免Children Cursor过多带来的负面性能影响。
以下是在11.2.0.2中的一个简单测试,两次查询,产生两个children cursor:
[oracle@ittest2 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 15 23:30:58 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create user eygle identified by eygle;
User created.
SQL> grant connect,resource,dba to eygle;
Grant succeeded.
SQL> connect eygle/eygle
Connected.
SQL> create table ebind as select * from dba_users;
Table created.
SQL> alter session set cursor_sharing=similar;
Session altered.
SQL> select count(*) from ebind where username='EYGLE';
COUNT(*)
----------
1
SQL> c/EYGLE/SYS
1* select count(*) from ebind where username='SYS'
SQL> /
COUNT(*)
----------
1
SQL> select SQL_TEXT from v$sql where sql_text like 'select count(*) from ebind%';
SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from ebind where username=:"SYS_B_0"
select count(*) from ebind where username=:"SYS_B_0"
再次查询,产生三个children cursor
SQL> select count(*) from ebind where username='SYSTEM';
COUNT(*)
----------
1
SQL> select SQL_TEXT from v$sql where sql_text like 'select count(*) from ebind%';
SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from ebind where username=:"SYS_B_0"
select count(*) from ebind where username=:"SYS_B_0"
select count(*) from ebind where username=:"SYS_B_0"
SQL> select SQL_TEXT,version_count from v$sqlarea where sql_text like 'select count(*) from ebind%';
SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from ebind where username=:"SYS_B_0"
3
SQL> col sql_text for a60
SQL> /
SQL_TEXT VERSION_COUNT
------------------------------------------------------------ -------------
select count(*) from ebind where username=:"SYS_B_0" 3
指定cursor_sharing_exact提示,让SQL拒绝强制绑定:
SQL> select /*+ cursor_sharing_exact */ count(*) from ebind where username='MDSYS';
COUNT(*)
----------
1
SQL> select SQL_TEXT,version_count from v$sqlarea where sql_text like 'select count(*) from ebind%';
SQL_TEXT VERSION_COUNT
------------------------------------------------------------ -------------
select count(*) from ebind where username=:"SYS_B_0" 3
SQL> select SQL_TEXT,version_count from v$sqlarea where sql_text like 'select%from ebind%';
SQL_TEXT VERSION_COUNT
------------------------------------------------------------ -------------
select /*+ cursor_sharing_exact */ count(*) from ebind where 1
username='MDSYS'
select count(*) from ebind where username=:"SYS_B_0" 3
SQL> select /*+ cursor_sharing_exact */ count(*) from ebind where username='SYSMAN';
COUNT(*)
----------
1
SQL> select SQL_TEXT,version_count from v$sqlarea where sql_text like 'select%from ebind%';
SQL_TEXT VERSION_COUNT
------------------------------------------------------------ -------------
select /*+ cursor_sharing_exact */ count(*) from ebind where 1
username='MDSYS'
select count(*) from ebind where username=:"SYS_B_0" 3
select /*+ cursor_sharing_exact */ count(*) from ebind where 1
username='SYSMAN'
cursor_sharing是一个需要非常谨慎的参数,其可能带来很多负面的影响。