Read consistency overhead

SQL performance can degrade for many reasons, some of most common are:
– plan changes
– data skewness
– low caching efficiency
– data growth
– contention.

All these factors are relatively well known. A somewhat less common, although not exceptionally rare scenario, is read consistency overhead due to concurrent DML against queried tables. Because of being less common, this scenario is often overlooked, which leads to false diagnoses (and eventually to “fixes” that can do more harm than good).

Read consistency

Read consistency dictates that:
1) changes by other sessions should not be seen until they are committed
2) changes by other sessions made after the current query started should not be seen even if they are committed.

These rules protect from inconsistent results. But consistency comes at a certain performance price, since they require reads of undo data for modified blocks, and I/O is generally the slowest part in the database operation. Additionally, there’s also some CPU overhead because the information read from undo needs to be applied to reconstruct the right point-in-time version of the data. If DML is intensive enough, all this can cause serious slowdown, especially considering that there may be some “positive feedback loop” kind of effect here: the slower a query is, the more undo you need to make it read-consistent, the more undo you read, the slower your query becomes. Eventually, after a certain threshold, SQL performance would spiral down until the query undo needs exceed undo retention, and the query fails with ORA-1555.

Recognizing this situation can be tricky because even though Oracle provides statistics for consistency-related I/O requests, in most real-life scenarios they are not very useful (e.g. to use session-level stats you need to take the baseline, and you don’t normally know that you need to take a baseline until after a performance issue occurs). There are some indirect ways to observe the read consistency overhead, e.g. by noticing that SQL is taking more I/O than it should. Or you can notice that I/O per execution varies a lot even though everything else — data, SQL inputs, plan — remain the same, and this variation is in phase with changes in DML activity against queryied tables. But the most straightforward and reliable way is by looking at breakdown of disk reads made by the query by their source.

ASH query

This is an ideal case for ASH, because with ASH you don’t need to worry about baselines, or any typical tracing problems such as database server filesystem access. With ASH, all you need (apart from having the Diagnostic and Tuning Pack license to use it) is to run a simple query:

select nvl(ts.contents, 'not in I/O') "I/O type",
round(100*count(distinct sample_time)/
    sum(count(distinct sample_time))over(),1)
        "wall clock time (%)",
    sum(count(1))over(),1) "DB time (%)"
from dba_hist_active_sess_history ash,
dba_data_files df,
dba_tablespaces ts
where ash.p1 = df.file_id (+)
and df.tablespace_name = ts.tablespace_name (+)
and sql_id = :sql_id
and sql_exec_id = :sql_exec_id
group by ts.contents
order by count(distinct sample_time) desc;

In this query, :sql_exec_id is optional in case you’re only interested in a specific execution of a statement that was executed multiple times during the interval for which ASH is available. The query shows which fraction of query time is spent on reading data, and the breakdown of data read time by tablespace contents type (UNDO/PERMANENT/TEMPORARY).

Note that it returns two kinds of time: wall clock time and DB time. They are different for parallel queries and this difference can be very important (like in a recent case when failure to take this difference into account has prevented me from identifying the root cause early), but this is a broad topic which I’m hoping to cover in a separate post.

One limitation of this query is that it doesn’t show the overhead due to undo blocks read from the cache — from the database point of view, this will look as CPU time, without possibility to drill down any further (there are some ways to do that on the OS level though, e.g. in Alexander Anokhin’s blog; Craig Shallahamer also posted an article about that which is part 1 of a series).

Using results

Read consistency overhead cannot be eliminated: if you read and modify data at the same time, you’re going to pay the performance price. In this sense, this problem cannot be resolved. However, knowing how much of a price you are paying and for what is still extremely helpful:
– you know where performance overhead and/or instability is coming from which eliminates the risk of blaming some non-related activity for it
– you know how much performance benefits you can get if users move the reading and writing processes to different time slots (if that’s at all possible)
– in some special cases you can sacrifice consistency for performance by using temp tables — and you will be able to know how much you’ll save performancewise (although generally giving up read consistency is a very bad idea)
– in some cases you may want to consider various replication/redundancy mechanism to mitigate the problem, and this will provide you the “benefit” side of the cost/benefit analysis.


Flexible and powerful architecture of Oracle database allows to maintain read consistency without reading blocking writers. However, even without locking, there’s still a performance price for read consistency. This overhead cannot be eliminated, but it can be measured using ASH.

3 thoughts on “Read consistency overhead

  1. Hi Nikolay, We are facing similar UNDO read issue. In our case, its a JAVA batch job performing row by row insert from multiple concurrent sessions (as and when input file from customer is available )and it happens 24/7. So any long running queries running during this period(when high volume of inserts happening from Java) getting affected and went for high UNDO read slowing down the SELECT query execution time. and i was thinking apart from reducing the “select” query execution time, if something can be done with the DML.

    I was testing how much UNDO gets generated by the INSERT(Conventional VS Bulk VS direct path) in presence of index in my transaction table(tab1). Below is the results, and it seems to me like in case of both BULK and Direct path insert the UNDO generated is lesser compared to the row by row inserts. So want your opinion if we would really see this effect of lesser UNDO generation if we will make the insert BULK(with lesser frequency i.e. once or twice in an hour) rather row by row which is happening at current scenario 24/7? or missing anything here?

    For conventional insert:- (10000 individual inserts )

    Stats from v$transaction:-
    100000 1310 10480

    undo change vector size from v$mystat : 14250016

    For bulk insert – (INSERT INTO Tab1 Select * from stage_tab where rownum<10001)

    Stats from v$transaction:-
    28540 572 4576

    undo change vector size from v$mystat : 8340848

    For Direct path Insert:- (INSERT /*+APPEND*/ INTO Tab1 Select * from stage_tab where rownum<10001)

    Stats from v$transaction:-
    743 299 2392

    undo change vector size from v$mystat: 4672896

    Also you mentioned "in some cases you may want to consider various replication/redundancy mechanism to mitigate the problem" not sure, if you are pointing towards golden gate replication, but in this case also , it will replicate same DML in the target tables(as done by individual Insert statements), there by creating same amount of UNDO, correct me if wrong?

    1. Hi Kabi,

      thanks for your comment. I’m not surprised by your results as in general bulk DML operations tend to produce less overhead.

      As for various replication solutions — there is a number of different replication solutions including, but not limited to, GoldenGate replication. They have different mechanisms of synchronizing the target from the source. “Replaying” DML is one of such mechanisms, but there are others (e.g. based on redo application). Also, all kinds of replication solutions contain various optimizations (where small transactions are batched together to be applied more efficiently) so even though some residual read-consistency overhead may still be present, the size of the effect is usually much smaller compared to running a report on the original OLTP environment.

      Best regards,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s