1. 首页
  2. IT资讯

使用10g 的Segment Advisor来确认是否需要对表做shrink

http://203.208.39.132/search?q=cache:B8NQ1opRXtIJ:space.itpub.net/10972173/viewspace-600791+Segment+Advisor&cd=8&hl=zh-CN&ct=clnk&gl=cn&client=aff-360homepage&st_usg=ALhdy2-r4nklEUMmkytuhXODEOB_O1MKcQ

–我建立对多个表的分析但是结果只获取了一个表的分析结果,为何?

[@more@]

Segment Advisor10g中新增的一个工具,可以用来估算表的空间利用率,并给出相应的建议,确定是否需要进行shrink。以下为测试过程:

1. 表test大小为80M,初始时数据比较紧凑,对其执行批量delete操作

SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name=’TEST’;

SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024——————————————————————————— —————— —————TEST TABLE 80

SQL> select count(*) from test;

COUNT(*)———- 738624

SQL> delete from test where rownum<=400000;

400000 rows deleted.

SQL> commit;

Commit complete.

2.执行Segment Advisor,对表的存储空间进行检查

SQL> variable id number;SQL> begin 2 declare 3 name varchar2(100); 4 descr varchar2(500); 5 obj_id number; 6 begin 7 name:=’TEST’; 8 descr:=’Segment Advisor Example’; 9 10 dbms_advisor.create_task ( 11 advisor_name => ‘Segment Advisor‘, 12 task_id => :id, 13 task_name => name, 14 task_desc => descr); 15 16 dbms_advisor.create_object ( 17 task_name => name, 18 object_type => ‘TABLE’, 19 attr1 => ‘SYS’, 20 attr2 => ‘TEST’, 21 attr3 => NULL, 22 attr4 => NULL, 23 attr5 => NULL, 24 object_id => obj_id); 25 26 dbms_advisor.set_task_parameter( 27 task_name => name, 28 parameter => ‘recommend_all’, 29 value => ‘TRUE’); 30 31 dbms_advisor.execute_task(name); 32 end; 33 end; 34 /

PL/SQL procedure successfully completed.

SQL> select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 2 from dba_advisor_findings af, dba_advisor_objects ao 3 where ao.task_id = af.task_id 4 and ao.object_id = af.object_id 5 and ao.owner = ‘SYS’;

TASK_NAME SEGNAME PARTITION TYPE MESSAGE——————– ——————– ———- ———- ————————-TEST TEST TABLE Enable row movement of th e table SYS.TEST and perf orm shrink, estimated sav ings is 43132568 bytes.

从可以看出,Segment Advisor给出的建议是对表做shrink,并且估算出可以节省的空间约为43132568 bytes。

3. 执行shrink操作

SQL> select table_name,row_movement from user_tables where table_name=’TEST’;

TABLE_NAME ROW_MOVE—————————— ——–TEST DISABLED

SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space;

Table altered.

SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name=’TEST’;

SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024—————————— —————— —————TEST TABLE 34

–我建立对多个表的分析但是结果只获取了一个表的分析结果,为何?

Begin — exec dbms_advisor.delete_task(‘WF_BILL_IN_Y’); Declare Name Varchar2(100); Descr Varchar2(500); Obj_Id Number; Begin Name := ‘WF_BILL_IN_Y’; Descr := ‘Segment Advisor Example’; — variable id number; Dbms_Advisor.Create_Task(Advisor_Name => ‘Segment Advisor’, Task_Id => :Id, Task_Name => Name, Task_Desc => Descr); Dbms_Advisor.Create_Object(Task_Name => Name, Object_Type => ‘TABLE’, Attr1 => ‘TL’, Attr2 => Name, –WF_BILL_IN_Y WF_BILL_IN Attr3 => Null, Attr4 => Null, Attr5 => Null, Object_Id => Obj_Id); Dbms_Advisor.Set_Task_Parameter(Task_Name => Name, Parameter => ‘recommend_all’, Value => ‘TRUE’); Dbms_Advisor.Execute_Task(Name); End;End;/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/450962/viewspace-1027710/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/194854.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code