Bug 29613690 : CHILD CURSOR LEAK DUE TO BIND MISMATCH

Child cursors are increasing due to bind mismatch.
The problematic query is following.(SQL_ID=8y78t7xj7h2fk)
 
  SELECT ...
  FROM    WWUJT628 a628
          ...
          LEFT JOIN
                  (SELECT DISTINCT NR_ADV_SEQ FROM WWUJT632 
                  WHERE NO_ISSUE_ADMIN = :B1) a632
          ON
                  (a628.NR_ADV_SEQ = a632.NR_ADV_SEQ)
          LEFT JOIN WWUJT080 a080b
          ON
                  (a080b.ID_ROLE_ADMIN_M = a630.ID_ROLE_ADMIN_M_REQ_DEST)
  WHERE   a628.ZZ_LOGIC_DEL_FLG   = 'N'
          AND a628.NO_ISSUE_ADMIN = :B2
          AND
          ((a628.DT_PUBLIC IS NOT NULL 
            AND a628.ID_SITUA_UNDER_LIMIT <= :B3)
            OR
           (a628.DT_PUBLIC IS NULL AND a628.ID_SITUA_REGIST = :B4
            AND a628.CD_COM_REGIST   = :B5))
          ...
  ORDER BY DECODE(a630.CD_EVAL_PROCESS, NULL, '1', '0'),
          a628.DT_PUBLIC                               ,
          a628.NR_ADV_SEQ;
 
DIAGNOSTIC ANALYSIS:
--------------------
Result of selecting from V$SQL and V$SQL_SHARED_CURSOR(05_after.lst)
 
  SQL> select sql_id, child_number, plan_hash_value,IS_BIND_SENSITIVE, 
       IS_BIND_AWARE, IS_SHAREABLE from v$sql where sql_id='8y78t7xj7h2fk';
  
  SQL_ID                     CHILD_NUMBER PLAN_HASH_VALUE IS IS IS
  -------------------------- ------------ --------------- -- -- --
  8y78t7xj7h2fk                         0      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         1      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         2      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         3      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         4      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         5      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         6      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         7      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         8      2238769467 Y  Y  Y
  8y78t7xj7h2fk                         9      2238769467 Y  Y  Y
  
  SQL> select BIND_EQUIV_FAILURE from V$SQL_SHARED_CURSOR  
       where sql_id='8y78t7xj7h2fk';
  
  BI
  --
  N
  Y
  Y
  Y
  Y
  Y
  Y
  Y
  Y
  Y
 
  V$SQL_SHARED_CURSOR.REASON shows:
 
  <ChildNode><ChildNumber>8</ChildNumber><ID>39</ID><reason>
  Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>
  3801990668</init_ranges_in_first_pass></ChildNode>
 
One of these parameters and patch are doen't resolve the problem.
 
  - alter system set "_optimizer_use_feedback"=false ;
  - alter system set "_optimizer_adaptive_cursor_sharing"=false ;
  - alter system set "_fix_control"='23596611:OFF' ;
  - alter system set "_optimizer_extended_cursor_sharing" = none ;
  - Bug 28794230 
    12.2 CURSOR MUTEX X DUE TO SQL NOT SHARED BECAUSE OF BIND_EQUIV_FAILURE
 
They can avoid the problem by setting one of these parameters.
 
  - alter system set "_optimizer_extended_cursor_sharing_rel" = none;
  - alter system set optimizer_adaptive_plans=false ;
 
 
  - Event 10053 level 1
  - Event 10507 level 15652
    (KKOCS_TRC_CMP3|KKOCS_TRC_BEV3|KKOCS_TRC_EXE3|KKOCS_TRC_STA1
     KKOCS_TRC_TFB1|KKOCS_TRC_CFB2|KKOCS_TRC_AR1)
  - Cursortrace level 99172
  - Library cache dump level 16
 
WORKAROUND:
-----------
  - alter system set "_optimizer_extended_cursor_sharing_rel" = SIMPLE;
    or
  - alter system set optimizer_adaptive_plans=true ;

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号