66 lines
1.9 KiB
SQL
66 lines
1.9 KiB
SQL
@@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 |