Segment Advisor可用于监控某个对象的碎片程度,并对其进行回收,从而提高性能,注意以下数字的变化,以下是我做的一个测试脚本,供参考!
SQL>
SQL> select bytes/1024/1024 from user_segments where segment_name='B';
BYTES/1024/1024
--------------- 45
SQL> select count(*) from b;
COUNT(*)
----------
405696
SQL> delete from b where rownum<100001;
已删除100000行。
SQL> commit;
提交完成。
SQL> select bytes/1024/1024 from user_segments where segment_name='B';
BYTES/1024/1024
--------------- 45
SQL> create index ind_b on b(object_id);
索引已创建。
SQL> delete from b where rownum<100001;
已删除100000行。
SQL> commit;
提交完成。
SQL> select bytes/1024/1024 from user_segments where segment_name='B';
BYTES/1024/1024
45
SQL> variable id number;
SQL> begin
2 declare
3 name varchar2(100);
4 descr varchar2(500);
5 obj_id number;
6 begin
7 name := 'advisor_'||to_char(sysdate,'yyyymmdd');
8 descr := 'Segment Advisor Example';
9 dbms_advisor.create_task(advisor_name => 'Segment Advisor',
10 task_id => :id,
11 task_name => name,
12 task_desc => descr);
13 dbms_advisor.create_object(task_name => name,
14 object_type => 'TABLE',
15 attr1 => 'ZAKI',
16 attr2 => 'B',
17 attr3 => NULL,
18 attr4 => NULL,
19 attr5 => NULL,
20 object_id => obj_id);
21 dbms_advisor.set_task_parameter(task_name => name,
22 parameter => 'recommend_all',
23 value => 'TRUE');
24 dbms_advisor.execute_task(name);
25 end;
26 end;
27 /
PL/SQL 过程已成功完成。
SQL> select af.task_name,
2 ao.attr2 segname,
3 ao.attr3 partition,
4 ao.type,
5 af.message
6 from dba_advisor_findings af, dba_advisor_objects ao
7 where ao.task_id = af.task_id
8 and ao.object_id = af.object_id
9 and ao.owner = 'ZAKI';
TASK_NAME SEGNAME MESSAGE
advisor_t_test_resumable A
进行压缩, 估计可以省出 10950496 字节。
advisor_20111010
B
启用表 ZAKI.B 的行移动并执行收缩, 估计会节省 21204669 字节。
SQL> select bytes/1024/1024 from user_segments where segment_name='B';
BYTES/1024/1024
---------------
45
SQL> select count(*) from b;
COUNT(*)
----------
205696
SQL> begin
2 declare
3 name varchar2(100);
4 descr varchar2(500);
5 obj_id number;
6 begin
7 name := 'advisor_A_'||to_char(sysdate,'yyyymmdd');
8 descr := 'Segment Advisor Example';
9 dbms_advisor.create_task(advisor_name => 'Segment Advisor',
10 task_id => :id,
11 task_name => name,
12 task_desc => descr);
13 dbms_advisor.create_object(task_name => name,
14 object_type => 'TABLE',
15 attr1 => 'ZAKI',
16 attr2 => 'A',
17 attr3 => NULL,
18 attr4 => NULL,
19 attr5 => NULL,
20 object_id => obj_id);
21 dbms_advisor.set_task_parameter(task_name => name,
22 parameter => 'recommend_all',
23 value => 'TRUE');
24 dbms_advisor.execute_task(name);
25 end;
26 end;
27 /
PL/SQL 过程已成功完成。
SQL> select af.task_name,
2 ao.attr2 segname,
3 ao.attr3 partition,
4 ao.type,
5 af.message
6 from dba_advisor_findings af, dba_advisor_objects ao
7 where ao.task_id = af.task_id
8 and ao.object_id = af.object_id
9 and ao.owner = 'ZAKI';
TASK_NAME SEGNAME MESSAGE
--------
advisor_t_test_resumable
A
进行压缩, 估计可以省出 10950496 字节。
advisor_20111010
B
启用表 ZAKI.B 的行移动并执行收缩, 估计会节省 21204669 字节。
advisor_A_20111010
A
此对象中的空闲空间小于 10MB。
SQL> select bytes/1024/1024 from user_segments where segment_name='B';
BYTES/1024/1024
---------------
45
SQL> alter table b shrink space cascade;
alter table b shrink space cascade
*
第 1 行出现错误:
ORA-10636: ROW MOVEMENT is not enabled
SQL> alter table b enable row movement;
表已更改。
SQL> select table_name,row_movement from user_tables where table_name='B';
TABLE_NAME ROW_MOVE
------------------------------ --------
B ENABLED
SQL> alter table b shrink space cascade; --回收对象的同时回收对象上索引的空间!
表已更改。
SQL> select bytes/1024/1024 from user_segments where segment_name='B';
BYTES/1024/1024
---------------
20.25
SQL> begin
2 declare
3 name varchar2(100);
4 descr varchar2(500);
5 obj_id number;
6 begin
7 name := 'advisor_ind_'||to_char(sysdate,'yyyymmdd');
8 descr := 'Segment Advisor Example';
9 dbms_advisor.create_task(advisor_name => 'Segment Advisor',
10 task_id => :id,
11 task_name => name,
12 task_desc => descr);
13 dbms_advisor.create_object(task_name => name,
14 object_type => 'INDEX',
15 attr1 => 'ZAKI',
16 attr2 => 'IND_B',
17 attr3 => NULL,
18 attr4 => NULL,
19 attr5 => NULL,
20 object_id => obj_id);
21 dbms_advisor.set_task_parameter(task_name => name,
22 parameter => 'recommend_all',
23 value => 'TRUE');
24 dbms_advisor.execute_task(name);
25 end;
26 end;
27 /
PL/SQL 过程已成功完成。
SQL> select af.task_name,
2 ao.attr2 segname,
3 ao.attr3 partition,
4 ao.type,
5 af.message
6 from dba_advisor_findings af, dba_advisor_objects ao
7 where ao.task_id = af.task_id
8 and ao.object_id = af.object_id
9 and ao.owner = 'ZAKI';
TASK_NAME SEGNAME MESSAGE
advisor_t_test_resumable A
进行压缩, 估计可以省出 10950496 字节。
advisor_20111010 B
启用表 ZAKI.B 的行移动并执行收缩, 估计会节省 21204669 字节。
advisor_A_20111010 A
此对象中的空闲空间小于 10MB。
TASK_NAME SEGNAME MESSAGE
advisor_ind_20111010 IND_B
此对象中的空闲空间小于 10MB。