134 lines
4.7 KiB
SQL
134 lines
4.7 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display SQL Binds from AWR
|
|
* Version : 10.2 and above
|
|
* Parameters : 1 - SQL_ID
|
|
* 2 - Number of Days (Default 7 days)
|
|
* 2 - Whereclause
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 27-Aug-15 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE sql_id
|
|
UNDEFINE days
|
|
UNDEFINE whereclause
|
|
UNDEFINE force_matching_signature
|
|
UNDEFINE use_force_matching_signature
|
|
|
|
DEFINE sql_id="&&1"
|
|
DEFINE days="&&2"
|
|
DEFINE whereclause="&&3"
|
|
DEFINE force_matching_signature=""
|
|
|
|
COLUMN _days NEW_VALUE days NOPRINT
|
|
COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&days','','7','&&days') "_days"
|
|
, DECODE('&&sql_id','%','%','') "_force_matching_signature"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
|
|
COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT
|
|
|
|
set term off
|
|
select to_char(ss2.force_matching_signature) "_force_matching_signature"
|
|
from v$database d
|
|
, dba_hist_sqlstat ss2
|
|
where d.dbid = ss2.dbid
|
|
AND ss2.sql_id = '&&sql_id' and rownum = 1;
|
|
|
|
select CASE WHEN '&&force_matching_signature' IS NULL THEN '0'
|
|
WHEN '&&force_matching_signature' = '' THEN '0'
|
|
ELSE '&&force_matching_signature'
|
|
END "_force_matching_signature"
|
|
from dual
|
|
;
|
|
|
|
set term on
|
|
|
|
/************************************/
|
|
|
|
Prompt
|
|
Prompt **************************************************************
|
|
Prompt * AWR SQL Bind Values
|
|
Prompt * (Force Matching Signature = '&&force_matching_signature')
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - SQL Id = '&&sql_id'
|
|
PROMPT * - Days = '&&days'
|
|
PROMPT * - WhereClause = '&&whereclause'
|
|
Prompt **************************************************************
|
|
|
|
COLUMN seperator HEADING "!|!|!|!" FORMAT a1
|
|
COLUMN end_interval_time HEADING "Snap Time" FORMAT a15
|
|
COLUMN instance_number HEADING "I#" FORMAT 99
|
|
COLUMN SQL_ID HEADING "SQLId" FORMAT a13
|
|
COLUMN plan_hash_value HEADING "Plan|Hash|Value" FORMAT 999999999999
|
|
COLUMN bind_values HEADING "Bind Values" FORMAT a100
|
|
COLUMN bind_datatype HEADING "Bind DataType" FORMAT a80
|
|
|
|
SELECT TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI') end_interval_time
|
|
, ss.instance_number
|
|
, ss.sql_id
|
|
, ss.plan_hash_value
|
|
, (select listagg(NVL(name,':B' || position)
|
|
||'['
|
|
|| datatype_string || NVL2(scale
|
|
,'(' || scale || NVL2(precision,','|| precision,'') || ')'
|
|
,''
|
|
)
|
|
|| ']'
|
|
||'='
|
|
|| CASE
|
|
WHEN datatype_string <> 'TIMESTAMP' THEN NVL(value_string,'NULL')
|
|
ELSE TO_CHAR(ANYDATA.ACCESStimestamp(VALUE_ANYDATA))
|
|
END
|
|
,','
|
|
) WITHIN GROUP (order by position)
|
|
from table(dbms_sqltune.extract_binds(ss.BIND_DATA))
|
|
) bind_values
|
|
/*
|
|
, (select listagg(NVL(name,':B' || position) || '='
|
|
|| datatype_string || NVL2(scale,'(' || scale || NVL2(precision,','|| precision, '') || ')' ,'' )
|
|
,','
|
|
) WITHIN GROUP (order by position)
|
|
from table(dbms_sqltune.extract_binds(ss.BIND_DATA))
|
|
) bind_datatype
|
|
*/
|
|
from v$database d
|
|
JOIN dba_hist_snapshot s ON s.dbid = d.dbid
|
|
JOIN dba_hist_sqlstat ss ON ss.dbid = s.dbid AND ss.instance_number = s.instance_number AND ss.snap_id = s.snap_id
|
|
AND ss.executions_delta > 0
|
|
where s.end_interval_time > SYSDATE - &&days
|
|
AND ss.sql_id LIKE CASE ss.force_matching_signature
|
|
WHEN 0 THEN '&&sql_id'
|
|
ELSE '%'
|
|
END
|
|
AND ss.force_matching_signature = '&&force_matching_signature'
|
|
&&whereclause
|
|
ORDER BY s.end_interval_time
|
|
, ss.instance_number
|
|
;
|
|
|
|
|
|
@@footer
|