一、描述
從oracle 10g開始，oracle引入了段顧問（Segment Advisor），用於檢查數據庫中是否有與存儲空間相關的建議，並且從10gR2開始，oracle自動調度並運行一個段顧問作業，定時分析數據庫中的段，並將分析結果放在內部表中。但是很多情況下，作為DBA，我們都會將oracle自帶的各種調度作業（統計信息收集、段顧問、SQL顧問等等）禁用，進而通過手工進行控制執行類似作業（或者為了節省資源）。因此很多情況下，我們都沒有用到段顧問這個非常實用的功能。這篇文章就是講述在表對象和表空間級別通過手工運行段顧問來生成段建議的方法。
二、作用
優化SQL語句時，可以幫助我們更准確的判斷是否需要回收表內的碎片空間。如果不運行段顧問建議，我們必須得通過create table as select一張臨時表方式才能准確的得知是否有必要進行表空間回收,以及空間的回收率等等信息。優化SQL語句時，可以幫助我們准確判斷是否需要重建或者move表來消除表內的行鏈接。可以想想，如果沒有這個建議，我們又需要做多少工作來判斷。日常主動維護時，可以幫助我們主動發現表內碎片較多和行鏈接較嚴重的表對象列表，有助於我們提前處理，避免類似問題的發生。
三、段顧問的分析結果類型
適合進行段收縮操作來回收空間的段具有大量行鏈接的段可能會從OLTP壓縮中受益的段
四、手工運行段顧問步驟
創建一個段顧問任務為這個任務分配一個對象（指定以表對象或者表空間級別來運行，同時制定用戶名和表名或者表空間名）設置任務參數（執行段顧問執行時的一些參數，例如：運行時長限制參數、僅生產與空間相關建議/所有類型的對象生成建議）執行這個任務
五、手工運行段顧問的代碼
declare
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
begin
my_task_name :='advisor_test tab Advice'; --運行任務名，可以任意指定，不過建議為有意義的名稱
my_task_desc :='Manual Segment Advisor Run';--運行任務描述，可以任意指定，不過建議為有意義的描述
-----step 1
/* 創建一個段顧問任務 */
dbms_advisor.create_task(
advisor_name => 'Segment Advisor', --運行段顧問任務這個參數必須指定為Segment Advisor
task_id => my_task_id,
task_name => my_task_name,
task_desc =>my_task_desc);
-----step 2
/* 為這個任務分配一個對象 */
dbms_advisor.create_object(
task_name=>my_task_name,
object_type=>'TABLE', --指定對象級別，如果為表對象則為'TABLE'，如果為表空間級別則為'TABLESPACE'
attr1=>'DBMON', ---如果在表對象級別運行，這個屬性為用戶名，表空間級別這個屬性為表空間名字
attr2 => 'ADVISOR_TEST', ---如果在表對象級別運行，這個屬性為表名，表空間級別這個屬性為null
attr3 => NULL,
attr4=>null,
attr5=>null,
object_id=>obj_id);
-----step 3
/* 設置任務參數 */
dbms_advisor.set_task_parameter(
task_name => my_task_name,
/* 設置段顧問運行參數"ecommend_all"的值,為TRUE則為所有類型的對象的生成建議，為FALSE則僅生成與空間相關的建議 */
/* 另一個滾問運行參數"time_limit",制定顧問運行的時間限制，默認值為無限制 */
parameter=>'recommend_all', ---
value=>'TRUE');
-----step 4
/* 執行這個任務 */
dbms_advisor.execute_task(my_task_name);
end;
/
六、查詢段顧問分析結果的語句
(1)
select
/* "|chr(13)||chr(10)"為windows平台的換行符，如果是linux等其它平台，請用"chr(10)"代替 */
'Task name :'||f.task_name||chr(13)||chr(10)||
'Segment name :'||o.attr2 ||chr(13)||chr(10)||
'Sement type :'||o.type ||chr(13)||chr(10)||
'partition name:'||o.attr3 ||chr(13)||chr(10)||
'Message :'||f.message ||chr(13)||chr(10)||
'More info :'||f.more_info TASK_ADVICE
from dba_advisor_findings f,dba_advisor_objects o
where o.task_id=f.task_id
and o.object_id=f.object_id
and f.task_name = 'advisor_test tab Advice'
order by f.task_name;
(2)
通過查詢TABLE(dbms_space.asa_recommendations(all_runs=>'TRUE',show_manual=>'TRUE',show_findings=>'FALSE'))來查看建議;
第一個參數true表示運行歷次運行結果，false表示最近一次的結果
第二個參數true表示返回手工運行段顧問的結果，false表示返回自動運行段顧問的結果
第三個參數true表示僅顯示分析結果，false表示顯示分析結果和分析建議
備注：案例中使用（1）語句來查看分析結果
七、案例1（表對象級別運行）
SQL> create table advisor_test as select * from dba_objects;
Table created
SQL> insert into advisor_test select * from advisor_test;
72525 rows inserted
SQL> /
145050 rows inserted
SQL> /
290100 rows inserted
SQL> commit;
Commit complete
SQL> delete advisor_test where rownum<100000;
99999 rows deleted
SQL> /
99999 rows deleted
SQL> commit;
Commit complete
declare
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
begin
my_task_name :='advisor_test tab Advice';
my_task_desc :='Manual Segment Advisor Run';
-----step 1
dbms_advisor.create_task(
advisor_name => 'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc =>my_task_desc);
-----step 2
dbms_advisor.create_object(
task_name=>my_task_name,
object_type=>'TABLE',
attr1=>'DBMON',
attr2 => 'ADVISOR_TEST',
attr3 => NULL,
attr4=>null,
attr5=>null,
object_id=>obj_id);
-----step 3
dbms_advisor.set_task_parameter(
task_name => my_task_name,
parameter=>'recommend_all',
value=>'TRUE');
-----step 4
dbms_advisor.execute_task(my_task_name);
end;
/
已執行
SQL> select
2 /* "|chr(13)||chr(10)"為windows平台的換行符，如果是linux等其它平台，請用"chr(10)"代替 */
3 'Task name :'||f.task_name||chr(13)||chr(10)||
4 'Segment name :'||o.attr2 ||chr(13)||chr(10)||
5 'Sement type :'||o.type ||chr(13)||chr(10)||
6 'partition name:'||o.attr3 ||chr(13)||chr(10)||
7 'Message :'||f.message ||chr(13)||chr(10)||
8 'More info :'||f.more_info TASK_ADVICE
9 from dba_advisor_findings f,dba_advisor_objects o
10 where o.task_id=f.task_id
11 and o.object_id=f.object_id
12 and f.task_name = 'advisor_test tab Advice'
13 order by f.task_name;
TASK_ADVICE
--------------------------------------------------------------------------------
Task name :advisor_test tab Advice
Segment name :ADVISOR_TEST
Sement type :TABLE
partition name:
Message :啟用表 DBMON.ADVISOR_TEST 的行移動並執行收縮, 估計可以節省 285435
31 字節。
More info :分配空間:75497472: 已用空間:46953941: 可回收空間:28543531:
八、案例2（表空間級別運行）
declare
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
begin
my_task_name :='Tablespace Advice';
my_task_desc :='Manual Segment Advisor Run';
-----step 1
dbms_advisor.create_task(
advisor_name => 'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc =>my_task_desc);
-----step 2
dbms_advisor.create_object(
task_name=>my_task_name,
object_type=>'TABLESPACE',
attr1=>'USERS',
attr2 => null,
attr3 => NULL,
attr4=>null,
attr5=>null,
object_id=>obj_id);
-----step 3
dbms_advisor.set_task_parameter(
task_name => my_task_name,
parameter=>'recommend_all',
value=>'TRUE');
-----step 4
dbms_advisor.execute_task(my_task_name);
end;
/
SQL> select
2 /* "|chr(13)||chr(10)"為windows平台的換行符，如果是linux等其它平台，請用"chr(10)"代替 */
3 'Task name :'||f.task_name||chr(13)||chr(10)||
4 'Segment name :'||o.attr2 ||chr(13)||chr(10)||
5 'Sement type :'||o.type ||chr(13)||chr(10)||
6 'partition name:'||o.attr3 ||chr(13)||chr(10)||
7 'Message :'||f.message ||chr(13)||chr(10)||
8 'More info :'||f.more_info TASK_ADVICE
9 from dba_advisor_findings f,dba_advisor_objects o
10 where o.task_id=f.task_id
11 and o.object_id=f.object_id
12 and f.task_name = 'Tablespace Advice'
13 order by f.task_name;
TASK_ADVICE
--------------------------------------------------------------------------------
Task name :Tablespace Advice
Segment name :T_SCEGAOKAOQUERY_GZ
Sement type :TABLE
partition name:
Message :此對象中的空閒空間小於 10MB。
More info :分配空間:65536: 已用空間:8192: 可回收空間:57344:
Task name :Tablespace Advice
Segment name :T_SCEGAOKAOQUERY_GZ
Sement type :TABLE
partition name:
TASK_ADVICE
--------------------------------------------------------------------------------
Message :此對象中的空閒空間小於 10MB。
More info :分配空間:3145728: 已用空間:2871921: 可回收空間:273807:
Task name :Tablespace Advice
Segment name :T_SCEGAOKAOQUERY
Sement type :TABLE
partition name:
Message :此對象中的空閒空間小於 10MB。
More info :分配空間:46137344: 已用空間:44837534: 可回收空間:1299810:
--------為了排版,省略後面的輸出--------------
備注:最後可以運行delete_task來刪除任務,如下exec dbms_advisor.delete_task(task_name => 'Tablespace Advice');
