Let’s now look at the second reason developers are tempted to commit updates in a procedural loop, which arises from their (misguided) attempts to use a “limited resource” (undo segments) sparingly. This is a configuration issue; you need to ensure that you have enough undo space to size your transactions correctly. Committing in a loop, apart from generally being slower, is also the most common cause of the dreaded ORA-01555 error. Let’s look at this in more detail.
As you will appreciate after reading the previous chapter, Oracle’s multiversioning model uses undo segment data to reconstruct blocks as they appeared at the beginning of your statement or transaction (depending on the isolation mode). If the necessary undo information no longer exists, you will receive an ORA-01555: snapshot too old error message, and your query will not complete. So, if you are modifying the table that you are reading (as in the previous example), you are generating undo information required for your query.
Your UPDATE generates undo information that your query will probably be making use of to get the read-consistent view of the data it needs to update. If you commit, you are allowing the system to reuse the undo segment space you just filled up. If it does reuse the undo, wiping out old undo data that your query subsequently needs, you are in big trouble. Your SELECT will fail and your UPDATE will stop partway through. You have a partly finished logical transaction and probably no good way to restart it (more about this in a moment).
Let’s see this concept in action with a small demonstration. In a small test database, we set up a table:
$ sqlplus eoda/foo@PDB1
SQL> create table t as select * from all_objects; Table created.
SQL> create index t_idx on t(object_name); Index created.
SQL> exec dbms_stats.gather_table_stats( user, ‘T’, cascade=>true ); PL/SQL procedure successfully completed.
I then created a very small undo tablespace and altered the system to use it. Note that by setting AUTOEXTEND off, I have limited the size of all UNDO to be 10MB or less in this system:
SQL> create undo tablespace undo_small datafile ‘/tmp/undo_small.dbf’ size 10m reuseautoextend off;Tablespace created.
SQL> alter system set undo_tablespace = undo_small;System altered.
Now, with only the small undo tablespace in use, I ran this block of code to do theUPDATE:
SQL> beginfor x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum rfrom twhere object_name > ‘ ‘ )loopupdate set object_name = lower(x.object_name)where rowid = x.rid;if ( mod(x.r,100) = 0 ) thencommit;end if;end loop;commit;end;
beginERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-06512: at line 2
I get the error. I should point out that I added an index hint to the query and a WHERE clause to make sure I was reading the table randomly (together, they caused the cost- based optimizer to read the table “sorted” by the index key). When we process a table via an index, we tend to read a block for a single row, and then the next row we want will be on a different block. Ultimately, we will process all of the rows on block 1, just not all at the same time. Block 1 might hold, say, the data for all rows with OBJECT_NAMEs starting with the letters A, M, N, Q, and Z. So we would hit the block many times, since we are reading the data sorted by OBJECT_NAME, and presumably many OBJECT_NAMEs start with letters between A and M. Since we are committing frequently and reusing undo space, we eventually revisit a block where we can simply no longer roll back to the point our query began, and at that point we get the error.
This was a very artificial example just to show how it happens in a reliable manner. My UPDATE statement was generating undo. I had a very small undo tablespace to play with (10MB). I wrapped around in my undo segments many times, since they are used in a circular fashion. Every time I committed, I allowed Oracle to overwrite the undo data I generated. Eventually, I needed some piece of data I had generated, but it no longer existed and I received the ORA-01555 error.
You would be right to point out that in this case, if I had not committed on line 10,
I would have received the following error:
beginERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDO_SMALL’
ORA-06512: at line 6
The major differences between the two errors are as follows:
•\ The ORA-01555 example left my update in a totally unknown state.Some of the work had been done; some had not.
•\ There is absolutely nothing I can do to avoid the ORA-01555 error,given that I committed in the cursor FOR loop.
•\ The ORA-30036 error can be avoided by allocating appropriate resources in the system. This error is avoidable by correct sizing; the first error is not. Further, even if I don’t avoid this error, at least the update is rolled back, and the database is left in a known, consistent state—not halfway through some large update.
The bottom line here is that you can’t “save” on undo space by committing frequently—you need that undo. I was in a single-user system when I received the ORA-01555 error. It takes only one session to cause that error, and many times even in real life it is a single session causing its own ORA-01555 errors. Developers and DBAs need to work together to size these segments adequately for the jobs that need to be done.
There can be no shortchanging here. You must discover, through analysis of your system, what your biggest transactions are and size appropriately for them. The dynamic performance view V$UNDOSTAT can be very useful to monitor the amount of undo you are generating and the duration of your longest running queries. Many people consider things like temp, undo, and redo as overhead—things to allocate as little storage to as possible.
This is reminiscent of a problem the computer industry had on January 1, 2000, which was all caused by trying to save 2 bytes in a date field. These components of the database are not overhead, but rather are key components of the system. They must be sized appropriately (not too big and not too small).
Speaking of UNDO segments being too small, make sure to set your undo tablespace back to your regular one after running these tests, for example:
SQL> alter system set undo_tablespace=undotbs2;
SQL> drop tablespace undo_small including contents and datafiles;
If you don’t set your undo tablespace back to a normally sized one, you’ll be hitting ORA-30036 errors for the rest of the book!
