巨大的 Oracle 重做日志
每个星期二晚上 10 点，oracle 会突然生成大量 REDO 日志，直到磁盘空间耗尽。根据日志，我的应用程序在此期间没有运行任何大型查询或任何内容。
我唯一能找到的是 dba_scheduler_job_run_details 表当时启动了一个 oracle 作业。我在谷歌上找不到有关这份工作的任何信息，所以我迫切需要任何想法。
来自 dba_scheduler_job_run_details 的信息：
JOB_NAME：ORA$AT_SA_SPC_SY_254
状态：已停止
ACTUAL_START_DATE：11-03-22 22:00:02.125060000 CST6CDT
RUN_DURATION 9:4:19.0
At 10PM each Tuesday all of a sudden oracle is generating huge REDO logs until the disk runs out of space. My application is not running any huge queries or anything during this time according to the logs.
The only thing I can find is that the dba_scheduler_job_run_details table started an oracle job right at that time. I can't find any info on google about this job, so am desperate for any ideas.
Info from dba_scheduler_job_run_details:
JOB_NAME: ORA$AT_SA_SPC_SY_254
STATUS: STOPPED
ACTUAL_START_DATE: 11-03-22 22:00:02.125060000 CST6CDT
RUN_DURATION 9:4:19.0
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
晚上 10 点通常是自动统计数据收集开始的时间。虽然它通常每天运行。在 11g 统计数据收集中使用自动任务而不是调度程序，尝试使用如下查询查找统计作业：
select * from dba_autotask_job_history order by window_start_time desc;但即使问题是由统计数据引起的，这似乎很奇怪，它会导致太多的 REDO。通常收集统计数据需要大量的阅读和少量的写作。除非你有很多经常变化的小桌子；在这种情况下，统计信息量可能比实际数据量大得多。如果是这种情况，您可能需要更频繁地收集统计数据，或者可能锁定统计数据。
或者统计过程可能在特定表上崩溃。这将向您显示上次分析的表，也许它会给您一个线索：
select last_analyzed, dba_tables.* from dba_tables order by 1 desc nulls last;10PM is usually the time that automatic statistics gathering starts. Although it normally runs every day. In 11g stats gathering uses auto tasks instead of the scheduler, try looking for the stats job with a query like this:
select * from dba_autotask_job_history order by window_start_time desc;But even if the problem is caused by statistics, it seems odd that it would cause too much REDO. Usually gathering statistics is a lot of reading and a very small amount of writing. Unless you've got many small tables that change all the time; in that case the amount of statistics information could be much larger than the actual data. If that's the case you may need to gather the stats more often, or maybe lock the stats.
Or possibly the statistics process is blowing up on a specific table. This will show you what table was last analyzed, maybe it will give you a clue:
select last_analyzed, dba_tables.* from dba_tables order by 1 desc nulls last;我的东西生成了巨大的REDOLOG，那么你一定有巨大的DML活动。例如，清理脚本尝试清除一些数据，但失败、回滚，然后尝试一次又一次地执行相同的任务...
证明/反驳您的疑虑的最佳方法是“日志挖掘工具”。它使用起来并不简单，但它会告诉您哪些语句（以及针对哪个表）生成了大部分重做以及时间。
I something generates huge REDOLOG, then you must have huge DML activity. For examaple cleanup script which tries to purge some data, but fails, rollbacks, and then tries to do the same task again and again and again...
The best way how to prove/disprove your doubts is the "Log miner tool". It's not trivial to use, but it will tell you which statements (and against which table) generated most of the redo and that time.