This discussion is archived
1 2 3 6 Previous Next 83 Replies Latest reply: Jan 7, 2012 1:03 PM by 909589 RSS

Why not use Redo log for consistent read

631403 Newbie
Currently Being Moderated
Oracle 11.1.0.7:

This might be a stupid question.

As I understand if a select was issued at 7:00 AM and the data that select is going to read has changed at 7:10 AM even then Oracle will return the data that existed at 7:00 AM. And for this Oracle needs the data in Undo segments.

My question is since redo also has past and current information why can't redo logs be used to retreive that information? Why is undo required when redo already has all that information.
  • 1. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Its not a stupid question as anything related to the functioning of Oralce database is not stupid. But the understanding of yours about redo is wrong. I do know that its termed that redo contains both the images of old and new but its actually not the correct thing. What redo contains is only change vectors which simply put, are just the changed entries of those places in the block which have got modified. This is true for both Data blocks which contain your data and Undo blocks as well which contain your old image. Think about it, what is an Undo Segment, its just like any other segment with the only difference that its maintained and modified by Oracle itself. When you modify a value, for example 100 to 200, the redo entery or the changed entry 200 is maintained in the Redo Buffer and from there, to the redo log file. The old entry must also modify the Undo Segment's some block and has to write itself there so there is a modification involved for that also which is again nothing but an update issued to the Undo segment. So the image changed for the Undo is also maintained in the Redo. Now, if let's suppose you lose both of your data block and the undo block, which means you lose the files of these two, using the redo you can recover both! Taht's what actually means by when it is said that redo contains both old and new image.

    Now, if you have understood the above said, you must know that actual old/consistent image is maintained in the Undo segment only using which the consistent copy is created. Redo is used for the recovery whereas, Undo is used for the Read Consistacy.

    HTH
    Aman....
  • 2. Re: Why not use Redo log for consistent read
    Pavan DBA Expert
    Currently Being Moderated
    since redo also has past and current information

    can you be more clear on what you mean by past and current information? do you mean past and current values?
  • 3. Re: Why not use Redo log for consistent read
    Pavan DBA Expert
    Currently Being Moderated
    Here is the Guru !

    Once again great explanation Aman... Sir
  • 4. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Just a guy with very little knowledge sir, nothing else!

    Aman....
  • 5. Re: Why not use Redo log for consistent read
    680087 Pro
    Currently Being Moderated
    Hi,
    user628400 wrote:
    My question is since redo also has past and current information why can't redo logs be used to retreive that information? Why is undo required when redo already has all that information.
    Redo logs may be used by a DBMS (note that I'm intentionally not saying "Oracle" here) to support rollback/rollback to savepoint/read consistency. And AFAIK this is true for some DBMSs. The problem with such an approach is increased load on the redo thread in case of mixed workload: it is being utilized by many different transactions which are writing (because of modifying data) and at the same time reading (rollback/read consistency) to it, which make it a point of contention for IO. Oracle database, on the other hand, was deliberately written to support mixed concurrent workload, and handle it in scalable fashion. This is why redo thread is a separated stream of data which is just written sequentially to the redo log. With separated undo data in form of the rollback segments, Oracle can effectively organize information needed to flash back data block to a point in time which is needed to reader, thus eliminating blocked readers when the data is being modified. This is why Oracle's multi-version read consistency model is able to handle well both read and write workload (write is the biggest issue, actually).
  • 6. Re: Why not use Redo log for consistent read
    631403 Newbie
    Currently Being Moderated
    Thanks. I am still trying to understand the example that you mentioned.

    If data was changed to 100 to 200 wouldn't both the values be there in redo logs. As I understand:

    1. Insert row with value 100 at 7:00 AM and commit. 100 will be writen to redo log
    2. update row to 200 at 8:00 AM and commit. 200 will be written to redo log

    So in essence 100 and 200 both are there in the redo logs and if select was issued at 7:00 data can be read from redo log too. Please correct me if I am understanding it incorrectly.
  • 7. Re: Why not use Redo log for consistent read
    sb92075 Guru
    Currently Being Moderated
    Please correct me if I am understanding it incorrectly.
    What you describe is correct for your scenario.
    But Oracle can not assume/expect that the 100 value exists in redo logfile.
    What happens if/when 100 was INSERT/UPDATE more than 10 years ago?
    The only place Oracle can know for sure where to obtain the 100 value is from UNDO.
  • 8. Re: Why not use Redo log for consistent read
    448778 Journeyer
    Currently Being Moderated
    Yes redo contains the change vectors which can be used to construct the redo and the undo sql, but since redo log are used in the circular fashion there is less likelihood that data you are looking for is still in the redo log .

    You can use Logminer and mine the corrresponding log file to get the redo and undo sql you fired.
  • 9. Re: Why not use Redo log for consistent read
    631403 Newbie
    Currently Being Moderated
    Thanks. I get that piece but isn't it the same problem with UNDO? It's written as it expires and there is no guranteee until we specifically ask oracle to gurantee the UNDO retention? I guess I am trying to understand that UNDO was created for effeciency purposes so that there is less performance overhead as compared to reading and writing from redo.
  • 10. Re: Why not use Redo log for consistent read
    jgarry Guru
    Currently Being Moderated
    user628400 wrote:
    Thanks. I get that piece but isn't it the same problem with UNDO? It's written as it expires and there is no guranteee until we specifically ask oracle to gurantee the UNDO retention? I guess I am trying to understand that UNDO was created for effeciency purposes so that there is less performance overhead as compared to reading and writing from redo.
    Redo is to guarantee that the data has made it "into" the database, so you can redo it later if you need to recover. Undo is to be able to recreate the look of the data at a specific time. So yes, if there is not enough undo to recreate the data from the desired time, you will get an ORA-15xx type of error and the transaction will fail. Whoopie, you might have a mad user. If the redo is lost and the database needs to be recovered, your database will lose data and you should be fired. Redo is Oracle's Achilles' heel. That is why you mirror the redo and archive the logs. Also, Oracle is optimistic about whether transactions are going to be committed to the db, so it makes sense for it to assume they've been committed and do other things to account for it not being committed yet. Stuff things in sequentially as fast as possible, read them randomly when necessary. This also means Oracle can be lazy about figuring out when to actually write stuff into the db - it's on disk in the redo, guaranteed, and database blocks can be juggled about in memory as required, then written at leisure. If the cleaning lady yanks the power cord, 3000 people don't lose their committed transactions, no matter where Oracle was in writing them to the db - because the transaction isn't considered committed until Oracle comes back and says it wrote that redo. Assuming you haven't made some silly configuration that makes that untrue.

    In a way, you can use redo for consistent read, that is what flashback database technology is about, though it also needs special flashback logs. Other flashback technology is based on undo. All these things and more require that you thoroughly understand the redo and undo concepts, in the context of many people doing many things at once.
  • 11. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    user628400 wrote:
    Thanks. I get that piece but isn't it the same problem with UNDO? It's written as it expires and there is no guranteee until we specifically ask oracle to gurantee the UNDO retention? I guess I am trying to understand that UNDO was created for effeciency purposes so that there is less performance overhead as compared to reading and writing from redo.
    And this also you said,

    >
    If data was changed to 100 to 200 wouldn't both the values be there in redo logs. As I understand:
    1. Insert row with value 100 at 7:00 AM and commit. 100 will be writen to redo log
    2. update row to 200 at 8:00 AM and commit. 200 will be written to redo log
    So in essence 100 and 200 both are there in the redo logs and if select was issued at 7:00 data can be read from redo log too. Please correct me if I am understanding it incorrectly.
    I guess you didnt understand the explaination that I did. Its not the old data that is kept. Its the changed vector of Undo that is kept which is useful to "recover" it when its gone but not useful as such for a select statement. Whereas in an Undo block, the actual value is kept. You must remember that its still a block only which can contain data just like your normal block which may contain a table like EMP. So its not 100,200 but the change vectors of these things which is useful to recover the transaction based on their SCN numbers and would be read in that order as well. And to read the data from Undo, its quite simple for oracle to do so using an Undo block as the transaction table which holds the entry for the transaction, knows where the old data is kept in the Undo Segment. You may have seen XIDSEQ, XIDUSN, XIDSLOT in the tranaction id which are nothing but the information that where the undo data is kept. And to read it, unlke redo, undo plays a good role.

    About the expiry of Undo, you must know that only INACTIVE Undo extents are marked for expiry. The Active Extents which are having an ongoing tranaction records, are never marked for it. You can come back after a lifetime and if undo is there, your old data would be kept safe by oracle since its useful for the multiversioning. Undo Retention is to keep the old data after commit, something which you need not to do if you are on 11g and using Total Recall feature!

    HTH
    Aman....
  • 12. Re: Why not use Redo log for consistent read
    631403 Newbie
    Currently Being Moderated
    this is awesom explanation and now I feel I am getting closer to what I was really looking for. So now I understand that undo blocks are stored in redo and not the real values. My only question is that is it possible that if database crashes and while recovering, the undo data is not there tha's needed by redo or archive logs? I am thinking undo segments will have to be real big to help database recover upto the most recent archive/redo log. I am sure I am missing some more points here but it looks like if redo keeps the undo blocks then if DB is recovering from days old archive logs then undo needs to be big enough to hold all that data. I am surely missing something here badly.
  • 13. Re: Why not use Redo log for consistent read
    sb92075 Guru
    Currently Being Moderated
    I am surely missing something here badly.
    Yes, you are.
    The instance is always consistent & current to the point in time of the most recent COMMIT.
  • 14. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    well, i am editing and removing my repply because it got messed up badly due to pda being used. give me some time and i shall reply again. sorry for any confusion.

    Edited by: Aman.... on Jan 22, 2010 11:05 AM
1 2 3 6 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points