This discussion is archived
1 2 3 4 5 6 Previous Next 83 Replies Latest reply: Jan 7, 2012 1:03 PM by 909589 Go to original post RSS
  • 45. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Ksg,

    I believe that the link given to you by another poster from Jonathan's blog post, which is about this very thread only ,sums it up nicely. Change Vector is a representation of the change which has been done and marked in the redo log. Its basically the metadata structure which describes to oracle about what you have done. If you see the blog post, you would see that JL has mentioned a small redo log dump snippet which does show it as well. The same information is recorded in the archive log as well since its nothing but a copy of the online log.

    Aman....
  • 46. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Sripada,

    Please see the link mentioned by another user for the JL's blog post. Hope that may help.

    Aman....
  • 47. Re: Why not use Redo log for consistent read
    KSG Explorer
    Currently Being Moderated
    Hi Aman,

    Thanks for your reply.

    Please bare with me

    In the thread, doubts on hot backup
    you have said Please note that only for the first time, the complete block is going to be copied into the log buffer, not all the time+ (But this is the case of hot backup)

    In this thread previously you have explained So the first thing you need to remember from now is that with the DML, the entire block doesn't get logged into the log buffer. Think about it, how it is possible in anyway since the size of the log buffer, in general is much smaller compared to other memory areas.+

    Now, Change Vector is a representation of the change which has been done and marked in the redo log+ ...So for example, If i update a value from 100 to 200, will oracle mark the value 200 in redolog if so where this 200 gets stored? Could you please elobrate me with an example (classic ex: while updating from 100 to 200)

    Please let me know what actually goes/happens in the redolog buffer.

    Thanks in advance
    KSG
  • 48. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    d Please note that only for the first time, the complete block is going to be copied into the log buffer, not all the time (But this is the case of hot backup)
    Yes, this is the case only with the hot backup. Other than that, its not possible to happen. See below,
    SQL> shut immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup  mount
    alter databsae ORACLE instance started.
    
    Total System Global Area  171573248 bytes
    Fixed Size                  1335276 bytes
    Variable Size             117440532 bytes
    Database Buffers           46137344 bytes
    Redo Buffers                6660096 bytes
     
    
    alter databsae aDatabase mounted.
    SQL>   2  SQL> r   
      2  
    SQL> alter database archivelog;
    
    Database altered.
    
    SQL> alter database open;
    
    Database altered.
    
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL> alter system checkpoint;
    
    System altered.
    
    SQL> select name from V$database;
    
    NAME
    ---------
    PROD
    
    SQL> select name from V$datafile;
    
    NAME
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/prod/system01.dbf
    /u01/app/oracle/oradata/prod/sysaux01.dbf
    /u01/app/oracle/oradata/prod/undotbs01.dbf
    /u01/app/oracle/oradata/prod/users01.dbf
    /u01/app/oracle/oradata/prod/rcat.ts
    /u01/app/oracle/oradata/prod/prod_ts
    
    6 rows selected.
    
    SQL> select name from V$tablespace;
    
    NAME
    ------------------------------
    SYSTEM
    SYSAUX
    UNDOTBS1
    USERS
    TEMP
    RCAT
    PROD_TS
    
    7 rows selected.
    
    SQL> create table test_tab as select * from dba_objects tablespace prod_ts;
    create table test_tab as select * from dba_objects tablespace prod_ts
                                                                  *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    
    
    SQL> create table test_tab as select * from dba_objects;
    
    Table created.
    
    SQL> alter table test_tab move tablespace prod_ts; 
    
    Table altered.
    
    SQL> set autot trace stat
    *SQL> update test_tab set object_id=1 where rownum<=1;*
    
    1 row updated.
    
    
    Statistics
    ----------------------------------------------------------
             24  recursive calls
              3  db block gets
             81  consistent gets
            294  physical reads
            *116  redo size*
            679  bytes sent via SQL*Net to client
            620  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> commit;
    
    Commit complete.
    
    SQL> *alter tablespace prod_ts begin backup;*
    
    Tablespace altered.
    
    SQL> update prod_ts set object_id=2 where rownum<=1;
    update prod_ts set object_id=2 where rownum<=1
           *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> update test_tab set object_id=2 where rownum<=1;
    
    1 row updated.
    
    
    Statistics
    ----------------------------------------------------------
              4  recursive calls
              3  db block gets
             76  consistent gets
             88  physical reads
           *8680  redo size*
            679  bytes sent via SQL*Net to client
            620  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> commit;
    
    Commit complete.
    
    SQL> update test_tab set object_id=2 where rownum<=1;
    
    1 row updated.
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              2  db block gets
              4  consistent gets
              0  physical reads
            468  redo size
            679  bytes sent via SQL*Net to client
            620  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> update test_tab set object_id=2 where rownum<=4;
    
    4 rows updated.
    
    
    Statistics
    ----------------------------------------------------------
              4  recursive calls
              1  db block gets
             76  consistent gets
              0  physical reads
            564  redo size
            680  bytes sent via SQL*Net to client
            620  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              4  rows processed
    
    SQL> update test_tab set object_id=2 where rownum<=40;
    
    40 rows updated.
    
    
    Statistics
    ----------------------------------------------------------
              4  recursive calls
              3  db block gets
             76  consistent gets
              0  physical reads
           3512  redo size
            680  bytes sent via SQL*Net to client
            621  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             40  rows processed
    
    SQL> commit;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    You can see here that when we didnt have the tablespace in the backup mode, there was very small redo which got generated. But when we did put the tablespace in the backup mode, 8kb(8092) additional redo is added. But this didnt change after it.Even when I did update 4 rows together as well, the amount of redo being generated was much smaller than the first time. So should be clear that that only for the first time, the whole block would be logged in the log buffer, not subsequently.

    In the redo, the value 200 is converted into bits and bytes which are stored. The Change Vector is nothiong but the repreentation of that changed value 200 only. I guess you are confused and I believe others as well over here that the where is the value ? The answer is , the value is right there in the redo log buffer. The only difference is, for you what is a value of 200, for Oracle its change vector associated to a particular transaction. That's all! Does that clear some things?

    Aman....
  • 49. Re: Why not use Redo log for consistent read
    Franck Pachot Journeyer
    Currently Being Moderated
    Hi,

    When oracle does any change to a block (table block, index block, rollback segment block,...) the change operation is a change vertor. That change vector describes the operation and what changes within the block (such as row x column n has now value n). The change vector header has the Data Block Address to identify the block (file number and block id) as well as information about the 'version' of the block that is touched: incarnation number (incremented when block is formatted) and sequence number (incremented at every changes).

    So change vector is for only one block. Several change vectors can be grouped in one redo record in order to keep atomicity about a change that concerns several blocks. This is the case for example when a change modifies both the data block and the rollback segment block. That must be atomic, even in case of failure: both must be done or none of them.

    Those redo records are written to the stream of redo: the redo thread. Each instance has its thread. It is a log buffer that is periodically flushed to redo log files.

    Redo records are created also for several operations: a commit for example generates a redo record, and in addition to that forces the redo log buffer to be flushed to disk.

    When a redo log file must be overwritten is is copied as an archived log file (when database is in archivelog mode)

    Regards,
    Franck.
  • 50. Re: Why not use Redo log for consistent read
    KSG Explorer
    Currently Being Moderated
    Hi Aman,

    Thanks for a good explanation as usual.
    But Still I would like to bug you. Please bare.

    If I made 100MB of changes in Db buffer cache, would redolog buffer occupies same 100MB of space?
    or
    if the value 200 occupy 10m in db buffer cache. would redolog buffer occupies same 10m of space to store the value 200?


    What is the advantage of storing the value with change vector? wouldl it reduce the size of space?

    Thanks
    KSG
  • 51. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    If I made 100MB of changes in Db buffer cache, would redolog buffer occupies same 100MB of space?
    You must know that within the buffer cache, the IO unit is block size. So if we take your point in general and assume that we have 8 blocks of 8kb each , we have 64kb of data available in the buffer cache. Let's say we would change from each buffer 1kb, so we have made 8 buffers dirty but the redo generation is of 8kb (1kb from each buffer) and that 8kb is recorded in the redo log buffer. Surely enough this is done to store only the delta which we would need to do the recovery. The change vector is the way Oracle maps this information within the redo log buffer.

    HTH
    Aman....
  • 52. Re: Why not use Redo log for consistent read
    KSG Explorer
    Currently Being Moderated
    Hi Aman,

    Thanks for your reply

    you said

    So the first thing you need to remember from now is that with the DML, the entire block doesn't get logged into the log buffer. Think about it, how it is possible in anyway since the size of the log buffer, in general is much smaller compared to other memory areas. If we would log a full block inthe log buffer, with few blocks only, it would touch its maximum size. So this is something you need to understand and remember

    But now I get your point

    Let's say we would change from each buffer 1kb, so we have made 8 buffers dirty but the redo generation is of 8kb (1kb from each buffer) and that 8kb is recorded in the redo log buffer. Surely enough this is done to store only the delta which we would need to do the recovery.

    For my understanding, I feel both the statements conflicts each other.

    My understanding: All the changed blocks gets logged into the log buffer. Am I correct with this point?

    Please guide me

    Thanks
    KSG
  • 53. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Dude,

    You need to make sure that you stick to one point. Now, if I have to confuse you, there may be situations where none of the changes go to the redo log, how about this? Or let's finish you off completely, from 10g, there is actually no need to use redo log buffer since the redo is maintained within the shared pool only* . Hows this? Is it conflicting as well :-) ? There are various things which depends on various reasons. But if we are trying to understand, we must stick to one point first and most importantly, the basic approach.

    The first statement( which I have explained couple of times already) is only applicable when you have the tablespace in the backup mode. In that case too, only for the first time , the entire block is copied. After that, there is no difference in the working and Ihave shown it already. The 2nd statement talks about what happens all the time assuming that there is no backup mode used( not even needed as well since one must use RMAN).

    Did it clear some doubts?

    Aman....
  • 54. Re: Why not use Redo log for consistent read
    KSG Explorer
    Currently Being Moderated
    Hi Aman,

    I am far from understanding your new points.

    From your explanation, now I'm clear regarding backup mode.

    But in this thread Why not use Redo log for consistent read
    at this point we were not discussing about backup mode at all.

    I'm not sure whether change blocks gets logged on redolog or not?

    I feel hard to ask repeatedly, which you already explained many times.

    But Still i'm not convinced with my understanding.

    Thanks
    KSG
  • 55. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    No issues about asking again. I get to repeat the same thing many times in my classes , its better and less painful here :-) .

    What's not clear ?
    I'm not sure whether change blocks gets logged on redolog or not?
    Why won't they get logged? What you need to know is that the "entire block" will not be logged into the redo log buffer but only the changed portion. So in a simple manner, if the table EMP contains 14 rows and you modify row 1, only row 1 would be logged into the redo. Is it fine and makes something clear?

    Aman....
  • 56. Re: Why not use Redo log for consistent read
    KSG Explorer
    Currently Being Moderated
    Hi Aman,

    Thanks for your reply.

    Why won't they get logged? What you need to know is that the "entire block" will not be logged into the redo log buffer but only the changed portion. So in a simple manner, if the table EMP contains 14 rows and you modify row 1, only row 1 would be logged into the redo. I do understand on hot backup only changed portion gets updated on redolog.
    Please let's forget backup mode scenario.

    I mess up with the point which you have said in this thread. Could you please explain me with an illustration on the below mentioned statement.

    When you do an update to an Oracle data block, whch means you change a salary value from 100 to 200, a change is done over the actual value(here 100). For this change, a Change Vector is generated which is actually an information that tells to oracle that this thing has happened and a code representation of the Update command for this value is generated. This Change Vector gets logged in the redo log buffer. For a transaction, multiple changes are logged into a redo stream which isnothing but the collection of the change vectors of that transaction. So the first thing you need to remember from now is that with the DML, the entire block doesn't get logged into the log buffer. Think about it, how it is possible in anyway since the size of the log buffer, in general is much smaller compared to other memory areas. If we would log a full block inthe log buffer, with few blocks only, it would touch its maximum size. So this is something you need to understand and remember

    Thanks
    KSG
  • 57. Re: Why not use Redo log for consistent read
    VijayaraghavanKrishnan Pro
    Currently Being Moderated
    Hi Aman,

    +
     from 10g, there is actually no need to use redo log buffer since the redo is maintained within the shared pool only*
    +

    Where to read about this, I am searching it a lot bt not able to get the relevant documents, Can you please explain about the above and post the relevant links please.

    Thanks,
    Vijayaraghavan K
  • 58. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    You can read a bit about it [here.|http://blog.aristadba.com/?p=17]

    Cheers
    Aman....
  • 59. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Girish,
    I do understand on hot backup only changed portion gets updated on redolog.Please let's forget backup mode scenario.
    {sigh}

    No, unfortunately you still don't understand. Yes let's forget about backup. Repeat it as long as you won't remember it, "only changed entries go into the redo log buffer, not the whole buffer" . Seriously, I am not sure why you are skipping this thing that only in the tablespace being in the backup mode, for the very first time only, the whole buffer gets logged into the redo log buffer. Other than that and even in this case as well after the first time, only changed values go in the log buffer .

    You need to understand and remember this Girish. Please don't change my statements and confused. As I said before, stick to what I am saying , understand one thing at one time and than proceed further.
    When you do an update to an Oracle data block, whch means you change a salary value from 100 to 200, a change is done over the actual value(here 100). For >this change, a Change Vector is generated which is actually an information that tells to oracle that this thing has happened and a code representation of the >Update command for this value is generated. This Change Vector gets logged in the redo log buffer. For a transaction, multiple changes are logged into a redo >stream which isnothing but the collection of the change vectors of that transaction. So the first thing you need to remember from now is that with the DML, the entire >block doesn't get logged into the log buffer. Think about it, how it is possible in anyway since the size of the log buffer, in general is much smaller compared to >other memory areas. If we would log a full block inthe log buffer, with few blocks only, it would touch its maximum size. So this is something you need to >understand and remember
    Okay, tell me what you haev understood from the above said. We shall discuss that rather than me saying anything.

    HTH
    Aman....

Legend

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