Honcho’s Oracle Blog

Focusing Specifically On Oracle Database Administration

Use cursor_sharing_exact refused to SQL variable binding
 In the use cursor_sharing parameters, may cause some SQL children cursor too high, cause competition, the competition may include library cache lock of all kinds of Child Cursor distribution, release, scanning and lock.

For certain SQL can through the cursor_sharing_exact hint, forced SQL hard parse, avoid too many children cursor the negative impact on performance

The following is 10.2.0.1 in one of the simple test, two inquires the, have two children cursor:


[oracle@rac2 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 5 15:36:22 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn honcho/honcho
Connected.
SQL> create table t1 as select * from dba_objects;        

Table created.

SQL> commit;

Commit complete.

SQL> alter session set cursor_sharing=similar;

Session altered.

SQL> select count(*) from t1 where owner='HONCHO';

  COUNT(*)
----------
         2

SQL> c/HONCHO/SYS
  1* select count(*) from t1 where owner='SYS'
SQL> L
  1* select count(*) from t1 where owner='SYS'
SQL> /

  COUNT(*)
----------
     22957

SQL> select SQL_TEXT from v$sql where sql_text like 'select count(*) from t1%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from t1 where owner=:"SYS_B_0"
select count(*) from t1 where owner=:"SYS_B_0"

 

Once again inquires , produce the three children cursor:

SQL> select count(*) from t1 where owner='SYSTEM';

  COUNT(*)
----------
       454

SQL> select sql_text from v$sql where sql_text like 'select count(*) from t1%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from t1 where owner=:"SYS_B_0"
select count(*) from t1 where owner=:"SYS_B_0"
select count(*) from t1 where owner=:"SYS_B_0"

SQL> select SQL_TEXT,version_count from v$sqlarea where sql_text like 'select count(*) from t1%';

SQL_TEXT                                                     VERSION_COUNT
------------------------------------------------------------ -------------
select count(*) from t1 where owner=:"SYS_B_0"                           3

 

Designated cursor_sharing_exact hint, let SQL refused to force binding:

SQL> select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SCOTT';

  COUNT(*)
----------
         6

SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select count(*) from t1%';

SQL_TEXT                                                     VERSION_COUNT
------------------------------------------------------------ -------------
select count(*) from t1 where owner=:"SYS_B_0"                           3

SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select%from t1%';

SQL_TEXT                                                                         VERSION_COUNT
-------------------------------------------------------------------------------- -------------
select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SCOTT'                      1
select count(*) from t1 where owner=:"SYS_B_0"                                               3

SQL> select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SYSMAN';

  COUNT(*)
----------
      1321

SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select%from t1%';

SQL_TEXT                                                                         VERSION_COUNT
-------------------------------------------------------------------------------- -------------
select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SYSMAN'                     1
select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SCOTT'                      1
select count(*) from t1 where owner=:"SYS_B_0"                                               3

Cursor_sharing is a need to be very careful parameters, which may bring a lot of negative effects.

阅读更多
个人分类: Administration
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

Use cursor_sharing_exact refused to SQL variable binding

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