Tuesday, June 8, 2010

LOBSEGMENT defragmentation

Method 1: Shrink space, which is slow
Method 2: "Move" LobSegment


[oracle@vmxdb01 ~]$ sqlplus donghua/ora123

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 8 15:02:14 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @lob_test.sql
SQL> col segment_name for a30
SQL> col index_name for a30
SQL> set lin 90
SQL> col mbytes for 99999999999
SQL> set timing on
SQL>
SQL> drop table tbl_lob_test purge;

Table dropped.

Elapsed: 00:00:01.70
SQL>
SQL> CREATE TABLE tbl_lob_test
2 (
3 col1 CHAR (2000),
4 col2 CHAR (2000),
5 col3 CHAR (2000),
6 message CLOB
7 )
8 LOB (message) STORE AS (TABLESPACE users DISABLE STORAGE IN ROW);

Table created.

Elapsed: 00:00:00.21
SQL>
SQL> insert into tbl_lob_test
2 select owner, object_name,object_name, object_name from dba_objects;

72458 rows created.

Elapsed: 00:00:56.44
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME from dba_lobs
2 where table_name='TBL_LOB_TEST' and owner='DONGHUA';

SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000074592C00004$$ SYS_IL0000074592C00004$$

Elapsed: 00:00:00.14
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;

SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 4194304
TBL_LOB_TEST 603979776
SYS_LOB0000074592C00004$$ 603979776

Elapsed: 00:00:02.15
SQL>
SQL> delete from tbl_lob_test;

72458 rows deleted.

Elapsed: 00:01:13.64
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;

SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 11534336
TBL_LOB_TEST 603979776
SYS_LOB0000074592C00004$$ 603979776

Elapsed: 00:00:02.62
SQL>
SQL> insert into tbl_lob_test
2 select owner, object_name,object_name, object_name from dba_objects;

72458 rows created.

Elapsed: 00:01:26.81
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;

SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 14680064
TBL_LOB_TEST 603979776
SYS_LOB0000074592C00004$$ 1207959552

Elapsed: 00:00:01.68
SQL>
SQL> alter table tbl_lob_test modify lob (message) (shrink space);

Table altered.

Elapsed: 00:02:29.18
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;

SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 14680064
TBL_LOB_TEST 603979776
SYS_LOB0000074592C00004$$ 603979776

Elapsed: 00:00:01.09
SQL>
SQL> alter table tbl_lob_test
2 move lob (message) STORE AS tbl_lob_test_lob_msg
3 (TABLESPACE users
4 index tbl_lob_test_lob_msg_idx (tablespace users) );

Table altered.

Elapsed: 00:01:06.87
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;

SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 4194304
TBL_LOB_TEST 603979776
TBL_LOB_TEST_LOB_MSG 603979776

Elapsed: 00:00:00.05
SQL>
SQL> truncate table tbl_lob_test;

Table truncated.

Elapsed: 00:00:02.10
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;

SEGMENT_NAME MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$ 65536
TBL_LOB_TEST 65536
TBL_LOB_TEST_LOB_MSG 65536

Elapsed: 00:00:00.06
SQL>
SQL> drop table tbl_lob_test purge;

Table dropped.

Elapsed: 00:00:00.49
SQL>
SQL> CREATE TABLE tbl_lob_test
2 (
3 col1 CHAR (2000),
4 col2 CHAR (2000),
5 col3 CHAR (2000),
6 MESSAGE CLOB
7 )
8 LOB (
9 MESSAGE)
10 STORE AS
11 tbl_lob_test_lob_msg (
12 TABLESPACE users
13 DISABLE STORAGE IN ROW
14 INDEX tbl_lob_test_lob_msg_idx ( TABLESPACE users ));

Table created.

Elapsed: 00:00:00.18
SQL>
SQL> insert into tbl_lob_test values (1,2,3,4);

1 row created.

Elapsed: 00:00:00.06
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
2 group by segment_name;

SEGMENT_NAME MBYTES
------------------------------ ------------
TBL_LOB_TEST_LOB_MSG_IDX 65536
TBL_LOB_TEST 65536
TBL_LOB_TEST_LOB_MSG 65536

Elapsed: 00:00:00.05
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME from dba_lobs
2 where table_name='TBL_LOB_TEST' and owner='DONGHUA';

SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
TBL_LOB_TEST_LOB_MSG TBL_LOB_TEST_LOB_MSG_IDX

Elapsed: 00:00:00.04