Oracle性能相关常用脚本(SQL)(三)
SUM (dhss.elapsed_time_delta) elapsed_time,
CASE SUM (dhss.executions_delta) WHEN 0 THEN 1 ELSE SUM (dhss.executions_delta) END
AS executions_delta
--DHSS.EXECUTIONS_DELTA = No of queries execution (per hour)
FROM dba_hist_sqlstat dhss
WHERE dhss.snap_id IN
(SELECT snap_id
FROM dba_hist_snapshot
WHERE begin_interval_time >= TO_DATE ('&input_start_date', 'YYYYMMDD HH24:MI')
AND end_interval_time <= TO_DATE ('&input_end_date', 'YYYYMMDD HH24:MI'))
AND dhss.parsing_schema_name LIKE UPPER ('%&input_username%')
GROUP BY dhss.sql_id) x
WHERE x.sql_id = dhst.sql_id
ORDER BY elapsed_time_sec DESC;
9、SQL语句被执行的次数
[sql]
--exe_delta表明在指定时间内增长的次数
-- filename: sql_exec_num.sql
-- How many Times a query executed
SET LINESIZE 180
SET VERIFY OFF
SELECT TO_CHAR (s.begin_interval_time, 'yyyymmdd hh24:mi:ss'),
sql.sql_id AS sql_id,
sql.executions_delta AS exe_delta,
sql.executions_total
FROM dba_hist_sqlstat sql, dba_hist_snapshot s
WHERE sql_id = '&input_sql_id'
AND s.snap_id = sql.snap_id
AND s.begin_interval_time > TO_DATE ('&input_start_date', 'YYYYMMDD HH24:MI')
AND s.begin_interval_time < TO_DATE ('&input_end_date', 'YYYYMMDD HH24:MI')
ORDER BY s.begin_interval_time;