« SAP系统 SYSFAIL 失败TCPSBUILD 案例一则 | Blog首页 | 《循序渐进Oracle》一书的历史与评价 »
使用 cursor_sharing_exact 拒绝SQL变量绑定
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2011/03/cursor_sharing_exact.html
在使用了cursor_sharing参数后,可能会导致某些SQLChildren Cursor过高,引起竞争,这些竞争可能包括library cache lock的各类Child Cursor的分配、释放、扫描和锁定:链接:https://www.eygle.com/archives/2011/03/cursor_sharing_exact.html
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是一个需要非常谨慎的参数,其可能带来很多负面的影响。
历史上的今天...
>> 2018-03-15文章:
>> 2009-03-15文章:
>> 2006-03-15文章:
>> 2005-03-15文章:
By eygle on 2011-03-15 16:31 | Comments (0) | SQL.PLSQL | 2757 |