文章详情页_Oracle 段顾问

从 Oracle 10g 开始，Oracle 引入了段顾问（Segment Advisor），用于检查数据库中是否有与存储空间相关的建议，而且从10gR2開始，Oracle 会主动调度并执行一个段顾问作业，定时分析数据库中的段，并将分析结果放在内部表中。

作用

优化 SQL 语句时，能够帮助我们更准确的推断是否需要回收表的碎片空间
优化SQL语句时，能够帮助我们准确推断是否须要重建或者 move 表来消除表内的行链接
日常主动维护时，能够帮助我们主动发现表内碎片较多和行链接较严重的表对象列表，有助于我们提前处理，避免类似问题的发生

自动生成段顾问建议

通过 DBMS_SPACE 包获得段顾问

SQL> set linesize 120;
SQL> set pagesize 1000;

select 
  'Segment Advice -------------------------' || chr(10) ||
  'TABLESPACE NAME  : ' || tablespace_name   || chr(10) ||
  'SEGMENT OWNER    : ' || segment_owner     || chr(10) ||
  'SEGMENT_NAME     : ' || segment_name      || chr(10) ||
  'ALLOCATED_SPACE  : ' || allocated_space   || chr(10) ||
  'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
  'RECOMMENDATIONS  : ' || recommendations   || chr(10) ||
  'SOLUTION 1       : ' || c1                || chr(10) ||
  'SOLUTION 2       : ' || c2                || chr(10) ||
  'SOLUTION 3       : ' || c3 Advice
from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

Segment Advice -------------------------
TABLESPACE NAME  : TZDM_SODS
SEGMENT OWNER    : TZDM_SODS
SEGMENT_NAME     : STD_THISTRADESTOCK
ALLOCATED_SPACE  : 11244929024
RECLAIMABLE_SPACE: 318706732
RECOMMENDATIONS  : 进行压缩, 估计可以节省 318706732 字节。
SOLUTION 1       : alter table "TZDM_SODS"."STD_THISTRADESTOCK" modify partition "SYS_P847" shrink space
SOLUTION 2       : alter table "TZDM_SODS"."STD_THISTRADESTOCK" modify partition "SYS_P847" shrink space COMPACT
SOLUTION 3       :

Segment Advice -------------------------
TABLESPACE NAME  : TZDM_SODS
SEGMENT OWNER    : TZDM_SODS
SEGMENT_NAME     : STG_EI_BOND_INFO
ALLOCATED_SPACE  : 1
RECLAIMABLE_SPACE: 88
RECOMMENDATIONS  : 将对象 TZDM_SODS.STG_EI_BOND_INFO 移动到快速存储, 估计 I/O 等待减少时间为 6 毫秒
SOLUTION 1       :
SOLUTION 2       :
SOLUTION 3       :

通过数据字典视图，获取前一天所生成的段顾问

select 
  'Segment Advice -------------------------' || chr(10) ||
  'Task Name        : ' || f.task_name       || chr(10) || 
  'Start Run Time   : ' || to_char(e.execution_start, 'yyyy-mm-dd hh24:mi') || chr(10) || 
  'Segment Name     : ' || o.attr2           || chr(10) ||
  'Segment Type     : ' || o.type            || chr(10) ||
  'Partition Name   : ' || o.attr3           || chr(10) ||
  'Message          : ' || f.message         || chr(10) ||
  'More Info        : ' || f.more_info       || chr(10) ||
  '-----------------------------------------' Advice
from dba_advisor_findings   f,
     dba_advisor_objects    o,
     dba_advisor_executions e
where o.task_id   = f.task_id
  and o.object_id = f.object_id 
  and f.task_id   = e.task_id
  and e.execution_start > sysdate - 1
  and e.advisor_name = 'Segment Advisor'
order by f.task_name;

Segment Advice -------------------------
Task Name        : SYS_AUTO_SPCADV_11012210102018
Start Run Time   : 2018-10-10 22:01
Segment Name     : PK_STD_THISBONDPROPERTY
Segment Type     : INDEX
Partition Name   :
Message          : 进行压缩, 估计可以节省 311156694 字节。
More Info        : 分配空间:1347420160: 已用空间:1036263466: 可回收空间:311156694:
-----------------------------------------

Segment Advice -------------------------
Task Name        : SYS_AUTO_SPCADV_11012210102018
Start Run Time   : 2018-10-10 22:01
Segment Name     : ACH_CINDUSTRY
Segment Type     : TABLE
Partition Name   :
Message          : 启用表 TZDM_ODS.ACH_CINDUSTRY 的行移动并执行收缩, 估计可以节省 31066381 字节。
More Info        : 分配空间:92274688: 已用空间:61208307: 可回收空间:31066381:
-----------------------------------------

手工生成段顾问

DBMS_ADVISOR 包被用来手工生成段顾问，步骤为：

创建一个段顾问任务
为这个任务分配一个对象（指定表对象级别或表空间级别）
设置任务參数
运行这个任务

