Chapter 5

Overview

In Chapter 4 on Transactions, we covered the basic mechanics of redo and rollback (also known as undo). There we covered what redo is. Simply put, it is the information Oracle records in the online redo log files in order to replay your transaction in the event of a failure. It allows Oracle to, in effect, 'redo' your transaction(s). We also covered undo, or rollback, the information Oracle records in the rollback segments in order to undo or roll back your transaction. Additionally, we touched on a couple of issues, such as why you might get an ORA-01555: snapshot too old error, and why you might see checkpoint not complete, cannot allocate new log. What I would like to do in this chapter, is go over in more depth the concepts behind redo and rollback, and what you, the developer, need to know about them.

Redo and rollback is a topic that bridges the DBA and developer roles. Both need a good fundamental understanding of their purpose, how they work, and how to avoid issues with regards to them. We'll be covering this information here. What we will not cover are the things your DBA should be exclusively in charge of figuring out and tuning. For example, how to find the optimum setting for RECOVERY_PARALLELISM or the FAST_START_IO_TARGET init.ora parameters are topics we will not cover. Instead, we will concentrate on the things that a database developer should be concerned with, and how they will impact your application.

Redo

Redo log files are extremely crucial to the Oracle database. These are the transaction logs for the database. They are used only for recovery purposes; their only purpose in life is to be used in the event of an instance or media failure. If the power goes off on your database machine, causing an instance failure, Oracle will use the online redo logs in order to restore the system to exactly the point it was at, immediately prior to the power outage. If your disk drive fails, Oracle will utilize archived redo logs as well as online redo logs in order to restore a backup of that drive to the correct point in time. Additionally, if you 'accidentally' drop a table, or remove some critical information and commit this operation, you can restore a backup of the affected data and restore it to the point in time immediately prior to the 'accident', using these online and archive redo log files.

Oracle maintains two types of redo log files, online and archived. Every Oracle database has at least two online redo log files. These online redo log files are used in a circular fashion. Oracle will write to log file 1, and when it gets to the end of that file, it will switch to log file 2, and begin writing to this one. When it has filled log file 2, it will switch back to log file 1 (assuming we have only two redo log files, if you have three, it would of course proceed onto the third file). Archived redo log files are simply copies of old, full online redo log files. As the system fills up log files, the ARCH process will make a copy of the online redo log file in another location. These archived redo log files are used to perform media recovery, when a failure is caused by a disk drive going bad, or some other physical fault. Oracle can take these archived redo log files, and apply them to backups of the data files to catch them up to the rest of the database. They are the transaction history of the database.

Redo, or transaction logs, are one of the major features that make a database a database. They are perhaps its most important recovery structure, although without the other pieces such as rollback segments, distributed transaction recovery, and so on, nothing works. They are major components of what sets a database apart from a file system. The online redo logs allow us to effectively recover from a power outage - one that happens while Oracle might be in the middle of a write. The archived redo logs allow us to recover from media failures, when for instance, the hard disk goes bad. Without them, the database would not offer any more protection than a file system.

It is important for us to understand how these log files might impact on us as developers. We will look at how the different ways we can write our code affect their utilization. We will look at why some database errors (specifically the ORA-01555: snapshot too old) happen, and how to avoid them. We've already seen the mechanics of redo in Chapter 4, and now we'll look at some specific issues. Many of these scenarios might be detected by you, but would be fixed by the DBA as they affect the database instance as a whole. We'll start with what happens during a COMMIT, and then get into commonly asked questions and issues surrounding the online redo logs.

What Does a COMMIT Do?

Sometimes, people want to understand exactly what happens during a COMMIT, and as a developer you should have an understanding of what goes on. A COMMIT is a very fast operation, regardless of the transaction size. One might think that the bigger a transaction is (in other words, the more data it affects) the longer a COMMIT will take. This is not true. The response time of a COMMIT is generally 'flat', regardless of the transaction size. This is because a COMMIT does not really have too much work to do, but what it does do is vital.

One of the reasons this is an important fact to understand and embrace, is that it will lead you down the path of letting your transactions be as big as they should be. Many developers artificially constrain the size of their transactions, committing every so many rows, instead of committing when a logical unit of work has been performed. They do this in the mistaken belief that they are lessening the resources on the system, when in fact they are increasing them. If a COMMIT of one row takes X units of time, and the COMMIT of one thousand rows takes the same X units of time, then performing work in a manner that does 1000 one-row COMMITs will not take an additional 1000*X units of time to perform. By only committing when you have to (when the transaction is complete), you will not only increase performance, but also reduce contention for shared resources (the log files, various internal latches, and the like). A simple example demonstrates that it necessarily takes longer:

tkyte@TKYTE816> create table t ( x int ); tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> declare   2          l_start number default dbms_utility.get_time;   3          begin   4          for i in 1 .. 1000   5          loop   6                  insert into t values ( 1 );   7          end loop;   8          commit;   9          dbms_output.put_line  10          ( dbms_utility.get_time-l_start || ' hsecs' );  11  end;  12  / 7 hsecs      PL/SQL procedure successfully completed.      tkyte@TKYTE816> declare   2          l_start number default dbms_utility.get_time;   3  begin   4          for i in 1 .. 1000   5          loop   6                  insert into t values ( 1 );   7                  commit;   8          end loop;   9          dbms_output.put_line  10          ( dbms_utility.get_time-l_start || ' hsecs' );  11  end;  12  / 21 hsecs      PL/SQL procedure successfully completed. 

In this case, it is three times longer - your mileage will vary on this. When you compound the above example with multiple users doing the same work, all committing too frequently, the numbers will go up rapidly. We've seen this, time and time again with other similar situations. For example, we've seen how not using bind variables and performing hard parses frequently severely reduces concurrency due to library cache contention, and excessive CPU utilization. Even when we switch to using bind variables, soft parsing too frequently incurs massive overhead. You must only perform operations when you need to - a COMMIT is just another operation like a parse. It is best to size your transactions based on business need, not based on misguided attempts to lessen resource usage on the database.

So, why is a COMMIT's response time fairly flat, regardless of the transaction size? Before we even go to COMMIT in the database, we have already done the really hard work. We've already modified the data in the database, so we've already done 99.9 percent of the work. For example, operations such as the following have already taken place:

  • Rollback segment records have been generated in the SGA.

  • Modified data blocks have been generated in the SGA.

  • Buffered redo for the above two items has been generated in the SGA.

  • Depending on the size of the above three, and the amount of time spent, some combination of the above data may be flushed onto disk already.

  • All locks have been acquired.

When we COMMIT, all that is left to happen is the following:

  • Generate a SCN (System Change Number) for our transaction.

  • LGWR writes all of our remaining buffered redo log entries to disk, and records the SCN in the online redo log files as well. This step is actually the COMMIT. If this step occurs, we have committed. Our transaction entry is removed, this shows that we have committed. Our record in the V$TRANSACTION view will 'disappear'.

  • All locks held by our session are released, and everyone who was enqueued waiting on locks we held will be released.

  • Many of the blocks our transaction modified will be visited and 'cleaned out' in a fast mode if they are still in the buffer cache.

As you can see, there is very little to do in order to process a COMMIT. The lengthiest operation is, and always will be, the activity performed by LGWR, as this is physical disk I/O. The amount of time spent by LGWR here will be gated (limited) by the fact that it has already been flushing the contents of the redo log buffer on a recurring basis. LGWR will not buffer all of the work you do for as long as you do it. Rather, it will incrementally flush the contents of the redo log buffer in the background as we are going along. This is to avoid having a COMMIT wait for a very long time in order to flush all of your redo at once. LGRW does this flushing continuously as we are processing at least:

  • Every three seconds.

  • When one third or one MB full.

  • Upon any transaction COMMIT.

So, even if we have a long running transaction, much of the buffered redo log it generates will have been flushed to disk, prior to committing. On the flip side of this however, is the fact that when we COMMIT, we must wait until all buffered redo we've generated that has not been written yet, is safely on disk. That is, our call to LGWR is a synchronous one. While LGWR may use asynchronous I/O to write in parallel to our log files, our transaction will wait for LGWR to complete all writes, and receive confirmation that the data exists on disk before returning.

In case you are not familiar with it, the SCN I refer to above is a simple timing mechanism Oracle uses to guarantee the ordering of transactions, and to enable recovery from failure. It is also used to guarantee read-consistency, and checkpointing in the database. Think of the SCN as a ticker; every time someone COMMITs, the SCN is incremented by one.

The last point to clarify from the above list, is what is meant by 'cleaned out' as related to database blocks. I said that we would revisit some of the blocks our transaction modified during the COMMIT process, and clean them out. This refers to the lock-related information we store in the database block header. In the section on Block Cleanout below, we will discuss this in more detail. Briefly, we are cleaning out our transaction information on the block, so the next person who visits the block won't have to. We are doing this in a way that need not generate redo log information, saving considerable work later.

In order to demonstrate that a COMMIT is a 'flat response time' operation, I'll generate varying amounts of redo, and time the INSERTs and COMMITs. In order to do this, we'll need a couple of GRANTs on some V$ tables (see Chapter 10, Tuning Strategies and Tools, for more information on these tables). Once we get those GRANTs, we'll set up a fairly large table to test with. In this example, I use the ALL_OBJECTS view to generate rows of data, and INSERT enough copies of these rows to give me about 100,000 rows to work with (your INSERTs may need to be executed more or less times to achieve the same row counts):

