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

最新推荐文章于 2019-08-26 18:01:23 发布

cuichao5297

最新推荐文章于 2019-08-26 18:01:23 发布

阅读量98

点赞数

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 Advisor是10g中新增的一个工具，可以用来估算表的空间利用率，并给出相应的建议，确定是否需要进行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;
/