注册 登录
> Oracle

Oracle手工生成段建议（Segment Advisor）

系统 1659 0
一、描写叙述
从oracle 10g開始，oracle引入了段顾问（Segment Advisor），用于检查数据库中是否有与存储空间相关的建议，而且从10gR2開始，oracle自己主动调度并执行一个段顾问作业，定时分析数据库中的段，并将分析结果放在内部表中。可是非常多情况下， 作为DBA，我们都会将oracle自带的各种调度作业（统计信息收集、段顾问、SQL顾问等等）禁用，进而通过手工进行控制执行类似作业（或者为了节省资源）。因此非常多情况下，我们都没实用到段顾问这个非常实用的功能。这篇文章就是讲述在 表对象和表空间级别 通过手工执行段顾问来生成段建议的方法。
二、作用
  • 优化SQL语句时，能够帮助我们更准确的推断是否须要回收表内的碎片空间。假设不执行段顾问建议，我们必须得通过create table as select一张暂时表方式才干准确的得知是否有必要进行表空间回收,以及空间的回收率等等信息。
  • 优化SQL语句时，能够帮助我们准确推断是否须要重建或者move表来消除表内的行链接。能够想想，假设没有这个建议，我们又须要做多少工作来推断。
  • 日常主动维护时，能够帮助我们主动发现表内碎片较多和行链接较严重的表对象列表，有助于我们提前处理，避免类似问题的发生。
三、段顾问的分析结果类型
  • 适合进行段收缩操作来回收空间的段
  • 具有大量行链接的段
  • 可能会从OLTP压缩中受益的段
四、手工执行段顾问步骤
  1. 创建一个段顾问任务
  2. 为这个任务分配一个对象（指定以表对象或者表空间级别来运行，同一时候制定username和表名或者表空间名）
  3. 设置任务參数（运行段顾问运行时的一些參数，比如：运行时长限制參数、仅生产与空间相关建议/全部类型的对象生成建议）
  4. 运行这个任务
五、手工执行段顾问的代码
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' ,              - - - 假设在表对象级别执行，这个属性为username，表空间级别这个属性为表空间名字
  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');

Oracle手工生成段建议（Segment Advisor）


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧，站长会非常 感谢您的哦！！！

发表我的评论
最新评论 总共0条评论