eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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





历史上的今天...
    >> 2009-03-15文章:
    >> 2006-03-15文章:
           梁启超之死
           我的写作进度
    >> 2005-03-15文章:

无觅

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


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com