Oracle性能相关常用脚本(SQL)
在缺乏的可视化工具来监控数据库性能的情形下，常用的脚本就派上用场了，下面提供几个关于Oracle性能相关的脚本供大家参考。以下脚本均在Oracle 10g测试通过，Oracle 11g可能要做相应调整。
1、寻找最多BUFFER_GETS开销的SQL 语句
[sql]
--filename: top_sql_by_buffer_gets.sql
--Identify heavy SQL (Get the SQL with heavy BUFFER_GETS)
SET LINESIZE 190
COL sql_text FORMAT a100 WRAP
SET PAGESIZE 100
SELECT *
FROM ( SELECT sql_text,
sql_id,
executions,
disk_reads,
buffer_gets
FROM v$sqlarea
WHERE DECODE (executions, 0, buffer_gets, buffer_gets / executions) >
(SELECT AVG (DECODE (executions, 0, buffer_gets, buffer_gets / executions))
+ STDDEV (DECODE (executions, 0, buffer_gets, buffer_gets / executions))
FROM v$sqlarea)
AND parsing_user_id != 3D
ORDER BY 4 DESC) x
WHERE ROWNUM <= 10;
2、寻找最多DISK_READS开销的SQL 语句
[sql]
--filename:top_sql_disk_reads.sql
--Identify heavy SQL (Get the SQL with heavy DISK_READS)
SET LINESIZE 190
COL sql_text FORMAT a100 WRAP
SET PAGESIZE 100
SELECT *
FROM ( SELECT sql_text,
sql_id,
executions,
disk_reads,
buffer_gets
FROM v$sqlarea
WHERE DECODE (executions, 0, disk_reads, disk_reads / executions) >
(SELECT AVG (DECODE (executions, 0, disk_reads, disk_reads / executions))
+ STDDEV (DECODE (executions, 0, disk_reads, disk_reads / executions))
FROM v$sqlarea)
AND parsing_user_id != 3D
ORDER BY 3 DESC) x
WHERE ROWNUM <= 10
3、寻找最近30分钟导致资源过高开销的事件
[sql]
--filename:top_event_in_30_min.sql
--Last 30 minutes result those resources that are in high demand on your system.
SET LINESIZE 180
COL event FORMAT a60
COL total_wait_time FORMAT 999999999999999999
SELECT active_session_history.event,
SUM (
active_session_history.wait_time
+ active_session_history.time_waited)
total_wait_time
FROM v$active_session_history active_session_history
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880
AND SYSDATE
AND active_session_history.event IS NOT NULL
GROUP BY active_session_history.event
ORDER BY 2 DESC;
4、查找最近30分钟内等待最多的用户
[sql]
--filename:top_wait_by_user.sql
--What user is waiting the most?
SET LINESIZE 180
COL event FORMAT a60
COL total_wait_time FORMAT 999999999999999999
SELECT ss.sid,
NVL (ss.username, 'oracle') AS username,
SUM (ash.wait_time + ash.time_waited) total_wait_time
FROM v$active_session_history ash, v$session ss
WHERE ash.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE AND ash.session_id = ss.sid
GROUP BY ss.sid, ss.username
ORDER BY 3 DESC;
5、查找30分钟消耗最多资源的SQL语句
[sql]
--filename:top_sql_by_wait.sql
-- What SQL is currently using the most resources?
SET LINESIZE 180
COL sql_text FORMAT a90 WRAP
COL username FORMAT a20 WRAP
SET PAGESIZE 200
SELECT *
FROM ( SELECT sqlarea.sql_text,
dba_users.username,
sqlarea.sql_id,
SUM (active_session_history.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 wa