tkyte@TKYTE816> connect sys/change_on_install      sys@TKYTE816> grant select on v_$mystat to tkyte;      Grant succeeded.      sys@TKYTE816> grant select on v_$statname to tkyte;      Grant succeeded.      sys@TKYTE816> connect tkyte/tkyte      tkyte@TKYTE816> drop table t;      Table dropped.      tkyte@TKYTE816> create table t   2  as   3  select * from all_objects   4  /      Table created.      tkyte@TKYTE816> insert into t select * from t;      21979 rows created.      tkyte@TKYTE816> insert into t select * from t;      43958 rows created.      tkyte@TKYTE816> insert into t select * from t where rownum < 12000;      11999 rows created.      tkyte@TKYTE816> commit;      Commit complete.      tkyte@TKYTE816> create or replace procedure do_commit( p_rows in number )   2  as   3      l_start        number;   4      l_after_redo   number;   5      l_before_redo  number;   6  begin   7      select v$mystat.value into l_before_redo   8        from v$mystat, v$statname   9       where v$mystat.statistic# = v$statname.statistic#  10         and v$statname.name = 'redo size';  11  12      l_start := dbms_utility.get_time;  13      insert into t select * from t where rownum < p_rows;  14      dbms_output.put_line  15      ( sql%rowcount || ' rows created' );  16      dbms_output.put_line  17      ( 'Time to INSERT: ' ||  18         to_char( round( (dbms_utility.get_time-l_start)/100, 5 ),  19                 '999.99') ||  20        ' seconds' );  21  22      l_start := dbms_utility.get_time;  23      commit;  24      dbms_output.put_line  25      ( 'Time to COMMIT: ' ||  26         to_char( round( (dbms_utility.get_time-l_start)/100, 5 ),  27                 '999.99') ||  28        ' seconds' );  29  30      select v$mystat.value into l_after_redo  31        from v$mystat, v$statname  32       where v$mystat.statistic# = v$statname.statistic#  33         and v$statname.name = 'redo size';  34  35      dbms_output.put_line  36      ( 'Generated ' ||  37         to_char(l_after_redo-l_before_redo,'999,999,999,999') ||  38        ' bytes of redo' );  39      dbms_output.new_line;  40  end;  41  /      Procedure created. 

Now we are ready to see the effects of committing various sizes of transactions. We will call the above procedure, ask it to create new rows in varying sizes, and then report the results:

tkyte@TKYTE816> set serveroutput on format wrapped tkyte@TKYTE816> begin   2      for i in 1 .. 5   3      loop   4          do_commit( power(10,i) );   5      end loop;   6  end;   7  / 9 rows created Time to INSERT:     .06 seconds Time to COMMIT:     .00 seconds Generated            1,512 bytes of redo      99 rows created Time to INSERT:     .06 seconds Time to COMMIT:     .00 seconds Generated           11,908 bytes of redo      999 rows created Time to INSERT:     .05 seconds Time to COMMIT:     .00 seconds Generated          115,924 bytes of redo      9999 rows created Time to INSERT:     .46 seconds Time to COMMIT:     .00 seconds Generated        1,103,524 bytes of redo      99999 rows created Time to INSERT:   16.36 seconds Time to COMMIT:     .00 seconds Generated       11,220,656 bytes of redo           PL/SQL procedure successfully completed.      tkyte@TKYTE816> show parameter log_buffer      NAME                                 TYPE    VALUE ------------------------------------ ------- ------------------------------ log_buffer                           integer 512000 

As you can see, as we generate varying amount of redo from 1,512 bytes to 11,220,656 bytes, the time to COMMIT is not measurable using a timer with a one hundredth of a second resolution. I timed the INSERTs specifically to demonstrate that the timer logic actually 'works'. If you do something that takes a measurable amount of time, it'll report it - the COMMITs just happened too fast. As we were processing, and generating the redo log, LGWR was constantly flushing our buffered redo information to disk in the background as we went along. So, when we generated eleven MB of redo log information, LGWR was busy flushing every 170 KB or so (a third of 512,000 bytes). When it came to the COMMIT, there wasn't much left to do - not much more than when we created nine rows of data. You should expect to see similar (but not exactly the same) results, regardless of the amount of redo generated.

What Does a ROLLBACK Do?

Now, if we change the COMMIT to ROLLBACK, we can expect a totally different result. The time to roll back will definitely be a function of the amount of data modified. I changed the DO_COMMIT routine we developed in the What Does a COMMIT Do? section to perform a ROLLBACK instead (simply change the COMMIT on line 23 to ROLLBACK) and the timings are very different. For example:

9 rows created Time to INSERT:     .06 seconds Time to ROLLBACK:     .02 seconds Generated            1,648 bytes of redo      99 rows created Time to INSERT:     .04 seconds Time to ROLLBACK:     .00 seconds Generated           12,728 bytes of redo      999 rows created Time to INSERT:     .04 seconds Time to ROLLBACK:     .01 seconds Generated          122,852 bytes of redo      9999 rows created Time to INSERT:     .94 seconds Time to ROLLBACK:     .08 seconds Generated        1,170,112 bytes of redo      99999 rows created Time to INSERT:    8.08 seconds Time to ROLLBACK:    4.81 seconds Generated       11,842,168 bytes of redo           PL/SQL procedure successfully completed. 

This is to be expected, as a ROLLBACK has to physically undo the work we've done. Similar to a COMMIT, there is a series of operations that must be performed. Before we even get to the ROLLBACK, the database has already done a lot of work. To recap, the following would have happened:

When we ROLLBACK:

A COMMIT on the other hand just flushes any remaining data in the redo log buffers. It does very little work compared to a ROLLBACK. The point here is that you don't want to roll back unless you have to. It is expensive since you spend a lot of time doing the work, and you'll also spend a lot of time undoing the work. Don't do work unless you are sure you are going to want to COMMIT it. This sounds like common sense; of course, I wouldn't do all of the work unless I wanted to COMMIT it. I have seen many times however, where a developer will utilize a 'real' table as a temporary table, fill it up with data, and then roll back. Below, we'll talk about true temporary tables, and how to avoid this issue.

How Much Redo Am I Generating?

As a developer, you will find that it can be relevant to be able to measure how much redo your operations generate. The more redo you generate, the longer your operations will take, and the slower the entire system will be. You are not just affecting your session, but every session. Redo management is a point of serialization within the database - eventually all transactions end up at LGWR, asking it to manage their redo and COMMIT their transaction. The more it has to do, the slower the system will be. By seeing how much redo an operation tends to generate, and testing more than one approach to a problem, you can find the best way to do things.

It is pretty straightforward to see how much redo I am generating, as shown above. I used the dynamic performance view V$MYSTAT, which has just my session's statistics in it, joined to V$STATNAME. I retrieved the value of statistic named redo size. I didn't have to guess at this name, because I used the view V$STATNAME to find it:

ops$tkyte@DEV816> select * from v$statname   2  where name like 'redo%';      STATISTIC# NAME                                CLASS ---------- ------------------------------ ----------         61 redo synch writes                       8         62 redo synch time                         8         98 redo entries                            2         99 redo size                               2        100 redo buffer allocation retries          2        101 redo wastage                            2        102 redo writer latching time               2        103 redo writes                             2        104 redo blocks written                     2        105 redo write time                         2        106 redo log space requests                 2        107 redo log space wait time                2        108 redo log switch interrupts              2        109 redo ordering marks                     2      14 rows selected. 

Now we are ready to investigate how we might go about determining the amount of redo a given transaction would generate. It is straightforward to estimate how much redo will be generated if you know how much data you will be modifying. Below, I will create a table whose row size is about 2010 bytes, plus or minus a couple of bytes. Since the CHAR data type always consumes the maximum amount of storage, this row is 2000 bytes for the CHAR, seven bytes for the DATE, and three bytes for the number - about 2010 bytes, plus some row overhead:

tkyte@TKYTE816> create table t ( x int, y char(2000), z date );      Table created. 

We'll take a look at what it takes to INSERT, then UPDATE, and then DELETE one, ten, and lots of these rows. We will also see if there is any measurable difference between updating a set of rows in bulk, versus updating each row one by one, based on the amount of redo generated. We already know that updating row by row is slower than using a single UPDATE statement.

To measure the redo generated, we will use either the SQL*PLUS AUTOTRACE facility, or a direct query against a view of V$MYSTAT/V$STATNAME that shows our session's redo size:

tkyte@TKYTE816> create or replace view redo_size   2  as   3  select value   4    from v$mystat, v$statname   5   where v$mystat.statistic# = v$statname.statistic#   6     and v$statname.name = 'redo size';      View created. 

For the statements that AUTOTRACE will trace (INSERTs, UPDATEs, and DELETEs), we'll use it. For our PL/SQL blocks, we'll resort to V$MYSTAT/V$STATNAME instead, since AUTOTRACE will not generate information for these statements.

Now, for the process to exercise redo generation, we will use the table T above which has a fairly fixed row size of 2010 bytes if all of the columns are not Null. We will do various operations and measure the redo generated for each. What we will do is INSERT a single row, then ten rows with a single statement, then 200 rows with a single statement, and then 200 rows one at a time. We will do similar operations for UPDATE and DELETE as well.

The code for this example follows. Instead of the usual cut and paste directly from SQL*PLUS, we'll just look at the statements that were used, and then look at a table that summarizes the results:

set autotrace traceonly statistics insert into t values ( 1, user, sysdate );      insert into t select object_id, object_name, created   from all_objects  where rownum <= 10;      insert into t select object_id, object_name, created   from all_objects  where rownum <= 200 /      declare   l_redo_size number;   l_cnt       number := 0; begin    select value into l_redo_size from redo_size;    for x in ( select * from all_objects where rownum <= 200 )    loop       insert into t values       ( x.object_id, x.object_name, x.created );       l_cnt := l_cnt+1;    end loop;    select value-l_redo_size into l_redo_size from redo_size;    dbms_output.put_line( 'redo size = ' || l_redo_size ||                          ' rows = ' || l_cnt ); end; / 

The above code fragment does our INSERTs as described - 1, 10, 200 at a time, and then 200 individual INSERTs. Next, we'll do the UPDATEs:

