SET TRANSACTION...Read Consistency

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

SECTION 1:

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 ?

SECTION 2:

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).

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 Received on Fri Aug 11 1995 - 00:00:00 CEST