Oracle 效能相關常用指令碼(SQL)
在缺乏的視覺化工具來監控資料庫效能的情形下,常用的指令碼就派上用場了,下面提供幾個關於Oracle效能相關的指令碼供大家參考。以下指令碼均在Oracle 10g測試透過,Oracle 11g可能要做相應調整。
1、尋找最多BUFFER_GETS開銷的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 語句
- --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分鐘導致資源過高開銷的事件
- --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分鐘內等待最多的使用者
- --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語句
- --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、等待最多的物件
- --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語句
- --注該查詢受到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語句
- --注該查詢受到awr快照相關引數的影響
- --Author : Robinson
- --Blog : http://blog.csdn.net/robinson_0612
- 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,
- 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語句被執行的次數
- --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 '&input_end_date', 'YYYYMMDD HH24:MI')
- ORDER BY s.begin_interval_time;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23490154/viewspace-1062420/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE常用SQL指令碼2OracleSQL指令碼
- sql_trace相關指令碼SQL指令碼
- 檢視Oracle基礎配置資訊和效能相關資訊的指令碼Oracle指令碼
- ORACLE DBA常用SQL指令碼工具->管理篇(zt)OracleSQL指令碼
- oracle_ray.sh 常用的oracle sql功能指令碼OracleSQL指令碼
- DG相關指令碼指令碼
- Oracle DB 相關常用sql彙總6[知乎系列續]OracleSQL
- ORACLE DBA常用SQL指令碼工具->管理篇(1) (轉)OracleSQL指令碼
- QTP - 指令碼相關收集QT指令碼
- Oracle SQL效能優化常用方法OracleSQL優化
- ORACLE及OS相關監控指令碼 For AIX6.1Oracle指令碼AI
- Oracle效能問題檢查 - 常用查詢指令碼(final)Oracle指令碼
- 【效能調整】等待事件(七) io相關等待查詢指令碼事件指令碼
- Oracle DB 相關常用sql彙總7【手工繫結sql執行計劃】OracleSQL
- Oracle SQL效能最佳化常用方法OracleSQL
- Oracle 密碼相關Oracle密碼
- Oracle DBA常用監控指令碼Oracle指令碼
- 常用的Oracle指令碼參考Oracle指令碼
- SQL Performance Analyzer SPA常用指令碼彙總SQLORM指令碼
- 【Oracle】--PL/SQL匯入Oracle sql指令碼"傻瓜教程"OracleSQL指令碼
- 【SCRIPT】Oracle統計資訊相關SQLOracleSQL
- 資料庫效能SQL監控指令碼資料庫SQL指令碼
- Oracle sql指令碼中註釋OracleSQL指令碼
- 刪除 AP 發票相關指令碼指令碼
- ORACLE常用定時備份指令碼Oracle指令碼
- oracle pga使用情況常用指令碼:Oracle指令碼
- ORACLE DBA常用語句和指令碼Oracle指令碼
- oracle報表相關指令碼Oracle指令碼
- MS SQL 日常維護管理常用指令碼(下)SQL指令碼
- MS SQL 日常維護管理常用指令碼(上)SQL指令碼
- sql相關SQL
- 解剖Nginx·自動指令碼篇(7)型別相關指令碼系列Nginx指令碼型別
- Oracle 表空間查詢相關sqlOracleSQL
- oracle實用sql(5)--session相關資訊OracleSQLSession
- Oracle - 表空間相關常用操作語句Oracle
- shell指令碼自動化採集效能sql指令碼SQL
- 常用指令碼指令碼
- 記錄shell指令碼程式設計相關指令碼程式設計