update t set y=lower(y) where rownum = 1;      update t set y=lower(y) where rownum <= 10;      update t set y=lower(y) where rownum <= 200;      declare   l_redo_size number;   l_cnt       number := 0; begin   select value into l_redo_size from redo_size;   for x in ( select rowid r from t where rownum <= 200 )   loop      update t set y=lower(y) where rowid = x.r;      l_cnt := l_cnt+1;   end loop;   select value-l_redo_size into l_redo_size from redo_size;   dbms_output.put_line( 'redo size = ' || l_redo_size ||                         ' rows = ' || l_cnt ); end; / 

and then the DELETEs:

delete from t where rownum = 1;      delete from t where rownum <= 10;      delete from t where rownum <= 200;      declare   l_redo_size number;   l_cnt       number := 0; begin    select value into l_redo_size from redo_size;    for x in ( select rowid r from t ) loop       delete from t where rowid = x.r;       l_cnt := l_cnt+1;    end loop;    select value-l_redo_size into l_redo_size from redo_size;    dbms_output.put_line( 'redo size = ' || l_redo_size ||                          ' rows = ' || l_cnt ); end; / 

Here are the end results:

Operation

Rows Affected

Total Redo

Average per Row

INSERT one row

1

2,679

2,679

INSERT 10 rows in one statement

10

22,260

2,226

INSERT 200 rows in one statement

200

442,784

2,213

INSERT 200 rows, one at a time

200

464,224

2,321

UPDATE one row

1

4,228

4,228

UPDATE 10 rows in one statement

10

42,520

4,252

UPDATE 200 rows in one statement

200

849,600

4,248

UPDATE 200 rows individually

200

849,700

4,248

DELETE one row

1

2,236

2,236

DELETE 10 rows in bulk

10

23,688

2,369

DELETE 200 rows in bulk

200

469,152

2,345

DELETE 200 rows one at a time

200

469,212

2,346

   

The interesting thing to note is that, in general, whether you UPDATE 200 rows with one statement or 200 statements, the same amount of redo is generated. The same is true for the DELETEs - one statement or 200 statements, the result is pretty much the same. An INSERT behaves a little differently. It generates slightly more redo for single row inserts which is reasonable, given that it must organize data on the block differently when inserting one at a time, versus many at a time (it does slightly more work).

As you can see, the redo generated is a function of the amount of data modified. If we INSERT 2000 byte rows, we generate a little more than 2000 bytes per row. When we UPDATE, we generate double the amount of redo (we log the data and rollback as you recall, so that makes sense). A DELETE is similar to an INSERT in size. The entire row is recorded in the rollback segment and this is logged, as well as the block changes themselves, accounting for the small disparity. So, as long as you understand the amount of data you will be modifying, and to some extent, how you modify it, determining the amount of redo is straightforward.

This is not a case study that proves that single row processing is as efficient as set processing. It only shows that the amount of redo generated is the same. We have seen in other sections that procedurally processing a row at a time is never as efficient as doing the set operation itself. Additionally, if you are tempted to throw a COMMIT in the loop, as many people do in the mistaken belief it will conserve some resource, you'll only compound the problem. Now that we know how to measure the redo we generate, we can see the effect of this bad idea clearly. We'll use the same example schema from above and measure what happens when we throw a COMMIT in the loop:

tkyte@TKYTE816> declare   2    l_redo_size number;   3    l_cnt       number := 200;   4    procedure report   5    is   6    begin   7       select value-l_redo_size into l_redo_size from redo_size;   8       dbms_output.put_line( 'redo size = ' || l_redo_size ||   9                             ' rows = ' || l_cnt || ' ' ||  10                             to_char(l_redo_size/l_cnt,'99,999.9') ||  11                             ' bytes/row' );  12     end;  13  begin  14    select value into l_redo_size from redo_size;  15    for x in ( select object_id, object_name, created  16                 from all_objects  17                where rownum <= l_cnt )  18    loop  19          insert into t values  20          ( x.object_id, x.object_name, x.created );  21          commit;  22    end loop;  23    report;  24  25    select value into l_redo_size from redo_size;  26    for x in ( select rowid rid from t )  27    loop  28        update t set y = lower(y) where rowid = x.rid;  29        commit;  30        end loop;  31    report;  32  33    select value into l_redo_size from redo_size;  34    for x in ( select rowid rid from t )  35    loop  36        delete from t where rowid = x.rid;  37        commit;  38    end loop;  39    report;  40  end;  41  / redo size = 530396 rows = 200   2,652.0 bytes/row redo size = 956660 rows = 200   4,783.3 bytes/row redo size = 537132 rows = 200   2,685.7 bytes/row      PL/SQL procedure successfully completed. 

As you can see, committing each row increased the amount of redo generated measurably, (the table below summarizes this). There are other performance-related issues as to why you should not COMMIT every row (or possibly even groups of rows). We saw concrete proof of this above, where a COMMIT for each row took three times as long as committing when the transaction was complete. There is the overhead of calling into the database kernel with each individual statement, there is unnecessary latching and contention for shared resources for each individual statement. The bottom line is that if you can do it in a single SQL statement - do it. Additionally make sure you COMMIT when the transaction is complete, never before.

Operation

Rows affected

Total redo (no COMMITs)

Total redo (with COMMITs)

% increase

INSERT 200 rows

200

442,784

530,396

20%

UPDATE 200 rows

200

849,600

956,660

13%

DELETE 200 rows

200

469,152

537,132

14%

The method we outline above is useful in general for seeing the side effects of various other options. One question that comes up frequently is, other than the fact that you can modify the values of a row in a BEFORE trigger, are there any other differences? Well, as it turns out, yes there is. A BEFORE trigger tends to add additional redo information, even if it does not modify any of the values in the row. In fact, this is an interesting case study and using the techniques above we'll discover that:

In order to perform this test, we'll use the table T from above:

create table t ( x int, y char(N), z date ); 

but we'll create it with varying sizes for N. In this example, we'll use N = 30, 100, 500, 1000, and 2000 to achieve rows of varying widths. I used a simple log table to capture the results of my many runs:

create table log ( what varchar2(15), -- will be no trigger, after or before                    op varchar2(10),   -- will be insert/update or delete                    rowsize int,       -- will be the size of Y                    redo_size int,     -- will be the redo generated                    rowcnt int );      -- will be the count of rows affected 

After we run our test for various sized Y columns, we'll analyze the results. I used this stored procedure to generate my transactions, and record the redo generated. The subprocedure REPORT is a local procedure (only visible in the DO_WORK procedure), and it simply reports out on screen what happened, and captures the findings into our LOG table. The main body of the procedure does the real transactions we are monitoring. They all start by capturing our session's current redo size, performing some work, committing, and then generating the report:

tkyte@TKYTE816> create or replace procedure do_work( p_what in varchar2 )   2  as   3    l_redo_size number;   4    l_cnt       number := 200;   5   6    procedure report( l_op in varchar2 )   7    is   8    begin   9       select value-l_redo_size into l_redo_size from redo_size;  10       dbms_output.put_line(l_op || ' redo size = ' || l_redo_size ||  11                             ' rows = ' || l_cnt || ' ' ||  12                             to_char(l_redo_size/l_cnt,'99,999.9') ||  13                             ' bytes/row' );  14      insert into log  15      select p_what, l_op, data_length, l_redo_size, l_cnt  16        from user_tab_columns  17       where table_name = 'T'  18         and column_name = 'Y';  19     end;  20  begin  21    select value into l_redo_size from redo_size;  22    insert into t  23    select object_id, object_name, created  24      from all_objects  25     where rownum <= l_cnt;  26    l_cnt := sql%rowcount;  27    commit;  28    report('insert');  29  30    select value into l_redo_size from redo_size;  31    update t set y=lower(y);  32    l_cnt := sql%rowcount;  33    commit;  34    report('update');  35  36    select value into l_redo_size from redo_size;  37    delete from t;  38    l_cnt := sql%rowcount;  39    commit;  40    report('delete');  41  end;  42  /      Procedure created. 

Now, once I have this in place, I drop and create the table T, changing the size of the column Y. I then run the following script to test the various scenarios (no TRIGGER, before TRIGGER, and after TRIGGER):

tkyte@TKYTE816> truncate table t;      Table truncated.      tkyte@TKYTE816> exec do_work( 'no trigger' ); insert redo size = 443280 rows = 200   2,216.4 bytes/row update redo size = 853968 rows = 200   4,269.8 bytes/row delete redo size = 473620 rows = 200   2,368.1 bytes/row      PL/SQL procedure successfully completed.      tkyte@TKYTE816> create or replace trigger before_insert_update_delete   2  before insert or update or delete on T for each row   3  begin   4          null;   5  end;   6  /      Trigger created.      tkyte@TKYTE816> truncate table t;      Table truncated.      tkyte@TKYTE816> exec do_work( 'before trigger' ); insert redo size = 465640 rows = 200   2,328.2 bytes/row update redo size = 891628 rows = 200   4,458.1 bytes/row delete redo size = 473520 rows = 200   2,367.6 bytes/row      PL/SQL procedure successfully completed.      tkyte@TKYTE816> drop trigger before_insert_update_delete;      Trigger dropped.      tkyte@TKYTE816> create or replace trigger after_insert_update_delete   2  after insert or update or delete on T   3  for each row   4  begin   5          null;   6  end;   7  /      Trigger created.      tkyte@TKYTE816> truncate table t;      Table truncated.      tkyte@TKYTE816> exec do_work( 'after trigger' ); insert redo size = 465600 rows = 200   2,328.0 bytes/row update redo size = 854028 rows = 200   4,270.1 bytes/row delete redo size = 473580 rows = 200   2,367.9 bytes/row      PL/SQL procedure successfully completed. 

