94 lines
3.8 KiB
SQL
94 lines
3.8 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display similar SQLs not using bind varaibles
|
|
* Parameters : 1 - Top Number of Rows (Default is 30)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ --------------------------------------------------------------
|
|
* 06-Jul-15 Vishal Gupta Added force_matching_signature as the input parameter
|
|
* 08-SEP-14 Vishal Gupta Added memory in the output
|
|
* 25-Oct-12 Vishal Gupta Excluded PL/SQL statements (force_matching_signature=0)
|
|
* 11-Oct-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE TOP_ROWCOUNT
|
|
UNDEFINE FORCE_MATCHING_SIGNATURE
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE TOP_ROWCOUNT="&&1"
|
|
DEFINE FORCE_MATCHING_SIGNATURE="&&2"
|
|
DEFINE WHERECLAUSE="&&3"
|
|
|
|
COLUMN _TOP_ROWCOUNT NEW_VALUE TOP_ROWCOUNT NOPRINT
|
|
COLUMN _FORCE_MATCHING_SIGNATURE NEW_VALUE FORCE_MATCHING_SIGNATURE NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&TOP_ROWCOUNT','','30','&&TOP_ROWCOUNT') "_TOP_ROWCOUNT"
|
|
, DECODE('&&FORCE_MATCHING_SIGNATURE','','%','&&FORCE_MATCHING_SIGNATURE') "_FORCE_MATCHING_SIGNATURE"
|
|
FROM DUAL
|
|
;
|
|
set term on
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
|
|
PROMPT *************************************************************************************
|
|
PROMPT * S I M I L A R S Q L S T A T E M E N T S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Top Row Count = "&&TOP_ROWCOUNT" (Default Value 30)
|
|
PROMPT * - Force Matching Signature = "&&FORCE_MATCHING_SIGNATURE" (Default Value %)
|
|
PROMPT * - WhereClause = "&&WHERECLAUSE"
|
|
PROMPT *************************************************************************************
|
|
|
|
COLUMN force_matching_signature HEADING "Force|Matching|Signature" FORMAT 999999999999999999999
|
|
COLUMN mem HEADING "Memory|(MB)" FORMAT 999,999
|
|
COLUMN sql_count HEADING "Distinct|SQL|Count" FORMAT 9,999,999
|
|
COLUMN executions HEADING "Execs" FORMAT 9,999,999
|
|
COLUMN loaded_versions HEADING "Loaded|Vers" FORMAT 9,999,999
|
|
COLUMN min_last_active_time HEADING "Min|Last|ActiveTime" FORMAT a16
|
|
COLUMN max_last_active_time HEADING "Max|Last|ActiveTime" FORMAT a16
|
|
COLUMN parsing_schema_name HEADING "SchemaName" FORMAT a16
|
|
COLUMN module HEADING "Module" FORMAT a20 TRUNCATE
|
|
COLUMN sql_text HEADING "SQL Text" FORMAT a70 TRUNCATE
|
|
|
|
SELECT a.*
|
|
FROM
|
|
( SELECT sql.force_matching_signature
|
|
, max(sql_id) sql_id
|
|
, sum(sql.sharable_mem + sql.persistent_mem + sql.runtime_mem)/power(1024,2) mem
|
|
, count( DISTINCT sql.sql_id) sql_count
|
|
, SUM(sql.executions) executions
|
|
, sum(sql.loaded_versions) loaded_versions
|
|
, TO_CHAR(min(sql.last_active_time),'DD-MON-YY HH24:MI') min_last_active_time
|
|
, TO_CHAR(max(sql.last_active_time),'DD-MON-YY HH24:MI') max_last_active_time
|
|
, sql.parsing_schema_name
|
|
, max(sql.module) module
|
|
, max(sql.sql_text) sql_text
|
|
FROM GV$SQLAREA sql
|
|
WHERE force_matching_signature <> 0 --Exclude PL/SQL statements
|
|
AND force_matching_signature like '&&FORCE_MATCHING_SIGNATURE'
|
|
&&WHERECLAUSE
|
|
GROUP BY force_matching_signature
|
|
, parsing_schema_name
|
|
ORDER BY sql_count DESC
|
|
) a
|
|
WHERE rownum <= &&TOP_ROWCOUNT
|
|
;
|
|
|
|
|
|
@@footer
|