oracle實用sql(4)--undo advisor

selectshen發表於2016-06-04
OracleSQL

點選(此處)摺疊或開啟

  1. DECLARE
  2.   v_tbs_name varchar2(30);
  3.   v_tbs_size number;
  4.   v_auto_extend boolean;
  5.   v_undo_retention number;
  6.   v_retention_guarantee boolean;
  7.   
  8.   v_problem varchar2(1000);
  9.   v_recommendation varchar2(1000);
  10.   v_rationale varchar2(1000);
  11.   v_rec_undo_retention number;
  12.   v_rec_tbs_size number;
  13.   
  14.   v_autotune_enable boolean;
  15.   
  16.   v_return_boolean boolean;
  17.   v_return_number number;
  18.   v_return_varchar varchar2(1000);
  19. BEGIN
  20.   --undo info
  21.   v_return_boolean:=dbms_undo_adv.undo_info(table_space_name => v_tbs_name,
  22.                                        table_space_size => v_tbs_size,
  23.                                        auto_extend => v_auto_extend,
  24.                                        undo_retention => v_undo_retention,
  25.                                        retention_guarantee => v_retention_guarantee);
  26.   dbms_output.put_line('table_space_name:'||v_tbs_name);
  27.   dbms_output.put_line('table_space_size:'||v_tbs_size);
  28.   dbms_output.put_line('auto_extend:'||case when v_auto_extend then 'YES' else 'NO' end);
  29.   dbms_output.put_line('undo_retention:'||v_undo_retention);
  30.   dbms_output.put_line('retention_guarantee:'||case when v_retention_guarantee then 'YES' else 'NO' end);
  31.   
  32.   --以下函式都可以加上時間範圍,awr快照範圍
  33.   --required_undo_size
  34.   v_return_number := dbms_undo_adv.required_undo_size(retention => v_undo_retention);
  35.   dbms_output.put_line('required_undo_size:'||v_return_number);
  36.   --undo_health
  37.   v_return_number := dbms_undo_adv.undo_health(problem => v_problem,
  38.                                    recommendation => v_recommendation,
  39.                                    rationale => v_rationale,
  40.                                    retention => v_rec_undo_retention,
  41.                                    utbsize => v_rec_tbs_size);
  42.   dbms_output.put_line('problem:'||v_problem);
  43.   dbms_output.put_line('recommendation:'||v_recommendation);
  44.   dbms_output.put_line('rationale:'||v_rationale);
  45.   dbms_output.put_line('retention:'||v_rec_undo_retention);
  46.   dbms_output.put_line('utbsize:'||v_rec_tbs_size);
  47.   --undo_advisor
  48.   v_return_varchar := dbms_undo_adv.undo_advisor(instance => 1);
  49.   dbms_output.put_line('undo_advisor:'||v_return_varchar);
  50.   --autotune enable
  51.   v_return_boolean := dbms_undo_adv.undo_autotune(v_autotune_enable);
  52.   dbms_output.put_line('undo_autotune:'||case when v_autotune_enable then 'YES' else 'NO' end);
  53.   --longest_query
  54.   v_return_number := dbms_undo_adv.longest_query;
  55.   dbms_output.put_line('longest_query:'||v_return_number);
  56.   --required_retention
  57.   v_return_number := dbms_undo_adv.required_retention;
  58.   dbms_output.put_line('required_retention:'||v_return_number);
  59. END;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-2113778/，如需轉載，請註明出處，否則將追究法律責任。

相關文章