michaelnewport (AT) yahoo (DOT) com (michael newport) wrote in message news:<63b202d.0408101012.1d178269 (AT) posting (DOT) google.com>...
Mike, sometimes you get a "snapshot too old" because your rollback
segments really are too small. How large are your segments in
relation to the amount of data (include index changes) you are trying
to change. Another common cause is too frequently committing during a
process that reads and updates the same table.

Unlike say DB2 UDB which says to commit often Oracle is designed so
that you should do large amounts of work between commits if concurrent
access requirements allow. If your are updating then OLTP activity
against the same table limits how long you want to hold a modified row
while inserts have no such concurrent activity and can be held for a
longer period without affecting any other session.

Also as you mentioned speeding up the processing of the query will
also reduce the chance of a snapshot too old error if the cause of the
problem is other sessions changing and committing data you need in
very small transactions. How does the explain look? Does the join
order appear to be the best order? Is the join method the best choice
for the data in question? Are you dealing with skewed data values?

If you are using an UNDO tablespace instead of manually configured
rollback setting the undo_retention period to be longer may help you
out.

HTH -- Mark D Powell --


HTH -- Mark D Powell --