Showing results for 
Search instead for 
Did you mean: 

Oracle 9i and auto UNDO, auto PGA

Go to solution
Henrique Silva_3
Regular Advisor

Oracle 9i and auto UNDO, auto PGA

Internal HP app ( SGBU OVSD ), 24/7, 100 GB Oracle instance, with growth forecast of 100% by the end of the year. MCSG environment ( looking into RAC ).

Since I am of the believer that if it is not broken do not change it, we have migrated to Oracle 9i before the end of the year, but did not have the proper time to test out the various 9i goodies.

Had some performance issues with 8i, related to query response time, which were solved somewhat by making sure we had CBO and stats analyzed more often, however, the SGBU lab had promised that the performance would be even better under 9i, which have not materialized, in part because we are basically running this instance under 9i with mostly 8i init.ora parameters.

So, the plan is to start playing with these changes on our staging environment, since we have very few chances to bounce the prod DB instance.

We are thinking about setting the sga_max_size ( in hind sight, we should have done that one at the migration time ) and do the AUM.

I have been reading about AUM but one thing that I am not seeing is how to initially sized the UNDO tablespace. This does not seem that important, as you can probably size it as big as your RBS ( we have 50 RBSs, 8 MB each ), so, you could have your UNDO tbs being 400 MB or a bit more for safety, and then, monitor v$undostat to make sure things are going well ( after 24 hours of usage ). Is this an acceptable assumption ?

Also, what is the experience out there about retention time ? Is the default, 900 secs ok, or have you found that you ahd to change that parameter as well ?

And finally, what is the perception as far as auto PGA managment ? Any good/ad experiences with it ?

Later down the line, we will look into buffer pools, latch/lock issues and finally, the worse of all, sql application code.

Thanks for any input here.


"to be or not to be, what was the question ???? "
Julio Yamawaki
Esteemed Contributor

Re: Oracle 9i and auto UNDO, auto PGA


About AUM:
1. Size the tablespace same size of rollback tablespace
2. first try of UNDO_RETENTION=900, normally it´s not enough, so, look at alert.ora to see same ORA-1555 errors. If you find these, take a look at v$undostat, field maxquerylen and set UNDO_RETENTION to this until you see no more ORA-1555 errors
3. for SGA_MAX_SIZE, set to a value like total memory - operating system memory
4. Automatic PGA = we use, but in our case, it´s not possible to see any performance gain, I think it depends on the type of application that you will use

Indira Aramandla
Honored Contributor

Re: Oracle 9i and auto UNDO, auto PGA

Hi Henrique,

1. About AUM and the sizing of the UNDO tablespace

To distinguish between the two types of segments, ROLLBACK segments are called UNDO segments when AUM is enabled. If you decide to use AUM, you have to create at least one UNDO tablespace to store the undo segments automatically created.

Sizing an UNDO tablespace requires three pieces of data.

