采用optimizer_index_cost_adj优化SQL语句

原创 2004年07月26日 16:51:00

我们知道,oracle有的时候,可能会因为统计信息的不准确或者是优化器的问题,导致语句选择错误的执行计划,如本来该走索引扫描的,但是采用了全表扫描。对于很多系统更趋向使用索引扫描,因为往往实际上,索引扫描的代价的确是小于全表扫描的。。。

我们看一个实际的例子
SQL> set autot trace exp
SQL> select count(*) from auction_auctions a, auction_bids b
  2  where a.id=b.auction and auction_type='a'
  3  and ends>to_date('2004-05-21','yyyy-mm-dd')
  4  and approve_status>=0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=65151 Card=1 Bytes=80)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=65151 Card=955916 Bytes=76473280)
   3    2       VIEW OF 'index$_join$_001' (Cost=64015 Card=95048 Bytes=4467256)
   4    3         HASH JOIN
   5    4           HASH JOIN
   6    5             PARTITION RANGE (ITERATOR)
   7    6               INDEX (FAST FULL SCAN) OF 'IND_AUCTION_ZOO_CLO_STA_CAT' (NON-UNIQUE) (Cost=8713 Card=95048 Bytes=4467256)
   8    5             PARTITION RANGE (ITERATOR)
   9    8               INDEX (FAST FULL SCAN) OF 'PK_AUCTION_AUCTIONS_ID' (UNIQUE) (Cost=8713 Card=95048 Bytes=4467256)
  10    4           PARTITION RANGE (ITERATOR)
  11   10             INDEX (FAST FULL SCAN) OF 'IND_AUC_USER_CLO_TYP_BID_STA' (NON-UNIQUE) (Cost=8713 Card=95048 Bytes=4467256)
  12    2       INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost=397 Card=955920 Bytes=31545360)
         
在以上的例子中,对该表的一个查询,采用了索引快速扫描的方法,并通过hash连接多个扫描结果,对于一个非常大的表,查询时间在10s以下。

但是,当我们修改查询条件为如下时:
     
SQL> select count(*) from auction_auctions a, auction_bids b
  2  where a.id=b.auction and auction_type='a'
  3  and ends>to_date('2004-07-01','yyyy-mm-dd')
  4  and approve_status>=0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=35001 Card=1 Bytes=80)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=35001 Card=807907 Bytes=64632560)
   3    2       PARTITION RANGE (ITERATOR)
   4    3         TABLE ACCESS (FULL) OF 'AUCTION_AUCTIONS' (Cost=33888 Card=68567 Bytes=3222649)
   5    2       INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost=397 Card=955920 Bytes=31545360)
 
这个时候,我们可以看到,Oracle采用了全表扫描,虽然这个查询语句所查询的记录数比上个查询的记录数要少,但是因为这个表是已ends分区的分区表,当ends>07-01时,只需要在一个分区中查询即可,Oracle错误的计算了成本(Cost=35001,远远小于上面采用索引的成本Cost=65151),所以采用在分区范围内的全表扫描。
但是实际情况是,该查询需要>10分钟才能获得查询结果。显然,这里oracle计算成本的时候是有问题的,这里的全表扫描的速度远远低于索引扫描的时间。

那么,我们有什么办法能让oracle回到索引扫描的执行计划呢?我们首先考虑hint。
SQL> select /*+ index(a IND_AUCTION_ZOO_CLO_STA_CAT) index(a PK_AUCTION_AUCTIONS_ID) index(a IND_AUC_USER_CLO_TYP_BID_STA) */
  2  count(*) from auction_auctions a, auction_bids b
  3  where a.id=b.auction and auction_type='a'
  4  and ends>to_date('2004-07-21','yyyy-mm-dd')
  5  and approve_status>=0;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=143358 Card=1 Bytes=80)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=143358 Card=807907 Bytes=64632560)
   3    2       PARTITION RANGE (ITERATOR)
   4    3         TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'AUCTION_AUCTIONS' (Cost=142245 Card=68567 Bytes=3222649)
   5    4           INDEX (SKIP SCAN) OF 'IND_AUC_USER_CLO_TYP_BID_STA' (NON-UNIQUE) (Cost=28314 Card=152804)
   6    2       INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost=397 Card=955920 Bytes=31545360)
 
可以看到,不管是怎么指定索引,Oracle只能是很死板的选用一个它认为最快的索引,而不会使用上面的比较优化的多个索引快速扫描的方法,而且通过指定的索引扫描,oracle计算出来的成本也很大(Cost=143358),实际上,该语句的执行也是非常慢,甚至慢于分区全表扫描。
 
有什么办法,能让oracle最简单的使用上面的执行计划呢?我们可以考虑参数OPTIMIZER_INDEX_COST_ADJ,这个初始化参数代表一个百分比,取值范围在1到10000之间。该参数表示索引扫描和全表扫描成本的比较,缺省值100表示索引扫描成本等价转换与全表扫描成本。

