Re: Why ORA-1555 snapshot too old.

"Chuck" <chuckh_at_softhome.net> wrote in message news:<ap45bs$rqa24$1_at_ID-85580.news.dfncis.de>...
> This pl/sql block does not do any DML. The session he logs in with does no
> DML. All it does is run a pl/sql block with a select inside a loop. If my
> understanding is correct, unless I issue a "set transaction read only",
> read consistency should start all over for each new select. That's the way
> it works in pure SQL. Does that not hold true inside of pl/sql?

It does. Read-consistency is statement-based. However, you can get ora-1555 even with relatively short selects if:

table gets constantly updated, and this is the case,

updating (inserting) transactions are short and many, and this is the case.

The problem here is that rollback segment gets overwritten before select statement finishes. This happens besause every transaction creates undo copy of the database block. If you have many small transactions, you have many undo copies of database blocks. You can also have many undo copies of the same block.

The cure would be to increase rollback segment size, maybe increase optimal size or remove it altogether. The best cure would be to use 9i undo tablespace.

On more thing here. In many places, developers intentionally divide transactions, sometimes on DBAs insistence, because rollback segments can't handle big transactions. This practice is stupid. You don't have to spend lots of money to increase rollback segments size, you don't have to spend a lot of work doing that. If you try to live with small rollback segments, you get ORA-1555 once in a while, no matter what.

>
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3DB57845.B7BCD4D5_at_exesolutions.com...
> [snipped]
> > A transaction starts with the first DML statement and ends with either
> COMMIT
> > or ROLLBACK. In a loop you can any darn thing you want ... and it is one
> > transaction until you end it.
> >
> > Which does not mean that the solution is to put COMMIT into your loop.
> That is
> > almost always a bad idea. Far better to increase the size of your rollback
> > segments.
> >
> > Daniel Morgan
> >
Received on Wed Oct 23 2002 - 11:00:13 CDT