@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display top SQL statements creating child cursors
*  Parameters : 1 - SQL_ID
*               2 - INST_ID  (Default %)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  13-Jul-12  Vishal Gupta  Created
*
*/

/************************************
*  INPUT PARAMETERS
************************************/
DEFINE SQL_ID="&&1"
DEFINE INST_ID="&&2"


COLUMN  _INST_ID          NEW_VALUE  INST_ID            NOPRINT

set term off
SELECT DECODE('&&INST_ID','','%','&&INST_ID')             "_INST_ID"
FROM DUAL;
set term on

PROMPT ***************************************************************
PROMPT * Reason for SQL Child Cursor Creation
PROMPT *  
PROMPT * Input Parameters 
PROMPT *   - SQL_ID   = '&&SQL_ID'
PROMPT *   - INST_ID  = '&&INST_ID' (Default '%')
PROMPT ***************************************************************


set serveroutput on 


DECLARE
  v_count number;
  v_sql varchar2(500);
  v_inst_id varchar2(2) := '&&INST_ID';
  v_sql_id varchar2(30) := '&&SQL_ID';
BEGIN
  v_sql_id := lower(v_sql_id);
  dbms_output.put_line(chr(13)||chr(10));
  dbms_output.put_line('Instance ID : '||v_inst_id);
  dbms_output.put_line('SQL ID      : '||v_sql_id);
  dbms_output.put_line('Reason      : ');
  dbms_output.put_line('------------------------');
  FOR c1 in 
    (select column_name  
       from dba_tab_columns 
      where table_name ='GV_$SQL_SHARED_CURSOR' 
        and column_name not in ('INST_ID','SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER', 'REASON')
      order by column_id)
  LOOP
    v_sql :=    'select count(*) from GV$SQL_SHARED_CURSOR ' 
             || ' where sql_id='||''''||v_sql_id||''' ';
             IF  v_inst_id <> '%' THEN
                 v_sql := v_sql || 'and inst_id = '||v_inst_id||' ' ;
             END IF; 
             v_sql := v_sql || ' and '||c1.column_name||'='||''''||'Y'||''' ';
             
    execute immediate v_sql into v_count;
    IF v_count > 0 
    THEN 
      dbms_output.put_line(' - '||rpad(c1.column_name,30)||' count: '||v_count);
    END IF;
  END LOOP;
END;
/


/*
COLUMN reason           HEADING 'Mismatch Reason'        FORMAT a50

SELECT c.sql_id
     , count(1) "Count"
     , extractvalue(XMLType(c.reason),'/ChildNode/reason') reason
FROM GV$SQL_SHARED_CURSOR  c
where c.sql_id = '&&SQL_ID'
and c.inst_id like '&&INST_ID'
and c.reason is not null
and dbms_lob.substr(c.reason,1) <> ' '
GROUP BY sql_id, extractvalue(XMLType(c.reason),'/ChildNode/reason')
;
*/

UNDEFINE INST_ID
UNDEFINE SQL_ID

@@footer