/* from Daniel Fink */
DECLARE
   CURSOR sql_mismatch_cur IS
      SELECT     sql_id,
                 COUNT(child_number) children,
                 COUNT(DECODE(unbound_cursor,'Y',1,'N',null,null)) unbound_cursor, 
                 COUNT(DECODE(sql_type_mismatch,'Y',1,'N',null,null)) sql_type_mismatch,
                 COUNT(DECODE(optimizer_mismatch,'Y',1,'N',null,null))optimizer_mismatch,
                 COUNT(DECODE(outline_mismatch,'Y',1,'N',null,null))outline_mismatch,
                 COUNT(DECODE(stats_row_mismatch,'Y',1,'N',null,null)) stats_row_mismatch,
                 COUNT(DECODE(literal_mismatch,'Y',1,'N',null,null)) literal_mismatch,
                 COUNT(DECODE(sec_depth_mismatch,'Y',1,'N',null,null)) sec_depth_mismatch,
                 COUNT(DECODE(explain_plan_cursor,'Y',1,'N',null,null)) explain_plan_cursor,
                 COUNT(DECODE(buffered_dml_mismatch,'Y',1,'N',null,null)) buffered_dml_mismatch,
                 COUNT(DECODE(pdml_env_mismatch,'Y',1,'N',null,null)) pdml_env_mismatch,
                 COUNT(DECODE(inst_drtld_mismatch,'Y',1,'N',null,null)) inst_drtld_mismatch,
                 COUNT(DECODE(slave_qc_mismatch,'Y',1,'N',null,null)) slave_qc_mismatch,
                 COUNT(DECODE(typecheck_mismatch,'Y',1,'N',null,null)) typecheck_mismatch,
                 COUNT(DECODE(auth_check_mismatch,'Y',1,'N',null,null)) auth_check_mismatch,
                 COUNT(DECODE(bind_mismatch,'Y',1,'N',null,null)) bind_mismatch,
                 COUNT(DECODE(describe_mismatch,'Y',1,'N',null,null)) describe_mismatch,
                 COUNT(DECODE(language_mismatch,'Y',1,'N',null,null)) language_mismatch,
                 COUNT(DECODE(translation_mismatch,'Y',1,'N',null,null)) translation_mismatch,
                 COUNT(DECODE(row_level_sec_mismatch,'Y',1,'N',null,null)) row_level_sec_mismatch,
                 COUNT(DECODE(insuff_privs,'Y',1,'N',null,null)) insuff_privs,
                 COUNT(DECODE(insuff_privs_rem,'Y',1,'N',null,null)) insuff_privs_rem,
                 COUNT(DECODE(remote_trans_mismatch,'Y',1,'N',null,null)) remote_trans_mismatch,
                 COUNT(DECODE(logminer_session_mismatch,'Y',1,'N',null,null)) logminer_session_mismatch,
                 COUNT(DECODE(incomp_ltrl_mismatch,'Y',1,'N',null,null)) incomp_ltrl_mismatch,
                 COUNT(DECODE(overlap_time_mismatch,'Y',1,'N',null,null)) overlap_time_mismatch,
                 COUNT(DECODE(sql_redirect_mismatch,'Y',1,'N',null,null)) sql_redirect_mismatch,
                 COUNT(DECODE(mv_query_gen_mismatch,'Y',1,'N',null,null)) mv_query_gen_mismatch,
                 COUNT(DECODE(user_bind_peek_mismatch,'Y',1,'N',null,null)) user_bind_peek_mismatch,
                 COUNT(DECODE(typchk_dep_mismatch,'Y',1,'N',null,null)) typchk_dep_mismatch,
                 COUNT(DECODE(no_trigger_mismatch,'Y',1,'N',null,null)) no_trigger_mismatch,
                 COUNT(DECODE(flashback_cursor,'Y',1,'N',null,null)) flashback_cursor,
                 COUNT(DECODE(anydata_transformation,'Y',1,'N',null,null)) anydata_transformation,
                 COUNT(DECODE(incomplete_cursor,'Y',1,'N',null,null)) incomplete_cursor,
                 COUNT(DECODE(top_level_rpi_cursor,'Y',1,'N',null,null)) top_level_rpi_cursor,
                 COUNT(DECODE(different_long_length,'Y',1,'N',null,null)) different_long_length,
                 COUNT(DECODE(logical_standby_apply,'Y',1,'N',null,null)) logical_standby_apply,
                 COUNT(DECODE(diff_call_durn,'Y',1,'N',null,null)) diff_call_durn,
                 COUNT(DECODE(bind_uacs_diff,'Y',1,'N',null,null)) bind_uacs_diff,
                 COUNT(DECODE(plsql_cmp_switchs_diff,'Y',1,'N',null,null)) plsql_cmp_switchs_diff
      FROM       v$sql_shared_cursor
      GROUP BY   sql_id
      HAVING     COUNT(child_number) > 10
      ORDER BY   2 desc;
      
      sql_mismatch_rec   sql_mismatch_cur%ROWTYPE;
      
      
