Friday, October 15, 2010

To size undo tablespace properly

To properly size the UNDO tablespace, it is suggested that you actually observe the workload through the system. After a good workload has been given to the system, you can use the following code to ask Oracle to determine the proper sizing for an UNDO tablespace.
SQL> SET SERVEROUTPUT on
SQL> DECLARE
utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
dbms_output.put_line('undo size : '||utbsiz_in_MB||'MB');
end;
/
undo size : 2800MB
PL/SQL procedure successfully completed.
Run this package during peak hours ie when the load is more in this case it is suggested that we need to set undo tbs to 2800M

No comments: