Oracle性能测试和优化

1、Oracle性能测试和优化测试目的：测试Oracle入库以及并发查询性能测试环境：主机：Sun Enterprise 420R (2 X UltraSPARC-II 450MHz)内存：2048MB存储：Sun T3、Raid 0+1、RAW device(裸设备)、Cache 1G、FC 1Gbps(100M/S)操作系统：SunOS 5.9 Generic_118558-08 sun4u卷管理软件：Veritas Volume Manager数据库：Oracle 9i RAC / Oracle 10g光纤HUB ：Sun 7 Port*1 Gbps测试程序：使用Oracle OCI接口的C

2、程序(import)，4个子进程同时执行插入操作，连接同一个实例数据库设计：采用Oracle Partition技术，按小时划分，一天分为24个分区。数据在线保存60天，则经常在线分区为24*60=1440个，在每个分区Pxxxx上根据通话时间字段建立局部分区索引，然后在整个表上根据手机号字段建立全局分区索引Sun 3510 磁盘阵列性能测试T3 Cache模式：WriteThrough测试方法T3阵列的缓存写模式缺省下是writethrough，未启动应用前，首先用mkfile写阵列测试从主机到阵列的I/O性能，通过iostat观察测试结果结论经过观察可以发现最高的写入速率在10MB/S左右

3、，且I/O服务响应时间太长，初步评估I/O系统会成为系统性能瓶颈。T3 Cache模式：WriteBehand测试方法T3阵列的缓存写模式是writebehand，未启动应用前，首先用mkfile写阵列测试从主机到阵列的I/O性能，通过iostat观察测试结果结论经过观察可以发现最高的写入速率在91MB/S左右，且I/O服务响应时间大大缩短，估计I/O系统的改进会为系统性能带来一定提升两种方式比较1. Cache模式设置为WriteBehand时，性能远高于WriteThrough2. Cache模式设置为WriteBehand，安全性稍差，如果此时数据库出现硬件故障，则数据库无法恢复一致性并

4、发插入的性能测试用例一测试环境描述1. 4个子进程并行执行插入操作；使用批量导入模式，一次绑定3000条记录；连接同一数据库实例2. T3 Cache模式设置为：WriteBehand(高性能，安全性稍差)3. Oracle 9i RAC + 9.2.0.4 Patch Package4. 索引：Global Partition Index(手机号) 、Local Partition Index(时间)5. 分区表：一小时一个分区，每个分区1.0-1.5亿条数据，全表数据约11亿3400万条6. 一条消息约80字节相关sql语句建表语句：CREATE TABLE log_table( src_

5、addr VARCHAR2(11), dest_addr VARCHAR2(11), saft_flag CHAR(1), priorityCHAR(1), request_qosCHAR(1), request_qopCHAR(1), start_timeVARCHAR2(14), statusCHAR(6), contextVARCHAR2(150) ) TABLESPACE TEST PCTUSED40PCTFREE0STORAGE (INITIAL4MNEXT4MMAXEXTENTS2147483645PCTINCREASE0BUFFER_POOLDEFAULT )PARTITION