The above output was from a run where the size of Y was 2000 bytes. After all of the runs were complete, I was able to query the log table and see:

tkyte@TKYTE816> break on op skip 1 tkyte@TKYTE816> set numformat 999,999      tkyte@TKYTE816> select op, rowsize, no_trig, before_trig-no_trig, after_trig- no_trig   2  from ( select op, rowsize,   3             sum(decode( what, 'no trigger', redo_size/rowcnt,0 ) )                                                                 no_trig,   4             sum(decode( what, 'before trigger', redo_size/rowcnt, 0 ) )                                                                 before_trig,   5             sum(decode( what, 'after trigger', redo_size/rowcnt, 0 ) )                                                                 after_trig   6           from log   7          group by op, rowsize   8       )   9   order by op, rowsize  10  /      OP          ROWSIZE  NO_TRIG BEFORE_TRIG-NO_TRIG AFTER_TRIG-NO_TRIG ---------- -------- -------- ------------------- ------------------ delete           30      272                   0                  0                 100      344                  -0                 -0                 500      765                  -0                 -0               1,000    1,293                  -1                 -0               2,000    2,368                  -1                 -0      insert           30       60                 213                213                 100      136                 208                208                 500      574                 184                184               1,000    1,113                 162                162               2,000    2,216                 112                112      update           30      294                 189                  0                 100      431                 188                  0                 500    1,238                 188                 -0               1,000    2,246                 188                 -0               2,000    4,270                 188                  0      15 rows selected. 
Note 

If you are curious about this query and how I pivoted the result set, see Chapter 12 on Analytic Functions where I discuss pivoting of result sets in detail.

What the inner most query generated was a resultset that computed the average redo bytes generated per row for each of the three test cases. The outer query simply displays the average bytes per row for the case where no trigger was in place, and then the subsequent two columns show the difference between the other cases, and the case with no trigger in place. So, we can see that for the DELETE cases, there was no measurable difference in the amount of redo generated per row at any time. Looking at the INSERT case however, we can see that there was an overhead of 213 to 112 bytes per row, regardless of the trigger type used (before or after). The bigger the row, the less the overhead for some reason (I did not investigate why this is so, but just that this is the case). Lastly, for the UPDATE case we see two things. First, the amount of redo generated is constant regardless of the redo size - the overhead for UPDATEs is fixed. Secondly, we can see that an AFTER trigger is much more efficient for UPDATEs, as it does not affect the redo generation at all. For this reason, you can develop a rule of thumb; use AFTER triggers whenever possible for UPDATEs. If, and only if, you need functionality available in the BEFORE trigger should you actually use one.

So, now you know how to estimate the amount of redo, which every developer should be able to do. You can:

In most cases, this will be a good estimate. The doubling on the UPDATEs is a guess - it really depends on how you modify the data. The doubling assumes you take a row of X bytes, and UPDATE it to be a row of X bytes. If you take a small row and make it big, you will not double the value (it will behave more like an INSERT). If you take a big row and make it small, you will not double the value (it will behave like a DELETE). The doubling is a 'worst case' number, as there are various options and features that will impact this, for example the existence of indexes (or lack thereof as in my case) will contribute to the bottom line. The amount of work that must be done in order to maintain the index structure may vary from UPDATE to UPDATE, and so on. Side effects from triggers have to be taken into consideration (in addition to the fixed overhead described above). Implicit operations performed on your behalf, such as an ON DELETE CASCADE setting on a foreign key, must be considered as well. This will allow you to estimate the amount of redo for sizing/performance purposes. Only real-world testing will tell you for sure. Given the above script, you can see how to measure this for yourself, given any of your objects and transactions.

Can I Turn Off Redo Log Generation?

This question is often asked. The simple short answer is 'no', since redo logging is crucial for the database; it is not overhead, it is not a waste. You do need it, regardless of whether you believe you do or not. It is a fact of life, and it is the way the database works. However, that said, there are some operations that can be done without generating redo log in some cases.

Some SQL statements and operations support the use of a NOLOGGING clause. This does not mean that all operations against the object will be performed without generating redo log, just that some very specific operations will generate significantly less redo then normal. Note that I said 'significantly less', not 'none'. All operations will generate some redo - all data dictionary operations will be logged regardless of the logging mode. The amount of redo generated can be significantly less. For example, I ran the following in a database running in ARCHIVELOG mode. If you test on a NOARCHIVELOG mode database, you will not see any differences. The CREATE TABLE will not be logged, with the exception of the data dictionary modifications in a NOARCHIVELOG mode database. Oracle 7.3 users however, will see the difference, as this optimization was not present in that database. They will have to use UNRECOVERABLE instead of the NOLOGGING keyword as well (NOLOGGING used to be UNRECOVERABLE in prior releases of Oracle). If you would like to see the difference on a NOARCHIVELOG mode database, you can replace the DROP TABLE and CREATE TABLE with a DROP INDEX and CREATE INDEX statement on some table. These operations are logged by default, regardless of the mode in which the database is running. This also points out a valuable tip - test your system in the mode it will be run in production, as the behavior may be different. Your production system will be running in ARCHIVELOG mode; if you perform lots of operations that generate redo in this mode, but not in NOARCHIVELOG mode, you'll want to discover this during testing, not during rollout to the users! Now for the example of the NOLOGGING clause:

tkyte@TKYTE816> column value new_value old_value tkyte@TKYTE816> select value from redo_size;           VALUE ----------    5195512      tkyte@TKYTE816> create table t   2  as   3  select * from all_objects   4  /      Table created.      tkyte@TKYTE816> select value-&old_value REDO_GENERATED from redo_size; old   1: select value-&old_value REDO_GENERATED from redo_size new   1: select value-   5195512 REDO_GENERATED from redo_size      REDO_GENERATED --------------        2515860 

Here, over 2.5 MB of redo is generated in my database.

tkyte@TKYTE816> drop table t;      Table dropped.      tkyte@TKYTE816> select value from redo_size;           VALUE ----------    7741248      tkyte@TKYTE816> create table t   2  NOLOGGING   3  as   4  select * from all_objects   5  /      Table created.      tkyte@TKYTE816> select value-&old_value REDO_GENERATED from redo_size; old   1: select value-&old_value REDO_GENERATED from redo_size new   1: select value-   7741248 REDO_GENERATED from redo_size      REDO_GENERATED --------------          43264 

This time, there is only 50 KB of redo generated.

As you can see, this makes a tremendous difference; 2.5 MB of redo versus 50 KB. The 2.5 MB is the actual table data itself - it was written directly to disk, with no redo log generated for it. Of course, it is now obvious that we will do everything we can with NOLOGGING, right? In fact the answer is a resounding no. You must use this very carefully, and only after discussing the issues with the person in charge of backup and recovery. Let's say you create this table and it is now part of your application (for example, you used a CREATE TABLE AS SELECT NOLOGGING as part of an upgrade script). Your users modify this table over the course of the day. That night, the disk that the table is on fails. No problem the DBA says - we are running in ARCHIVELOG mode, we can perform media recovery. The problem is however, that the initially created table, since it was not logged, is not recoverable from the archived redo log. This table is unrecoverable and this brings out the very most important point about NOLOGGING operations - they must be coordinated with your DBA and the system as a whole. If you use them, and others are not aware of the fact, you may compromise the ability of your DBA to recover your database fully after a media failure. They must be used judiciously and carefully.

The important things to note with NOLOGGING operations are:

There are two ways to use the NOLOGGING option. You have already seen one method, by embedding the keyword NOLOGGING in the SQL command itself at the appropriate location. The other method allows operations to be performed implicitly in a NOLOGGING mode. For example, I can alter an index to be NOLOGGING by default. This means that subsequent direct path loads and direct path inserts performed which affect this index, will not be logged (the index will not generate redo - other indexes and the table itself might but this index will not).

The operations that may be performed in a NOLOGGING mode are:

Used appropriately on an ARCHIVELOG mode database, NOLOGGING can speed up many operations by dramatically reducing the amount of redo log generated. Suppose you have a table you need to move from one tablespace to another. You can schedule this operation to take place immediately before a backup occurs - you would ALTER the table to be NOLOGGING, move it, rebuild the indexes (without logging as well), and then ALTER the table back to logging mode. Now, an operation that might have take X hours can happen in X/2 hours perhaps. The appropriate use of this feature includes involvement of the DBA, or whoever is responsible for database backup and recovery. If they are not aware of the use of this feature and a media failure occurs, you may lose data. This is something to seriously consider.

Cannot Allocate a New Log?

I see this happen all of the time, though not on my database of course! You are getting warning messages to this effect (this will be found in your alert.log on your server):

Sun Feb 25 10:59:55 2001 Thread 1 cannot allocate new log, sequence 326 Checkpoint not complete 

It might say Archival required instead of Checkpoint not complete, but the effect is pretty much the same. This is really something the DBA should be looking out for however. In the event that they do not do this, it is something you need to look for yourself. This message will be written to the alert.log on the server whenever the database attempts to reuse an online redo log file, and finds that it cannot. This will happen when DBWR has not yet finished checkpointing the data protected by the redo log, or ARCH has not finished copying the redo log file to the archive destination. If DBWR or ARCH do not mean anything to you, please review Chapter 2 on Architecture, for more information. At this point in time, the database effectively halts as far as the end user is concerned. It stops cold. DBWR or ARCH will be given priority to flush the blocks to disk. Upon completion of the checkpoint or archival, everything goes back to normal. The reason the database suspends user activity is that there is simply no place to record the changes they are making. Oracle is attempting to reuse an online redo log file, but because either the file is needed to recover the database in the event of a failure (Checkpoint not complete), or the archiver has not yet finished copying it (Archival required). Oracle must wait (and our end users will wait) until the redo log file can safely be reused.

