检查及设置合理的undo表空间(三)
O_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
4、后记
a、undo究竟要多大，推荐的做法是根据你的业务高峰期得到一个峰值，然后适当高于峰值来启用自动扩展。
b、undo保留时间与所需的空间成正比，保留时间越长，空间开销越大，不过出现ORA-01555的几率也就越低。
c、在未启用自动扩展的情形下，对于新的事务，UNDO_RETENTION会回收空闲空间，并重新使用，如果启用自动扩展，则该功能被忽略，而是先扩展。