Database Reference
In-Depth Information
The only solutions to this problem are to either make it so that the manual undo segments are sized so that they
wrap only every six to ten minutes, or make it so your queries never take more than two to three minutes to execute.
The first suggestion is based on the fact that you have queries that take five minutes to execute. In this case, the DBA
needs to make the amount of permanently allocated undo two to three times larger. The second (perfectly valid)
suggestion is equally appropriate. Any time you can make the queries go faster, you should. If the undo generated
since the time your query began is never overwritten, you'll avoid ORA-01555 .
Under automatic undo management, things are much easier from the ORA-01555 perspective. Rather than having
to figure out how big the undo space needs to be and then preallocating it, the DBA tells the database how long the
longest-running query is and sets that value in the UNDO_RETENTION parameter. Oracle will attempt to preserve undo
for at least that duration of time. If sufficient space to grow has been allocated, Oracle will extend an undo segment
and not wrap around—in trying to obey the UNDO_RETENTION period. This is in direct contrast to manually managed
undo, which will wrap around and reuse undo space as soon as it can. It is primarily for this reason, the support of the
UNDO_RETENTION parameter, that I highly recommend automatic undo management whenever possible. That single
parameter reduces the possibility of an ORA-01555 error greatly (when it is set appropriately).
When using manual undo management, it is also important to remember that the probability of an ORA-01555
error is dictated by the smallest undo segment in your system, not the largest and not the average. Adding one “big”
undo segment will not make this problem go away. It only takes the smallest undo segment to wrap around while a
query is processing, and this query stands a chance of an ORA-01555 error. This is why I was a big fan of equi-sized
rollback segments when using the legacy rollback segments. In this fashion, each undo segment is both the smallest
and the largest. This is also why I avoid using “optimally” sized undo segments. If you shrink an undo segment that
was forced to grow, you are throwing away a lot of undo that may be needed right after that. It discards the oldest undo
data when it does this, minimizing the risk, but still the risk is there. I prefer to manually shrink undo segments during
off-peak times if at all.
I am getting a little too deep into the DBA role at this point, so we'll move on to the next case. It's just important
that you understand that the ORA-01555 error in this case is due to the system not being sized correctly for your
workload. The only solution is to size correctly for your workload. It is not your fault, but it is your problem since you
hit it. It's the same as if you run out of temporary space during a query. You either configure sufficient temporary
space for the system, or you rewrite the queries so they use a plan that does not require temporary space.
To demonstrate this effect, we can set up a small, but somewhat artificial test. We'll create a very small undo
tablespace with one session that will generate many small transactions, virtually assuring us that it will wrap around
and reuse its allocated space many times—regardless of the UNDO_RETENTION setting, since we are not permitting
the undo tablespace to grow. The session that uses this undo segment will be modifying a table, T . It will use a full
scan of T and read it from “top” to “bottom.” In another session, we will execute a query that will read the table T via
an index. In this fashion, it will read the table somewhat randomly: it will read row 1, then row 1,000, then row 500,
then row 20,001, and so on. In this way, we will tend to visit blocks very randomly and perhaps many times during
the processing of our query. The odds of getting an ORA-01555 error in this case are virtually 100 percent. So, in one
session we start with the following:
EODA@ORA12CR1> create undo tablespace undo_small
2 datafile '/tmp/undo.dbf' size 2m
3 autoextend off
4 /
Tablespace created.
EODA@ORA12CR1> alter system set undo_tablespace = undo_small;
System altered.
Search WWH ::




Custom Search