oracle性能调整的十大要点-PCTFREE、PCTUSED

八、PCTFREE、PCTUSED

1）PCTFREE、PCTUSED使你能控制一个segment里所有数据块里free space的使用。
PCTFREE：一个数据块保留的用于块里已有记录的可能更新的自由空间占block size的最小比例。
PCTUSED：在新记录被插入block里之前这个block可以用于存储行数据和其他信息的空间所占的最小比率。

2）这两个参数的使用
如果创建表的时候指定pctfree＝20％，oracle会在这个表的data segment的每个block都保留20％的空间用于已有记录的更新。Block的已使用空间上升到整个block size的80％时，这个block将移出free list；在提交了delete、update之后，oracle server处理这条语句并检查对应block的已使用空间是否低于PCTUSED，如果是，则这个block放进free list。

3）PCTFREE、PCTUSED的设定
• PCTFREE
– Default 10
– Zero if no UPDATE activity
– PCTFREE = 100 × upd / (average row length)
• PCTUSED
– Default 40
– Set if rows deleted
– PCTUSED = 100 – PCTFREE – 100 × rows × (average row length) / blocksize
其中，upd ： the average amount added by updates, in bytes。This is determined by subtracting the average row length of intercurrent average row length;
average row length：在运行了analyize命令之后，这个值可以从dba_tables中的avg_row_len列中获得。
rows ： the number of rows to be deleted before free list maintenance occurs。

4）Delete、update可以增加block的自由空间，但是释放出来的空间有可能是不连续的，oracle在下列情况下会对碎片进行整理：一个block有足够的自由空间容纳row piece，但是由于每个碎片都较小以至这个row piece不能存放在一个连续的section中。

6、Migration和Chaining

1）如果一行的数据太大以至一个单独的block容纳不下，会产生两种现象：
A、Chaining：行数据太大以至一个空block容纳不下，oracle会将这一行的数据存放在一个或多个block 组成的block chain中，insert、update都可能导致这个问题，在某些情况下row chaining是不能避免的。
B、Migration：一次update操作可能导致行数据增大，以至它所在的block容纳不下，oracle server会去寻找一个有足够自由空间容纳整行数据的block，如果这样的block存在，oracle server把整行移到新的block，在原位置保存一个指向新存放位置的镜像行，镜像行的rowid和原来的rowid一致。
Chaining、Migration的弊端：insert、update的性能降低，索引查询增加了IO次数。

2）检测migration和chaining：
Analyize table table_name compute statistics；
Select num_rows,chain_cnt from dba_tables where table_name=’...’;
查询镜像行：
Analyize table table_name list chained rows；
Select owner_name,table_name,head_rowid from chained_rows where table_name=’...’;
产生Migration的原因可能是由于PCTFREE设置的太低以至没有保留足够的空间用于更新。
可以通过增加PCTFREE的值避免行镜像产生。

3）消除镜像行的步骤：
运行analyize table ... list chained rows;
复制镜像行到另一个表tmp；
从源表中删除这些行；
从tmp中将这些行插回到源表中。
脚本：
/* Get the name of the table with migrated rows */
accept table_name prompt ’Enter the name of the table with migrated rows: ’
/* Clean up from last execution */
set echo off
drop table migrated_rows;
drop table chained_rows;
/* Create the CHAINED_ROWS table */
@?/rdbms/admin/utlchain
set echo on
spool fix_mig
/* List the chained & migrated rows */
analyze table &table_name list chained rows;
/* Copy the chained/migrated rows to another table */
create table migrated_rows as
select orig.* from &table_name orig, chained_rows cr
where orig.rowid = cr.head_rowid
and cr.table_name = upper(’&table_name’);
/* Delete the chained/migrated rows from the original table */
delete from &table_name
where rowid in ( select head_rowid from chained_rows );
/* Copy the chained/migrated rows back into the original table */
insert into &table_name select * from migrated_rows;
spool off
使用这个脚本时，必须将涉及到的外键约束去掉。

7、索引重组

在一个不稳定的表上建索引会影响性能，一个索引block只有完全空时才能进入free list，即使一个索引block里只含有一个条目，它也必须被维护，因此索引需要进行阶段性的重建。

1）检查索引是否需要重组
A、收集一个index的使用统计
ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;
B、查看收集的统计数据
SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;

Column Description
LF_ROWS Number of values currently in the index
LF_ROWS_LEN Sum in bytes of the length of all values
DEL_LF_ROWS Number of values deleted from the index
DEL_LF_ROWS_LEN Length of all deleted values

C、如果浪费超过20％则索引需要重建
ALTER INDEX acct_no_idx REBUILD;
D、或者对索引进行整理
Alter index acct_no_idx coalesce;

2）标记未使用的索引
A、 开始监测索引的使用
Alter index hr.emp_name_ix monitoring usage;
B、 停止监测索引的使用
Alter index hr.emp_name_ix nomonitoring usage;
C、 查询索引的使用情况
Select index_name,used from v$object_usage;
删除未使用过的索引，可以降低DML操作的成本，从而提升系统性能。

为了尽可能经济的利用block，应对存在较多空block、镜像行的表进行重建，对建立不稳定表上的索引应有规律的进行重建，并尽可能创建本地管理的表空间。