select x from t where rownum<2为何会很慢？

最新推荐文章于 2021-04-08 21:33:08 发布

转载最新推荐文章于 2021-04-08 21:33:08 发布 · 359 阅读

本文通过一个Oracle数据库中慢查询的实际案例，分析了当执行简单查询时出现高延迟的原因。通过对不同删除操作的影响进行对比，揭示了数据库块扫描效率的问题所在。

今天在对一个表执行select x from t where rownum<2的时候很慢，大约要一两分钟才能返回。

set autotrace on来看consistent gets竟然有9万多

Statistics
———————————————————-
0 recursive calls
4 db block gets
93910 consistent gets
93809 physical reads
480 redo size
293 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

plan很简单，没有任何问题

Execution Plan
———————————————————-
0 SELECT STATEMENT ptimizer=RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF ‘T’

以前也遇到过这种应该很快返回但是却很慢的问题：　INDEX RANGE SCAN (MIN/MAX)很慢的问题

很快便想到了原因，并做了一个简单的测试

一个block只能存放一行数据

SQL> create table t(x int, y varchar2(2000))
2 pctfree 70
3 pctused 30
4 tablespace data01;

Table created.

顺序插入1000行数据，占有1000个block

SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i,lpad(’x',2000));
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

删除前面500行数据

SQL> delete from t where x<500;

499 rows deleted.

我们看到rownum<2的时候还是会读取前面已经删除的500个block

SQL> set autotrace traceonly
SQL> select x from t where rownum<2;

Execution Plan
———————————————————-
0 SELECT STATEMENT ptimizer=RULE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF ‘T’

Statistics
———————————————————-
0 recursive calls
0 db block gets
504 consistent gets
0 physical reads
0 redo size
484 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

回滚，改为删除后面的500行

SQL> rollback;

Rollback complete.

SQL> delete from t where x>500;

9500 rows deleted.

consistent gets降低到4，在第一个block上就找到了数据
SQL> set autotrace traceonly
SQL> select x from t where rownum<2;

Execution Plan
———————————————————-
0 SELECT STATEMENT ptimizer=RULE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF ‘T’

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

结论：
Execution Plan
———————————————————-
0 SELECT STATEMENT ptimizer=RULE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF ‘T’

执行计划顺序检索每个block,如果为空就继续向下走，和文章开始的那个索引的例子类似

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-665698/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-665698/