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

oradbHome · 发表于 2009-1-8 10:53

棉花 . 但是我还是不明白我的统计信息哪里错了. 导致使用了时间的index .

这个例子就选择了正确的index

test@stat.r> create index IND_TAB_O on TAB (OWNER);

索引已创建。

test@stat.r> create index IND_TAB_T on TAB (LAST_DDL_TIME);

索引已创建。

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;

执行计划
----------------------------------------------------------
Plan hash value: 133653500

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |          |    1 | 177 |    3  (34)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |          |    |    |          |       |
| 2 | VIEW                      |          |    1 | 177 |    3  (34)| 00:00:01 |
|*  3 | SORT ORDER BY STOPKEY    |          |    1 | 97 |    3  (34)| 00:00:01 |
| 4 |    TABLE ACCESS BY INDEX ROWID| TAB    |    1 | 97 |    2 (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN       | IND_TAB_O |    1 |    |    1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
5 - access("P"."OWNER"='1')

棉花糖ONE · 发表于 2009-1-8 10:55

p.ownerid 的直方图收集下看看

oradbHome · 发表于 2009-1-8 10:56

ownerid 这个index 我昨天已经rebuild了. 原来blevel 3 , rebuild 后 blevel 为2了. 但是还是使用change_time的index .

oradbHome · 发表于 2009-1-8 11:04

原帖由 棉花糖ONE 于 2009-1-8 10:55 发表
p.ownerid 的直方图收集下看看

开始有直方图, 不起作用,我就给删除掉了.刚才我有添加上也不起作用.

现在就差charge_time 上index rebuild 了.

anlinew · 发表于 2009-1-8 11:19

原帖由 oradbHome 于 2009-1-8 10:53 发表
棉花 . 但是我还是不明白我的统计信息哪里错了. 导致使用了时间的index .

这个例子就选择了正确的index

test@stat.r> create index IND_TAB_O on TAB (OWNER);

索引已创建。

test@stat.r> create index IND_TAB_T on TAB (LAST_DDL_TIME);

索引已创建。

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;

执行计划
----------------------------------------------------------
Plan hash value: 133653500

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |          |    1 | 177 |    3  (34)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |          |    |    |          |       |
| 2 | VIEW                      |          |    1 | 177 |    3  (34)| 00:00:01 |
|*  3 | SORT ORDER BY STOPKEY    |          |    1 | 97 |    3  (34)| 00:00:01 |
| 4 |    TABLE ACCESS BY INDEX ROWID| TAB    |    1 | 97 |    2 (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN       | IND_TAB_O |    1 |    |    1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM

1、alter table TAB modify (last_ddl_time not null)
2、将owner='1' 改成owner='SYS'

oradbHome · 发表于 2009-1-8 11:31

原帖由 anlinew 于 2009-1-8 11:19 发表

1、alter table TAB modify (last_ddl_time not null)
2、将owner='1' 改成owner='SYS'

使用了last_ddl_time 的index .

test@stat.r>
test@stat.r> SELECT * FROM (
  2 SELECT *
  3    FROM TAB p
  4 WHERE p.owner='SYS'
  5 ORDER BY p.last_ddl_time
  6 ) WHERE ROWNUM < 10;

执行计划
----------------------------------------------------------
Plan hash value: 3461240345

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |          |    9 |  1593 |    4 (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY             |          |    |    |          |       |
| 2 | VIEW                      |          |    9 |  1593 |    4 (0)| 00:00:01 |
|*  3 | TABLE ACCESS BY INDEX ROWID| TAB    | 23349 |  2211K|    4 (0)| 00:00:01 |
| 4 |    INDEX FULL SCAN          | IND_TAB_T | 20 |    |    2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)
3 - filter("P"."OWNER"='SYS')

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

oradbHome · 发表于 2009-1-8 11:33

index 中记录最大值和最小值. 在进行判断的时候应该先进行边界值的判定

anlinew · 发表于 2009-1-8 11:35

hint使其使用owner上的索引，对比一致读

oradbHome · 发表于 2009-1-8 11:46

原帖由 anlinew 于 2009-1-8 11:35 发表
hint使其使用owner上的索引，对比一致读

一致读差了很多.
owner='SYS'  记录很多.没有选择index 是正确的

test@stat.r> SELECT * FROM (
  2  SELECT /*+ INDEX(P IND_TAB_O)*/ *
  3 FROM TAB p
  4 WHERE p.owner='SYS'
  5 ORDER BY p.last_ddl_time
  6  ) WHERE ROWNUM < 10;

已选择9行。

执行计划
----------------------------------------------------------
Plan hash value: 133653500

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |          |    9 |  1593 |    |  1250 (1)| 00:00:16 |
|*  1 |  COUNT STOPKEY                |          |    |    |    |          |       |
| 2 | VIEW                      |          | 23349 |  4035K|    |  1250 (1)| 00:00:16 |
|*  3 | SORT ORDER BY STOPKEY    |          | 23349 |  2211K|  5944K|  1250 (1)| 00:00:16 |
| 4 |    TABLE ACCESS BY INDEX ROWID| TAB    | 23349 |  2211K|    | 729 (1)| 00:00:09 |
|*  5 |    INDEX RANGE SCAN       | IND_TAB_O | 23349 |    |    | 56 (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
5 - access("P"."OWNER"='SYS')

统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      646  consistent gets
      0  physical reads
      0  redo size
   1294  bytes sent via SQL*Net to client
      338  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      9  rows processed

test@stat.r> SELECT * FROM (
  2  SELECT  *
  3 FROM TAB p
  4 WHERE p.owner='SYS'
  5 ORDER BY p.last_ddl_time
  6  ) WHERE ROWNUM < 10;

已选择9行。

执行计划
----------------------------------------------------------
Plan hash value: 3461240345

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |          |    9 |  1593 |    4 (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY             |          |    |    |          |       |
| 2 | VIEW                      |          |    9 |  1593 |    4 (0)| 00:00:01 |
|*  3 | TABLE ACCESS BY INDEX ROWID| TAB    | 23349 |  2211K|    4 (0)| 00:00:01 |
| 4 |    INDEX FULL SCAN          | IND_TAB_T | 20 |    |    2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)
3 - filter("P"."OWNER"='SYS')

统计信息
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      5  consistent gets
      0  physical reads
      0  redo size
   1308  bytes sent via SQL*Net to client
      338  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      9  rows processed

[ 本帖最后由 oradbHome 于 2009-1-8 11:53 编辑 ]

anlinew · 发表于 2009-1-8 11:55

对于含rownum <n 这样条件的查询来说
真实的执行成本除了与数据逻辑分布情况有关，还与数据的物理分布情况有很大关系

比如再插入相同数量的数据

insert into test select '1' OWNER,  OBJECT_NAME  ,  SUBOBJECT_NAME,  OBJECT_ID,
  DATA_OBJECT_ID,  OBJECT_TYPE,  CREATED,  LAST_DDL_TIME ,  TIMESTAMP,
  STATUS,  TEMPORARY,  GENERATED,  SECONDARY
  from test where owner='SYS'
之后再对比：

SELECT * FROM (
SELECT *
   FROM TAB p
   WHERE p.owner='1'
   ORDER BY p.last_ddl_time
) WHERE ROWNUM < 10;
与owner='SYS'时的一致读

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