6、BY RANGE (start_time)(PARTITION log_20050601_01 VALUES LESS THAN ('20050601010000') TABLESPACE TEST, PARTITION log_20050601_02 VALUES LESS THAN ('20050601020000') TABLESPACE TEST, PARTITION log_20050601_23 VALUES LESS THAN ('20050601230000') TABLESPACE TEST, PARTITION log_200

7、50601_24 VALUES LESS THAN ('20050602000000') TABLESPACE TEST );手机号字段建全局分区索引（global partition index）：CREATE INDEX log_global_part_idx ON log_table(src_addr)TABLESPACE INDXGLOBAL PARTITION BY RANGE(src_addr)(PARTITION log_1340_1340 VALUES LESS THAN('#39;), PARTITION log_1340_13

8、41 VALUES LESS THAN('#39;), PARTITION log_1348_1349 VALUES LESS THAN('#39;), PARTITION log_1349_1350 VALUES LESS THAN('#39;), PARTITION log_1397_1398 VALUES LESS THAN('#39;), PARTITION log_1398_1399 VALUES LESS THAN(MAXVALUE) );时间字段建局部分

9、区索引（local partition index）：create index log_src_addr_idx on log_table(start_time) LOCAL TABLESPACE INDX INITRANS 10 MAXTRANS 255 STORAGE ( INITIAL4M NEXT4M MAXEXTENTS2147483645 PCTINCREASE0 )NOLOGGINGPARALLEL;预期结果A. Sun StorEdge T3 cache模式设置为writebehand ，当执行write时，数据写入T3 cache即返回成功标识，数据由cache刷到磁盘的过程

10、由T3自动完成B. T3 cache -> 磁盘的操作主要是批量数据写入、数据块连续写入，所以性能比writethrough模式高很多C. 数据 -> cache的速度大于cache -> 磁盘的速度，随着数据的持续插入，cache中缓存的数据增加，插入速度将逐渐减慢D. 由于使用全局分区索引(B-tree索引)，随着表中消息数量增加，B-tree索引degree增加，插入速度将逐渐减慢E. import程序首先在分区1插入5000万，然后在分区2插入5000万，直到分区8，然后再循环向分区1插入5000万，依此类推；在时间字段建局部分区索引，各分区索引独立存在，当每个分区数

11、据量在5000万-1亿、1亿-1.5亿时，性能有可能出现突降；预计整表在数据量达到4亿、8亿时，性能应该有明显降低测试结果结论1. T3 在writebehand模式下性能远远大于writethrough模式2. 预期结果中C、D两项得到确认，曲线成下降状态3. 预期结果中E项得到确认，曲线中有两处下降明显，分别在表中数据达到4亿、8亿条时出现4. 使用全局分区索引给数据整理带来隐患，当数据量达到一定程度后，删除分区的速度将是无法忍受的，将达到10个小时以上测试用例二测试环境描述1. Oracle 10g + 10.1.0.4.0 Patch Package2. 索引：Local Partit

12、ion Index(手机号) 、Local Partition Index(时间)3. 分区表：一小时一个分区，每个分区1.00-1.5亿条数据，全表数据约10亿2660万条4. 其他条件与测试用例1相同相关sql语句建表语句：同测试用例1 手机号字段建局部分区索引（local partition index）：CREATE INDEX log_global_part_idx ON log_table(src_addr) LOCALTABLESPACE INDXINITRANS 10 MAXTRANS 255 STORAGE ( INITIAL4M NEXT4M MAXEXTENTS21474

13、83645 PCTINCREASE0 )NOLOGGINGPARALLEL;时间字段建局部分区索引（local partition index）：同测试用例1预期结果A. 数据 -> cache的速度大于cache -> 磁盘的速度，随着数据的持续插入，cache中缓存的数据增加，插入速度将逐渐减慢B. 使用局部分区索引(B-tree索引)，随着分区中纪录数增加，B-tree索引degree增加，插入速度将逐渐减慢C. import程序首先在分区1插入5000万，然后在分区2插入5000万，直到分区8，然后再循环向分区1插入5000万，依此类推；时间、手机号字段建局部分区索引，各分

14、区索引独立存在，当每个分区数据量在5000万-1亿、1亿-1.5亿时，性能有可能出现突降；预计整表在数据量达到4亿、8亿时，性能应该有明显降低D. 局部索引对插入性能的影响比全局索引小，从曲线图看，下降趋势应该较平缓测试结果结论1. 预期结果中B两项得到确认，曲线成下降状态2. 预期结果中C项得到确认，曲线中有两处下降明显，分别在表中数据达到4亿、8亿条时出现3. 预期结果中D项得到确认，曲线下降比较平缓两种环境下性能的比较测试环境、测试程序比较测试结果比较结论1. 测试2使用局部分区索引，插入速度下降较慢2. 测试2使用oracle 10g，由于对10g不熟悉，没有做任何优化；测试前磁盘没有

15、做碎片整理；测试在解决方案验证中心进行，测试服务器是公用的，测试1基本在周末进行，没有其他程序强占资源，测试2有可能受其他测试影响，所以整体效率不如测试13. 测试2使用局部分区索引，便于数据整理，例如：删除分区4. 为提高DML操作性能，一个分区中的数据不能过多5. 对DML（插入、删除）操作频繁的表，建议使用局部分区索引查询的性能测试用例三测试环境描述1. 同测试用例一相关sql语句（A、B、c共三类）A. select * from log_table where src_addr='xxxxxxxxxxx'B. select * from log_table where

16、 src_addr='#39; and start_time>'20050601020000' and start_time<'20050601040000'C. select * from log_table where start_time>'20050601020000' and start_time<'20050601040000' and src_addr='#39;预期结果A. 手机号使用全局分区索引，查询速度比不建索引应该有显著提

17、高测试结果A类语句执行计划Execution Plan-0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10071255 Bytes=1208550600)10 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOG_TABLE' (Cost= 2 Card=10071255 Bytes=1208550600)21 INDEX (RANGE SCAN) OF 'LOG_GLOBAL_PART_IDX' (NON-UNIQUE) (Cost=1 Card=4028502)B类语句执行

18、计划Execution Plan-0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=24 Bytes=1536)10 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOG_TABLE' (Cost=2 Card=24 Bytes=1536)21 INDEX (RANGE SCAN) OF 'LOG_GLOBAL_PART_IDX' (NON-UNIQUE) (Cost=1 Card=1209600)C类语句执行计划Execution Plan-0SELECT STATEMENT O

19、ptimizer=CHOOSE (Cost=2 Card=24 Bytes=1536)10 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOG_TABLE' (Cost=2 Card=24 Bytes=1536)21 INDEX (RANGE SCAN) OF 'LOG_GLOBAL_PART_IDX' (NON-UNIQUE) (Cost=1 Card=1209600)结论优点：1. 建立索引后，查询速度有了极大的提高，性能提高千倍以上2. 从执行计划上看，A、B、C类语句都使用了索引（手机号），根据rowid定位表中纪录

20、，查询速度有极大提高不足：1. B、C类语句在两个分区中查找数据，理论上应该比全表查询快，但从执行计划分析，oracle执行B、C类语句与A类语句一样，并没有根据查询条件确定分区范围2. 当执行truncate、drop分区操作时，全局索引影响性能测试用例四测试环境描述1. 同测试用例二相关sql语句（A、B、c共三类）A. select * from log_table where src_addr='xxxxxxxxxxx'B. select * from log_table where src_addr='#39; and start_ti

21、me>'20050601020000' and start_time<'20050601040000'C. select * from log_table where start_time>'20050601020000' and start_time<'20050601040000' and src_addr='#39;预期结果A. 手机号使用局部分区索引B. 使用局部分区索引，便于数据整理，例如：truncate、drop分区测试结果A类语句执行计划Execution P

22、lan-0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=83 Card=9 Bytes=657)10 PX COORDINATOR21 PX SEND* (QC (RANDOM) OF ':TQ10000' (Cost=83 Card=9 Bytes=657:Q1000)32 PX PARTITION RANGE* (ALL) (Cost=83 Card=9 Bytes=657) :Q100043 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'LOG_TABLE' :Q1000 (TABL

23、E) (Cost=83 Card=9 Bytes=657)54 INDEX* (RANGE SCAN) OF 'LOG_GLOBAL_PART_IDX' (INDEX):Q1000 (Cost=73 Card=9)B类语句执行计划Execution Plan-0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=17 Card=3 Bytes=219)10 PX COORDINATOR21 PX SEND* (QC (RANDOM) OF ':TQ10000' (Cost=17 Card=3 Bytes=219):Q100032

24、PX PARTITION RANGE* (ITERATOR) (Cost=17 Card=3 Bytes=219):Q100043 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'LOG_TABLE' :Q1000 (TABLE) (Cost=17 Card=3 Bytes=219)54 INDEX* (RANGE SCAN) OF 'LOG_GLOBAL_PART_IDX' (INDEX):Q1000 (Cost=7 Card=9)2PARALLEL_TO_SERIAL3PARALLEL_COMBINED_WITH_CHILD4PARALLEL_COMBINED_WITH_PARENT5PARALLEL_COMBINED_WITH_PARENTC类语句执行计划Execution Plan-0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=17 Card=3 Bytes=219)10 PX COORDINATOR21 PX SEND* (QC (RAN