【IT168 技术文档】

　　 Oracle性能优化:数据库配置和IO问题
      Oracle性能优化经验分享之系统参数设置
      Oracle性能优化之优化排序操作
      Oracle性能优化之诊断latch竞争
Oracle性能优化之Rollback(undo)Segment优化
Oracle性能优化之LockContention
Oracle性能优化之应用优化
       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';
 

　　　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;
 

　　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、镜像行的表进行重建，对建立不稳定表上的索引应有规律的进行重建，并尽可能创建本地管理的表空间。