(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

Attached is a document to discuss the sizing of UNDO tablespace.

2. UNDO retention
UNDO retention is there so one know how long one can keep the data in the rollback segments before it is overwritten by newer transactions. As undo_retention controls how long commited data is saved. If you 'set it as high as possible', your undo tablespace could end up being enormous. That being said, there are a couple of points to consider when setting it:

You want to set the undo_retention large enough so that your undo segments grow large enough to provide a read consistent view to your longest running statement. Another point to consider is in 9i you have flashback queries, where to can query the data as it existed at a certain point in time. You'd want to have a good idea of how far back you wanted to support and how large your undo tablespace could potentially get

3. Sga_max_size
Sga_max_size allows you to change the size of the shared pool, buffer cache, larger pool and the process private memory without shutting down the database. It's like an upper bound on the SGA. It allows you to set at run time how much memory is used for SGA. if you are going to change SGA size dynamically you can set it. IF SGA_MAX_SIZE value is less than the sum of all components specified or defaulted at initialization time, then the setting in the initialization parameter file is ignored.

4. Auto PGA management
Automatic PGA Memory Managment in 9i refer to Note:223730.1 inMetalink.

Indira A
Never give up, Keep Trying
Honored Contributor

Re: Oracle 9i and auto UNDO, auto PGA

hi henrique,

to you your question about the undo retention period:

You should ask yourself the following questions....

How long do you need it for? You would want undo to be retained for at least as long as your longest running query. Also, if you have any "flashback" requirements, they must be considered as well.

You would like your undo to be as small as possible BUT as big as it needs be.

This is because if it is:
a. TOO SMALL: ora-1555 snapshot too old.
b. TOO LARGE: performance issue.

Hence, you should figure out your long running query times and start with that.

hope this helps!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

Re: Oracle 9i and auto UNDO, auto PGA

With Oracle9I, most of the buffer areas in the SGA can be changed dynamically. With this feature SGA configuration can be changed without stopping the instance.

sga_max_size: The size of the physical memory occupied by the SGA can be calculated approximately with the following formula:

sga_max_size = db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1m

Automatic PGA Tuning provides following advantages:

- Simple PGA memory tuning.
- Reduced administration work for the DBA (only one parameter).
- The total PGA memory is easier to quantify than the per sort area per dedicated database shadow process.
- No recalculation of the sort_area_size is necessary if the number of work processes increases.
- Better memory use: Physical memory is only allocated by the shadow process that actually needs the memory for a sort or a similar process.
- Higher system performance, shorter response times, more efficient memory usage; The ideal situation, in which the optimum amount of PGA memory is available to the Oracle shadow process, means that SQL queries are processed faster. The response time (OLTP) decreases or the system performance (OLAP, DSS) increases.

However, one possible disadvantage is that too much PGA memory may be reserved in advance and consequently wasted if the PGA_AGGREGATE_TARGET calculated is too high. The automatic PGA is activated using the following parameters:


2. PGA_AGGREGATE_TARGET=nnnM/G (MB or GB) (Limit=10M4096G)

Estimating a good start value of PGA_AGGREGATE_TARGET

Method 1

For Data Warehouse: PGA_AGGREGATE_TARGET = * 40%


Method 2: Use the following formula as an additional guide value and lower limit:

pga_aggregate_target >= (sort_area_size+hash_area_size+ bitmap_merge_size+create_bitmap_area_size) * (#shadow processes / 10)

You can use the size of the previous rollback segment tablespace as an indicator for the lower limit for the size of the Undo tablespace.

The data files for the Undo tablespace should not allow an AUTOEXTEND. Otherwise, with a large undo_retention time, the Undo tablespace could become unpredictably large (for a long transaction).

Ideally, you should select undo_retention at a size that is large enough for the Undo tablespace to be used correctly (approx.If the undo_retention is too small, space remains unused in the Undo tablespace and is consequently wasted.However, if the undo_retention you have selected is too large, this means you want to have an undo_retention that you would not be able to keep within the specified Undo tablespace size due to the given change rate in the system.

'undo_retention' can be changed dynamically. To be able to keep to a certain URT ('Undo Retention Time'), the Undo tablespace must be correspondingly large enough to retain the changes to active transactions.If the Undo tablespace is too small, Undo information is overwritten (Data consistency precedes read consistency). It is highly probably that this will result in the appearance of "snapshot too old" messages.

A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Honored Contributor

Re: Oracle 9i and auto UNDO, auto PGA

hi again,

a few more notes.

If you set a high undo retention means you are saying to Oracle -- PLEASE try to keep this undo for N hours. Oracle will extend undo segments as much as possible as it needs space in order to accomodate that request. If it cannot extend anymore (undo tablespace is full and no autoextend is possible on the datafiles) -- it will prematurely expire the oldest undo data in that segment -- meaning the undo retention request you made will not be observed.

With undo retention -- the option to reused already allocated extents is not so simple. You will reused an already allocated extent IF it contains no transaction data that falls in the retention period. If you have a 1 hour retention and you find an already allocated undo extent that contains undo for a transaction performed 59 minutes ago -- Oracle will not use that extent unless *forced* to by an out of space condition.

Setting undo retention artifically high will cause undo segments to grow until there is no more space available. If you set up a 32gb undo segment and set

a) undo retention to 1 hour
b) undo retention to 1 day

come back in a day and tell me how much undo data you have in each case. One would *probably* have much less than 32gb allocated (i'm assuming you generate less than 32gb of undo per hour) the other would probably have 24 times or more as much undo as a) does.

Finally, if you want to know if sufficient undo space has been allocated, you should monitor v$undostat to see if you are prematurely expiring undo extents due to lack of space.

hope this helps too!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)