If you see that your sessions spend a lot of time waiting on a 'log file switch', 'log buffer space', or 'log file switch checkpoint or archival incomplete', then you are most likely hitting this (refer to Chapter 10, Tuning Strategies and Tools, for how to see what wait events your session is suffering from). You will notice it during prolonged periods of database modifications if your log files are sized incorrectly, or because DBWR and ARCH need to be tuned by the DBA or System Administrator. I frequently see this with the 'starter' database that has not been customized. The 'starter' database typically sizes the redo logs far too small for any sizable amount of work (including the initial database build of the data dictionary itself). As soon as you start loading up the database, you will notice that the first 1000 rows go fast, and then things start going in spurts; 1000 go fast, then hang, then fast, then hang, and so on. These are the indications you are hitting this condition.

There are a couple of things you can do to solve this:

Block Cleanout

If you recall earlier, Chapter 3 on Locking and Concurrency, when we talked about data locks and how they were managed, I described how they are actually attributes of the data, stored on the block header. A side effect of this is that the next time that block is accessed, we may have to 'clean it out', in other words, remove the transaction information. This action generates redo and causes the block to become 'dirty' if it wasn't already. What this means is that a simple SELECT may generate redo, and may cause lots of blocks to be written to disk with the next checkpoint. Under most normal circumstances however, this will not happen. If you have mostly small to medium-sized transactions (OLTP), or you are a data warehouse that analyzes tables after bulk operations, you'll find the blocks are generally 'cleaned' for you. If you recall from the earlier section What Does a COMMIT Do? one of the steps of COMMIT-time processing is to revisit our blocks if they are still in the SGA, if they are accessible (no one else is modifying them), and then clean them out. This activity is known as a commit clean out. Our transaction cleans out the block enough so that a SELECT (read) will not have to clean it out. Only an UPDATE of this block would truly clean out our residual transaction information, and since this is already generating redo, the cleanout is not noticeable.

We can force a cleanout to happen to see the side effects by understanding how the commit cleanout functions. Oracle will allocate lists of blocks we have modified in a commit list associated with our transaction. Each of these lists is 20 blocks long, and Oracle will allocate as many of these lists as it needs up to a point. If the sum of the blocks we modify exceeds 10 percent of the block buffer cache size, Oracle will stop allocating new lists for us. For example, if our block buffer cache is set to 3,000, Oracle will maintain a list of up to 300 blocks (10 percent of 3,000) for us. Upon COMMIT, Oracle will process each of these lists of 20 block pointers, and if the block is still available, it will perform a fast cleanout. So, as long as the number of blocks we modify does not exceed 10 percent of the number of blocks in the cache and our blocks are still in the cache and available to us, Oracle will clean them out upon COMMIT. Otherwise, it just skips them (does not clean them out). Given this understanding, we can set up artificial conditions to see how this works. I set my DB_BLOCK_BUFFERS to a low value of 300. Then, I created a table such that a row fits on exactly one block - we'll never have two rows per block. Then, I fill this table up with 499 rows and COMMIT. We'll measure the amount of redo I have generated so far, run a SELECT that will visit each block, and then measure the amount of redo that SELECT generated.

Surprising to many people, the SELECT will have generated redo. Not only that, but it will also have 'dirtied' these modified blocks, causing DBWR to write them again. This is due to the block cleanout. Next, I'll run the SELECT once again and we'll see that no redo is generated. This is expected as the blocks are all 'clean' at this point.

tkyte@TKYTE816> create table t   2  ( x char(2000) default 'x',   3    y char(2000) default 'y',   4    z char(2000) default 'z' )   5  /      Table created.      tkyte@TKYTE816> insert into t   2  select 'x','y','z'   3  from all_objects where rownum < 500   4  /      499 rows created.      tkyte@TKYTE816> commit;      Commit complete. 

So, this is our table with one row per block (in my 8 KB block size database). Now we will measure the amount of redo generated during the read of the data:

tkyte@TKYTE816> column value new_value old_value tkyte@TKYTE816> select * from redo_size;           VALUE ----------    3250592      tkyte@TKYTE816> select *   2    from t   3   where x = y;      no rows selected      tkyte@TKYTE816> select value-&old_value  REDO_GENERATED from redo_size; old   1: select value-&old_value  REDO_GENERATED from redo_size new   1: select value-   3250592  REDO_GENERATED from redo_size      REDO_GENERATED --------------          29940      tkyte@TKYTE816> commit;      Commit complete. 

So, this SELECT generated about 30 KB of redo during it's processing. This represents the block headers it modified during the full scan of T. DBWR will be writing these modified blocks back out to disk at some point in the future. Now, if we run the query again:

tkyte@TKYTE816> select value from redo_size;           VALUE ----------    3280532      tkyte@TKYTE816> select *   2    from t   3   where x = y;      no rows selected      tkyte@TKYTE816> tkyte@TKYTE816> select value-&old_value  REDO_GENERATED from redo_size; old   1: select value-&old_value  REDO_GENERATED from redo_size new   1: select value-   3280532  REDO_GENERATED from redo_size      REDO_GENERATED --------------              0      Commit complete. 

we see that no redo is generated - the blocks are all clean.

If we were to re-run the above example with block buffers set to 6,000 instead of 300, we'll find that we generate no redo on any of the SELECTs - we will have dirtied no blocks during either of our SELECT statements. This is because the 499 blocks we modified fit comfortably into 10 percent of our block buffer cache, and we are the only user. There is no one else is mucking around with the data, and no one else is causing our data to be flushed to disk or accessing those blocks. In a live system, it will be normal for at least some of the blocks to not be cleaned out sometimes.

Where this behavior will most affect you, is after a large INSERT (as demonstrated above), UPDATE, or DELETE - one that affects many blocks in the database (anything more than 10 percent of the size of the cache will definitely do it). You will notice that the first query to touch the block after this will generate a little redo and dirty the block, possibly causing it to be rewritten if DBWR had already flushed it, or the instance had been shutdown, clearing out the buffer cache all together. There is not too much you can do about it. It is normal and to be expected. If Oracle did not do this deferred cleanout of a block, a COMMIT could take as long to process as the transaction itself. The COMMIT would have to revisit each and every block, possibly reading them in from disk again (they could have been flushed). If you are not aware of block cleanouts and how they work, it will be one of those mysterious things that just seem to happen for no reason. For example, you UPDATE a lot of data and COMMIT. Now you run a query against that data to verify the results. The query appears to generate tons of write I/O and redo. Seems impossible if you are unaware of this - it was to me the first time I saw it. You go and get someone to observe this with you but it is not reproducible, as the blocks are now 'clean' on the second query. You simply write it off as one of those 'mysteries'.

In an OLTP system, you will probably never see this happening. All of the transactions are short and sweet. Modify a couple of blocks and they all get cleaned out. In a warehouse where you make massive UPDATEs to the data after a load, block cleanouts may be a factor in your design. Some operations will create data on 'clean' blocks. For example, CREATE TABLE AS SELECT, direct path loaded data, direct path inserted data; they will all create 'clean' blocks. An UPDATE, normal INSERT, or DELETE, may create blocks that need to be cleaned with the first read. This could really affect you if your processing consists of:

You will have to realize that the first query to touch the data will incur some additional processing if the block needs to be cleaned. Realizing this, you yourself should 'touch' the data after the UPDATE. You just loaded or modified a ton of data, you need to analyze it at the very least. Perhaps you need to run some reports yourself to validate the load. This will clean the block out, and make it so the next query doesn't have to do this. Better yet, since you just bulk loaded the data, you now need to refresh the statistics anyway. Running the ANALYZE command to update statistics will clean out all of the blocks as well.

Log Contention

This, like the Cannot Allocate New Log message, is something the DBA must fix, typically in conjunction with the system administrator. However, it is something you might detect if they aren't watching close enough. When we discuss the important V$ dynamic performance views in Chapter 10 on Tuning Strategies and Tools, we will look at how to see what exactly we are waiting on. Many times, the biggest wait event will be 'log file sync'. If it is, you are experiencing contention on the redo logs; they are not going fast enough. This can happen for many reasons. One application reason (one that the DBA cannot fix, but which the developer must fix) is that you are committing too frequently - committing inside of a loop doing INSERTs for example. Here, you have introduced the COMMIT in the misguided hope of reducing your need for resources. Committing too frequently, aside from being a bad programming practice, is a surefire way to introduce lots of log file waits, as we must wait for LGWR to flush our redo log buffers to disk. Normally, LGWR can do this in the background and we don't have to wait. When we COMMIT more often than we should (or have to), we wait more than we should. Assuming all of your transactions are correctly sized (you are not committing more frequently than your business rules dictate), the most common causes for log file waits that I've seen are:

If at all possible, you really want at least five dedicated devices for logging, and optimally six in order to mirror your archives as well. In the days of 9, 20, 36 GB, and larger disks, this is getting harder, but if you can set aside four of the smallest, fastest disks you can find and one or two big ones, you can affect LGWR and ARCH in a positive fashion. To lay out the disks, we would break them into three groups:

You would place redo log group 1 with members A and B onto Group 1. You would place redo log group 2 with members C and D onto Group 2. If you have groups 3, 4, and so on, they'll go onto the odd and even groups of disks. The effect of this is that LGWR, when using group 1, will write to disk 1 and disk 3 simultaneously. When this group fills up, LGWR will move to disks 2 and 4. When they fill up it will go back to disks 1 and 3. Meanwhile, ARCH will be processing the full online redo logs, and writing them to Group 3, the big disk. The net effect is neither ARCH nor LGWR is ever reading a disk being written to, or writing to a disk being read from - there is no contention:

click to expand

So, when LGWR is writing Group 1, ARCH is reading Group 2, and writing to the archive disks. When LGWR is writing Group 2, ARCH is reading Group 1, and writing to the archive disks. In this fashion, each of LGWR and ARCH will have their own dedicated devices, and will not be contending with anyone, not even each other.