BEGIN
   dbms_output.enable(1000000);
   FOR sql_mismatch_rec IN sql_mismatch_cur
   LOOP
       dbms_output.put_line('SQL ID => '||sql_mismatch_rec.sql_id);
       DECLARE
             CURSOR statement_text_cur IS 
                SELECT    REGEXP_REPLACE(REPLACE(sql_text, CHR(13)),'( ){2,}',' ') parsed_piece
                FROM      v$sqltext
                WHERE     sql_id = sql_mismatch_rec.sql_id
                ORDER BY  piece;
             
             statement_text_rec statement_text_cur%ROWTYPE;
       BEGIN
          FOR statement_text_rec IN statement_text_cur
          LOOP
             dbms_output.put_line(statement_text_rec.parsed_piece);
          END LOOP;
       END;
       dbms_output.put_line(CHR(10));
       dbms_output.put_line(CHR(9)||RPAD('# of Children =>',50)||TO_CHAR(sql_mismatch_rec.children));
       
       IF sql_mismatch_rec.unbound_cursor != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Not fully built =>',50)||TO_CHAR(sql_mismatch_rec.unbound_cursor)); 
       END IF; 
                 
       IF sql_mismatch_rec.sql_type_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('SQL type =>',50)||TO_CHAR(sql_mismatch_rec.sql_type_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.optimizer_mismatch != 0 THEN
          dbms_output.put_line(CHR(9)||RPAD('Optiimizer environment =>',50)||TO_CHAR(sql_mismatch_rec.optimizer_mismatch));
       END IF;
       
       IF sql_mismatch_rec.outline_mismatch != 0 THEN
          dbms_output.put_line(CHR(9)||RPAD('Outlines=>',50)||TO_CHAR(sql_mismatch_rec.outline_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.stats_row_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Existing statistics =>',50)||TO_CHAR(sql_mismatch_rec.stats_row_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.literal_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Non-data literal values =>',50)||TO_CHAR(sql_mismatch_rec.literal_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.sec_depth_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Security Level =>',50)||TO_CHAR(sql_mismatch_rec.sec_depth_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.explain_plan_cursor != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Explain plan cursor =>',50)||TO_CHAR(sql_mismatch_rec.explain_plan_cursor));
       END IF;
                 
       IF sql_mismatch_rec.buffered_dml_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Buffered DML =>',50)||TO_CHAR(sql_mismatch_rec.buffered_dml_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.pdml_env_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('PDML environment =>',50)||TO_CHAR(sql_mismatch_rec.pdml_env_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.inst_drtld_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Insert direct load =>',50)||TO_CHAR(sql_mismatch_rec.inst_drtld_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.slave_qc_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Slave cursor/Coordinator =>',50)||TO_CHAR(sql_mismatch_rec.slave_qc_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.typecheck_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Not fully optimized =>',50)||TO_CHAR(sql_mismatch_rec.typecheck_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.auth_check_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Authorization/translation check failed =>',50)||TO_CHAR(sql_mismatch_rec.auth_check_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.bind_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Bind metadata =>',50)||TO_CHAR(sql_mismatch_rec.bind_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.describe_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Typecheck heap =>',50)||TO_CHAR(sql_mismatch_rec.describe_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.language_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Language handle =>',50)||TO_CHAR(sql_mismatch_rec.language_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.translation_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Base objects =>',50)||TO_CHAR(sql_mismatch_rec.translation_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.row_level_sec_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Row level security =>',50)||TO_CHAR(sql_mismatch_rec.row_level_sec_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.insuff_privs != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Insufficient privileges on objects =>',50)||TO_CHAR(sql_mismatch_rec.insuff_privs)); 
       END IF;
       
       IF sql_mismatch_rec.insuff_privs_rem != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Insufficient privileges on remote objects =>',50)||TO_CHAR(sql_mismatch_rec.insuff_privs_rem)); 
       END IF;
                 
       IF sql_mismatch_rec.remote_trans_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Remote base objects =>',50)||TO_CHAR(sql_mismatch_rec.remote_trans_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.logminer_session_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('LogMiner Session parameters mismatch =>',50)||TO_CHAR(sql_mismatch_rec.logminer_session_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.incomp_ltrl_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Unsafe/non-data binds or literals =>',50)||TO_CHAR(sql_mismatch_rec.incomp_ltrl_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.overlap_time_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('ERROR_ON_OVERLAP_TIME =>',50)||TO_CHAR(sql_mismatch_rec.overlap_time_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.sql_redirect_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('SQL redirection mismatch =>',50)||TO_CHAR(sql_mismatch_rec.sql_redirect_mismatch)); 
       END IF;
       
       IF sql_mismatch_rec.mv_query_gen_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Internal:mv hard parse =>',50)||TO_CHAR(sql_mismatch_rec.mv_query_gen_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.user_bind_peek_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('User binds are different =>',50)||TO_CHAR(sql_mismatch_rec.user_bind_peek_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.typchk_dep_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Typecheck dependencies =>',50)||TO_CHAR(sql_mismatch_rec.typchk_dep_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.no_trigger_mismatch != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Trigger mismatch =>',50)||TO_CHAR(sql_mismatch_rec.no_trigger_mismatch)); 
       END IF;
                 
       IF sql_mismatch_rec.flashback_cursor != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Flashback =>',50)||TO_CHAR(sql_mismatch_rec.flashback_cursor)); 
       END IF;
                 
       IF sql_mismatch_rec.anydata_transformation != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Opaque type transformation =>',50)||TO_CHAR(sql_mismatch_rec.anydata_transformation)); 
       END IF;
                 
       IF sql_mismatch_rec.incomplete_cursor != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Cursor is incomplete =>',50)||TO_CHAR(sql_mismatch_rec.incomplete_cursor)); 
       END IF;
                 
       IF sql_mismatch_rec.top_level_rpi_cursor != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('RPI cursor =>',50)||TO_CHAR(sql_mismatch_rec.top_level_rpi_cursor)); 
       END IF;
                 
       IF sql_mismatch_rec.different_long_length != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('LONG value =>',50)||TO_CHAR(sql_mismatch_rec.different_long_length)); 
       END IF;
                 
       IF sql_mismatch_rec.logical_standby_apply != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Logical standby =>',50)||TO_CHAR(sql_mismatch_rec.logical_standby_apply)); 
       END IF;
                 
       IF sql_mismatch_rec.diff_call_durn != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Slave SQL cursor/single call =>',50)||TO_CHAR(sql_mismatch_rec.diff_call_durn)); 
       END IF;
                 
       IF sql_mismatch_rec.bind_uacs_diff != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('Bind UACs =>',50)||TO_CHAR(sql_mismatch_rec.bind_uacs_diff)); 
       END IF;
                 
       IF sql_mismatch_rec.plsql_cmp_switchs_diff != 0 THEN 
          dbms_output.put_line(CHR(9)||RPAD('PL/SQL compiler switches =>',50)||TO_CHAR(sql_mismatch_rec.plsql_cmp_switchs_diff));
       END IF;
   END LOOP;
END;
/