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.