心得技巧
html5HTML/XhtmlCSSXML/XSLTDreamweaver教程Frontpage教程心得技巧

Oracle导致Redo日志暴增的SQL语句排查

发布时间：2022-06-29 发布网站：脚本宝典
脚本宝典收集整理的这篇文章主要介绍了Oracle导致Redo日志暴增的SQL语句排查，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

概述

一、oracle查询最近几天每小时归档日志产生数量的脚本写法

SELECT SubSTR(TO_CHAR(First_time, 'MM/DD/RR HH:MI:SS'), 1, 5)                             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:MI:SS'), 10, 2), '08', 1, 0)) H08,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '09', 1, 0)) H09,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '10', 1, 0)) h10,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '11', 1, 0)) H11,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '12', 1, 0)) H12,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '13', 1, 0)) H13,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '14', 1, 0)) H14,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '15', 1, 0)) H15,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '16', 1, 0)) H16,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '17', 1, 0)) H17,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '18', 1, 0)) H18,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '19', 1, 0)) H19,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '20', 1, 0)) H20,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '21', 1, 0)) H21,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '22', 1, 0)) H22,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '23', 1, 0)) H23,
       COUNT(*)                                                                           total
From v$LOG_history a
WHERE first_time >= to_char(Sysdate - 10)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5) DESC;

查询结果

Oracle导致Redo日志暴增的SQL语句排查

 

二、查看最近2小时"块改变"最多的segment

redo大量产生必然是由于大量产生"块改变"。从awr视图中找到"块改变"最多的segment。

这是查询最近2小时(120分钟)的，begin_interval_time> sysdate - 120/1440，大家也可以自定义修改查询最近多少分钟的。

 1 select *
 2 from (SELECT to_char(begin_interval_time, 'yyYY_MM_DD HH24:MI') snap_time,
 3              dhsso.object_name,
 4              SUM(db_block_changes_delta)
 5       FROM dba_hist_seg_stat dhss,
 6            dba_hist_seg_stat_obj dhsso,
 7            dba_hist_snapshot dhs
 8       WHERE dhs.snap_id = dhss.snap_id
 9         AND dhs.instance_number = dhss.instance_number
10         AND dhss.obj# = dhsso.obj#
11         AND dhss.dataobj# = dhsso.dataobj#
12         AND begin_interval_time > sysdate - 120 / 1440
13       GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'), dhsso.object_name
14       order by 3 desc)
15 where rownum <= 5;

查询结果：

Oracle导致Redo日志暴增的SQL语句排查

 

三、从awr视图中找出步骤1中排序靠前的对象涉及的SQL

说明：LIKE '%MON_MODS$%'中MON_MODS是步骤1中查询出来的OBJECT_NAME

 1 SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
 2        dbms_lob.substr(sql_text, 4000, 1),
 3        dhss.instance_number,
 4        dhss.sql_id,
 5        executions_delta,
 6        rows_PRocessed_delta
 7 FROM dba_hist_sqlstat dhss,
 8      dba_hist_snapshot dhs,
 9      dba_hist_sqltext dhst
10 WHERE UPPER(dhst.sql_text) LIKE '%MON_MODS$%'
11   AND dhss.snap_id = dhs.snap_id
12   AND dhss.instance_Number = dhs.instance_number
13   AND dhss.sql_id = dhst.sql_id;

查询结果

Oracle导致Redo日志暴增的SQL语句排查

 

四、从ASH相关视图找到执行这些SQL的session、module和machine

1 select * from dba_hist_active_sess_history WHERE sql_id = 'c9n8kv7afchtd';
2 select * from v$active_session_history where sql_Id = 'c9n8kv7afchtd';
c9n8kv7afchtd是SQL_ID,替换第二步查询的结果SQL_ID列

 

脚本宝典总结

以上是脚本宝典为你收集整理的Oracle导致Redo日志暴增的SQL语句排查全部内容，希望文章能够帮你解决Oracle导致Redo日志暴增的SQL语句排查所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
标签：javaloadloadmacphppython排序排序程序员程序员
猜你在找的心得技巧相关文章
其他相关热搜词更多
phpjavapython程序员loadpost-format-gallerydivString参数开发ListcapMapClass工具安装SQL数据库资源this
全站导航更多
html5HTML/XhtmlCSSXML/XSLTDreamweaver教程Frontpage教程心得技巧JavaScriptASP.NETPHP编程正则表达式AJAX相关ASP编程JSP编程编程10000问CSS/HTMLFlexvbsDOS/BAThtahtcpythonperl游戏相关VBA远程脚本ColdFusionMsSqlMysqlmariadboracleDB2mssql2008mssql2005SQLitePostgreSQLMongoDB星外虚拟主机华众虚拟主机Linuxwin服务器FTP服务器DNS服务器Tomcatnginxzabbix云和虚拟化bios系统安装系统系统进程Windows系列LINUXRedHat/CentosUbuntu/DebianFedoraSolaris红旗Linux建站经验微信营销网站优化网站策划网络赚钱网络创业站长故事alexa域名photoshop教程摄影教程Fireworks教程CorelDraw教程Illustrator教程Painter教程Freehand教程IndesignSketch笔记本主板内存CPU存储显卡显示器光存储鼠标键盘平板电脑安全教程杀毒防毒安全设置病毒查杀脚本攻防入侵防御工具使用业界动态Exploit漏洞分析
最新心得技巧教程
热门心得技巧教程