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.