Today we talk about the purpose of Oracle undo and give a demonstration of its use and little bit of what Oracle is doing under the covers. We also show how query performance can be affected by what else is going on in the database.
In the last post on transactions, we showed how data modifications made by one user were not visible to another user until the first user committed the changes, thereby ending the transaction. Undo is the mechanism Oracle uses to make this happen. Undo provides read consistency: Oracle guarantees that your query will return its data exactly as it was stored in the database when the query began, even if other users modify the table after your query started.
Undo is also used to restore data to its previous image in the case of a rollback. This applies whether the rollback is user initiated, or due to a transaction being terminated by a crashed process.
At the very simplest level, when I begin an update transaction, Oracle will store a pre-image of the modifications I make in undo segments in the undo tablespace, using normal data blocks.
Let’s make a simple table.
U1> create table t1
2 as select rownum id, a.*
3 from all_source a;
Table created.
U1> insert into t1
2 select rownum id, a.*
3 from all_source a;
231901 rows created.
U1> commit;
Commit complete.
U1> create index i1 on t1(id, text);
Index created.
U1> exec dbms_stats.gather_table_stats('U1','T1')
PL/SQL procedure successfully completed.
Now, in a second session, let’s run a query. We’ll turn on tracing and flush the buffer cache. This will force Oracle to read from disk. It will also allow us to see what types of blocks are being read:
U2> set autotrace on stat U2> alter session set tracefile_identifier=undotest; Session altered. U2> alter system flush buffer_cache; System altered. U2> alter session set events '10046 trace name context forever, level 12'; Session altered. U2> select /* firstquery */ count(distinct line) 2 from u1.t1 3 where id <= 5000; COUNT(DISTINCTLINE) ------------------- 3306 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 13695 consistent gets 13692 physical reads
Notice we did 13695 consistent gets – which are just oracle block reads from the database buffer. We also did 13692 physical block reads.
Now, back in session one:
U1> update t1 set id = (id * 3) 2 where id <= 5000; 10000 rows updated.
We’ve modified 10000 rows.
U1> select distinct sid from v$mystat; SID ---------- 91 U1> select t.used_ublk 2 from v$transaction t, v$session s 3 where t.addr = s.taddr 4 and s.sid = 91; USED_UBLK ---------- 275
V$transaction shows that the uncommitted update has generated 275 blocks of undo. This is saving the before image—what the data looked like prior to the update statement we made.
Back in session two, let’s run the same query again:
U2> alter system flush buffer_cache; System altered. U2> select /* secondquery */ count(distinct line) 2 from u1.t1 3 where id <= 5000; COUNT(DISTINCTLINE) ------------------- 3306 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 23805 consistent gets 13801 physical reads
This time we did 23805 consistent gets, compared to 13695 the first time. Since session one has modified the blocks and not committed, we have to use the undo blocks to recreate what the data looks like prior to the changes. This is why we are reading so many more blocks.
U1> commit; Commit complete. U1> select t.used_ublk 2 from v$transaction t, v$session s 3 where t.addr = s.taddr 4 and s.sid = 91; no rows selected
Session one commits, the transaction is ended, and the undo blocks are released.
U2> select /* thirdquery */ count(distinct line) 2 from u1.t1 3 where id <= 5000; COUNT(DISTINCTLINE) ------------------- 1666 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 13805 consistent gets 13685 physical reads
Now session two can see the modified data, giving a different result for the query, and the number of consistent gets has also decreased to approximately what it was prior to the changes.
Looking at the raw trace file that we generated shows that for query one, run in session two before we ran the update, all the reads are from file #4.
… WAIT #266388400: nam='direct path read' ela= 238 file number=4 first dba=11595 block cnt=13 obj#=20099 tim=633713817835 WAIT #266388400: nam='direct path read' ela= 423 file number=4 first dba=11625 block cnt=15 obj#=20099 tim=633713820337 WAIT #266388400: nam='direct path read' ela= 14509 file number=4 first dba=39424 block cnt=8 obj#=20099 tim=633713835807 WAIT #266388400: nam='direct path read' ela= 385 file number=4 first dba=39433 block cnt=15 obj#=20099 tim=633713836741 …
Query two, on the other hand, starts with sequential reads from file #3, before shifting to reads from file #4.
<many similar previous lines deleted for brevity> … WAIT #266388400: nam='db file sequential read' ela= 139 file#=3 block#=956 blocks=1 obj#=0 tim=633863879611 WAIT #266388400: nam='db file sequential read' ela= 148 file#=3 block#=955 blocks=1 obj#=0 tim=633863879812 WAIT #266388400: nam='db file sequential read' ela= 139 file#=3 block#=954 blocks=1 obj#=0 tim=633863880173 WAIT #266388400: nam='direct path read' ela= 176 file number=4 first dba=39449 block cnt=15 obj#=20099 tim=633863881072 WAIT #266388400: nam='direct path read' ela= 34 file number=4 first dba=39465 block cnt=15 obj#=20099 tim=633863881295 …
U1> select file_id, file_name 2 from dba_data_files 3 where file_id in (3, 4); FILE_ID FILE_NAME ---------- -------------------------------------------------- 4 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF 3 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
The autotrace we ran during the session shows we were reading more blocks in session two after session one ran its update; the trace file proves the additional reads were due to undo blocks being read.
This can be a big reason why a query may run more slowly at different times of the day despite its execution plan not changing. There could be nothing wrong with the query itself, but other processes could be modifying its source tables. In order for Oracle to honor read consistency, it has to use undo blocks to build an image of the source table’s blocks as they appeared when your query began. All the extra reads take time, which can show up as slower query performance.
Recent Comments