I am a developer..Why should I care about REDO?

Thats the sort of mentality most people have in Oracle Development team..The code (SQL or PLSQL or PSP or forms) should work as expected.why should a developer worry about redo? And isnt that anyways a DBA's job?

All perfect questions and assumptions..Lets see some facts abt Redo.

Redo logs are physical structures(files) of database that captures all the changes (similar to Transaction Logs in Sqlserver) and provides guarantee that even if your database crashes next minute right after your transaction is completed, you can be ensured to recover them back.

So how does it work?
Put it in very simple way..think you have a database supporting 100 applications..say 100 different schemas..you may have data stored across say 100 datafiles (just an example)..now since database doesnt which files are critical and which files are not..(all are datafiles..whether an application is used by 1 user or 1 million users..a change to data is a change to data!) ..so saving changes to all 100 files every single second is next to impossible (& unwise) task. So what does oracle do? Oracle manages changes to data (to datafiles) at its own break-time..so when the user changes a row, the data may or may not get into actual datafile.. the changed data for all you know may just be in RAM (volatile memory) and if the box crashes/reboots the contents are gone..(just like - if you have stored something in buffer by using Ctrl+C in windows and restart the machine..your buffer data is lost)

so how is the data guaranteed to recover back? Thats where redo log comes in play..Oracle keeps writing all changes to data (like what row..old value..new value..etc) to an area in SGA called Log buffer..the moment a transaction is committed, instead of writing into data files, log buffer contents are written into redo log files.

okay , so why should a developer be concerned?
Well this has something to do with Oracle's internals..Say your DBA allocated 3 redo files..Oracle will write to #1..when it gets full..move to #2..when that gets full..move on to #3..and when thats full..move onto ..where?

it has to overwrite #1..but if it overwrites #1 logfile contents, how are you doing to get the data if crash happens..Are you thinking? Oracle just does the same thing!!! It starts thinking ..halting everything else going on at the database..it waits for another process called archiver, to take a copy of Redolog#1 to some safe area before it can overwrite the logfile.

Fine..enuf with internals..still why should developer be worried abt redo?

the answer to that question now is very simple..A DBA can only allocate enough number of redo log files to keep it going trouble-free..but its the statements or code which generates redo..so its the code written by developers which is root cause of redo generated..Oracle is great in parallelism..horizontal scaling ..multi-user support..all we have heard from marketing folks..but if there one place where its gets really into a bottleneck situation is "REDO".

so think about it..say a developer writes a statement which generates 2K bytes of redo data more than optimal/tuned statement..more such developers = more such statements; more users executing this application = more times the same statement will have to rexecute. potentially generating gobs of redo data at the database..more redo data = more redologs filled in at database..bringing the database to a halt each time Oracle has to wait for archiver to kick-in and back up the logfile before overwriting it..

Hmm..interesting..how do I know how much redo my statements are generating..

go back to autotrace..and run the statement with trace on..you would see a statistic showing up as "redo size"..It cant better any easier than that..

And believe me..the DBA can only see if the system is running out of log files and patch it more..DBA doesnt know which statement is causing such excessive redo (althought the session can be traced and then sql..99% that never happens..because the situation will be more of fire-fighting mode..when the database hangs, DBA cannot walk to developer's cube and ask "how dare you spoiled my day?")

So think about it this way..the less redo you (as a developer) generate..the more you are doing a favor to yourself and your DBA(s) too.. sort of like making his/her life peaceful..If you really work hard on getting keeping your code's redo size down, your DBA better realize your efforts and start treating you a free dinner or thank-you session at a bar.

thats also explains how a developer can easily mess up with DBAs weekend,if needed..write some bad code and put it as batch job to start sharp at Friday 10PM..your database will hang and DBAs get called/paged automatically..what a simple way to ruin their weekend -huh?

Whoever said Developers dont have rights to challenge DBAs - is utterly wrong. :)