我们修改该参数值
SQL> alter session set optimizer_index_cost_adj = 40;
Session altered.

SQL> select count(*) from auction_auctions a, auction_bids b
  2  where a.id=b.auction and auction_type='a'
  3  and ends>to_date('2004-07-01','yyyy-mm-dd')
  4  and approve_status>=0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=30505 Card=1 Bytes=80)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=30505 Card=807907 Bytes=64632560)
   3    2       VIEW OF 'index$_join$_001' (Cost=29391 Card=68567 Bytes=3222649)
   4    3         HASH JOIN
   5    4           HASH JOIN
   6    5             PARTITION RANGE (ITERATOR)
   7    6               INDEX (FAST FULL SCAN) OF 'IND_AUCTION_ZOO_CLO_STA_CAT' (NON-UNIQUE) (Cost=3484 Card=68567 Bytes=3222649)
   8    5             PARTITION RANGE (ITERATOR)
   9    8               INDEX (FAST FULL SCAN) OF 'PK_AUCTION_AUCTIONS_ID' (UNIQUE) (Cost=3484 Card=68567 Bytes=3222649)
  10    4           PARTITION RANGE (ITERATOR)
  11   10             INDEX (FAST FULL SCAN) OF 'IND_AUC_USER_CLO_TYP_BID_STA' (NON-UNIQUE) (Cost=3484 Card=68567 Bytes=3222649)
  12    2       INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost=397 Card=955920 Bytes=31545360)
 
这个时候,我们发现,oracle又选择了我们所希望的执行计划,在这个执行计划中,成本是30505
我们可以得到,Oracle认为走该执行计划的原始成本是
(30505/40)*100=76263

optimizer_index_cost_adj和optimizer_index_caching

最近一个客户的数据库从10g升级到11g
  • su377486
  • su377486
  • 2014年08月13日 21:03
  • 905

optimizer_index_caching和optimizer_index_cost_adj两个参数说明

一、optimizer_index_cost_adj参数 优化器计算通过索引扫描访问表数据的cost开销,可以通过这个参数进行调整。参数可用值的范围为1到10000。默认值为100,超过100后越大则...
  • oradh
  • oradh
  • 2014年06月09日 14:39
  • 1222

测试6——观察Optimizer_index_cost_adj 对执行计划的影响

在基于cbo的优化器模式下面,oracle会把sql语句的各种执行计划的成本做比较,取其最小的作为其最优执行计划,同时,在决定是用index scan 还是full table scan 访问...
  • changyanmanman
  • changyanmanman
  • 2014年04月23日 15:38
  • 1033

sql语句优化的13种方法

原文链接:http://blog.csdn.net/u012942818/article/details/53969972 1,什么是“执行计划”? 执行计划是数据库根据SQL语句和相关表的统计信息...
  • xie_xiansheng
  • xie_xiansheng
  • 2017年05月02日 10:08
  • 558

sql语句的细节优化

Sql优化这个话题说来好像很宏大似的,如果你的数据库负载能力超牛B,完全可以不去耗费心思去捋sql;但,话有说回来,一个有条有理的sql,就代表了一个程序员的个人风格,有谁不希望自己的代码被人模仿或称...
  • qq_16216221
  • qq_16216221
  • 2015年05月29日 16:58
  • 546

pl/sql优化小知识点

1.选择最有效的表名顺序 oracle的解析器按照从右到左的顺序处理from子句中的表名,因此写在from子句最后的表(基础表,drivingtable)将被最先处理。在from子句中包含多个表的情况...
  • luxiaopengpeng
  • luxiaopengpeng
  • 2016年04月15日 16:52
  • 709

sql语句优化总结

性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。 为了获得稳定的执行性能,SQL语句越简单越好。对复杂的SQL语句...
  • fyuanfena
  • fyuanfena
  • 2016年07月27日 09:26
  • 1252

sql优化实战:从300秒+到10秒(调整参数)

今天客户反馈有一个报表运行超时。 于是登录到系统上,查到运行缓慢报表的对应sql,代码如下: SELECT f_temp_biz_date AS f_temp_biz_date , ...
  • yupeigu
  • yupeigu
  • 2016年09月09日 17:21
  • 852

oracle sql语句优化原则经验总结

oracle sql语句优化 分类: Database 2008-11-15 14:02 836人阅读 评论(1) 收藏 举报 sqloracle优化数据库bufferd...
  • haiross
  • haiross
  • 2013年12月05日 15:51
  • 1540

数据库性能优化之SQL语句优化

一、问题的提出在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中数据的增加,系统的...
  • u011225629
  • u011225629
  • 2016年01月10日 15:40
  • 50072
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:采用optimizer_index_cost_adj优化SQL语句
举报原因:
原因补充:

(最多只允许输入30个字)