高水位线的压缩

高水位线的压缩

Oracle 10g开始采用了shrink技术

Shrink是通过事务的方式将数据行从一个数据块转移到另一个数据块。收缩过程中，表仍然可以进行DML操作

当然，事务要能够进行DML操作，还是需要等待收缩引起的事务锁释放。

收缩虽然是事务，但是数据并没有发生变化，因此不会引起触发器的触发。

使用shrink的前提条件

1、表所在的表空间必须使用ASSM（自动段空间管理）

2、在收缩表上必须启用row movement选项

建立一张表

SQL> create table test_shrik (id number ) tablespace users;

Table created.

查看各个表空间管理信息

SQL> select tablespace_name,t.segment_space_management

  2   from dba_tablespaces t

  3  where tablespace_name in ('SYSTEM','USERS');

TABLESPACE_NAME                SEGMEN

------------------------------ ------

SYSTEM                         MANUAL

USERS                          AUTO

插入数据

SQL> insert into test_shrik

  2  select rownum from dual connect by level <=200000;

200000 rows created.

SQL> commit ;

Commit complete.

查看统计信息

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

  2   from user_tables t

  3  where table_name = upper('test_shrik');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK

收集统计信息

SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');

PL/SQL procedure successfully completed.

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

  2   from user_tables t

  3  where table_name = upper('test_shrik');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                            306            0     197855

删除数据

SQL> delete from test_shrik where id >=100000 ;

100001 rows deleted.

SQL> commit ;   

Commit complete.

查看统计信息

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

  2   from user_tables t

  3  where table_name = upper('test_shrik');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                            306            0     197855

再次收集，查看信息

SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');

PL/SQL procedure successfully completed.

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

  2   from user_tables t

  3  where table_name = upper('test_shrik');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                            306            0     101835

启用表的行转移功能

SQL> alter table test_shrik enable row movement ;

Table altered.

压缩高水位线

SQL> alter table test_shrik shrink space ;

Table altered.

查看统计信息

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

  2   from user_tables t

  3  where table_name = upper('test_shrik');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                            306            0     101835

收集统计信息

SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');

PL/SQL procedure successfully completed.

再次查看

SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS

  2   from user_tables t

  3  where table_name = upper('test_shrik');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS

------------------------------ ---------- ------------ ----------

TEST_SHRIK                            151            0      99344

高水位线已经下降了

其他说明

第一次做这个实验的时候并没有成功。

shrink space报ora-10635

ORA-10635: Invalid segment or tablespace type

Cause: Cannot shrink the segment because it is not in auto segment space managed tablespace or it is not a data, index or lob segment.

Action: Check the tablespace and segment type and reissue the statement

原因：表建立在了system表空间。

TABLESPACE_NAME                SEGMEN

------------------------------ ------

SYSTEM                         MANUAL

而system表空间的段管理方式为手动。不满足shrink的要求。

shrink_clause 允许手动地释放表、索引组织表、索引、分区、物化视图和物化视图日志的空间。
这个语句只对自增长的表空间起作用。
这个语句起作用的前提是row movement。所以，你必须先enable row movement。
另外，如果你在表上有行级的触发器，你必须先disable掉触发器才行。

shrink_clause的限制条件
========================================
1. 对cluster，cluster table，或具有Long类型列的对象 不起作用。
2. 不支持具有function-based indexes 或 bitmap join indexes的表
3. 不支持mapping 表或index-organized表。
4. 不支持compressed 表

其他压缩高水位线的方法

Oracle10g之前采用了

move、exp/imp

以前方法的缺点

1 需要停掉业务

2 需要重建索引

下面语句来自

http://yangtingkun.itpub.net/post/468/11764

关于row movement 的副作用

可以看到，一个UPDATE语句被ORACLE拆成了三条语句。首先是对当前的记录进行更新（指定ROWID），第二步删除指定ROWID的记录，第三步用更新后的值重新插入记录。执行完第三步，新的记录已经插入到分区P2中了

如果按照相反的顺序执行SQL_UNDO，却发 现无法恢复到以前的状态。首先删除分区P2中的记录，这没有问题。第二步，插入新的记录，这里已经有问题了，插入时无法指定ROWID，记录仍然会插入到 P2分区中。最后进行更新，这是指定的ROWID是P1分区中的ROWID，这个ROWID在当前根本就不存在，因此，已经无法恢复到原来的状态了。

官方文档的说明：

Shrinking Database Segments Online

You use online segment shrink to reclaim fragmented free space below the high water

mark in an Oracle Database segment. The benefits of segment shrink are these:

■ Compaction of data leads to better cache utilization, which in turn leads to better

online transaction processing (OLTP) performance.

■ The compacted data requires fewer blocks to be scanned in full table scans, which

in turns leads to better decision support system (DSS) performance.

Segment shrink is an online, in-place operation. DML operations and queries can be

issued during the data movement phase of segment shrink. Concurrent DML

operation are blocked for a short time at the end of the shrink operation, when the

space is deallocated. Indexes are maintained during the shrink operation and remain

usable after the operation is complete. Segment shrink does not require extra disk

space to be allocated.

Segment shrink reclaims unused space both above and below the high water mark. In

contrast, space deallocation reclaims unused space only above the high water mark. In

shrink operations, by default, the database compacts the segment, adjusts the high

water mark, and releases the reclaimed space.

Segment shrink requires that rows be moved to new locations. Therefore, you must

first enable row movement in the object you want to shrink and disable any

rowid-based triggers defined on the object.

Shrink operations can be performed only on segments in locally managed tablespaces

with automatic segment space management (ASSM). Within an ASSM tablespace, all

segment types are eligible for online segment shrink except these:

■ IOT mapping tables

■ Tables with rowid based materialized views

■ Tables with function-based indexes

Invoking Online Segment Shrink

Before invoking online segment shrink, view the findings and recommendations of the

Segment Advisor. For more information, see "Using the Segment Advisor" on

page 14-16.

You invoke online segment shrink with Enterprise Manager (EM) or with SQL

commands in SQL*Plus. The remainder of this section discusses the command line

method.

You can shrink space in a table, index-organized table, index, partition, subpartition,

materialized view, or materialized view log. You do this using ALTER TABLE, ALTER

INDEX, ALTER MATERIALIZED VIEW, or ALTER MATERIALIZED VIEW LOG statement

with the SHRINK SPACE clause. Refer to Oracle Database SQL Reference for the syntax

and additional information on shrinking a database object, including restrictions.

Two optional clauses let you control how the shrink operation proceeds:

■ The COMPACT clause lets you divide the shrink segment operation into two phases.

When you specify COMPACT, Oracle Database defragments the segment space and

compacts the table rows but postpones the resetting of the high water mark and

the deallocation of the space until a future time. This option is useful if you have

long-running queries that might span the operation and attempt to read from

blocks that have been reclaimed. The defragmentation and compaction results are

saved to disk, so the data movement does not have to be redone during the second

phase. You can reissue the SHRINK SPACE clause without the COMPACT clause

during off-peak hours to complete the second phase.

The CASCADE clause extends the segment shrink operation to all dependent

segments of the object. For example, if you specify CASCADE when shrinking a

table segment, all indexes of the table will also be shrunk. (You need not specify

CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent

segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS

procedure of the DBMS_SPACE package.

As with other DDL operations, segment shrink causes subsequent SQL statements to

be reparsed because of invalidation of cursors unless you specify the COMPACT clause.