資料庫sql性能監控腳本,可将本腳本寫入job定時執行,用以監控執行緩慢的sql
---TOP DISK Reads(totally)
select *
from (select a.disk_reads "Physical Reads",
a.executions "Executions",
a.disk_reads / decode(a.executions, 0, 1, a.executions) "Reads per Exec",
a.cpu_time / 1000000 "CPU Time (s)",
a.elapsed_time / 100000 "Elapsed Time (s)",
a.sql_id,
a.PARSING_SCHEMA_NAME,
a.sql_text sql
from v$sqlarea a
order by 1 desc)
where rownum < 11;
---TOP DISK Reads Exec
select *
from (select a.disk_reads "Physical Reads",
a.executions "Executions",
a.disk_reads / decode(a.executions, 0, 1, a.executions) "Reads per Exec",
a.cpu_time / 1000000 "CPU Time(s)",
a.elapsed_time / 100000 "Elapsed Time (s)",
a.sql_id,
a.PARSING_SCHEMA_NAME,
a.sql_text sql
from v$sqlarea a
order by 3 desc)
where rownum < 11;
---TOP Elapsed Time(totally)
select *
from (select a.elapsed_time / 100000 "Elapsed Time (s)",
a.cpu_time / 1000000 "CPU Time(s)",
a.executions "Executions",
a.elapsed_time / decode(a.executions, 0, 1, a.executions) /
1000000 "Elap per Exec (s)",
a.cpu_time / decode(a.executions, 0, 1, a.executions) /
1000000 "CPU Time per Exec (s)",
a.sql_id,
a.PARSING_SCHEMA_NAME,
a.sql_text sql
from v$sqlarea a
order by 1 desc)
where rownum < 11;
---TOP Elapsed Time Exec
select *
from (select a.elapsed_time / 100000 "Elapsed Time (s)",
a.cpu_time / 1000000 "CPU Time(s)",
a.executions "Executions",
a.elapsed_time / decode(a.executions, 0, 1, a.executions)/1000000 "Elap per Exec (s)",
a.cpu_time / decode(a.executions, 0, 1, a.executions)/1000000 "CPU Time per Exec (s)",
a.sql_id,
a.PARSING_SCHEMA_NAME,
a.sql_text sql
from v$sqlarea a
order by 4 desc)
where rownum < 11;
---TOP Buffer Gets(totally)
select *
from (select a.buffer_gets "Buffer Gets",
a.executions "Executions",
a.buffer_gets / decode(a.executions, 0, 1, a.executions) "Gets per Exec",
a.cpu_time / 1000000 "CPU Time (s)",
a.elapsed_time / 100000 "Elapsed Time (s)",
a.sql_id,
a.PARSING_SCHEMA_NAME,
a.sql_text sql
from v$sqlarea a
order by 1 desc)
where rownum < 11;
---TOP Buffer Gets Exec
select *
from (select a.buffer_gets "Buffer Gets",
a.executions "Executions",
a.buffer_gets / decode(a.executions, 0, 1, a.executions) "Gets per Exec",
a.cpu_time / 1000000 "CPU Time(s)",
a.elapsed_time / 100000 "Elapsed Time (s)",
a.sql_id,
a.PARSING_SCHEMA_NAME,
a.sql_text sql
from v$sqlarea a
order by 3 desc)
where rownum < 11;