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