89 lines
2.8 KiB
SQL
89 lines
2.8 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Search SQL Text from AWR repository to find SQLId
|
|
* Parameters : 1 - SQLText to search (Use % as wild card)
|
|
* 2 - Number of days history to search (Default 7 days)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 08-JUL-14 Vishal Gupta Added plan_hash_value to output
|
|
* 12-JUN-13 Vishal Gupta Added force_matching_signature to output
|
|
* 08-MAR-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE FORCE_MATCHING_SINGATURE
|
|
UNDEFINE no_of_days
|
|
|
|
DEFINE FORCE_MATCHING_SINGATURE="&&1"
|
|
DEFINE no_of_days="&&2"
|
|
|
|
COLUMN _no_of_days NEW_VALUE no_of_days NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&no_of_days','','7','&&no_of_days') "_no_of_days"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT ************************************************************************
|
|
PROMPT * AWR SQL Text Search by using FORCE_MATCHING_SINGATURE
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - FORCE_MATCHING_SINGATURE - "&&FORCE_MATCHING_SINGATURE"
|
|
PROMPT * - NoOfDays - "&&no_of_days"
|
|
PROMPT ************************************************************************
|
|
|
|
COLUMN END_INTERVAL_TIME HEADING "Max Snap Time" FORMAT a18
|
|
COLUMN sql_text HEADING "SQLText" FORMAT a150 WRAP
|
|
COLUMN force_matching_signature HEADING "FORCE_MATCHING_SIGNATURE" FORMAT 999999999999999999999
|
|
|
|
set timing on
|
|
|
|
/*
|
|
SELECT --+ FULL(sql) parallel(12)
|
|
sql.sql_id
|
|
FROM dba_hist_sqltext sql
|
|
WHERE upper(sql.sql_text) like upper(q'[&&sqltext]')
|
|
AND upper(SQL.sql_text) NOT LIKE upper('%dba_hist_sqltext%')
|
|
;
|
|
*/
|
|
|
|
SELECT --+ FIRST_ROWS LEADING(s) USE_NL(sql ss s ) INDEX(ss.sn) FULL(s)
|
|
-- FULL(s) FULL(ss) FULL(sql) parallel(12)
|
|
TO_CHAR(max(s.END_INTERVAL_TIME),'DD-MON-YY HH24:MI:SS') END_INTERVAL_TIME
|
|
, ss.sql_id
|
|
, ss.plan_hash_value
|
|
, ss.force_matching_signature
|
|
FROM v$database d
|
|
, dba_hist_snapshot s
|
|
, dba_hist_sqlstat ss
|
|
, dba_hist_sqltext sql
|
|
WHERE d.dbid = s.dbid
|
|
AND s.end_interval_time > sysdate - &&no_of_days
|
|
AND s.dbid = ss.dbid
|
|
AND s.instance_number = ss.instance_number
|
|
AND s.snap_id = ss.snap_id
|
|
AND ss.dbid = sql.dbid
|
|
AND ss.sql_id = sql.sql_id
|
|
AND ss.force_matching_signature = &&FORCE_MATCHING_SINGATURE
|
|
GROUP BY ss.sql_id
|
|
, ss.plan_hash_value
|
|
, ss.force_matching_signature
|
|
ORDER BY max(s.END_INTERVAL_TIME) desc
|
|
;
|
|
|
|
set timing off
|
|
|
|
@@footer
|