@@header

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

/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE INST_ID
UNDEFINE TOP_ROWCOUNT
DEFINE INST_ID="&&1"
DEFINE TOP_ROWCOUNT="&&2"

COLUMN  _INST_ID          NEW_VALUE  INST_ID            NOPRINT
COLUMN  _TOP_ROWCOUNT     NEW_VALUE  TOP_ROWCOUNT       NOPRINT

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

/************************************
*  CONFIGURATION PARAMETERS
************************************/

    
PROMPT ***************************************************************
PROMPT * TOP &&TOP_ROWCOUNT SQL Ids with large number of child cursors 
PROMPT *  
PROMPT * Input Parameters 
PROMPT *   - Instance Number = '&&INST_ID'  (Default %)
PROMPT *   - Top Rows Count  = '&&TOP_ROWCOUNT' (Default 30)
PROMPT ***************************************************************

COLUMN inst_id          HEADING "Instance Number "         FORMAT 99
COLUMN sql_id           HEADING "SQLId"                    FORMAT a13
COLUMN sql_child_number HEADING "SQL|Child Cursor|Count"   FORMAT 99999


SELECT * FROM 
(
   SELECT c.inst_id
        , c.sql_id
        , count(c.child_number)  child_count
     FROM gv$sql_shared_cursor c
    WHERE c.inst_id LIKE '&&INST_ID'
   GROUP BY c.inst_id
          , c.sql_id
   ORDER BY count(c.child_number) DESC       
)
WHERE rownum <= &&TOP_ROWCOUNT
;

@@footer