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.