資料庫效能SQL監控指令碼
--1、消耗時間最多的前10條SQL語句；
SELECT round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
executions,
round(elapsed_time / executions / 1000, 2) "Elapsed Time per Exec (ms)",
parsing_schema_name "username",
sql_id,
sql_fulltext
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
elapsed_time,
rank() over(ORDER BY elapsed_time DESC NULLS LAST) elapsed_time_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE elapsed_time_rank <= 10;
--2、消耗記憶體資源最多的前10條SQL語句；
SELECT buffer_gets,
executions,
round(buffer_gets / decode(executions, 0, 1, executions), 0) "Gets per Exec",
round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
buffer_gets,
elapsed_time,
dense_rank() over(ORDER BY buffer_gets DESC NULLS LAST) buffer_gets_rank
FROM v$sql t
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE buffer_gets_rank <= 10;
--3、消耗I/O資源最多的前10條SQL語句；
SELECT disk_reads,
executions,
round(disk_reads / decode(executions, 0, 1, executions), 0) "Reads per Exec",
round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
disk_reads,
elapsed_time,
dense_rank() over(ORDER BY disk_reads DESC NULLS LAST) disk_reads_rank
FROM v$sql t
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE disk_reads_rank <= 10;
--4、執行頻率最多的前10條SQL語句；
SELECT executions,
rows_processed,
round(rows_processed / decode(executions, 0, 1, executions), 0) "Rows per Exec",
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
rows_processed,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY executions DESC NULLS LAST) exec_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE exec_rank <= 10;
---5、解析比最高的前10條SQL語句；
SELECT parse_calls,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
parse_calls,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY parse_calls DESC NULLS LAST) parse_calls_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE parse_calls_rank <= 10;
--6、排序次數最多的前10條SQL語句；
SELECT sorts,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
sorts,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY sorts DESC NULLS LAST) sorts_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE sorts_rank <= 10;
--7、Cost成本值最大的前10條SQL語句；
SELECT optimizer_cost,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
optimizer_cost,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY optimizer_cost DESC NULLS LAST) optimizer_cost_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE optimizer_cost_rank <= 10;
--8、檢視錶空間分配與空閒空間情況；
SELECT a.tablespace_name,
a.file_name,
round(a.bytes / 1024 / 1024, 0) allocated_mbytes,
b.free_mbytes
FROM dba_data_files a,
(SELECT file_id, round(SUM(bytes) / 1024 / 1024, 0) free_mbytes
FROM dba_free_space b
GROUP BY file_id) b
WHERE a.file_id = b.file_id
ORDER BY a.tablespace_name;
--9、顯示錶物件鎖的會話資訊；
SELECT o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.machine,
s.program,
s.osuser
FROM v$session s, v$locked_object l, dba_objects o
WHERE l.session_id = s.sid
AND l.object_id = o.object_id(+);
--10、顯示資料庫歸檔日誌定時備份情況資訊；
SELECT btype,
btype_key,
sequence#,
first_change#,
next_change#,
filesize,
filesize_display
FROM v$backup_archivelog_details t
ORDER BY sequence#;
--11、顯示資料庫資料檔案定時備份情況資訊；
SELECT btype,
btype_key,
file#,
incremental_level,
checkpoint_time,
filesize,
tsname,
filesize_display
FROM v$backup_datafile_details
ORDER BY btype_key,file#;
SELECT round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
executions,
round(elapsed_time / executions / 1000, 2) "Elapsed Time per Exec (ms)",
parsing_schema_name "username",
sql_id,
sql_fulltext
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
elapsed_time,
rank() over(ORDER BY elapsed_time DESC NULLS LAST) elapsed_time_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE elapsed_time_rank <= 10;
--2、消耗記憶體資源最多的前10條SQL語句；
SELECT buffer_gets,
executions,
round(buffer_gets / decode(executions, 0, 1, executions), 0) "Gets per Exec",
round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
buffer_gets,
elapsed_time,
dense_rank() over(ORDER BY buffer_gets DESC NULLS LAST) buffer_gets_rank
FROM v$sql t
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE buffer_gets_rank <= 10;
--3、消耗I/O資源最多的前10條SQL語句；
SELECT disk_reads,
executions,
round(disk_reads / decode(executions, 0, 1, executions), 0) "Reads per Exec",
round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
disk_reads,
elapsed_time,
dense_rank() over(ORDER BY disk_reads DESC NULLS LAST) disk_reads_rank
FROM v$sql t
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE disk_reads_rank <= 10;
--4、執行頻率最多的前10條SQL語句；
SELECT executions,
rows_processed,
round(rows_processed / decode(executions, 0, 1, executions), 0) "Rows per Exec",
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
rows_processed,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY executions DESC NULLS LAST) exec_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE exec_rank <= 10;
---5、解析比最高的前10條SQL語句；
SELECT parse_calls,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
parse_calls,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY parse_calls DESC NULLS LAST) parse_calls_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE parse_calls_rank <= 10;
--6、排序次數最多的前10條SQL語句；
SELECT sorts,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
sorts,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY sorts DESC NULLS LAST) sorts_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE sorts_rank <= 10;
--7、Cost成本值最大的前10條SQL語句；
SELECT optimizer_cost,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
optimizer_cost,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY optimizer_cost DESC NULLS LAST) optimizer_cost_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE optimizer_cost_rank <= 10;
--8、檢視錶空間分配與空閒空間情況；
SELECT a.tablespace_name,
a.file_name,
round(a.bytes / 1024 / 1024, 0) allocated_mbytes,
b.free_mbytes
FROM dba_data_files a,
(SELECT file_id, round(SUM(bytes) / 1024 / 1024, 0) free_mbytes
FROM dba_free_space b
GROUP BY file_id) b
WHERE a.file_id = b.file_id
ORDER BY a.tablespace_name;
--9、顯示錶物件鎖的會話資訊；
SELECT o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.machine,
s.program,
s.osuser
FROM v$session s, v$locked_object l, dba_objects o
WHERE l.session_id = s.sid
AND l.object_id = o.object_id(+);
--10、顯示資料庫歸檔日誌定時備份情況資訊；
SELECT btype,
btype_key,
sequence#,
first_change#,
next_change#,
filesize,
filesize_display
FROM v$backup_archivelog_details t
ORDER BY sequence#;
--11、顯示資料庫資料檔案定時備份情況資訊；
SELECT btype,
btype_key,
file#,
incremental_level,
checkpoint_time,
filesize,
tsname,
filesize_display
FROM v$backup_datafile_details
ORDER BY btype_key,file#;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29439655/viewspace-1378538/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫監控指令碼資料庫指令碼
- 監控資料庫指令碼資料庫指令碼
- 監控資料庫效能的SQL資料庫SQL
- 監控Oracle資料庫效能的指令碼段整理Oracle資料庫指令碼
- [zt]資料庫監控指令碼資料庫指令碼
- 資料庫監控指令碼（一）資料庫指令碼
- 資料庫監控指令碼（二）資料庫指令碼
- 資料庫監控指令碼（三）資料庫指令碼
- oracle資料庫效能監控的SQL(轉)Oracle資料庫SQL
- 監控資料庫效能的SQL彙總資料庫SQL
- 【SQL監控】SQL完全監控的指令碼SQL指令碼
- 資料庫效能監控資料庫
- 監控DB2資料庫指令碼DB2資料庫指令碼
- 監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- SQL Server資料庫監控SQLServer資料庫
- [轉]監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- 監控Oracle資料庫的常用shell指令碼(轉)Oracle資料庫指令碼
- 監控長時間執行的查詢(監控資料庫效能的SQL ) -- 轉資料庫SQL
- 幾個重要的指令碼來監控Oracle資料庫指令碼Oracle資料庫
- 【轉載】監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- Oracle 資料庫監控SQL語句Oracle資料庫SQL
- 【SQL】Oracle資料庫監控sql執行情況SQLOracle資料庫
- 【SQL】Oracle資料庫SQL監控報告示例SQLOracle資料庫
- 資料庫效能監控瓶頸理論資料庫
- Oracle慢SQL監控指令碼實現OracleSQL指令碼
- oracle 11g監控SQL指令碼OracleSQL指令碼
- 醬油DBA奉獻ORACLE資料庫監控健康指令碼（一）Oracle資料庫指令碼
- 監控指令碼指令碼
- 資料庫監控資料庫
- sqlserver資料庫系統效能監控步驟SQLServer資料庫
- SQL Server自動化運維繫列：監控效能指標指令碼SQLServer運維指標指令碼
- SQL Server 監控統計阻塞指令碼資訊SQLServer指令碼
- 通過shell指令碼同時監控多個資料庫負載指令碼資料庫負載
- mysql監控指令碼MySql指令碼
- DBA監控指令碼指令碼
- session指令碼監控Session指令碼
- 埠監控指令碼指令碼
- oracle 監控指令碼Oracle指令碼