oracle實用sql(2)--segment advisor

selectshen發表於2016-06-03
OracleSQL
segment advisor可以幫助分析表或索引的碎片,行連結問題.透過對問題表或索引收縮,移動或重建後減少儲存空間提高查詢速度.

點選(此處)摺疊或開啟

  1. --os:centos 6.6
  2. --db:oracle 11.2.0.4

  4. --建測試表
  5. create table scott.t_test01 as select * from dba_objects;
  6. --插入資料
  7. insert into scott.t_test01 select * from scott.t_test01;
  8. commit;
  9. insert into scott.t_test01 select * from scott.t_test01;
  10. commit;
  11. insert into scott.t_test01 select * from scott.t_test01;
  12. commit;
  13. --刪除一些資料
  14. delete from scott.t_test01 where rownum<300000;
  15. commit;

  17. --建立執行scott.T_TEST01的segment advisor task
  18. declare
  19.   v_id number;
  20.   v_task_name varchar2(100);
  21.   v_object_owner varchar2(50):='SCOTT';
  22.   v_object_name varchar2(50):='T_TEST01';
  23.   v_object_type varchar2(50):='TABLE';
  24. begin
  25.     dbms_advisor.create_task
  26.       (advisor_name => 'Segment Advisor'
  27.       ,task_id => v_id
  28.       ,task_name => v_task_name
  29.       ,task_desc => v_object_type||' '||v_object_owner||'.'||v_object_name
  30.       ,template => null
  31.       ,is_template => 'FALSE');
  32.     dbms_output.put_line('task_id:'||v_id);
  33.     dbms_advisor.create_object
  34.       (task_name => v_task_name
  35.       ,object_type => v_object_type
  36.       ,attr1 => v_object_owner
  37.       ,attr2 => v_object_name
  38.       ,attr3 => null
  39.       ,attr4 => null
  40.       ,attr5 => null
  41.       ,object_id => v_id);
  42.     dbms_advisor.set_task_parameter
  43.       (task_name => v_task_name
  44.       ,parameter => 'RECOMMEND_ALL'
  45.       ,value => 'TRUE');
  46.     dbms_advisor.set_task_parameter
  47.       (task_name => v_task_name
  48.       ,parameter => 'MODE'
  49.       ,value => 'COMPREHENSIVE');
  50.     dbms_advisor.set_task_parameter
  51.       (task_name => v_task_name
  52.       ,parameter => 'TIME_LIMIT'
  53.       ,value => 'UNLIMITED');
  54.     dbms_advisor.execute_task
  55.       (task_name => v_task_name);
  56. end;
  57. --輸入任務id
  58. /*task_id:21*/

  60. --根據上一步輸出的task_id檢視segment advisor任務資訊
  61. SELECT at.task_id, at.task_name, at.description, count(*) object_count, at.status, at.status_message,
  62.          at.pct_completion_time, at.created, at.last_modified,
  63.          at.execution_start, at.execution_end, at.owner, at.how_created
  64. FROM dba_advisor_tasks at, dba_advisor_objects ao
  65. WHERE at.task_id = ao.task_id
  66. AND at.task_id=21
  67. AND at.owner = ao.owner
  68. GROUP BY at.task_id, at.task_name, at.description, at.status, at.status_message,
  69.          at.pct_completion_time, at.created, at.last_modified,
  70.          at.execution_start, at.execution_end, at.owner, at.how_created;

  72. --根據task_id檢視segment advisor
  73. SELECT af.owner task_owner, af.task_name, ao.type Object_Type,
  74.        ao.attr1 object_owner, ao.attr2 object_name,
  75.        ao.attr3 subobject_name, to_char(ao.attr4) Tablespace_Name,
  76.        af.message, af.more_info, to_char(ao.task_id) task_id, to_char(ao.object_id) object_id
  77. FROM dba_advisor_findings af, dba_advisor_tasks at,
  78.        dba_advisor_objects ao
  79. WHERE af.task_id = at.task_id
  80. AND af.task_id=21
  81. AND af.owner = at.owner
  82. AND af.task_id = ao.task_id
  83. AND ao.object_id = af.object_id;

  85. --根據task_id檢視segment advisor recommendations
  86. SELECT aa.task_id, aa.object_id, aa.attr1, aa.attr2, aa.attr3,
  87.        initcap(ar.annotation_status) status, ar.rec_id
  88. FROM dba_advisor_actions aa, dba_advisor_tasks at,
  89.        dba_advisor_recommendations ar
  90. WHERE aa.task_id = at.task_id
  91. AND aa.task_id=21
  92. AND aa.owner = at.owner
  93. AND ar.task_id = at.task_id
  94. AND ar.rec_id = aa.rec_id;



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

相關文章