Prepare by: Nurullah Sharif

Scope: Undo Advisor

Undo Advisor help to estimate the undo tablespace size and also advise of undo retention.

SQL> @db
NAME      OPEN_MODE
--------- --------------------
COLLPROD  READ WRITE

Undo retention is 900 sec which 15 min

SQL> sho parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

Undo tablespace Size:

SQL> @tblspc
TABLESPACE_NAME                ALLOCATED_MB    USED_MB FREE_SPACE_MB PERCENT_USED
------------------------------ ------------ ---------- ------------- ------------
SYSAUX                                  300    -88.625       388.625       -29.54
SYSTEM                                  300    -55.625       355.625       -18.54
DATA                                  30720        109         30611          .35
UNDOTBS2                                125       5.75        119.25          4.6
UNDOTBS1                                140    18.8125      121.1875        13.44
USERS                                     5     1.3125        3.6875        26.25
SYSTEM                                  870    514.375       355.625        59.12
SYSAUX                                 1350    961.375       388.625        71.21

8 rows selected.

Note: To make the undo tablespace fixed-size, Oracle suggests that you first allow enough time after database creation to run a full workload, thus allowing the undo tablespace to grow to its minimum required size to handle the workload. Then, you can use the Undo Advisor to determine, if desired, how much larger to set the size of the undo tablespace to allow for long-running queries and Oracle Flashback operations.

Historical information in memory:

SQL> set serveroutput on
 SQL> DECLARE
 utbsiz_in_MB NUMBER;
 BEGIN
 utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
 dbms_output.put_line('=================================================================');
 dbms_output.put_line('The Minimum size of the undo tablespace required is : '||utbsiz_in_MB||'
 MB');
 dbms_output.put_line('=================================================================');
 end;
 /
==============================================
The Minimum size of the undo tablespace required is : 72 MB
==============================================

PL/SQL procedure successfully completed.

Function undo_info is used to get information about undo tablespace of the current instance.

it returns undo tablespace name, max possible size, if it is auto extensible, current undo retention value and if the undo tablespace has guaranteed undo retention.

SQL> set serveroutput on
 SQL> DECLARE
 tbs_name VARCHAR2(30);
 tbs_size NUMBER(10);
 tbs_autoextend BOOLEAN;
 tbs_retention NUMBER(5);
 tbs_guarantee BOOLEAN;
 undo_adv BOOLEAN;
 BEGIN
 dbms_output.put_line('=====================================================================');
 undo_adv := dbms_undo_adv.undo_info(tbs_name, tbs_size, tbs_autoextend, tbs_retention, tbs_guarantee);
 If undo_adv=TRUE then
 dbms_output.put_line('UNDO Tablespace Name : ' || tbs_name);
 dbms_output.put_line('UNDO tablespace is '|| CASE WHEN tbs_autoextend THEN 'Auto Extensiable' ELSE 'Fixed Size' END);
 If tbs_autoextend=TRUE then dbms_output.put_line('UNDO Tablespace Maximum size (MB) is : ' || TO_CHAR(tbs_size));
 else dbms_output.put_line('UNDO Tablespace Fixed size (MB) is : ' || TO_CHAR(tbs_size));
 end if;
 dbms_output.put_line('Undo Retention is ' || TO_CHAR(tbs_retention)||' Seconds' ||' Equivelant to ' ||round((tbs_retention/60),2) ||' Minutes');
 dbms_output.put_line('Retention : '||CASE WHEN tbs_guarantee THEN 'Guaranteed ' ELSE 'Not Guaranteed' END);
 else dbms_output.put_line('Function undo_info can only run if parameters undo_management is auto');
 end if;
 dbms_output.put_line('=====================================================================');
 END;
 /

==============================================

UNDO Tablespace Name : UNDOTBS2

UNDO tablespace is Auto Extensiable

UNDO Tablespace Maximum size (MB) is : 32768

Undo Retention is 900 Seconds Equivelant to 15 Minutes

Retention : Not Guaranteed

==============================================

PL/SQL procedure successfully completed.

Function longest_query returns the length of the longest query for a given period

SQL> SELECT 'The Length of the Longest Query in Memory is ' || dbms_undo_adv.longest_query LONGEST_QUERY FROM dual;
LONGEST_QUERY
--------------------------------------------------------------------------------
The Length of the Longest Query in Memory is 1472

The Output using Start/End time :

SQL> SELECT 'The Length of the Longest Query During This Time Range is ' ||dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual;

LONGEST_QUERY

The Length of the Longest Query During This Time Range is 1080

Function required_retention  returns the required value for parameter undo_retention to satisfy longest query based on undo statistics available

SQL> SELECT 'The Required undo_retention using Statistics In Memory is ' || dbms_undo_adv.required_retention required_retention FROM dual;

REQUIRED_RETENTION

——————————————————————————–

The Required undo_retention using Statistics In Memory is 1472

Current retention is 900,  and the required retention is 1472

SQL> SELECT 'The Required undo_retention During This Time Range is ' ||dbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual;

REQUIRED_RETENTION

——————————————————————————–

The Required undo_retention During This Time Range is 1080

SQL> SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;

REQUIRED_UNDO_SIZE

——————————————————————————–

The Required undo tablespace size using Statistics In Memory is 80 MB

Function undo_health is used to check if there is any problem with the current setting of undo_retention and undo tablespace size based on the historical information of given period , and provide recommendation to fix the problem.

If the return value is 0, no problem is found. Otherwise, parameter “problem” and “recommendation” are the problem and recommendation on fixing the problem.

The Output Parameters are :

problem: problem of the system. It can be for example : “long running query may fail” or “undo tablespace cannot satisfy undo_retention”.

recommendation: recommendation on fixing the problem found.

rationale: rationale for the recommendation.

retention: numerical value of retention if recommendation is to change retention.

utbsize: numberical value of undo tablespace size in MB if recommendation is to change undo tablespace size.

DECLARE
 prob VARCHAR2(100);
 reco VARCHAR2(100);
 rtnl VARCHAR2(100);
 retn PLS_INTEGER;
 utbs PLS_INTEGER;
 retv PLS_INTEGER;
 BEGIN
 retv := dbms_undo_adv.undo_health(prob, reco, rtnl, retn, utbs);
 dbms_output.put_line('=====================================================================');
 If retv=0 Then dbms_output.put_line('Problem: ' || prob || ' The undo tablespace is OK');
 ELSIF retv=2 Then dbms_output.put_line('Long running queries may fail , The recommendation is :' || reco);
 dbms_output.put_line('rationale: ' || rtnl);
 dbms_output.put_line('retention: ' || TO_CHAR(retn));
 ELSIF retv=3 Then
 dbms_output.put_line('The Undo tablespace cannot satisfy the longest query ,The recommendation is : ' || reco);
 dbms_output.put_line('rationale: ' || rtnl);
 dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
 dbms_output.put_line('retention: ' || TO_CHAR(retn));
 ELSIF retv=4 Then
 dbms_output.put_line('The System does not have an online undo tablespace , The recommendation is : ' || reco);
 dbms_output.put_line('rationale: ' || rtnl);
 dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
 ELSIF retv=1 Then
 dbms_output.put_line('The Undo tablespace cannot satisfy the specified undo_retention or The Undo tablespace cannot satisfy auto tuning undo retention , The recommendation is :' || reco);
 dbms_output.put_line('rationale: ' || rtnl);
 dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
 end if;
 dbms_output.put_line('=====================================================================');
 END;
 /

PL/SQL procedure successfully completed.

Needed undo tablespace for specified undo retention:-

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
 g.undo_block_per_sec) / (1024*1024)
 "NEEDED UNDO SIZE [MByte]"
 FROM (
 SELECT SUM(a.bytes) undo_size
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#
 ) d,
 v$parameter e,
 v$parameter f,
 (
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 undo_block_per_sec
 FROM v$undostat
 ) g
 WHERE e.name = 'undo_retention'
 AND f.name = 'db_block_size'
 /