做sql性能测试时要注意的小问题

一。做sql优化测试的时候你可能会遇到这样一种情况：对表insert，测完后再delete，再insert，delete。。。。这样几次后发现sql执行越来越慢，究竟是sql写的越来越有问题，还是其他问题，有时候甚至sql不变也会遇到这样的情况。下面我们来看看到底是怎么回事：

1.创建表test

SQL> create table test as select object_id,object_name from dba_objects;

2.查询表的记录数

SQL> set timing on;
SQL> set autotrace trace stat;
SQL> select count(1) from test;


Elapsed: 00:00:00.03


Statistics
----------------------------------------------------------
        191  recursive calls
          0  db block gets
        323  consistent gets
        236  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

3.插入数据

SQL> INSERT/*+append*/ INTO TEST SELECT 0,'qq' FROM TEST,(SELECT 1 FROM dual CONNECT BY level<100);

4929408 rows created.

4.将上步骤插入的数据删除

SQL> delete test where object_id=0;

4929408 rows deleted.

5.再次查询表的记录数（记录数量变化，sql语句也没变化）

SQL> select count(1) from test;

Elapsed: 00:00:01.57

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      15330  consistent gets
       6384  physical reads
     600496  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

       这样就出现表的数量没有变化，sql语句没有变化，但是执行时间却变长了，而且随着测试的次数越多，sql耗时越长。我们可以看到经过insert，delete后sql读取数据块的数量明显增多，这也是导致执行时间变长的原因。你也许会问，同样的数据量为什么读取的数据块会有这么大的差别？原因就在于高水位线(HWM)这个东西（可以通过SELECT A.BLOCKS FROM DBA_TABLES A WHERE A.TABLE_NAME='XX'来查看，查看前记得做表分析;），如果你不认为干预，这个HWM只会往上涨，不会随着你的delete动作下降，而表做全盘扫描的时候就是扫描这个HWM以下的数据块，所以就造成了扫描的数据块越来越多，耗时越来越长。

       解决办法：重新开始测试时，不用delete，用truncate，重新造数据

二。还有一种测试假象要注意，当你拿到一个有性能问题的sql后，一运行用了200s，经过你冥思苦想后，灵感突然来了，刷刷修改sql后一跑，竟然只用了100s！！！！你高兴的手舞足蹈，眉飞色舞，不过我很遗憾的告诉你，你高兴的太早了，当你拿之前有问题的sql再跑一遍时，耗时竟然变成了80s！！！很正常，因为第一次跑的时候可能离上次运行这个sql有一段时间了，内存中与之相关的数据缓存和共享缓存都被置换出去了，所以第一次跑很耗时，你修改sql后虽然sql语句有变化，但是与之相关的数据可能还存在于数据缓存中，减少了物理读，所以执行时间缩短很正常。。。。所以别高兴太早，多次反复测试，如果这时候发现性能有提高，那就可以放心的手舞足蹈了。。。。。。