Log files are the one set of Oracle files that benefit the most from the use of RAW disks. If there is one set of files you might consider for RAW, log files would be the ones. There is much back and forth discussion on the pros and cons of using RAW versus cooked file systems. As this is not a book on DBA/SA tasks, we won't get into them. I'll just mention that if you are going to use them anywhere, log files would be the best candidates. You never backup online redo log files, so the fact that they are on RAW partitions versus a cooked file system won't impact any backup scripts you might have. ARCH will always turn the RAW logs into cooked file system files (you cannot use a RAW device to archive to), hence the 'mystique' of RAW devices is very much minimized in this case.

Temporary Tables and Redo/Rollback

Temporary tables are a new feature of Oracle 8.1.5. As such, there is an amount of confusion surrounding them, in particular in the area of logging. In Chapter 6 on the different types of database Tables available to you, we will cover how and why you might use temporary tables. In this section, we will cover only the question of, 'How do temporary tables work with respect to logging?'

Temporary tables generate no redo for their blocks. Therefore, an operation on a temporary table is not 'recoverable'. When you modify a block in a temporary table, no record of this change will be made in the redo log files. However, temporary tables do generate rollback, and the rollback is logged. Hence, temporary tables will generate some redo. At first glance, it doesn't seem to make total sense: why would they need to generate rollback? This is because you can rollback to a savepoint within a transaction. You might erase the last 50 INSERTs into a temporary table, but not the first 50. Temporary tables can have constraints and everything else a normal table can have. They might fail a statement on the 500th row of a 500 row INSERT, necessitating a rollback of that statement. Since temporary tables behave in general just like a 'normal' table, temporary tables must generate rollback. Since rollback data must be logged, they will generate some redo log for the rollback they generate.

