性能调整 order by 和 rownum 导致执行计划改变.附录10053

oradbHome

原帖由 anlinew 于 2009-1-8 11:55 发表
对于含rownum  

插入owner=1的数据后.
test@stat.r> SELECT * FROM (
  2  SELECT  *
  3    FROM TAB p
  4   WHERE p.owner='1'
  5   ORDER BY p.last_ddl_time
  6  ) WHERE ROWNUM < 10;

采用默认采用owner列的index .
的逻辑读取 367  consistent gets

但是我analyze 后 . 就采用last_ddl_time列的index . 逻辑读取变为6. 和owner='SYS'的逻辑读基本一致.

我也同意rownum 的实际的数据物理存储有关系.

oradbHome

insert into tab select '1' OWNER,  OBJECT_NAME  ,  SUBOBJECT_NAME,  OBJECT_ID,
  DATA_OBJECT_ID,  OBJECT_TYPE,  CREATED,  LAST_DDL_TIME ,  TIMESTAMP,
  STATUS,  TEMPORARY,  GENERATED,  SECONDARY
  from tab where owner='SYS'

这样批量插入数据后owner=1的数据在物理上是在一起的. 所以使用LAST_DDL_TIME上index 返回结果很快.

anlinew

11:52:41 SQL> select * from (select * from test where owner='1' order by created) where rownum <10;

已选择9行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=9 Bytes=159
          3)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=5 Card=10 Bytes=1770)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=
          5 Card=7015 Bytes=596275)

   4    3         INDEX (FULL SCAN) OF 'I_TEST_3' (INDEX) (Cost=2 Card
          =42)

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

11:52:48 SQL> select * from (select * from test where owner='SYS' order by created) where rownum <10;

已选择9行。


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=9 Bytes=159
          3)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=5 Card=9 Bytes=1593)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=
          5 Card=6916 Bytes=587860)

   4    3         INDEX (FULL SCAN) OF 'I_TEST_3' (INDEX) (Cost=2 Card
          =38)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1403  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed


我这个例子更明显一些，一样的数据，一样的执行计划，很不一样的执行成本

[ 本帖最后由 anlinew 于 2009-1-8 13:38 编辑 ]

anlinew

原帖由 oradbHome 于 2009-1-8 13:27 发表
insert into tab select '1' OWNER,  OBJECT_NAME  ,  SUBOBJECT_NAME,  OBJECT_ID,
  DATA_OBJECT_ID,  OBJECT_TYPE,  CREATED,  LAST_DDL_TIME ,  TIMESTAMP,
  STATUS,  TEMPORARY,  GENERATED,  SECONDARY
  from tab where owner='SYS'

这样批量插入数据后owner=1的数据在物理上是在一起的. 所以使用LAST_DDL_TIME上index 返回结果很快.

这个不是主要的，看上面的例子，是更慢

oradbHome

原帖由 anlinew 于 2009-1-8 13:34 发表
11:52:41 SQL> select * from (select * from test where owner='1' order by created) where rownum  select * from (select * from test where owner='SYS' order by created) where rownum  

select * from (select * from test where owner='1' order by created) where rownum <10;

执行多次试一试.?
我第一次是 104  consistent gets , 第二次就变为 6 consistent gets .

anlinew

原帖由 oradbHome 于 2009-1-8 14:29 发表


select * from (select * from test where owner='1' order by created) where rownum  

那个例子的确会有偏差
这样构造的数据可能更符合我要表达的意思
[php]
insert into test select '1' OWNER,  OBJECT_NAME  ,  SUBOBJECT_NAME,  OBJECT_ID,
  DATA_OBJECT_ID,  OBJECT_TYPE, sysdate CREATED, sysdate  LAST_DDL_TIME ,  TIMESTAMP,
  STATUS,  TEMPORARY,  GENERATED,  SECONDARY
  from test where owner='SYS'
[/php]

你好好想想stopkey下数据的访问机制，应该就明白我的意思了

而实际情况下可能业务就是这个情况，要查的数据通常就是近期发生的数据

oradbHome

谢谢. 我在仔细研究下.

棉花糖ONE

最好的方法就是建组合索引,这样不管你where字段的选择性高不高,只要走组合索引都能快