« SAP系统 SYSFAIL 失败TCPSBUILD 案例一则 | Blog首页 | 《循序渐进Oracle》一书的历史与评价 »

使用 cursor_sharing_exact 拒绝SQL变量绑定

在使用了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是一个需要非常谨慎的参数，其可能带来很多负面的影响。

By eygle on 2011-03-15 16:31 | Comments (0) | SQL.PLSQL | 2757 |

HintsSQL