Re: SET TRANSACTION...Read Consistency

darryl snedeker <darryl.snedeker_at_amd.com> wrote:

>COMMIT;
>SET TRANSACTION READ ONLY;
 
>SELECT * FROM MOTHER_SHIP; /* Assume Mother_ship is HUGE */

>SECTION 1:
>----------
>From the scripts above, please describe the benefit of using READ ONLY vs. USE ROLLBACK SEGMENT
> RS_HUGE where RS_HUGE is a huge rollback segment.

Using a rollback segement and SET TRANSACTION READ ONLY have *nothing* to do with each other.

SET TRANSACTION READ ONLY allows you to perform a transaction that is completely serializable (you get repeatable reads, you do not ever get phantom reads). If you SET TRANSACTION READ ONLY and issue 5 queries, those queries will be consistent with eachother. For example

set transaction read only;

SELECT COUNT(*) from emp;
SELECT COUNT(*) FROM DEPT;
select count(*) from emp;

You will always get the same answer for queries 1 and 3 above, even it someone else deletes evey row in the emp table and commits while you execute the select count(*) from dept.

Typically you get STATEMENT level read consistency with Oracle. By using READ ONLY, you get transaction level consistency across statements.

These consistent reads are constructed by using information in the rollback segments, so in order to support long running, read only transactions, you would need to have larger then normal allocated rollback segments so that all of the needed rollback data was there for the read only transaction.

>From the text, I cannot discern what the differences would be. In both situations, I require
>read consistency and I am not making any changes to the database, so if there is a distinction,
>what would they be, if any ?

You use READ ONLY for transaction level consistency.

You use ROLLBACK SEGMENT when you have a specialized need to use a specific RS in an update transaction (for example, every weekend you delete 1,000,000 rows and create a special rollback segment just to hold it. You would use that special rollback segment for this transaction).

>SECTION 2:
>----------
>On the same note, consider the following code;
 

>COMMIT;
 
><pl/sql code>
 

>Let's assume that the pl/sql code includes code for inserting, updating, or deleting BUT for
>the purpose of this discussion, never satisfies the conditions to do so and only performs
>SELECTs. In terms of processing, these transactions should behave, with respect to Rollback
>Segment management, like any other SELECT script because the conditions to perform DMLs are
>never met and the need for facilitating read-consistency is still required. I would NEVER be
>successful if I utilized 'SET TRANSACTION READ ONLY' because the pl/sql code may, one day,
>perform a DML. The RDBMS will 'randomly'(?) assign this transaction to a Rollback segment and
>process the pl/sql.
 

>For the sake of this question, is there any measureable gain or loss with respect to using READ
> ONLY or not. It is my hope that the reponse to this question will provide me with a better
>understanding or hypothesis of what purpose 'READ ONLY' serves as well as how this may impact
>performance on extremely large queries (100's of millions of rows).

ROLLBACK segments are used in Oracle for two things--

A SELECT may access EVERY available rollback segment to perform consistent reads. A select may in fact have to access every rollback segment to process a query if lots of people are updating the table. USE ROLLBACK segement has *no* effect on the processing of a QUERY. If you issue set transaction read only, then all queries in a transaction will behave as if they were executed simultaneously at the beginning of the transaction (they will be consistent with respect to each other and will use rollback segments to do this)

An Insert/Update/Delete will use ONE rollback segment to store its undo information. All of the Insert/update/delete statements in any given transaction will use the SAME rollback segment. USE ROLLBACK segement is valid and affects the processing of Insert/Update/Delete statements.

>The response to these questions are not of an urgent need and it is my hope all responses
>address the questions.
 

>Thanks for all contributions.

>-D

Hope this helps...

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Sat Aug 12 1995 - 00:00:00 CEST