表对象级别段顾问

创建测试表，并删除大部分数据

SQL> create table tab_advisor as select * from dba_objects;
SQL> insert into tab_advisor select * from tab_advisor;
SQL> delete tab_advisor where rownum < 100000;

SQL> commit;

手工生成表对象级别段顾问

DECLARE
  my_task_id   number;
  obj_id       number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
BEGIN
  my_task_name :='TASK_ADVICE_TAB_ADVISOR';      --自定义任务名且唯一
  my_task_desc :='Manual Segment Advisor Run';   --自定义执行任务描述
  /* 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
  );
  /* 2. 为这个任务分配一个对象 */
  dbms_advisor.create_object(
    task_name    => my_task_name,
    object_type  => 'TABLE',             --表级别为 'TABLE'，表空间为 'TABLESPACE'
    attr1        => 'TZDM_DW',           --表级别为 'username'，表空间级别为 'tablespacename'
    attr2        => 'TAB_ADVISOR',       --表级别为 'tablename'，表空间级别为 NULL
    attr3        => NULL,
    attr4        => NULL,
    attr5        => NULL,
    object_id    => obj_id
  );
  /*3. 设置任务参数 */
  dbms_advisor.set_task_parameter(
    task_name    => my_task_name,
    parameter    => 'recommend_all',
    value        => 'TRUE'
  );
  /* 4. 运行该任务 */
  dbms_advisor.execute_task(my_task_name);
END;

PL/SQL 过程已成功完成。

查看生成的结果

select
  'Task name     : ' || f.task_name || chr(10) ||
  'Segment name  : ' || o.attr2     || chr(10) ||
  'Sement type   : ' || o.type      || chr(10) ||
  'partition name: ' || o.attr3     || chr(10) ||
  'Message       : ' || f.message   || 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 = 'TASK_ADVICE_TAB_ADVISOR'
order by f.task_name;

TASK_ADVICE
------------------------------------------------------------------------------------------
Task name     : TASK_ADVICE_TAB_ADVISOR
Segment name  : TAB_ADVISOR
Sement type   : TABLE
partition name:
Message       : 启用表 TZDM_DW.TAB_ADVISOR 的行移动并执行收缩, 估计可以节省 10978508 字节。
More info     : 分配空间:23068672: 已用空间:12090164: 可回收空间:10978508:

表空间对象级别段顾问

手工生成表空间对象级别段顾问

DECLARE
  my_task_id   number;
  obj_id       number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
BEGIN
  my_task_name :='TASK_ADVICE_TABLESPACE_TZDM_DW';  --自定义任务名且唯一
  my_task_desc :='Manual Segment Advisor Run';      --自定义执行任务描述
  /* 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
  );
  /* 2. 为这个任务分配一个对象 */
  dbms_advisor.create_object(
    task_name    => my_task_name,
    object_type  => 'TABLESPACE',        --表级别为 'TABLE'，表空间为 'TABLESPACE'
    attr1        => 'TZDM_DW',           --表级别为 'username'，表空间级别为 'tablespacename'
    attr2        => NULL,                --表级别为 'tablename'，表空间级别为 NULL
    attr3        => NULL,
    attr4        => NULL,
    attr5        => NULL,
    object_id    => obj_id
  );
  /*3. 设置任务参数 */
  dbms_advisor.set_task_parameter(
    task_name    => my_task_name,
    parameter    => 'recommend_all',
    value        => 'TRUE'
  );
  /* 4. 运行该任务 */
  dbms_advisor.execute_task(my_task_name);
END;

PL/SQL 过程已成功完成。

查看生成的结果

select
  'Task name     : ' || f.task_name || chr(10) ||
  'Segment name  : ' || o.attr2     || chr(10) ||
  'Sement type   : ' || o.type      || chr(10) ||
  'partition name: ' || o.attr3     || chr(10) ||
  'Message       : ' || f.message   || 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 = 'TASK_ADVICE_TABLESPACE_TZDM_DW'
order by f.task_name;

Task name     :TASK_ADVICE_TABLESPACE_TZDM_DW
Segment name  :MFACTGZ_SYMBOL_RATE
Sement type   :TABLE
partition name:
Message       :启用表 TZDM_DW.MFACTGZ_SYMBOL_RATE 的行移动并执行收缩, 估计可以节省 26173774 字节。
More info     :分配空间:30408704: 已用空间:4234930: 可回收空间:26173774:

Task name     :TASK_ADVICE_TABLESPACE_TZDM_DW
Segment name  :MDIM_TD_TISSUER
Sement type   :TABLE
partition name:
Message       :启用表 TZDM_DW.MDIM_TD_TISSUER 的行移动并执行收缩, 估计可以节省 132421812 字节。
More info     :分配空间:150994944: 已用空间:18573132: 可回收空间:132421812:

原创文章，转载请注明出处：http://www.opcoder.cn/article/15/