如何快速判断Oracle数据库是否运行缓慢
2023-07-21 20:48 abce 阅读(219) 评论(0) 编辑 收藏 举报查看过去一分钟数据库的响应时间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SET LINESIZE 200 PAGESIZE 50000 COL BEGIN_TIME FORMAT A17 COL END_TIME FORMAT A17 COL INST_ID FORMAT 999 COL "Response Time (msecs)" FORMAT 999,999,999,999.99 SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI' ) BEGIN_TIME, TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI' ) END_TIME, INST_ID, ROUND (VALUE * 10, 2) "Response Time (msecs)" FROM GV$SYSMETRIC WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time' ORDER BY INST_ID; |
查看过去一分钟数据库的最小、最大、平均的响应时间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SET LINESIZE 200 PAGESIZE 50000 COL BEGIN_TIME FORMAT A17 COL END_TIME FORMAT A17 COL INST_ID FORMAT 999 COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99 COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99 COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99 SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI' ) BEGIN_TIME, TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI' ) END_TIME, INST_ID, ROUND (MINVAL * 10, 2) "Min Response Time (msecs)" , ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)" , ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)" FROM GV$SYSMETRIC_SUMMARY WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time' ORDER BY INST_ID; |
现在就有了数据库当前的响应时间。接下来需要做的就是和已经建立的阈值或其它窗口的响应时间做比较。可能需要查看多个时间窗口,来确认是否有响应问题。
可以检查以下视图: dba_hist_sysmetric_history 和 dba_hist_sysmetric_summary。
v$sysmetric_history每小时刷新一次dba_hist_sysmetric_history上的数据,v$sysmetric_summary也每小时刷新一次dba_hist_sysmetric_summary上的数据,因此,如果你没有关于响应时间的基线,应该将当前的响应时间与存储在上述视图中的旧窗口进行对比,如下所示(出于实用目的,我将只显示输出的一个片段,而不是修改谓词):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | SET LINESIZE 200 PAGESIZE 50000 COL BEGIN_TIME FORMAT A17 COL END_TIME FORMAT A17 COL INST_ID FORMAT 999 COL "Response Time (msecs)" FORMAT 999,999,999,999.99 SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI' ) BEGIN_TIME, TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI' ) END_TIME, INSTANCE_NUMBER INST_ID, ROUND (VALUE * 10, 2) "Response Time (msecs)" FROM DBA_HIST_SYSMETRIC_HISTORY WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time' ORDER BY BEGIN_TIME DESC , INSTANCE_NUMBER; SET LINESIZE 200 PAGESIZE 50000 COL BEGIN_TIME FORMAT A17 COL END_TIME FORMAT A17 COL INST_ID FORMAT 999 COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99 COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99 COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99 SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI' ) BEGIN_TIME, TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI' ) END_TIME, INSTANCE_NUMBER INST_ID, ROUND (MINVAL * 10, 2) "Min Response Time (msecs)" , ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)" , ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)" FROM DBA_HIST_SYSMETRIC_SUMMARY WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time' ORDER BY BEGIN_TIME DESC , INSTANCE_NUMBER; |
这样就有了一个快速了解数据库是否运行缓慢的方法。在这种情况下,我们的生产数据库在预期的响应时间内运行。显然,如果遇到数据库响应时间缓慢的问题,应该调查根本原因,并根据具体情况采取纠正措施。
请记住,每个应用和每种实现都会有不同的响应时间要求;你的工作是根据每种情况的具体细节来了解响应时间是否令人满意。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2022-07-21 【PostgreSQL 15】PostgreSQL 15对UNIQUE和NULL的改进
2016-07-21 MySQL数据复制的校验
2016-07-21 Mysql复制-Slave库设置复制延迟
2016-07-21 MySQL Replication的相关文件
2015-07-21 11G新特性 -- ASM Fast Mirror Resync
2015-07-21 redhat 6.4 安装VirtualBox自动增强功能功:unable to find the sources of your current Linux kernel