vsql.sql
SQLテキストの一部からSQL_IDを確認する
code:SQL
SET AUTOT OFF
set feed on
SET COLSEP ' ' VERIFY OFF LINESIZE 300
COLUMN SQL_TEXT FOR A68
COLUMN SQL_ID FOR A13
COLUMN AVG_ELAP_SEC FOR 999,990.000
COLUMN LA_DATE FOR A10
COLUMN LA_TIME FOR A8
COLUMN MODULE FOR A40
col INST_ID for a8
col PARSING_SCHEMA_NAME for a22
col CHILD_NUMBER for a4 heading "CNO"
SELECT /* THISSQL */
SUBSTR(A.SQL_TEXT, 1, 60) SQL_TEXT
, A.SQL_ID
, to_char(B.CHILD_NUMBER) CHILD_NUMBER
, to_char(A.INST_ID) INST_ID
-- , B.CHILD_NUMBER
, A.PLAN_HASH_VALUE
, A.EXECUTIONS
, A.ELAPSED_TIME/decode(A.EXECUTIONS,0,1,A.EXECUTIONS)/1000000 AVG_ELAP_SEC
, TO_CHAR(A.LAST_ACTIVE_TIME, 'YYYY/MM/DD') LA_DATE
, TO_CHAR(A.LAST_ACTIVE_TIME, 'HH24:MI:SS') LA_TIME --直近の実行時刻
, B.MODULE
FROM
GV$SQLSTATS_PLAN_HASH A
,GV$SQL B
WHERE 1=1
AND A.INST_ID = B.INST_ID
AND A.SQL_TEXT LIKE '%&1.%'
AND NOT A.SQL_TEXT LIKE '%THISSQL%'
AND A.SQL_ID = B.SQL_ID
--AND A.LAST_ACTIVE_CHILD_ADDRESS = B.CHILD_ADDRESS
--and B.MODULE = 'JDBC Thin Client'
ORDER BY
A.LAST_ACTIVE_TIME
;
@default_settings