Thanks for the question, Sree.

Asked: February 01, 2011 - 11:36 am UTC

Last updated: October 23, 2012 - 11:10 am UTC

Version: 10g

Viewed 1000+ times

You Asked

Does Oracle need Rollback segment for a "Select". I thought it uses rollback segment only for DML operations but recently it failed with "Unable to extend rollback segment.." for a select statement?

Not sure If I can give you a screen shot with that error now. we added some space to that segment and itwent fine. we'll have to recreate that kind of scenario.
Is it possible to answer if yes/no ..i.e if it uses rollback segment or not for select queries?

and Tom said...

I didn't need a screen shot, all I asked was "was this for update - if it wasn't, it didn't happen the way you describe, if it was - it could be caused by the select yes"

In other words - it depends, but you haven't supplied sufficient data, the data i asked for - so I cannot say for sure.

A select uses undo - yes, but it only reads it, it does not generate it, it would not receive a "unable to extend".

Unless it was auditing or some other similar operation that caused it to extend (recursive sql). But then it would say "error at recursive sql" - and you would have mentioned that (I hope).

Unless it was a select for update - which works like an update in that it does update blocks to lock rows. It can generate undo and can hit an "unable to extend"

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> select used_ublk from v$transaction;

no rows selected

ops$tkyte%ORA11GR2> begin
  2          for x in (select * from t for update)
  3          loop
  4                  null;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
       621

that used 621 blocks of undo.

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What about "delayed block cleanout"

Robert, February 03, 2011 - 10:35 am UTC

Tom,

