段空间内进行了许多update和delete操作以后容易出现空间的空闲碎片,这将会给dml操作带来一定的性能影响。
使用segment advisor可以帮助判断是否有空间可以回收,
advisor可以在三个级别给出建议信息:
1,对象级别(object)
2,段级别(segment)
3,表空间级别(tablespace)
dbms_advisor.create_task(
advisor_name, --
task_id, --
task_name, --
task_desc --
);
dbms_advisor.create_object(
task_name ,--
object_type ,--
attr1 ,--
attr2 ,
attr3 ,
attr4 ,
attr5 ,
object_id
);
参数的详细解析如下:
Input Parameter
OBJECT_TYPE ATTR1 ATTR2 ATTR3 ATTR4
TABLESPACE tablespace_name NULL NULL Unused. Specify NULL.
TABLE schema_name table_name NULL Unused. Specify NULL.
INDEX schema_name index_name NULL Unused. Specify NULL.
TABLE PARTITION schema_name table_name table_partition_name Unused. Specify NULL.
INDEX PARTITION schema_name index_name index_partition_name Unused. Specify NULL.
TABLE SUBPARTITION schema_name table_name table_subpartition_name Unused. Specify NULL.
INDEX SUBPARTITION schema_name index_name index_subpartition_name Unused. Specify NULL.
dbms_advisor.set_task_parameter(
task_name ,--
parameter ,--
value ,--
);
上面的parameter和默认值分别为:
MODE COMPREHENSIVE
TIME_LIMIT UNLIMITED
RECOMMEND_ALL TRUE
一个对象级别(table)的例子:
SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name='T_TEST_RESUMABLE';
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
-------------------------------------------------------------------------------- ------------------ ---------------
T_TEST_RESUMABLE TABLE 11
SQL> select count(*) from t_test_resumable;
COUNT(*)
----------
99942
SQL> delete from t_test_resumable where rownum <50000;
49999 rows deleted
SQL> commit;
Commit complete
SQL>
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_t_test_resumable';
8 descr := 'Segment Advisor Example';
9
10 dbms_advisor.create_task(advisor_name => 'Segment Advisor',
11 task_id => :id,
12 task_name => name,
13 task_desc => descr);
14
15 dbms_advisor.create_object(task_name => name,
16 object_type => 'TABLE',
17 attr1 => 'TEST',
18 attr2 => 'T_TEST_RESUMABLE',
19 attr3 => NULL,
20 attr4 => NULL,
21 attr5 => NULL,
22 object_id => obj_id);
23
24 dbms_advisor.set_task_parameter(task_name => name,
25 parameter => 'recommend_all',
26 value => 'TRUE');
27
28 dbms_advisor.execute_task(name);
29 end;
30 end;
31
32 /
PL/SQL procedure successfully completed
id
---------
888
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 = 'TEST';
TASK_NAME SEGNAME PARTITION TYPE MESSAGE
------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
advisor_t_test_resumable T_TEST_RESUMABLE TABLE The free space in the object is less than 10MB.
SQL>
SQL>
SQL> Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
SEGMENT_NAME SUM(BYTES)/1024/1024
-------------------------------------------------------------------------------- --------------------
T_TEST_RESUMABLE 11
TEST_STORAGE2 0.3125
TEST_STORAGE1 0.0625
TEST_STORAGE3 2
TEST_STORAGE 0.25
SQL> select table_name,row_movement from user_tables where table_name='T_TEST_RESUMABLE';
TABLE_NAME ROW_MOVEMENT
------------------------------ ------------
T_TEST_RESUMABLE DISABLED
SQL> alter table t_test_resumable enable row movement;
Table altered
SQL> alter table t_test_resumable shrink space;
Table altered
SQL> Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
SEGMENT_NAME SUM(BYTES)/1024/1024
-------------------------------------------------------------------------------- --------------------
T_TEST_RESUMABLE 5
TEST_STORAGE2 0.3125
TEST_STORAGE1 0.0625
TEST_STORAGE3 2
TEST_STORAGE 0.25
SQL>
表占用的11m空间通过shrink后释放了6m。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-622387/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-622387/