一。做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语句有变化,但是与之相关的数据可能还存在于数据缓存中,减少了物理读,所以执行时间缩短很正常。。。。所以别高兴太早,多次反复测试,如果这时候发现性能有提高,那就可以放心的手舞足蹈了。。。。。。