当前位置:网站首页>DBA常用SQL语句(4)- Top SQL
DBA常用SQL语句(4)- Top SQL
2022-04-23 09:52:00 【Grainger】
逻辑读 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 %';
版权声明
本文为[Grainger]所创,转载请带上原文链接,感谢
https://cloud.tencent.com/developer/article/1986060
边栏推荐
- [educational codeforces round 80] problem solving Report
- How to obtain geographical location based on photos and how to prevent photos from leaking geographical location
- Construire neuf capacités de fabrication agile à l'ère métacosmique
- Pyqt5与通信
- High paid programmer & interview question series 91 limit 20000 loading is very slow. How to solve it? How to locate slow SQL?
- kernel-pwn学习(4)--Double Fetch&&0CTF2018-baby
- Planning and construction of industrial meta universe platform
- Solving Lucas number and combination theorem
- 最长公共前串
- How to use SQL statement union to get another column of another table when the content of a column in a table is empty
猜你喜欢
构建元宇宙时代敏捷制造的九种能力
Yyds dry goods inventory ubuntu18 0.4 install MySQL and solve error 1698: access denied for user ''root' '@' 'localhost' '
云身份过于宽松,为攻击者打开了大门
ABAP implementation publishes restful services for external invocation example
Simple understanding of arguments in JS
Yarn资源调度器
Kernel PWN learning (4) -- double fetch & 0ctf2018 baby
SAP CR transmission request sequence and dependency check
Amazon cloud technology entry Resource Center, easy access to the cloud from 0 to 1
2022年制冷与空调设备运行操作考试练习题及模拟考试
随机推荐
Comparison of overloading, rewriting and hiding
Solving Lucas number and combination theorem
ABAP 7.4 SQL Window Expression
重载、重写、隐藏的对比
中控学习型红外遥控模块支持网络和串口控制
Dropout技术之随机神经元与随机深度
[CF 1425d] danger of mad snakes
The central control learning infrared remote control module supports network and serial port control
Code source daily question div1 (701-707)
JS DOM event
Alibaba cloud architects interpret the four mainstream game architectures
ABAP publishes OData service samples from CDs view
[2020wc Day2] F. Clarice picking mushrooms (subtree and query, light and heavy son thought)
Educational Codeforces Round 81 (Rated for Div. 2)
杰理之通常程序异常情况有哪些?【篇】
Expansion of number theory Euclid
Three ways to create objects in JS
打印页面的功能实现
从知识传播的维度对比分析元宇宙
MacOS下使用CLion编译调试MySQL8.x