98 lines
2.7 KiB
MySQL
98 lines
2.7 KiB
MySQL
@@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 |