Oracle性能测试记录

Oracle性能测试记录

Oracle 11g,，IBM pc机，winxp，双核3GHz，3G内存 

1.查看表结构 

SQL> desc test;
Name            Type           Nullable Default Comments
--------------- -------------- -------- ------- --------
ID              NUMBER(8)                               
SER_CODE        VARCHAR2(128)                           
SOAP_ACTION     VARCHAR2(1024)                          
TARGET_SER_CODE VARCHAR2(100)                           
TARGET_SER_OPER VARCHAR2(256)                           
REPLY_SER_CODE  VARCHAR2(100)  Y                        
REPLY_SER_OPER  VARCHAR2(1024) Y   

2.获取总记录数（耗时3.297s） 

SQL> select count(*) from test;
 
  COUNT(*)
----------
    638976 

3.拷贝10W记录并创建新表 

SQL> create table test2 as select * from test where rownum < 100000;

耗时3.469s 

4.查询1000条记录

SQL> select * from test where rownum < 1000;

耗时0.966s 

5.选取rownum在2-8之间的记录 

SQL> select * from (select rownum rn , id from test) t where t.rn between 2 and 8;
 
        RN        ID
---------- ---------
         2       302
         3         1
         4         2
         5     30303
         6     30304
         7       301
         8       302

耗时3.515s

SQL> select * from (select rownum rn,id from test where rownum < 8) t where t.rn between 2 and 8;

        RN        ID
---------- ---------
         2       302
         3         1
         4         2
         5     30303
         6     30304
         7       301
         8       302

耗时0.056s 

6.综合测试

select * from (select rownum rn , id from test where rownum <10000) t where t.rn between 2 and 10000;

耗时4.812s

select * from (select rownum rn , id from test) t where t.rn between 2 and 10000;

耗时8.172s

select * from (select rownum rn , id from test where rownum <100000) t where t.rn between 2 and 100000;

耗时47.453

select * from (select rownum rn , id from test) t where t.rn between 2 and 100000;

耗时50.703s

结论：如果使用rownum选取某一范围记录，使用子查询方式时应该加上 where rownum语句，否则会影响性能。