PT - Change parameter CURSOR_SHARING from EXACT to FORCE

Scenario:

We Change the parameter CURSOR_SHARING from  EXACT to FORCE and find out expalin plan before and after the changes.

• Execute query on table and we find execution plan of query are different with different sql_id and hash value 

SQL> conn TEST/TEST

Connected.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='01-2010';

TOTAL

------------

  53,308,707

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='02-2010';

TOTAL

------------

  49,166,476

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='02-2010';

TOTAL

------------

  49,166,476

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='03-2010';

TOTAL

------------

  53,907,203

• Explain plan for previous query

SQL> COL SQL_TEXT FORMAT a30

SQL>  SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%my query%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

------------------------------ ------------- ------------- ----------

SELECT /* my query */ TO_CHAR( 4qa4ftwp1nks3             1  706366211

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='02-2010'

SELECT /* my query */ TO_CHAR( 6xt52tc3u9h82             1  128237826

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='01-2010'

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

------------------------------ ------------- ------------- ----------

SELECT /* my query */ TO_CHAR( agv63ft219k4w             1 1142212764

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='03-2010'

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

• Change parameter exact to force 

SQL> ALTER SESSION SET CURSOR_SHARING=FORCE;

Session altered.

SQL>  SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM TEST.TEST WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='01-2010';

TOTAL

--------------------------------------------------------------------------------

  53,308,707

SQL>  SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM TEST.TEST WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='02-2010';

TOTAL

--------------------------------------------------------------------------------

  49,166,476

SQL>  SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM TEST.TEST WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='03-2010';

TOTAL

--------------------------------------------------------------------------------

  53,907,203

• SQL_ID and Hash_Value are same for query ( dont do on production , first test on dev or uat db)

SQL> col SQL_TEXT format a30

SQL>  SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%my query%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

------------------------------ ------------- ------------- ----------

 SELECT /* my query */ TO_CHAR 2nhjw6xvhtcbs             1 1997320568

(SUM(ORDER_TOTAL),:"SYS_B_0")

TOTAL FROM TEST.TEST WHERE TO

_CHAR(ORDER_DATE,:"SYS_B_1") =

:"SYS_B_2"

SQL>

------------------------------------------------------------------------------------------------------------

set lines 200 pages 1000

col sql_text for a30

col user_name for a20

col address for a20

col sid for 99999999

select * from (

    select  sid ,

        sql_text, 

user_name, 

address,

count(*) as "cursors_open" 

from v$open_cursor 

group by sid ,sql_text, user_name ,address

order by count(*) desc)

where rownum < 6;

select sid,

       sql_id,

       address,

       count(*) ocursors

from v$open_cursor

group by sid,sql_id,address

having count(*) > 3

order by count(*) desc;

set lines 200 pages 1000

col name for a30

col current_value for 9999999999999

col total_value for a20

select st.name,

       max(ses.value) "current_value",

           p.value "total_value",

              round(100 * (max(ses.value) / p.value)) "PCT"

from v$statname st

inner join v$sesstat ses on st.statistic# = ses.statistic#

cross join v$parameter p

where p.name='open_cursors'

and

st.name='opened cursors current'

group by st.name,p.value;