This is not nearly as ominous as it seems. The primary SQL statements used against temporary tables are INSERTs and SELECTs. Fortunately, INSERTs generate very little rollback (you need to restore the block to 'nothing', and it doesn't take very much room to store 'nothing'), and SELECTs generate no rollback. Hence, if you use temporary tables for INSERTs and SELECTs exclusively, this section means nothing to you. It is only if you UPDATE or DELETE you might be concerned about this.

I set up a small test to demonstrate the amount of redo generated, an indication therefore, of the amount of rollback generated for temporary tables, since only the rollback is logged for them. In order to do this we will take an identically configured 'permanent' table and 'temporary' table, and then perform the same operations on them, measuring the amount of redo generated each time. The tables I used were simply:

tkyte@TKYTE816> create table perm   2  ( x char(2000) default 'x',   3    y char(2000) default 'y',   4    z char(2000) default 'z' )   5  /      Table created.      tkyte@TKYTE816> tkyte@TKYTE816> tkyte@TKYTE816> create global temporary table temp   2  ( x char(2000) default 'x',   3    y char(2000) default 'y',   4    z char(2000) default 'z' )   5  on commit preserve rows   6  /      Table created. 

I set up a small stored procedure to do some SQL on the table, and report the results:

tkyte@TKYTE816> create or replace procedure do_sql( p_sql in varchar2 )   2  as   3      l_start_redo    number;   4      l_redo            number;   5  begin   6  select value into l_start_redo from redo_size;   7   8      execute immediate p_sql;   9      commit;  10  11      select value-l_start_redo into l_redo from redo_size;  12  13      dbms_output.put_line  14      ( to_char(l_redo,'9,999,999') ||' bytes of redo generated for "' ||  15        substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' );  16  end;  17  /      Procedure created. 

Then, I ran equivalent INSERTs, UPDATEs, and DELETEs against them:

tkyte@TKYTE816> set serveroutput on format wrapped tkyte@TKYTE816> begin   2      do_sql( 'insert into perm   3               select 1,1,1   4                 from all_objects   5                where rownum <= 500' );   6   7      do_sql( 'insert into temp   8               select 1,1,1   9                 from all_objects  10                where rownum <= 500' );  11  12      do_sql( 'update perm set x = 2' );  13      do_sql( 'update temp set x = 2' );  14  15      do_sql( 'delete from perm' );  16      do_sql( 'delete from temp' );  17  end;  18  /  3,238,688 bytes of redo generated for "insert into perm"...     72,572 bytes of redo generated for "insert into temp"...  2,166,376 bytes of redo generated for "update perm set x = 2"...  1,090,336 bytes of redo generated for "update temp set x = 2"...  3,320,244 bytes of redo generated for "delete from perm"...  3,198,236 bytes of redo generated for "delete from temp"...      PL/SQL procedure successfully completed. 

As you can see:

The rules of thumb therefore are the following:

There are notable exceptions to the last rule of thumb. For example, if I UPDATE a column that is entirely Null with 2000 bytes of data, there will be very little rollback data generated. This UPDATE will behave like the INSERT. On the other hand, if I UPDATE a column with 2000 bytes of data to be Null, it will behave like the DELETE as far as redo generation is concerned. On average, you can expect an UPDATE against a temporary table to produce about 50 percent of the rollback/redo you would experience with a real table.

In general, common sense prevails on the amount of redo created. If the operation you perform causes rollback data to be created, then determine how easy or hard it will be to reverse (undo) the effect of your operation. If you INSERT 2000 bytes, the reverse of this is easy. You simply go back to no bytes. If you DELETE 2000 bytes, the reverse is INSERTing 2000 bytes. In this case, the redo is substantial.

Armed with this knowledge, you will avoid deleting from temporary tables. You can use TRUNCATE, or just let them empty themselves automatically after a COMMIT or when your session terminated. All of these methods generate no rollback and therefore, no redo. You will try to avoid updating a temporary table unless you really have to for the same reason. You will use temporary tables mostly as something to be INSERTed into and SELECTed from. In this fashion, you'll make optimum use of their unique ability to not generate redo.

Analyzing Redo

Lastly in the area of redo log, is a question that is asked frequently, 'How do you analyze these files?' In the past, prior to Oracle 8i, it was very difficult. You could call Oracle Support and get the magic command to 'dump' a redo log file. This would create a textual report that you could read and, if you really understood lots of internal, undocumented stuff about Oracle, you might be able to make sense of them. Practically speaking, this was not feasible, especially if you want to analyze the redo log files to find out when a table was dropped, or what transaction set the SALARY column to a negative value, and so on.

Enter LogMiner; which is a new package, DBMS_LOGMNR, supplied with Oracle 8i that allows you to load your redo log files into a database V$ table, and query them using SQL. You can see the before and after values of each column affected by a DML statement. You can see the SQL that would effectively 'replay' your transaction or 'undo' your transaction. You can look in there to find the DELETE against OBJ$ (a SYS-owned data dictionary table) to see when your table was dropped 'accidentally', in the hopes that your DBA can reverse to a point in time right before this DROP TABLE and recover it.

For now, I'm just going to mention Log Miner. In Appendix A on Necessary Supplied Packages, I go into some depth on the DBMS_LOGMNR package. Refer to the section at the back of this book for more details in this area.

Rollback

We've already discussed a lot of rollback segment topics. We've seen how they are used during recovery, how they interact with the redo logs, and that they are used for consistent, non-blocking reads of data. In this section, I would like to cover the most frequently raised issues with rollback segments. The bulk of our time will be spent on the infamous ORA-01555: snapshot too old error as this single issue causes more confusion than any other topic in the entire database set of topics. Before we do this, we'll investigate two other rollback-related issues. First, we will address the question of what generates the most/least undo (you might already be able to answer that yourself given the preceding examples with temporary tables). Then, we will look at using the SET TRANSACTION statement to pick a rollback segment, and discuss why we might want to use it. Then, we'll finish up with a detailed look at the mysterious ORA-01555.

What Generates the Most/Least Undo?

This is a frequently asked, but easily answered question. An INSERT will generate the least amount of undo, since all Oracle needs to record for this is a row ID to 'delete'. An UPDATE is typically second in the race (in most cases). All that needs to be recorded are the changed bytes. It is most common that you UPDATE some small fraction of the entire row's data. Therefore, a small fraction of the row must be remembered in the undo. Many of my examples above run counter to this rule of thumb, but that is because they update large, fixed sized rows, and update the entire row. It is much more common to UPDATE a row and change a small percentage of the total row. A DELETE will in general, generate the most undo. For a DELETE, Oracle must record the entire row's before image into the undo segment. The temporary table example, with regards to redo generation above, is a classic example of this. The INSERT generated very little undo that needed to be logged. The UPDATE generated an amount equal to the before image of the data that was changed, and the DELETE generates the entire set of data written into the rollback segment.

SET TRANSACTION

The SET TRANSACTION SQL statement may be used to 'pick' the rollback segment you would like your transaction to use. This is generally used in order to have a really big rollback segment for some large operation. I am generally not a big fan of this practice, especially when it is overused. For some infrequent mass updates, this might be an OK thing to do on a one-time basis. It is my opinion however, that you should have equi-sized rollback segments (all rollback segments are the same size), and you let the system pick and choose the one you are going to use for a given transaction. If you use rollback segments that can grow (MAXEXTENTS is high enough) and, if need be, have an optimal setting so they shrink back to some size after extending a large amount, you do not need to have a special 'big one'.

One of the problems with one big rollback segment, is that there is nothing to stop any other transaction from using it. There is nothing that you can realistically do to make a rollback segment belong to a single transaction. You are going to have others working in your space. It is just much more straightforward to let the system pick a rollback segment and let it grow. It also gets to be an issue if the tool you are using does not allow you to choose a rollback segment, for example IMP (import) does not, a snapshot refresh on the other hand does, SQLLDR does not, and so on. I believe strongly that your rollback segments should be sized for the transactions your system performs, and any rollback segment should be big enough.

That being said, there are infrequent, one-time uses for this statement. If you have to perform some sort of mass update of lots of information, this might be a good use of the feature. You would create a temporary rollback segment on some scratch disks and do your update. After the update is done, you would offline, and drop the rollback segment. Perhaps a better alternative to this even, would be to have the really large table partitioned in the first place, and perform a parallel update. In this case, each of the parallel query slaves will be assigned to their own rollback segment, making it possible for your transaction to, in effect, use all of the available rollback segments simultaneously. This is something you cannot do with a serially executed update.

'ORA-01555: snapshot too old'

The ORA-01555 is one of those errors that confound people. It is the foundation for many myths, inaccuracies, and suppositions. The error is actually straightforward and has only two real causes, but since there is a special case of one of them that happens so frequently, I'll say that there are three. They are:

Points one and two above are directly related to Oracle's read consistency model. As you recall from Chapter 2, Architecture, the results of your query are 'pre-ordained' - they are well-defined before Oracle goes to retrieve even the first row. Oracle provides this consistent point in time 'snapshot' of the database by using the rollback segments to roll back blocks that have changed since your query began. Every statement you execute such as:

update t set x = 5 where x = 2;      insert into t select * from t where x = 2;      delete from t where x = 2;      select * from t where x = 2; 

will see a read consistent view of T and the set of rows where x=2, regardless of any other concurrent activity in the database. All statements that 'read' the table take advantage of this read consistency. In the above, the UPDATE reads the table to find rows where x=2 (and then UPDATEs them). The INSERT reads the table to find where x=2, and then INSERTs them, and so on. It is this dual use of the rollback segments, both to roll back failed transactions, and to provide for read consistency that results in the ORA-01555.

The third item above is a more insidious cause of the ORA-01555, in that it can happen in a database where there is a single session, and this session is not modifying the table that raises the error! This doesn't seem possible; why would we need rollback data for a table we can guarantee is not being modified? We'll find out below.

Before we take a look at all three cases with illustrations, I'd like to share with you the solutions to the ORA-1555. In general they are:

We'll come back to these again below, as they are important facts to know. It seemed appropriate to display them prominently before we begin.

Rollback Segments Are in Fact Too Small

The scenario is this: you have a system where the transactions are small. As a result of this, you need very little rollback segment space allocated. Say, for example, the following is true:

That is more than sufficient undo for this database when processing transactions. The rollback segments will wrap around, and reuse space about every three to four minutes or so, on average. If we were to size rollback segments based on our transactions that do modifications, we did alright.

In this same environment however, you have some reporting needs. Some of these queries take a really long time to run - five minutes, perhaps. Here is where the problem comes in. If these queries take five minutes to execute and they need a view of the data as it existed when the query began, we have a very good probability of the ORA-01555 error occurring. Since our rollback segments will wrap during this query execution, we know that some rollback information generated since our query began is gone - it has been overwritten. If we hit a block that was modified near the time we started our query, the undo information for this block will be missing, and we will receive the ORA-01555.

Here is a small example. Let's say we have a table with blocks 1, 2, 3, ... 1,000,000 in it. The following is a serial list of events that could occur:

Time(min:secs)

Action

0:00

Our query begins.

0:01

Another session UPDATEs block 1,000,000. Rollback information for this is recorded into some rollback segment.

0:01

This UPDATE session COMMITs. The rollback data it generated is still there, but is now subject to being overwritten if we need the space.

1:00

Our query is still chugging along. It is at block 200,000.

1:01

Lots of activity going on, we have generated a little over 1.3 MB of rollback by now.

3:00

Our query is still going strong. We are at block 600,000 or so by now.

4:00

Our rollback segments start to wrap around and reuse the space that was active when our query began at time 0:00. Specifically, we have just reused the rollback segment space that the UPDATE to block 1,000,000 used back at time 0:01.

5:00

Our query finally gets to block 1,000,000. It finds it has been modified since the query began. It goes to the rollback segment and attempts to find the undo for that block to get a consistent read on it. At this point, it discovers the information it needs no longer exists. ORA-01555 is raised and the query fails.

This is all it takes. If your rollback segments are sized such that they have a good chance of wrapping around during the execution of your queries, and your queries access data that will probably be modified, you stand a very good chance of hitting the ORA-01555 on a recurring basis. It is at this point you must resize your rollback segments and make them larger (or have more of them). You need enough rollback configured to last as long as your long running queries. The system was sized for the transactions that modify data - and we forgot to size for the other components of the system. This is where one of the points of confusion comes into play. People will say, 'Well, we have X MB of rollback configured but they can grow - we have MAXEXTENTS set at 500 and each extent is 1 MB, so the rollback can get quite large.' The problem is that the rollback segments will never grow due to a query, only due to INSERTs, UPDATEs, and DELETEs. The fact that a long running query is executing does not cause Oracle to grow a rollback segment to retain the data in case it might need it. Only a long running UPDATE transaction would do this. In the above example, even if the rollback segments had the potential to grow, they will not. What you need to do for this system is have rollback segments that are already big. You need to permanently allocate space to the rollback segments, not give them the opportunity to grow on their own.

The only solutions to the above problem are to either make it so that the rollback segments are sized so they do not wrap but every six to ten minutes, or make it so your queries never take more than two to three minutes to execute. The first suggestion is based on the fact that we have queries that take five minutes to execute. In this case, the DBA needs to make the amount of permanently allocated rollback two to three times larger. The second suggestion, a perfectly valid suggestion, is equally appropriate. Any time you can make the queries go faster, you should. If the rollback generated since the time your query began is never overwritten, you will avoid the ORA-01555.

The important thing to remember is that the probability of an ORA-01555 is dictated by the smallest rollback segment in your system, not the largest, not the average. Adding one 'big' rollback segment will not make this problem go away. All it takes is for the smallest rollback segment to wrap around while a query is processing, and then this query stands a chance of an ORA-01555. This is why I am a big fan of equi-sized rollback segments. In this fashion, each rollback segment is both the smallest and the largest. This is also why I avoid using 'optimally' sized rollback segments. If you shrink a rollback segment that was forced to grow, you are throwing away a lot of undo that may be needed right after that. It discards the oldest rollback data when it does this, minimizing the risk but still, the risk is there. I prefer to manually shrink rollback segments during off peak time if at all. I am getting a little too deep into the DBA role at this point, so we'll be moving on to the next case. It is just important that you understand the ORA-01555 in this case, is due to the system not being sized correctly for your workload. The only solutions are to size correctly for your workload. It is not your fault, but it is your problem since you hit it. It is the same as if you run out of temporary space during a query. You either configure sufficient temporary space for the system, or you rewrite the queries so they use a plan that does not require temporary space.

In order to see this effect for yourself, we can set up a small, but somewhat artificial test. What I will do below is create a very small rollback segment. We'll have one session that will use only this rollback segment, virtually assuring us that it will wrap around and reuse its allocated space many times. The session that uses this rollback segment will be modifying a table T. It will use a full scan of T, and read it from 'top' to 'bottom'. In another session, we will execute a query that will read the table T via an index. In this fashion, it will read the table somewhat randomly. It will read row 1, then row 1000, then row 500, then row 20,001, and so on. In this way, we will tend to visit blocks very randomly, and perhaps many times during the processing of our query. The odds of getting an ORA-01555 in this case are virtually 100 percent. So, in one session we start with:

tkyte@TKYTE816> create rollback segment rbs_small   2  storage   3  ( initial 8k next 8k   4    minextents 2 maxextents 3 )   5  tablespace rbs_test   6  /      Rollback segment created.      tkyte@TKYTE816> alter rollback segment rbs_small online;      Rollback segment altered.      tkyte@TKYTE816> create table t   2  as   3  select *   4    from all_objects   5  /      Table created.      tkyte@TKYTE816> create index t_idx on t(object_id)   2  / Index created.      tkyte@TKYTE816> begin   2          for x in ( select rowid rid from t )   3          loop   4                  commit;   5                  set transaction use rollback segment rbs_small;   6                  update t   7                     set object_name = lower(object_name)   8                   where rowid = x.rid;   9          end loop;  10          commit;  11  end;  12  / 

Now, in another session, while this PL/SQL block is executing, we issue:

tkyte@TKYTE816> select object_name from t where object_id > 0 order by object_id;      OBJECT_NAME ------------------------------ i_obj# tab$ ... /91196853_activationactivation /91196853_activationactivation ERROR: ORA-01555: snapshot too old: rollback segment number 10 with name "RBS_SMALL" too small      3150 rows selected.      tkyte@TKYTE816> select count(*) from t;        COUNT(*) ----------      21773 

As you can see, it took a little while, but after reading about three thousand rows (about one seventh of the data) randomly, we eventually hit the ORA-01555. In this case, it was purely due to the fact that we read the table T via the index, and performed random reads all over the table. If we had full scanned the table instead, there is a good chance we would not get the ORA-01555 in this particular case. (Try it: change the SELECT query to be SELECT /*+ FULL(T) */ ... and see what happens. On my system, this query did not get the ORA-1555 in repeated runs). This is because both the SELECT and UPDATE would have been full scanning T, and the SELECT would most likely race ahead of the UPDATE during its scan (the SELECT just has to read, the UPDATE must read and update, therefore it'll go slower). By doing the random reads, we increase the probability that the SELECT will need to read a block, which the UPDATE modified and committed many rows ago. This just demonstrates the somewhat insidious nature of the ORA-01555. It's occurrence depends on how concurrent sessions access, and manipulate the underlying tables.

You Fetch Across COMMITs

This is simply a variation on the theme. It is the same case as above, but you are doing it to yourself. You need no help from another session. We've already investigated this in Chapter 4 on Transactions, and will briefly review it again. The bottom line is this; fetching across COMMITs is a surefire way to get the ORA-01555. My observation is that most occurrences of the ORA-01555 are because of this operation. The amusing thing is that people sometimes react to this error by committing even more frequently since the message says rollback segment too small. The thinking is that this will help the problem (that our modification must be using too much rollback is the incorrect conclusion here), when in fact it will only ensure that it happens even faster.

The scenario is that you have to update lots of information. You are hesitant to configure the system with sufficient rollback space for whatever reasons. So, you decide to COMMIT every X rows to 'save' on rollback. Never mind that this is both probably slower, and in the end, generates more undo and redo, it is also the surefire way to get into the ORA-01555. Continuing with the above example, I can easily demonstrate this. Using the same table T from above, simply execute:

tkyte@TKYTE816> declare   2          l_cnt number default 0;   3  begin   4          for x in ( select rowid rid, t.* from t where object_id > 0 )   5          loop   6                  if ( mod(l_cnt,100) = 0 )   7                  then   8                          commit;   9                          set transaction use rollback segment rbs_small;  10                  end if;  11                  update t  12                     set object_name = lower(object_name)  13                   where rowid = x.rid;  14                  l_cnt := l_cnt + 1;  15          end loop;  16      commit;  17  end;  18  / declare * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 10 with name "RBS_SMALL" too small ORA-06512: at line 4 

Here, we are performing a random read on the table T via the index. We UPDATE a single row at a time in the loop. Every 100 rows of UPDATEs, we COMMIT. At some point in time, we will revisit a block with our query that we modified with our UPDATE, and that block will no longer be recoverable from the rollback segments (since we overwrote that data long ago). Now we are in the uncomfortable position of having our UPDATE process fail partway through.

We could, as outlined in Chapter 4 on Transactions, come up with a more sophisticated method of updating the data. For example, we could find the minimum OBJECT_ID and the maximum. We could divide this up into ranges of 100 and do the UPDATEs, recording in another table what we had successfully updated so far. This makes the process, which should be a single statement and a single transaction, many individual transactions implemented in complex procedural code that could be restarted from a failure. For example:

tkyte@TKYTE816> create table done( object_id int );      Table created.      tkyte@TKYTE816> insert into done values ( 0 );      1 row created.      tkyte@TKYTE816> declare   2          l_cnt number;   3          l_max number;   4  begin   5          select object_id into l_cnt from done;   6          select max(object_id) into l_max from t;   7   8          while ( l_cnt < l_max )   9          loop  10                  update t  11                     set object_name = lower(object_name)  12                   where object_id > l_cnt  13                     and object_id <= l_cnt+100;  14  15                  update done set object_id = object_id+100;  16  17                  commit;  18                  set transaction use rollback segment rbs_small;  19                  l_cnt := l_cnt + 100;  20          end loop;  21  end;  22  /      PL/SQL procedure successfully completed. 

What we did here mostly, was to come up with a complex solution that has to be tested and reviewed for accuracy, and will run much slower than the simple:

update t set object_name = lower(object_name) where object_id > 0; 

The simple, and in my opinion correct, solution to this dilemma, is to configure an appropriate amount of rollback space for your system, and use the single UPDATE statement. If you have the very occasional large update, use a 'scratch' rollback segment that you create just for the large process, and drop it afterwards. It is so much easier and less error-prone than coming up with a complex solution that might fail, simply due to its complexity (programming error). It is so much easier to let the system do what it needs to do rather than try to come up with complex workarounds to 'save space'.

Delayed Block Cleanout

This cause of the ORA-01555 is harder to eliminate entirely, but is rare, as the circumstances under which it occurs do not happen frequently (at least not in Oracle 8i anymore). We have already discussed the block cleanout mechanism, but to summarize, it is the process whereby the next session to access a block after it has been modified, may have to check to see if the transaction that last modified the block is still active. Once it determines that it is not active, it cleans out the block so that the next session to access it does not have to go through the same process again. In order to clean out the block, Oracle determines the rollback segment used for the previous transaction (from the blocks header), and then determines whether the rollback header indicates whether it has been committed or not. This confirmation is accomplished in one of two ways. One way is that Oracle can determine that the transaction committed a long time ago, even though it's transaction slot has been overwritten in the rollback segment transaction table. The other way is that the COMMIT SCN is still in the transaction table of the rollback segment, meaning the transaction committed a short period of time ago, and it's transaction slot hasn't been overwritten.

In order to receive the ORA-01555 from a delayed block cleanout, all of the following conditions must be met:

Now, when our query gets to the block that was modified and committed before it began, it is in trouble. Normally, it would go to the rollback segment pointed to by the block and find the status of the transaction that modified it (in other words, find the COMMIT SCN of that transaction). If the COMMIT SCN is less than t1, our query can use this block. If the COMMIT SCN is greater tha t1, our query must roll back that block. The problem is however, that our query is unable to determine in this particular case if the COMMIT SCN of the block is greater than or less than t1. It is unsure as to whether it can use it or not. The ORA-01555 then results.

We can force this error to occur artificially with a single session, but it is more impressive if we use two sessions, as that will drive home the point that this error is not caused by fetching across COMMITs. We'll show both examples as they are both small, and very similar.

What we will do is create many blocks in a table that need to be cleaned out. We will then open a cursor on that table and, inside of a tight loop, initiate many transactions. I am making all modifications to go into the same rollback segment to cause this error to occur in a predicable fashion. Eventually, the ORA-01555 will occur as the outer query in the loop (SELECT * FROM T) hits this block cleanout issue, due to the small UPDATE inside the innermost loop modifying and committing frequently:

tkyte@TKYTE816> create table small( x int );      Table created.      tkyte@TKYTE816> insert into small values ( 0 );      1 row created.      tkyte@TKYTE816> begin   2      commit;   3      set transaction use rollback segment rbs_small;   4      update t   5         set object_type = lower(object_type);   6      commit;   7   8          for x in ( select * from t )   9          loop  10                  for i in 1 .. 20  11                  loop  12                          update small set x = x+1;  13                          commit;  14                          set transaction use rollback segment rbs_small;  15                  end loop;  16          end loop;  17  end;  18  / begin * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 10 with name "RBS_SMALL" too small ORA-06512: at line 8      tkyte@TKYTE816> select * from small;               X ----------     196900 

In order to 'achieve' the above error, I used the same table T as before with some 22,000 rows in it (about 300 blocks on my system). I had a block buffer cache of 300 blocks (10 percent of that is 30). The UPDATE on line 4 should have left about 270 blocks in need of a block cleanout. We then proceeded to SELECT * from this table, where lots of blocks were in need of cleanout. For every row we fetched, we performed 20 transactions, and I made sure that these transactions were directed to the same rollback segment that did the UPDATE in the first place (the goal after all is to overwrite that transaction slot). After processing about 10,000 rows in our query from T (as evidenced by the value in SMALL), we hit the ORA-01555.

Now, to show this is not caused by the fetch across COMMIT (it looks like it could be since we are fetching across a COMMIT), we'll use two sessions. The first session we'll start by creating a table called STOP_OTHER_SESSION. We'll use this table to notify the other session that generates lots of transactions, that it is time to stop. Then, it will dirty lots of blocks in the table again like the above did, and start a long running query on this table. The DBMS_LOCK.SLEEP is used to put a wait between each row fetched to make this simple query a long running one. It simulates the think time that the work would be performing on each row:

tkyte@TKYTE816> create table stop_other_session ( x int );      Table created.      tkyte@TKYTE816> declare   2          l_cnt number := 0;   3  begin   4      commit;   5      set transaction use rollback segment rbs_small;   6      update t   7         set object_type = lower(object_type);   8      commit;   9  10      for x in ( select * from t )  11      loop  12          dbms_lock.sleep(1);  13      end loop;  14  end; .15  / 

Now, while the above code is executing, you would run the following in another session:

tkyte@TKYTE816> create table small( x int );      Table created.      tkyte@TKYTE816> insert into small values ( 0 );      1 row created.      tkyte@TKYTE816> begin   2          commit;   3          set transaction use rollback segment rbs_small;   4          for i in 1 .. 500000   5          loop   6                  update small set x = x+1;   7          commit;   8          set transaction use rollback segment rbs_small;   9          for x in ( select * from stop_other_session )  10          loop  11               return; -- stop when the other session tells us to  12          end loop;  13      end loop;  14  end;  15  /      PL/SQL procedure successfully completed. 

After a while, the first session will report back with:

declare * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 10 with name "RBS_SMALL" too small ORA-06512: at line 10 

This is the same error, but this time we did not fetch across a COMMIT. In fact, no one was modifying any of the data we were reading.

As I said, the above is a rare case. It took a lot of conditions, that all must exist simultaneously to occur. We needed blocks that were in need of a cleanout to exist and these blocks are rare in Oracle8i (they used to occur more frequently in version 7.x and 8.0 releases, but are relatively rare in 8.1). An ANALYZE statement to collect statistics, gets rid of them so the most common causes, large mass updates and bulk loads, should not be a concern, since the tables need to be analyzed after such operations anyway. Most transactions tend to touch less then 10 percent of the block the buffer cache and hence, do not generate blocks that need to be cleaned out. In the event that you believe this issue is encountered, whereby a SELECT against a table that has no other DML applied to it is raising the ORA-01555, the things to try are:

Summary

In this chapter, we have taken a look at redo and rollback, and what they mean to the developer. What I have presented here is mostly things for you to be on the look out for, since it is actually the DBAs or SAs who must correct these issues. The most important things to take away from this chapter are the importance of redo and rollback, and the fact that they are not overhead - they are in fact integral components of the database, they are necessary and mandatory. Once you have a good understanding of how they work, and what they do, you'll be able to make better use of them. Understanding that you are not 'saving' anything by committing more frequently than you should (you are actually wasting resources, it takes more CPU, more disk, and more programming), is probably the most important point. Understand what the database needs to do, and then let the database do it.



Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net