Freeze DB view

Did you freeze your DB view? Or maybe by it happened by chance!!!...

Did you ever hit DB view freeze because of "set transaction read only".

Let us see what it does.

Under a test DB user and test table ABC which consist only one record. Start a read-only transaction.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from abc;

         P          Q

---------- ----------

         1          1

SQL> set transaction read only;

(Kept event 10046 level 12 trace for this session to see all background happening more detail)

*** 2019-05-16 15:46:33.912

WAIT #4574320352: nam='SQL*Net message from client' ela= 102202601 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208900920716638

CLOSE #4574320352:c=24,e=74,dep=0,type=1,tim=208900920717192

=====================

PARSING IN CURSOR #4574319096 len=25 dep=0 uid=56 oct=48 lid=56 tim=208900920721932 hv=580822032 ad='7000101abd74988' sqlid='9hb4u1cj9x90h'

set transaction read only                                                            --->>>>>> executed read only transaction.

END OF STMT

PARSE #4574319096:c=1149,e=4539,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=208900920721928

EXEC #4574319096:c=31,e=170,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=208900920722825

WAIT #4574319096: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208900920723039

From another parallel session of same DB user, delete the record and commit.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> delete abc;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from abc;

no rows selected

However, ready only session still shows the data. Strange…….

SQL> select * from abc;

         P          Q

---------- ----------

         1          1

*** 2019-05-16 15:47:37.956

WAIT #4574319096: nam='SQL*Net message from client' ela= 64036185 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208900984759358

CLOSE #4574319096:c=10,e=38,dep=0,type=0,tim=208900984759880

=====================

PARSING IN CURSOR #4574319096 len=17 dep=0 uid=56 oct=3 lid=56 tim=208900984760228 hv=3207421026 ad='70001017ed8ab68' sqlid='8619rrqzkur32'

select * from abc

END OF STMT

PARSE #4574319096:c=37,e=153,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3116099409,tim=208900984760226

EXEC #4574319096:c=22,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3116099409,tim=208900984760682

WAIT #4574319096: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208900984760802

FETCH #4574319096:c=37,e=148,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3116099409,tim=208900984761051

WAIT #4574319096: nam='SQL*Net message from client' ela= 299 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208900984761447

FETCH #4574319096:c=2,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3116099409,tim=208900984761572

STAT #4574319096 id=1 cnt=1 pid=0 pos=1 obj=285003302 op='TABLE ACCESS FULL ABC (cr=3 pr=0 pw=0 time=133 us cost=2 size=26 card=1)'  --->>>>>> SQL Full Table Scan on table ABC and data read from UNDO.

WAIT #4574319096: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208900984761808

As soon as a COMMIT (or ROLLBACK) in the read-only session, this release the session from frozen view.

SQL> commit;

Commit complete.

*** 2019-05-16 15:48:53.687

WAIT #4574319096: nam='SQL*Net message from client' ela= 14160546 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208901060490301

CLOSE #4574319096:c=13,e=53,dep=0,type=3,tim=208901060490868

=====================

PARSING IN CURSOR #4574306976 len=6 dep=0 uid=56 oct=44 lid=56 tim=208901060491131 hv=3480936638 ad='0' sqlid='23wm3kz7rps5y'

commit

END OF STMT

PARSE #4574306976:c=25,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=208901060491131

XCTEND rlbk=0, rd_only=1, tim=208901060491612                                                            --->>>>>> Notice a COMMIT for rollback the transaction.

EXEC #4574306976:c=63,e=231,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=208901060491773

WAIT #4574306976: nam='SQL*Net message to client' ela= 11 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208901060491895

And, now no data returned by the SQL.

SQL> select * from abc;

no rows selected

*** 2019-05-16 15:49:06.075

WAIT #4574306976: nam='SQL*Net message from client' ela= 12386034 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208901072878027

CLOSE #4574306976:c=5,e=44,dep=0,type=1,tim=208901072878713

PARSE #4574319096:c=13,e=138,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3116099409,tim=208901072879086

EXEC #4574319096:c=17,e=125,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3116099409,tim=208901072879374

WAIT #4574319096: nam='SQL*Net message to client' ela= 11 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208901072879596

FETCH #4574319096:c=28,e=216,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=3116099409,tim=208901072879991

And, SQLPLUS clean exit does a read-only commit (as part of SQLPLUS inbuilt functionality since there was no change in the current session after the COMMIT)

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

*** 2019-05-16 16:09:54.094

WAIT #4574319096: nam='SQL*Net message from client' ela= 1248015757 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=208902320896037

XCTEND rlbk=0, rd_only=1, tim=208902320901060                                      --->>>>>> Notice a rollback since previous transaction was read only.

CLOSE #4574319096:c=9,e=25,dep=0,type=0,tim=208902320901300

Here is how you can distinguish between a COMMIT or ROLLBACK in the event 10046 traces.

XCTEND rlbk=%d rd_only=%d

----------------------------------------------------------------------------

XCTEND A transaction end marker.

rlbk 1 if a rollback was performed, 0 if no rollback (commit).

rd_only 1 if transaction was read only, 0 if changes occurred.

XCTEND rlbk=0, rd_only=0	Commit followed by a change
XCTEND rlbk=0, rd_only=1	Commit but transaction was read only

The SET TRANSACTION statement begins a read-only or read-write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables.

 SET_TRANSACTION

 {READ_ONLY
 | READ_WRITE
 | ISOLATION_LEVEL {SERIALIZABLE | READ_COMMITTED}
 | USE_ROLLBACK_SEGMENT rollback_segment_name}
 [ NAME 'text' ];

But…

if UNDO is not properly sized then your query could hit "ORA-01555: snapshot too old:" which is a side effect of the delayed block cleanout and the solution is to have correctly sized UNDO for your entire system. Basically, UNDO is for read and write, not just only write.

Longest running query and longest running transaction will help to correctly size the UNDO.

From Oracle 9i, this is simplified as you may use an UNDO tablespace instead of rollback segments which allows to configure a retention period (how long do you want to keep undo) and it sizes as well.

Reference:-

ORA-01555 "Snapshot too old" - Detailed Explanation (Doc ID 40689.1)

Hope this help to understand side effect of read only transaction.

Any query, please let me know.