Oracle Database 19c -- A session stuck with cursor: pin S wait on X wait event

Problem

An application session stuck with the cursor: pin S wait on X wait event. Also accompanied wait events could be seen as 

• library cache lock, 
• library cache: mutex X, 
• cursor: mutex X, 
• cursor: mutex S 

These wait events usually indicate a problem with cursor sharing. Having checked the V$SQL_SHARED_CURSOR view against the offending sql_id, revealed around 4K child cursors:

SQL> select  count(*) from V$SQL_SHARED_CURSOR where sql_id='bt0pjxb7fry43';

  COUNT(*)
----------
      3468

As per the V$SQL_SHARED_CURSOR view, the cursor was not getting shared with a reason BIND_EQUIV_FAILURE

Solution

BIND_EQUIV_FAILURE means that the selectivity of bind values does not match the ones used to optimize an existing child cursor. When adaptive cursor sharing is used and the cursor is bind aware, then if the selectivity is outside of the current ranges and a new plan is desirable then a new child is raised with this as the reason code for non-sharing of the previous plan

Starting with 12.2, many unpublished bugs were seen related to high version count of an SQL statement with the BIND_EQUIV_FAILURE reason

As a workaround, disable the Adaptive cursor sharing feature:

alter system set "_optimizer_use_feedback"=false;
alter system set "_optimizer_adaptive_cursor_sharing"=false;
alter system set "_optimizer_extended_cursor_sharing_rel"=none;

Reference:

• Troubleshooting: High Version Count Issues (Doc ID 296377.1)
• 12.2 Cursor Mutex: x Due to Sql not Shared Because of Bind_equiv_failure (Doc ID 2539161.1)
• Adaptive Cursor Sharing: Worked Example (Doc ID 836256.1)