Database Reference
In-Depth Information
higher and let Oracle take care of figuring out how much undo to retain (this is the suggested approach; it's much
easier than trying to figure out the perfect undo size yourself ) or resize your undo segments and make them larger
(or have more of them). You need enough undo configured to last as long as your long-running queries. The system
was sized for the transactions that modify data—you forgot to size for the other components of the system.
With Oracle9 i and above, there are two methods to manage undo in the system:
Automatic undo management : Here, Oracle is told how long to retain undo for, via the
UNDO_RETENTION parameter. Oracle will determine how many undo segments to create based
on concurrent workload and how big each should be. The database can even reallocate
extents between individual undo segments at runtime to meet the UNDO_RETENTION goal set by
the DBA. This is the recommended approach for undo management.
Manual undo management : Here, the DBA does the work. The DBA determines how many
undo segments to manually create, based on the estimated or observed workload. The DBA
determines how big the segments should be based on transaction volume (how much undo is
generated) and the length of the long-running queries.
Manual undo management, where a DBA figures out how many undo segments to have and how big each should
be, is where one of the points of confusion comes into play. People say, “Well, we have XMB of undo configured, but
this can grow. We have MAXEXTENTS set at 500 and each extent is 1MB, so the undo can get quite large.” The problem
is that the manually managed undo segments will never grow due to a query; they will grow only due to INSERT s,
UPDATE s, and DELETE s. The fact that a long-running query is executing does not cause Oracle to grow a manual undo
segment to retain the data in case it might need it. Only a long-running UPDATE transaction would do this. In the
preceding example, even if the manual undo segments had the potential to grow, they will not. What you need to do
for this system is have manual undo segments that are already big. You need to permanently allocate space to the
undo segments, not give them the opportunity to grow on their own.
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.
 
Search WWH ::




Custom Search