Oracle性能相关常用脚本(SQL)(二)
ry.wait_time + active_session_history.time_waited)
total_wait_time
FROM v$active_session_history active_session_history, v$sqlarea sqlarea, dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
GROUP BY active_session_history.user_id,
sqlarea.sql_text,
sqlarea.sql_id,
dba_users.username
ORDER BY 4 DESC) x
WHERE ROWNUM <= 11;
6、等待最多的对象
[sql]
--filename:top_object_by_wait.sql
--What object is currently causing the highest resource waits
SET LINESIZE 180
COLUMN OBJECT_NAME FORMAT a30
COLUMN EVENT FORMAT a30
SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM (active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history, dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC;
7、寻找基于指定时间范围内的历史SQL语句
[sql]
--注该查询受到awr快照相关参数的影响
-- filename:top_sql_in_spec_time.sql
--Top SQLs Elaps time and CPU time in a given time range..
--X.ELAPSED_TIME/1000000 => From Micro second to second
--X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA => How many times the sql ran
SET PAUSE ON
SET PAUSE 'Press Return To Continue'
SET LINESIZE 180
COL sql_text FORMAT a80 WRAP
SELECT sql_text,
dhst.sql_id,
ROUND (x.elapsed_time / 1000000 / x.executions_delta, 3) elapsed_time_sec,
ROUND (x.cpu_time / 1000000 / x.executions_delta, 3) cpu_time_sec,
x.elapsed_time,
x.cpu_time,
executions_delta AS exec_delta
FROM dba_hist_sqltext dhst,
( SELECT dhss.sql_id sql_id,
SUM (dhss.cpu_time_delta) cpu_time,
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
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'))
GROUP BY dhss.sql_id) x
WHERE x.sql_id = dhst.sql_id
ORDER BY elapsed_time_sec DESC;
8、寻找基于指定时间范围内及指定用户的历史SQL语句
[sql]
--注该查询受到awr快照相关参数的影响
SELECT DBMS_LOB.SUBSTR (sql_text, 4000, 1) AS sql,
ROUND (x.elapsed_time / 1000000, 2) elapsed_time_sec,
ROUND (x.cpu_time / 1000000, 2) cpu_time_sec,
x.executions_delta AS exec_num,
ROUND ( (x.elapsed_time / 1000000) / x.executions_delta, 2) AS exec_time_per_query_sec
FROM dba_hist_sqltext dhst,
( SELECT dhss.sql_id sql_id,
SUM (dhss.cpu_time_delta) cpu_time,