【文本版|topic】 高级搜索
   名人堂 帮助 论坛制度 意见反馈 | 首页 博客 周新贴 招聘 专题 新闻
 RSS 底部
 
社区导航: 专家门诊   网络技术   操作系统   数据库   程序设计   系统应用   考试认证   CIO及信息化   站长交流   综合交流   下载基地  51CTO产品服务 【设为首页 | 收藏本站】
51CTO技术论坛» Oracle 10g / 9i » Oracle中optimizer_index_cost_adj参数的修正方式       [ 打印]  [ 订阅]  [ 收藏]  [ 推荐给朋友]   [ 本帖文本页]
 

论坛跳转:
     
标题: [原创] Oracle中optimizer_index_cost_adj参数的修正方式  ( 查看:291  回复:0 )   
 
national
新新人类  点击可查看详细



帖子 29
精华 0
无忧币 96
积分 41
阅读权限 20
注册日期 2008-2-21
最后登录 2008-7-24 离线

[查看资料]  [发短消息]  [Blog] 
       
发表于:2008-2-25 23:40　 　标题：Oracle中optimizer_index_cost_adj参数的修正方式
＜上一帖 |
CREATE TABLE t1 (
  id  INT,
  val VARCHAR2(20)
);
BEGIN
  FOR i IN 1..1000
  LOOP
    INSERT INTO t1(id) VALUES (1);
  END LOOP;
  COMMIT;
END;
/
首先来生成统计的信息
BEGIN
  DBMS_STATS.set_table_stats(
    ownname  => USER,
    tabname  => 'T1',
    numrows  => 10000
  );
END;
/
BEGIN
  DBMS_STATS.set_column_stats(
    ownname  => USER,
    tabname  => 'T1',
    colname  => 'ID',
    distcnt  => 10000
  );
END;
/
这下id的值成均匀分布
在SQL*PLUS中
SET WRAP OFF
SET LINESIZE 1000
SET AUTOTRACE TRACEONLY EXPLAIN -- 只显示查询路径
SELECT * FROM t1 WHERE id = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    25 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------
看到了吗?由于统计信息的不正确导致计算出了错误的答案
我建立一个索引
CREATE INDEX t1_id_idx ON t1(id);
生成统计信息
BEGIN
  DBMS_STATS.set_index_stats(
     ownname => USER,
     indname => 'T1_ID_IDX',
     numdist => 10000,
     numrows => 10000
  );
END;
/
SELECT * FROM t1 WHERE id = 1;
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
生成统计信息
BEGIN
  DBMS_STATS.set_index_stats(
     ownname => USER,
     indname => 'T1_ID_IDX',
     numdist => 10000,
     numrows => 10000,
     indlevel => 20
  );
END;
/
SELECT /*+ INDEX(t1 t1_id_idx) */ * FROM t1 WHERE id = 1;
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    25 |    22   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    25 |    22   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_IDX |     1 |       |    21   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
SELECT * FROM t1 WHERE id = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    25 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------
在这里我是使用提示强制使用索引, 接下来我使用OPTIMIZER_INDEX_COST_ADJ参数为了便于理解
我这样解释这个参数是一个全表扫描和索引访问的百分比这个值越小优化器则越倾向于访问索引,
反之这个值越大则越倾向于全表扫描。
我修改这个参数当前索引访问的代价是17我需要通过这个参数将代价修正为比全表扫描小1(15即可),
100 * (16(FTS) - 1) / 22(INDEX) = 100 * (16 - 1) / 22 = 68.1818 为了保险起见取趋于选索引的一边为68
ALTER SESSION SET optimizer_index_cost_adj = 68;
SELECT * FROM t1 WHERE id = 1;
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    25 |    15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    25 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_IDX |     1 |       |    14   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
再改回来
ALTER SESSION SET optimizer_index_cost_adj = 100;
为了验证公式的正确性我再修改索引的代价
BEGIN
  DBMS_STATS.set_index_stats(
     ownname => USER,
     indname => 'T1_ID_IDX',
     numdist => 1000,
     numrows => 1000,
     indlevel => 37
  );
END;
/
SELECT /*+ INDEX(t1 t1_id_idx) */ * FROM t1 WHERE id = 1;
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    25 |    39   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    25 |    39   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_IDX |     1 |       |    38   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
使用公式
100 * (16(FTS) - 1) / 39(INDEX) = 100 * (16 - 1) / 39 = 38.461 为了保险起见我取38
ALTER SESSION SET optimizer_index_cost_adj = 38;
SELECT * FROM t1 WHERE id = 1;
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    25 |    15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    25 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_IDX |     1 |       |    14   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
再一次改回来
ALTER SESSION SET optimizer_index_cost_adj = 100;
BEGIN
  DBMS_STATS.set_index_stats(
     ownname => USER,
     indname => 'T1_ID_IDX',
     numdist => 1000,
     numrows => 1000,
     indlevel => 5
  );
END;
/
SELECT * FROM t1 WHERE id = 1;
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    25 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    25 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_IDX |     1 |       |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
SELECT /*+ FULL(t1) */ * FROM t1 WHERE id = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    25 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------
使用公式
100 * (16(FTS) + 1) / 7(INDEX) = 100 * (16 + 1) / 7 = 242.857 为了保险起见我取243
(这里FTS我设置了17是为了将全表扫描的代价修正为超过16这样就可以很保险的进行全表扫描)
ALTER SESSION SET optimizer_index_cost_adj = 243;
SELECT * FROM t1 WHERE id = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    25 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------
为了达到修正的目的我采用的是将全表扫描的代价修正为FTS(cost + 1)
而索引扫描的代价修正为FTS(cost - 1)
(只需要加减1就可以超过界限,这里主要是为了标记简单,对于只要是正整数,
当然像取值范围,临界条件这样的问题就不用我讨论了)
这个思路是这样的
1, 当前为全表扫描,将索引访问的代价修正为比全表扫描小的代价,这样优化器就会去选择索引
2, 当前为索引扫描,将索引访问的代价修正为比全表扫描大的代价,这样优化器就会去选择全表扫描
我认为修正的公式
FTS -> INDEX(将全表扫描修正为索引访问)
optimizer_index_cost_adj = FLOOR((FTS COST  - 1) / INDEX COST * 100)
INDEX -> FTS(将索引访问修正为全表扫描)
optimizer_index_cost_adj = CEIL((FTS COST  + 1) / INDEX COST * 100)

可以看到, 对于CBO必须得有正确的统计信息, 从上面步骤来看我把Oracle给骗惨了, 没有了正确统计
信息的Oracle对于查询路径的选择是很不好的(当然由于这里例子太简单了, 不能很好的表示查询的不良选择)
看来经常进行表分析对于CBO来说是很重要的, 这也是DBA的一个日常的工作。

网络工程师到底该不该去考CCIE认证？
2008-2-25 23:401楼
[ 顶部 ]
     
论坛跳转:  

| | |
标记已读 · 删除论坛Cookies · 文本版 · WAP
 
| 诚征版主 | 版主堂 | 意见建议 | 大史记 | 论坛地图
Copyright©2005-2008 51CTO.COM  Powered by Discuz!
本论坛言论纯属发布者个人意见，不代表51CTO网站立场！如有疑义，请与管理员联系。
京ICP备05051492号