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;
Comments
Post a Comment