Re: Confused about Queries and Rollback Segments!

From: Fred Petillot <fpetillo_at_fr.oracle.com>
Date: Thu, 02 Dec 1999 12:19:42 +0000
Message-ID: <384663DD.71A00A5B@fr.oracle.com>

Jonathan E Miller wrote:

> When does a simple query acquire a rollback segment? Does it always need one
> or only when there are other transactions ocurring that may be updating the
> same rows that the query is returning?

I'm not sure of what you mean by "acquiring". To acquire a rollback segment, you

have to have an entry in v$transaction (XIDUSN then shows in which rbs you're working).
Now, a simple select may access RBS for two purposes:

building consistent read images,
doing cleanout.

Basically, locks for each line in a block point to a transaction entry in the bloc Interested Transaction List. The TX entry points to the rbs it's working in.
When a transaction commits, it doesnt revisit each block in order to clean those

pointers out. It just update the TX entry in the rbs header. The cleanout operation
is left for subsequent queries visiting the block. Upon seeing that a row it's interested in appears to be locked, the query visits the rollback segment pointed
by the relevant TX in the ITL. In the rbs transaction list, it can check that the
TX was committed or not and then either build a consistent image or do the block cleanout.

> We have frequentlly run into the
> snapshot too old problem seemingly when the only thing running is a large
> query.

There's an interesting set of public Oracle Notes that describe how the above mechanism can lead to 1555.

        ORA-01555 "Snapshot too old" - Overview             <Note:10630.1>
        ORA-01555 "Snapshot too old" - Detailed Explanation <Note:40689.1>
        ORA-01555 "Snapshot too old" - Causes and Solutions <PR:1005107.6>

Cheers,

Fred

>
>
> Thanks
> Jonathan

+-------------------------------------+----------------------------+
| Fred Petillot                       |     fpetillo_at_fr.oracle.com |
| Oracle France - Support Technique   |            +33 1 4762 8201 |
| 65, rue des Trois Fontanot          |            +33 6 8089 5135 |
| 92732 Nanterre Cedex                |                            |
+-------------------------------------+----------------------------+

Received on Thu Dec 02 1999 - 06:19:42 CST