Tunning undo

[dba]

HI all
i want to tune my undo tablesapce
Beacuse ORA-1555

Pl help me

[Sergei - Lab128]

I assume you are using Automatic Undo Management. ORA-1555 happens
when a long running query needs to see data as of time when the query
started but Oracle already overwritten undo information because it
uses space in UNDO tablespace in a circular buffer fashion.

Options available:
- run the query when transactional activity is low (it takes longer
time to overwrite data in UNDO);
- optimize the query to run faster.

If these options are not viable, then consider increasing UNDO
tablespace. I assume that datafile(s) in UNDO has fixed size. If so,
then run this query: "select avg(tuned_undoretention) from V
$UNDOSTAT;" This will tell roughly how long data in UNDO is kept
before overwriting. Compare that to the time needed for your long-
running query. Increase UNDO proportionally.

If the datafile(s) in UNDO are not fixed size, then check
"undo_retention" parameter. It can be too low. Change "undo_retention"
to the typical time needed for you long-running query and add extra
50%. This can cause your UNDO tablespace to grow.

Let me know if you need more help.
Sergei

[dba]

Hi
Thanks for replay

for a production server what is exact undo table space size?

and i want to check the current undo data how it possible ?

i want to see that data and the details that means which user
affecting that data
tablespace datafile and table belongs to how i check?

[Sergei - Lab128]

The size of UNDO should be big enough to provide time window to look
back for all long running queries running in the database. Please
note, that some queries don't need undo data. For example, if you run
a report on previous month and your tables are partitioned by month,
all DB blocks needed should be unchanged while the query run.

The exact answer on your ora-1555 depends on many things, I just
outlined the general approach. You may not need to change UNDO at all.
(How big it is, by the way?) I would focus on the query that received
1555 and analyze database activity from other sessions at the time of
execution. Did you have many small transaction running at the same
time or there were few big transactions? Do you have transactional DB
or data warehouse?

In Lab128, you can analyze a history of undo activity in "RB Writes"
in the Main window. Click on this graph and in the new window scroll
back in time. If you have periods of high activity - these are most
vulnerable to ora-1555. Select a period of time and you will see
Average for that period. Can you tell what sustained rate is during
high activity?

Did you run the query "select avg(tuned_undoretention) from V
$UNDOSTAT" ? what was the number?