What about "delayed block cleanout"?
Does that generate undo?
(I suppose it doesn't or you would have mentioned it... but maybe you could explain why it does not).

Thanks,

Robert.

February 03, 2011 - 3:35 pm UTC

we don't have to roll it back - I've never measured it - but I (strongly) believe it only generates redo.

you don't have to "commit" it either.

question regarding "Rollback Segment for Select"

Anand, February 05, 2011 - 4:38 am UTC

Hi Tom,

I am a novoice in this field and have just started.

I read your comment as below in the above post -

"select uses undo - yes, but it only reads it, it does not generate it, it would not receive a "unable to extend". "

As far as I know to maintain the read consistency - for every select there will be UNDO created which as far as I understand will generate the UNDO which contradicts your post above.
As I am a novoice I am not sure which one is correct.

Can I please request you to elaborate on this - so that I can move out of this ambiguity.

Also under this situation - Why does the oracle not oeverwrite the UNDO blocks than throwing the Error mentioned in this article - "Unable to Extend Rollback Segment"

February 06, 2011 - 12:09 pm UTC

As far as I know to maintain the read consistency - for every select there
will be UNDO created which as far as I understand will generate the UNDO which
contradicts your post above.
As I am a novoice I am not sure which one is correct

that should read

as far as I know, to maintain read consistency, for every select - there may be UNDO READ in order to ROLLBACK changes that happened to a block since the query began. Queries use UNDO generated by modifications in order to UNDO those modifications and rollback the blocks to the point in time the query began".

Point me to anything in the Oracle docs or anything I've written that says "selects generate undo in order to process". I don't think you'll find it.

... Also under this situation - Why does the oracle not oeverwrite the UNDO blocks
than throwing the Error mentioned in this article - "Unable to Extend Rollback
Segment" ...

I don't think what you say happened the way you say it - simply because under every day normal circumstances - select's do NOT generate undo. You would have to be doing a select for update or something like that (show us the query - or answer us as to whether it was a select for update) in order for it to generate undo.

And if it were a select for update - we cannot simply overwrite those undo blocks - we are not done with them yet, we haven't committed.

Amount of Additional I/O?

thtsang, March 21, 2011 - 4:45 am UTC

Is there any formula on the amount of additional I/O (I think it will appear as consistent gets?) if UNDO is needed to construct the read-consistent image? Or is there a range?

The reason is that I have a SQL suddenly doing around 4 time consistent gets as normal, while the execution plan wasn't changed. I want to explore whether read-consistence is a cause.

Thanks very much.

March 21, 2011 - 10:11 am UTC

"it depends"

if the block my query needs was updated once since my query began, I'll need to do one extra IO - or maybe zero extra IO's (if the block was in the cache already - we multi-version in the cache)

if the block my query needs was updated 10,000 times since my query began, I'll have to do somewhere between 0 and 10,000 extra IO's - depending on what versions of the block is in the cache.

Can you test your query in isolation? In isolation - you won't see any read consistent reads to undo really - unless you yourself update the data.

Thank you!

thtsang, March 22, 2011 - 1:02 am UTC

Thanks very much. So I assume this is the cause of the additional gets. Because (I missed this point in last comment) there are other queries updating data in the table.

So it seems to fall into a cycle if many sessions concurrently update a table: when a transaction in session A could not commit in time, queries in session B would need to build a read-consistent image, a slow process. Then the transaction in session B also could not commit in time, further affecting other sessions. Is this scenario realistic?

March 22, 2011 - 7:29 am UTC

... when a transaction in session A could not commit in time, ...

No, they always commit in time. A read consistent version is made whenever you hit a block that has been MODIFIED since your query began - it might be committed OR uncommitted changes. The commit frequency isn't it.

Actually, it could be said

"So it seems to fall into a cycle if many sessions concurrently update a table, and you have inefficient - long running queries in session B, then queries in session B would need to build a read-consistent image - which is a great speed up over having to WAIT for the blocks to become available as other databases might, or a great speed up over having to use shared read locks to get correct answers".

How is UNDO generated?

Robert, March 22, 2011 - 11:08 am UTC

Tom,

Is UNDO caused/generated by BLOCK changes or by ROW changes?
If by BLOCK, then would inserts (into a block) generate UNDO and thus cause extra gets for a select on that same block?

Also, UNDO is produced/stored by ROW (not block), correct? And only the CHANGES as stored as undo, correct?

So when Oracle has to put together a read-consistent image of a block it has to do at least 2 reads and a write, correct? .... 1 read from original block, 1 read from undo, 1 write to buffer cache to assemble the new block?

Will you please put it all together?

Thanks,

Robert.

March 22, 2011 - 11:36 am UTC

Is UNDO caused/generated by BLOCK changes or by ROW changes?

both.

if we modify the block header - undo is generated.

If you update a row, the block body is modified and undo is generated.

Undo is whatever we need to store to undo a modification to a block.

So when Oracle has to put together a read-consistent image of a block it has to
do at least 2 reads and a write, correct? .... 1 read from original block, 1
read from undo, 1 write to buffer cache to assemble the new block?

No, not really. The minimum read would be one - we read a block from the cache, put it through the read consistency routines, and discover "it is read consistent". Else we might have to read one, two, or N more blocks to roll back the changes.

Do Inserts Generate Additional UNDO?

Robert wood, March 22, 2011 - 12:10 pm UTC

Tom,

Going back to thtsang's question.
You said "updates" to a block would generate additional undo that might increase gets for a query..... but what about INSERTS?
Could we get additional gets for a query for inserts into a block instead of just updates on a block?

Thanks,

Robert.

March 22, 2011 - 12:31 pm UTC

when I wrote:

if the block my query needs was updated once since my query began,

you could replace the word updated with MODIFIED and it would make more sense.

Anything that modifies the block image we are interested in would cause this, so yes, an insert can easily do this.

is it correct?

biju george, September 05, 2012 - 2:01 am UTC

Am I understanding it right!

An employee table with dept_no column. Say we have a billion records with dept_no = 10.
Session1 did an insert but not committed -- insert into employee(empname,dept_no) values ('biju',10);
Session2 did an insert but not committed -- insert into employee(empname,dept_no) values ('george',10);
Session2 started a select -- select * from employee where dept_no = 10;
Session2 reads from undo segment for read consistency to reflect this new uncommitted record by session2
Session1 issues a commit;
Session2 reads from undo segment for read consistency to reflect this new record committed by session1 (or this would be ignored as the commit was done after the select started in session2)

If this sounds silly, please bear with me :-)

September 10, 2012 - 7:30 pm UTC

session 1 and session 2 would NOT see each others work since their queries started before the data was committed.

each sessions query will only see the data that was committed in the database (or added by themselves, a transaction may see its own data modifications) when their statement was opened.

snapshot too old??

S rahul, October 19, 2012 - 2:46 am UTC

We opened a cursor on a table of 20M records. It is a select statement using analytical function. It returns approx 7M records. Then it inserts into a temporary tables using bulk insert with a limit of 50000 i.e we are committing after every 50000 records. This block of code takes around 4-5 hrs. Sometimes it comes out with error snapshot too old. We have increased the undo tablespace as well but still it gives error. Why it gives this error as I assume 50000 limit is enough to clear undo tablespace?What could be the possible reason for this?
Thanks

October 23, 2012 - 11:10 am UTC

why are you using procedural code at all? just to make it slower?

why not just

insert into t select ....;

???????

you would need to set your undo retention to 6 hours.

you would need to ensure you don't prematurely expire undo (you would have to allocate enough undo to last 6 hours)

if you want to run a query that will run for 4-5 hours.

Breadcrumb

Question and Answer