【第三只眼：Oracle通过归档日志查看系统的业务高峰sql】

huoran668

已于 2024-02-28 14:43:55 修改

阅读量389

点赞数

分类专栏： oracle 文章标签： oracle 数据库 sql

于 2022-05-06 17:55:31 首次发布

本文链接：https://blog.csdn.net/huoran668/article/details/124615361

版权

本文介绍了如何通过Oracle查询过去一个月内每天各小时的归档日志切换次数来识别业务高峰时段，并展示了如何根据归档量查看特定时段的事件和SQL，以及针对高耗能SQL进行优化的方法。

摘要由CSDN通过智能技术生成

1 Oracle查询1个月内，每天按照小时查询日志的切换次数
SELECT TO_CHAR(FIRST_TIME, 'MM-DD') DAY,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24