Re: ORA-01555 Snapshot too old

When Oracle is executing a single large transactions, it keeps track of a blocks that have been changed by the transaction up to a limit, and then stops trying. The limit is 10% of the value of db_block_buffers - so that is the first critical number that goes into your estimate.

However, that 10% of tracked blocks will be eroded on a busy system, because any block from that list that has been flushed from the buffer (having been written to disc) will not be re-read for cleaning at commit time; moreover, I believe that any block that has been written, even if it has not been flushed from the buffer, will not be cleaned (Steve Adams has a note on that, I think, but I may be mis-quoting it - see www.ixora.com.au)

To minimise the impact, if the large transactions are easily contained, then you could consider a strategy of following then with lines like:

    select /*+ full(t) */ count(some_column) from tablet t;

    select /*+ index_ffs(t, ind_x) */ count(*)     from table t
    where index_col is not null;

and other queries that will guaranteeably scan all the dirty blocks serially and force them to be cleaned.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Ralf Pickart wrote in message
<78ef525e.0111070755.5573ca68_at_posting.google.com>...


>Hi,

>

>I've a question about ORA-01555 and long running queries.

>

>We get this error on a select because of the delayed block cleanout

>after a large insert into a table. Obviously Oracle uses the delayed

>block cleanout only on long running transactions.

>

>Now we are looking for a workaround for this really nerving error and

>we're asking us what is a long running transaction? Especially we want

>to know how long a transaction may be before Oracle delays the block

>cleanout? Or is there another way to avoid this error?

>

>Who knows the answer?

>

>Regards

>Ralf