first_rows模式下sql 优化

刚在路

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> col type for a10
SQL> show parameter optimizer_mode

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
optimizer_mode                       string     FIRST_ROWS

SQL> create table test_a as  select * from dba_objects;

Table created.

SQL> insert into test_a select * from dba_objects;

153134 rows created.

SQL> /

153134 rows created.

SQL> /

153134 rows created.

SQL> /

153134 rows created.

SQL> /

153134 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test_a     ;

  COUNT(*)
----------
    918804

SQL> create index IDX_A on TEST_A (OBJECT_ID);

Index created.

SQL> create index IDX_B on TEST_A (CREATED);

Index created.
SQL>create index IDX_C on TEST_A (OBJECT_ID, CREATED);
Index created.
SQL> set autot traceonly
SQL> select *
  2    from test_a t
  3   where t.object_id = 800
  4     and t.created >= to_date('2005-9-7 ', 'yyyy-mm-dd')
  5     and t.created <= to_date('2005-10-7 ', 'yyyy-mm-dd')
  6   order by t.created;

6 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=4 Bytes=7
          08)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_A' (Cost=7 Card=4 B
          ytes=708)

   2    1     INDEX (RANGE SCAN) OF 'IDX_B' (NON-UNIQUE) (Cost=3 Card=
          799)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4300  consistent gets
        109  physical reads
          0  redo size
       1464  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)
          6  rows processed

生产库有很多这样的order by 语句，也不可避免，
这个执行计划走IDX_B
idx_a的选择性很高，直方图已经收集。
这样的语句生产库上很多。该怎么优化呢？

westzq1984

SESSION级别设置 _sort_elimination_cost_ratio = 5 测试下

不过，还是应该多测试下，把optimizer_mode改回CHOOSE

刚在路

westzq1984 发表于 2012-4-23 19:30
SESSION级别设置 _sort_elimination_cost_ratio = 5 测试下

不过，还是应该多测试下，把optimizer_mode改 ...

_sort_elimination_cost_ratio = 5 可走对。
生产系统optimizer_mode不能改。

zhangitpub2012

没有分析过，期待高人解析。

lorikyo

create index IDX_C on TEST_A (CREATED,OBJECT_ID);  把created 放前面！

dotaddjj

oralce 9I下默认cbo认为排序是非常消耗内存的，默认应该是最高，所以你后面的order by create，oracle会选择使用create上的索引，来避免排序，也就很自然的选择了index_b了，_sort_elimination_cost_ratio隐含参数默认为0，也就是排序成本无限大，而这个值在9I cbo下会直接影响执行计划，而_sort_elimination_cost_ratio是用来衡量排序与不排序的成本百分比的，当不排序成本/排序成本>_sort_elimination_cost_ratio 执行计划走排序反之则不走排序

刚在路

lorikyo 发表于 2012-4-24 00:00
create index IDX_C on TEST_A (CREATED,OBJECT_ID);  把created 放前面！

法师 不行的 oralce 9I first_rows  当数据量大的时候 有order by  就只会走order by 的字段（order by 的字段有索引的话）

刚在路

dotaddjj 发表于 2012-4-24 00:40
oralce 9I下默认cbo认为排序是非常消耗内存的，默认应该是最高，所以你后面的order by create，oracle会选择 ...

飞鱼马甲？

ahdong2007

我想问的一个问题是，LZ 何以认为组合索引就会比 做单列索引快？

排序是一个耗时耗资源的操作，任何数据库都会尽量避免排序操作。

LZ 可以试试使用hints ，看看是否可强制SQL 走组合索引。

dotaddjj

ahdong2007 发表于 2012-4-24 09:43
我想问的一个问题是，LZ 何以认为组合索引就会比 做单列索引快？

排序是一个耗时耗资源的操作，任何数 ...

oracle 9I下_sort_elimination_cost_ratio隐含参数默认为0，oracle认为排序消耗巨大，就很自然的避免了排序，而使用了有索引的列来避免排序，而一般组合索引肯定比单列索引效率高，这是必然。oracle 10g下已经修改此隐含参数为1.