Systems Engineering and RDBMS

DBMS_UNDO_ADV

Posted by decipherinfosys on April 13, 2007

DBMS_UNDO_ADV package can be used to get information about sizing undo tablespace and to set undo_retention parameter.  This blog post will describe the various functions of this package and how to use them.

Get_undo_info

This function is used to get information about current undo tablespace name, max possible size, it is auto extensible or not, current undo retention value and if the undo tablespace has guaranteed undo retention.

DECLARE
v_table_space_name      VARCHAR2(30);
v_table_space_size_in_MB        NUMBER(9);
v_auto_extend      BOOLEAN;
v_undo_retention      NUMBER(9);
v_retention_guarantee    BOOLEAN;
v_undo_info_return    BOOLEAN;
BEGIN
v_undo_info_return := dbms_undo_adv.undo_info(v_table_space_name, v_table_space_size_in_MB, v_auto_extend, v_undo_retention, v_retention_guarantee);
dbms_output.put_line(‘UNDO Tablespace Name: ‘ || v_table_space_name);
dbms_output.put_line(‘UNDO Tablespace size (MB) : ‘ || TO_CHAR(v_table_space_size_in_MB));
dbms_output.put_line(‘If UNDO tablespace is auto extensible above size indicates max possible size of the undo tablespace’);
dbms_output.put_line(‘UNDO tablespace auto extensiable is : ‘|| CASE WHEN v_auto_extend THEN  ‘ON’ ELSE ‘OFF’ END);
dbms_output.put_line(‘Undo Retention (Sec): ‘ || v_undo_retention);
dbms_output.put_line(‘Retention : ‘||CASE WHEN v_retention_guarantee THEN ‘Guaranteed ‘ ELSE ‘NOT Guaranteed’ END);
END;

undo_autotune

This function is used to find auto tuning of undo retention is ENABLED or NOT.

Set serverout on
declare
v_autotune_return Boolean := null;
v_autotune_enabled boolean := null;
begin
v_autotune_return:= dbms_undo_adv.undo_autotune(v_autotune_enabled);
dbms_output.put_line(CASE WHEN v_autotune_return THEN ‘Information is available :’ ELSE ‘Information is NOT available :’ END||
CASE WHEN v_autotune_enabled THEN ‘Auto tuning of undo retention is ENABLED’ ELSE ‘Auto tuning of undo retention is NOT enabled’ END);
end;
/

Undo retention is automatically tuned by Oracle for completion of long running queries without getting the dreaded Ora-01555: snapshot too old error. It is not necessary to set UNDO_RETENTION parameter value to 0 to use this auto tune undo feature, in case if you specify non zero value for UNDO_RETENTION then undo retention is not tuned-down below the value you specified.

longest_query

This function is used to find the length of the longest query based on historical information in memory or in Statistics Workload Repository Facilities (SWRF) from sysdate-7 to sysdate

select dbms_undo_adv.longest_query from dual

There are two other version of longest_query, one takes starttime to endtime as input parameter and returns the length of the longest query based on historical information in memory or in (SWRF) for a given period, while other takes  two snapshot id to do same.

required_retention

This function is used to find appropriate value for undo_retention to support longest query based on historical information in memory or in Statistics Workload Repository Facilities (SWRF) from sysdate-7 to sysdate.

select dbms_undo_adv.required_retention from dual

Similar to longest_query there are two other version of required_retention and takes same parameters as mentioned above.

best_possible_retention

This function is used to get best possible undo retention which current undo tablespace can support based on undo stats from historical information in memory or in Statistics Workload Repository Facilities (SWRF) from sysdate-7 to sysdate.

select dbms_undo_adv.best_possible_retention from dual

Similar to longest_query there are two other version of best_possible_retention
and takes same parameters as mentioned above.

required_undo_size

This function is used to get required undo tablespace size (in MB) to support targeted undo retention (in Se, passed as input parameter to this function) based on historical information in memory or in Statistics Workload Repository Facilities (SWRF) from sysdate-7 to sysdate.

select  dbms_undo_adv.required_undo_size(1800) from dual

Similar to longest_query there are two other documented version of required_undo_size
and takes same parameters as mentioned above.

undo_health

This function is used to find out the problem in undo tablespace and get recommendation to fix the problem. If no problem found, return value is 0.

DECLARE
v_undo_health_return number;
v_retention number;
v_utbsize number;
v_problem VARCHAR2(1024);
v_recommendation VARCHAR2(1024);
v_rationale VARCHAR2(1024);
BEGIN
v_undo_health_return :=  dbms_undo_adv.undo_health(problem => v_problem,
recommendation => v_recommendation,
rationale => v_rationale,
retention => v_retention,
utbsize => v_utbsize);
dbms_output.put_line(‘Problem : ‘||v_problem);
dbms_output.put_line(‘Recommendation= : ‘||v_recommendation);
dbms_output.put_line(‘Rationale : ‘||v_retention);
dbms_output.put_line(‘Retention : ‘||v_retention);
dbms_output.put_line(‘UNDO tablespace size : ‘||v_utbsize);
END;

Here,

the problem could be “long running query may fail” or “undo tablespace cannot support undo_retention”.
recommendation could be the recommendation on fixing the problem found.
rationale could be rationale for the recommendation.
retention could be retention if recommendation is to change retention.
utbsize could be undo tablespace size in MB if recommendation is to change undo tablespace size.

Similar to longest_query there are two other version of undo_health and takes same parameters as mentioned above.

undo_advisor

It uses oracle’s advisor framework to find out problem and provide recommendations.

DECLARE
v_undo_advisor_return VARCHAR2(100);
BEGIN
v_undo_advisor_return := dbms_undo_adv.undo_advisor(instance => 1);
dbms_output.put_line(v_undo_advisor_return);
END;
Here
instance is instance id of the current instance.

Similar to the longest_query, there are two other version of undo_advisor and takes same parameters as mentioned above.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: