Thanks for the question, Magnus.
Asked: March 18, 2016 - 5:43 pm UTC
Last updated: March 19, 2016 - 6:45 am UTC
Version: 12c
Viewed 1000+ times
You Asked
Is there any kind of performance penalty for starting a read only transaction compared to starting a read write transaction? If so, what is the penalty (I/O, CPU)?
The reason I am asking this question is because we are converting a database from Oracle Rdb to Oracle Database, and in Rdb starting a read write transaction is almost instantaneous while starting a read only takes a small amount of time. The reason for this is that in Rdb, starting a read write transaction only requires a few CPU cycles while starting a read only transaction requires a write to disk which takes at least a few milliseconds.
Because of this, we found many years ago that for code that does a huge number batches of read only transaction, we could make significant performance improvements by doing read write transactions instead of read only (even though no writes are done). We are aware that we give up the read consistency by doing this but that's fine because those transactions typically just start a transaction, read one record and then commit.
So, the question is if there is a performance reason to use read write transactions for read only operations in Oracle Database as well, assuming you don't care about read consistency?
and Connor said...
I am unaware of any performance penalty with read only operation, other than the cost of issuing the "set transaction read only" which might involve (for example) a network trip etc.
I would contend that the overwhelming majority of Oracle database customers simply run their statements and let the database handle all of the transaction and read consistency semantics, ie, queries just run as queries, and insert/update/delete initiate transactions.
This is not to say there is not a place for read only data (it can be useful for reducing backup times etc).
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment