dbms_advisor 手动生成段顾问建议！

来源：互联网发布：app交互流程图软件编辑：程序博客网时间：2024/05/20 09:07

执行包需要dbms_advisor权限：

sys@ORCL> grant advisor to u1;授权成功。

创建段顾问任务，指定create_task的advisor_name参数为“段顾问”。查询dba_advisor_definitions来获得所有有效的顾问列表。

u1@ORCL> select * from dba_advisor_definitions;ADVISOR_ID ADVISOR_NAME                     PROPERTY---------- ------------------------------ ----------         1 ADDM                                    1         2 SQL Access Advisor                    271         3 Undo Advisor                            1         4 SQL Tuning Advisor                    935         5 Segment Advisor                         3         6 SQL Workload Manager                    0         7 Tune MView                             31         8 SQL Performance Analyzer              935         9 SQL Repair Advisor                    679        10 Compression Advisor                     3已选择10行。

手动执行段顾问：

sys@ORCL> DECLARE  2    my_task_id   number;  3    obj_id       number;  4    my_task_name varchar2(100);  5    my_task_desc varchar2(500);  6  BEGIN  7    my_task_name := 'BIG_TABLE Advice';  my_task_desc := 'Manual Segment Advisor Run';  9  --------- 10  -- Step 1 创建一个任务 11  --------- 12    dbms_advisor.create_task ( 13    advisor_name => 'Segment Advisor', 14    task_id      => my_task_id, 15    task_name    => my_task_name, 16    task_desc    => my_task_desc); 17  --------- 18  -- Step 2 为这个任务分配一个对象 19  --------- 20    dbms_advisor.create_object ( 21    task_name   => my_task_name, 22    object_type => 'TABLE', 23    attr1       => 'U1', 24    attr2       => 'BIG_TABLE', 25    attr3       => NULL, 26    attr4       => NULL, 27    attr5       => NULL, 28    object_id   => obj_id); 29  --------- 30  -- Step 3 设置任务参数 31  --------- 32    dbms_advisor.set_task_parameter( 33    task_name => my_task_name, 34    parameter => 'recommend_all',  value     => 'TRUE');--------- 37  -- Step 4 执行这个任务 38  --------- 39    dbms_advisor.execute_task(my_task_name); 40  END; 41  / PL/SQL 过程已成功完成。sys@ORCL> SELECT  2   'Segment Advice --------------------------'|| chr(10) ||  3   'TABLESPACE_NAME  : ' || tablespace_name   || chr(10) ||  4   'SEGMENT_OWNER    : ' || segment_owner     || chr(10) ||  5   'SEGMENT_NAME     : ' || segment_name      || chr(10) ||  6   'ALLOCATED_SPACE  : ' || allocated_space   || chr(10) ||  7   'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||  8   'RECOMMENDATIONS  : ' || recommendations   || chr(10) ||  9   'SOLUTION 1       : ' || c1                || chr(10) || 10   'SOLUTION 2       : ' || c2                || chr(10) || 11   'SOLUTION 3       : ' || c3 Advice 12  FROM 13  TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));ADVICE--------------------------------------------------------------------------------Segment Advice --------------------------TABLESPACE_NAME  : USERSSEGMENT_OWNER    : QYVSEGMENT_NAME     : BIG_TABLEALLOCATED_SPACE  : 125829120RECLAIMABLE_SPACE: 81788928RECOMMENDATIONS  : 压缩对象 QYV.BIG_TABLE, 估计可以节省 81788928 字节。SOLUTION 1       : alter table "QYV"."BIG_TABLE"  compress for oltpSOLUTION 2       : alter table "QYV"."BIG_TABLE"  moveSOLUTION 3       :Segment Advice --------------------------TABLESPACE_NAME  : USERSSEGMENT_OWNER    : U1SEGMENT_NAME     : BIG_TABLEALLOCATED_SPACE  : 125829120RECLAIMABLE_SPACE: 109535475RECOMMENDATIONS  : 启用表 U1.BIG_TABLE 的行移动并执行收缩, 估计可以节省 109535475 字节。SOLUTION 1       : alter table "U1"."BIG_TABLE" shrink spaceSOLUTION 2       : alter table "U1"."BIG_TABLE" shrink space COMPACTSOLUTION 3       : alter table "U1"."BIG_TABLE" enable row movementSegment Advice --------------------------TABLESPACE_NAME  : USERSSEGMENT_OWNER    : U1SEGMENT_NAME     : TTALLOCATED_SPACE  : 27262976RECLAIMABLE_SPACE: 17314513RECOMMENDATIONS  : 启用表 U1.TT 的行移动并执行收缩, 估计可以节省 17314513 字节。SOLUTION 1       : alter table "U1"."TT" shrink spaceSOLUTION 2       : alter table "U1"."TT" shrink space COMPACTSOLUTION 3       : alter table "U1"."TT" enable row movementsys@ORCL> SELECT  2   'Task Name        : ' || f.task_name  || chr(10) ||  3   'Segment Name     : ' || o.attr2      || chr(10) ||  4   'Segment Type     : ' || o.type       || chr(10) ||  5   'Partition Name   : ' || o.attr3      || chr(10) ||  6   'Message          : ' || f.message    || chr(10) ||  7   'More Info        : ' || f.more_info TASK_ADVICE  8  FROM dba_advisor_findings f  9      ,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 like 'BIG_TABLE Advice' 13  ORDER BY f.task_name;TASK_ADVICE-----------------------------------------------------------------------------------------------------------------------------------Task Name        : BIG_TABLE AdviceSegment Name     : BIG_TABLESegment Type     : TABLEPartition Name   :Message          : 启用表 U1.BIG_TABLE 的行移动并执行收缩, 估计可以节省 109535475 字节。More Info        : 分配空间:125829120: 已用空间:16293645: 可回收空间:109535475:Task Name        : BIG_TABLE AdviceSegment Name     : BIG_TABLESegment Type     : TABLEPartition Name   :Message          : 启用表 U1.BIG_TABLE 的行移动并执行收缩, 估计可以节省 109535475 字节。More Info        : 分配空间:125829120: 已用空间:16293645: 可回收空间:109535475:

删除一个任务：

u1@ORCL> exec dbms_advisor.delete_task('BIG_TABLE Advice');PL/SQL 过程已成功完成。