huge Oracle Redo Logs

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.

I开发者_Go百科nfo 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

---

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;

---

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.