1 2 3 4 5 6 Previous Next 83 Replies Latest reply: Jan 7, 2012 3:03 PM by 909589 Go to original post RSS
      • 45. Re: Why not use Redo log for consistent read
        Aman....
        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....
          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
            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....
              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
                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
                  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....
                    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
                      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....
                        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
                          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....
                            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
                              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
                                Vijayaraghavan Krishnan
                                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....
                                  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....
                                    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....