Oracle Undo Tablespace Advisor(DBMS_UNDO_ADV).

As per Doc ID 1580225.1
The package DBMS_UNDO_ADV is undocumented, and it is used internally by the Undo Advisor .

dbms_undo_adv package gives advise based on historical information present in memory or Automatic Workload
Repository. The default retention of AWR is 7 days.

Function longest_query : Returns the length of the longest query for a given period .

Method 1

SELECT dbms_undo_adv.longest_query LONGEST_QUERY FROM dual;

 
Method 2 (using Start/End time)

SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual;

 
Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual;


Function required_retention: returns the undo_retention value required for running the longest query.

Method 1

select dbms_undo_adv.required_retention from dual;

Method 2 (using Start/End time)

SELECT Ddbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual;



Function best_possible_retention: Returns the best possible undo retention which the current 
undo tablespace can support.

Note:If undo tablespace autoextensible then "maxsize" setting will be taken into account to
 calculate best_possible_retention.


Method 1

SELECT dbms_undo_adv.best_possible_retention best_retention FROM dual;

Method 2 (using Start/End time)

SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE) best_retention FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.best_possible_retention(345, 768) best_retention FROM dual; 


Function required_undo_size: Returne the appropriate undo tablespace size to satisfy undo retention value.

Method 1

SELECT dbms_undo_adv.required_undo_size(900) required_undo_size FROM dual;

Method 2 (using Start/End time)

SELECT dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) required_undo_size FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.required_undo_size(900, 345, 768) required_undo_size FROM dual;