Andrew Fraser DBA

Oracle hint cursor_sharing_exact using sql patch

Bind variable peeking is still a cause of plan instability, even after 11gR1’s introduction of adaptive cursor sharing. In my case adaptive cursor sharing wasn’t helpful for a daily batch job, because the sql was aged out of cache by the time the next scheduled run came around. Making everything worse was having parameter cursor_sharing set to the non default value of ‘force’ at 3rd party vendors specification, meaning literal values useful for the optimizer were being hidden by :SYS_B0 generated bind variables.

The fix for me was to add a /*+ cursor_sharing_exact */ hint to the SQL statement, and being unable to directly edit the SQL code I used a SQL Patch to do that:

DECLARE
   l_sql_text CLOB ;
BEGIN
   SELECT sql_fulltext INTO l_sql_text FROM v$sqlarea WHERE ROWNUM < 2 AND sql_id = '82wxzdhxw224m' ;
   SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch (
        sql_text => l_sql_text
      , hint_text => 'CURSOR_SHARING_EXACT'
      , name => 'Ainapps cursor_sharing_exact'  -- 30 characters max
      , description => 'Ainapps cursor_sharing_exact hint 09-Nov-2015 Andrew Fraser'  -- 500 characters max
   ) ;
END ;
/

The SQL patch is then visible in view dba_sql_patches.

The last line of plan output will show:

Note
-----
   - SQL patch "Ainapps cursor_sharing_exact" used for this statement

Because there may now be child cursors both with and without that SQL Patch in cache, specify cursor_child_no => NULL to see all child cursors listed in plan output:

SELECT * FROM TABLE ( DBMS_XPLAN.display_cursor ( sql_id => '82wxzdhxw224m' , cursor_child_no => NULL ) ) ;

From version 12c, the optional force_match => TRUE parameter can be passed to i_create_patch to apply for different literal values.

If this is happening a lot, removing histograms may be a better option than hinting/patching individual statements.