Do Frequent Commits Fix ORA-01555?

I answered a question on the OTN forum this morning that I figured is pertinent enough to post on my blog…it’s a very common question among DBAs and developers, and I think there are plenty of myths surrounding it as well.

Question: I am updating 1 million rows in Oracle 10g platform, normally when I do it in oracle 9i I run it as batch process and commit after each batch. Obviously to avoid/control undo generation. But in Oracle 10g I am told undo management is automatic and I do not need run the update as batch process.

Is this right please throw some light on this new feature - automatic undo management

Answer: Automatic undo management was available in 9i as well, and my guess is you were probably using it there. However, I’ll assume for the sake of this writing that you were using manual undo management in 9i and are now on automatic.

Automatic undo management depends upon UNDO_RETENTION, a parameter that defines how long Oracle should try to keep committed transactions in UNDO. However, this parameter is only a suggestion. You must also have an UNDO tablespace that’s large enough to handle the amount of UNDO you will be generating/holding, or you will get ORA-01555: Snapshot too old, rollback segment too small errors.

You can use the UNDO advisor to find out how large this tablespace should be given a desired UNDO retention, or look online for some scripts…just google for: oracle undo size

Oracle 10g also gives you the ability to guarantee undo. This means that instead of throwing an error on SELECT statements, it guarantees your UNDO retention for consistent reads and instead errors your DML that would cause UNDO to be overwritten.

Now, for your original question…yes, it’s easier for the DBA to minimize the issues of UNDO when using automatic undo management. If you set the UNDO_RETENTION high enough with a properly sized undo tablespace you shouldn’t have as many issues with UNDO. How often you commit should have nothing to do with it, as long as your DBA has properly set UNDO_RETENTION and has an optimally sized UNDO tablespace. Committing more often will only result in your script taking longer, more LGWR/DBWR issues, and the “where was I” problem if there is an error (if it errors, where did it stop?).

Lastly (and true even for manual undo management), if you commit more frequently, you make it more possible for ORA-01555 errors to occur. Because your work will be scattered among more undo segments, you increase the chance that a single one may be overwritten if necessary, thus causing an ORA-01555 error for those that require it for read consistency.

It all boils down to the size of the undo tablespace and the undo retention, in the end…just as manual management boiled down to the size, amount, and usage of rollback segments. Committing frequently is a peroxide band-aid: it covers up the problem, tries to clean it, but in the end it just hurts and causes problems for otherwise healthy processes.

For any readers using IE, you may notice that the page content doesn’t position properly on the page. It looks great on other browsers, but IE has a serious issue when it comes to padding, margins, and really anything pixel related.

I’m working on fixing this issue so IE users can enjoy the same brilliant clarity Firefox users have. At least while visiting my site.

In the meantime, go Firefox!

I received a question from a reader that I figured I’d address here.

Question: The oracle documentation tells us that when we put a tablespace in backup mode, the first DML in the session logs the entire block in the redo log buffer and not just the changed vectors. i have two questions regarding this:

1) Can we simulate an example to see this happening?
2) What can be the purpose of logging the entire block the first time and not do the same subsequently?

Answer:

No problem! Below, I’ve created a simulation. Pay attention to the “redo size” statistic in each.

First, I have updated a single row of the employees table.

SQL> set autotrace trace stat
SQL> update employees set first_name = 'Stephen' where employee_id = 100;

1 row updated.

Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        292  redo size
        669  bytes sent via SQL*Net to client
        598  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> rollback;

Rollback complete.

Notice the redo size was only 292 bytes, not a very large amount. Now, let’s put the USERS tablespace into hot backup mode.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> update employees set first_name = 'Stephen' where employee_id = 100;

1 row updated.

Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
          1  consistent gets
          0  physical reads
       8652  redo size
        670  bytes sent via SQL*Net to client
        598  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Wow! Quite a bit of a difference. This time, we can see that at least an entire block was written to redo; 8,652 bytes total. Let’s run it one more time, with the tablespace still in hot backup mode.

SQL> /

1 row updated.

Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        292  redo size
        671  bytes sent via SQL*Net to client
        598  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

This time, it only used 292 bytes, the same as the original amount. However, to address your second question, we’re going to attempt changing a different block, by changing a record in the departments table instead of employees.

SQL> update departments set department_name = 'Test Dept.' where department_id = 270;

1 row updated.

Statistics
----------------------------------------------------------
         17  recursive calls
          1  db block gets
          5  consistent gets
          1  physical reads
       8572  redo size
        673  bytes sent via SQL*Net to client
        610  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

The result is that another entire block was written to redo. In your question, you stated: “The oracle documentation tells us that when we put a tablespace in backup mode, the first DML in the session logs the entire block in the redo log buffer and not just the changed vectors”

This is close, but not right on the mark. It is not the first DML of the session, but the first DML to a block that is written to redo. You’re absolutely right when you imply that the first DML of the session would make no sense. However, when Oracle writes the first DML for the block, it ensures that the redo logs/archive trail contains at least one full representation of each block that is changed. Subsequent changes will therefore be safe.

This process exists to resolve block fractures. A block fracture occurs when a block is being read by the backup, and being written to at the same time by DBWR. Because the OS (usually) reads blocks at a different rate than Oracle, your OS copy will pull pieces of an Oracle block at a time. What if the OS copy pulls half a block, and while that is happening, the block is changed by DBWR? When the OS copy pulls the second half of the block it will result in mismatched halves, which Oracle would not know how to reconcile.

This is also why the SCN of the datafile header does not change when a tablespace enters hot backup mode. The current SCNs are recorded in redo, but not in the datafile. This is to ensure that Oracle will always recover over the datafile contents with redo entries. When recovery occurs, the fractured datafile block will be replaced with a complete block from redo, making it whole again. After Oracle can be certain it has a complete block, all it needs are the vectors.

I hope this answered your question!

OpenWorld is over, and it’s time to get back to blogging! I’ve got plenty of updates, including Tip #5, my trip to Jamaica, OpenWorld itself (including my presentation), and other various notes. Expect updates over the next couple days.