1、避免动态分配的缺陷 创建本地管理的表空间； 合理设置segment的大小； 监控将要扩展的segment： SELECT owner, table_name, blocks, empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < .1;
2、high water mark 记录在segment header block中，在segment创建的时候设定在segment的起始位置，当记录被插入的时候以5个block的增量增加，truncate可以重设high water mark的位置，但delete不能。 在full table scan中，oracle会读取high water mark以下的所有的数据块，所以high water mark以上的块也许会浪费存储空间，但不会降低性能。
可以通过下列方法收回表中high water mark以上的块： Alter table_name deallocate unused； 对于high water mark以下的块： 使用import/export工具：export数据；drop或truncate表；import数据。或者利用alter table tanle_name move命令去移动表的存储位置（此时需要重建索引）。
3、表统计 用analyize命令生成表统计，然后到dba_table查询相关信息。 ANALYZE TABLE ndls.t_wh_shipping_bill COMPUTE STATISTICS; SELECT num_rows, blocks, empty_blocks as empty,avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner ='NDLS' AND table_name='T_WH_SHIPPING_BILL';
Columns Description NUM_ROWS Number of rows in the table BLOCKS Number of blocks below the table high-water mark EMPTY_BLOCKS Number of blocks above the table high-water mark AVG_SPACE Average free space in bytes in the blocks below high-water mark AVG_ROW_LEN Average row length, including row overhead CHAIN_CNT Number of chained or migrated rows in the table
4、block size 通过下列方法可以最小化block的访问次数： 使用更大的block size；紧密压缩行；阻止行镜像。后两者存在冲突，越多的行被压缩在一个block里，越容易产生镜像。Block size 在数据库创建的时候设定，不能被轻易改变，是读取数据文件时最小的IO单元，大小范围是2K－64K，应该设置成OS块的整数倍，小于或等于OS IO时能读取的存储区域。
较小的block size的优点：极少block竞争；有利于较小的行和随机访问。缺点是存在相当高的成本，每个block的行数更少，可能需要读取更多的index块。Block size的选择影响系统的性能，在一个OLTP环境中，较小的block size更合适，而在DSS环境中，适宜选择较大的block size。
5，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、镜像行的表进行重建，对建立不稳定表上的索引应有规律的进行重建，并尽可能创建本地管理的表空间。
转载于:https://www.cnblogs.com/zndavid/archive/2009/04/08/1431861.html
相关资源：Oracle性能调整的十大要点