Oracle DBA常用SQL语句（4）- Top SQL

逻辑读 TOP 10

select *
from (select sqt.logicr logical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.logicr  sqt.exec)) Reads_per_Exec ,
(100 * sqt.logicr) 
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID =7634
AND E.SNAP_ID =7637
AND B.DBID = 3629726729
AND E.DBID = 3629726729
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads') Total_rate,
nvl((sqt.cput  1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap  1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(buffer_gets_delta) logicr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = 3629726729
and instance_number = 1
and 7634 < snap_id
and snap_id <= 7637
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 3629726729
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID =7634
AND E.SNAP_ID =7637
AND B.DBID = 3629726729
AND E.DBID = 3629726729
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads') > 0
order by nvl(sqt.logicr, -1) desc, sqt.sql_id)
where rownum < 65and(rownum <= 10
or Total_rate > 1);

物理读 TOP 10

select *
from (select sqt.dskr Physical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.dskr  sqt.exec)) Reads_per_Exec ,
(100 * sqt.dskr) 
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = $P{p_beg_snap}
AND E.SNAP_ID = $P{p_end_snap}
AND B.DBID = 1273705906
AND E.DBID = 1273705906
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') Total_rate,
nvl((sqt.cput  1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap  1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = 1273705906
and instance_number = 1
and $P{p_beg_snap} < snap_id
and snap_id <= $P{p_end_snap}
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 1273705906
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = $P{p_beg_snap}
AND E.SNAP_ID = $P{p_end_snap}
AND B.DBID = 1273705906
AND E.DBID = 1273705906
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65and(rownum <= 10
or Total_rate > 1);

消耗 CPU TOP 10

select *
from (select nvl((sqt.elap  1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput  1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap  sqt.exec  1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap  (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = 7396
AND E.SNAP_ID = 7399
AND B.DBID = 1273705906
AND E.DBID = 1273705906
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = 65972167
and instance_number = 1
and 7396 < snap_id
and snap_id <= 7399
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 1273705906
order by nvl(sqt.cput, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);

执行时间 TOP 10

select *
from (select nvl((sqt.elap  1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput  1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap  sqt.exec  1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap  (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = $P{p_beg_snap}
AND E.SNAP_ID = $P{p_end_snap}
AND B.DBID = 1273705906
AND E.DBID = 1273705906
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = 1273705906
and instance_number = 1
and $P{p_beg_snap} < snap_id
and snap_id <= $P{p_end_snap}
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 1273705906
order by nvl(sqt.elap, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);

查找需要使用绑定变量的 sql

select substr(sql_text,1,40), count(*)
from v$sqlarea
group by substr(sql_text,1,40) having count(*) > 50;

再执行下面的语句,找出具体的 sql 代码

select sql_text from v$sqlarea where sql_text like 'insert into test %';