sql - For a long running report, do I use a read only or serializable transaction? -

sql - For a long running report, do I use a read only or serializable transaction? -

i have long running study written in sql*plus couple of selects. i'd alter transaction isolation level consistent view on data. found 2 possible solutions:

set transaction isolation level serializable;

and

set transaction read only;

which 1 utilize study , why? performance implications? implications on other sessions (this production database).

please note question 2 options above, not various isolation levels.

does serializable blocks changes table queried report?

i naively assume read only little bit less stressful database, there no info changes expected. true, oracle take advantage of that?

this article oracle documentation gives lot of detailed info different transaction isolation levels. http://docs.oracle.com/cd/b10501_01/server.920/a96524/c21cnsis.htm

in example, sounds wanting serializable. oracle not block when reading data, using serializable in read-only query should not block queries or crud operations in other transactions.

as mentioned in other answers, using read only isolation level similar using serializable, except read only not allow inserts, updates, or deletes. however, since read only not sql standard , serializable is, utilize serializable in situation since should accomplish same thing, clear other developers in future, , because oracle provides more detailed documentation going "behind scenes" serializable isolation level.

here info serializable, article referenced above (i added comments in square brackets clarification):

serializable isolation mode provides more consistency protecting against phantoms [reading inserts other transactions] , nonrepeatable reads [reading updates/deletes other transactions] , can of import read/write transaction executes query more once.

unlike other implementations of serializable isolation, lock blocks read write, oracle provides nonblocking queries [non-blocking reads] , fine granularity of row-level locking, both of cut down write/write contention.

sql oracle