基于COST的Oracle机制（有趣案例）

最新推荐文章于 2024-04-23 21:45:50 发布

叽哩古哩

最新推荐文章于 2024-04-23 21:45:50 发布

阅读量477

点赞数

分类专栏： sql语句

sql语句专栏收录该内容

35 篇文章

订阅专栏

本文通过实验展示了Oracle如何根据成本计算选择合适的执行计划，包括全表扫描与索引扫描的成本计算，以及如何理解优化器的选择逻辑。通过具体案例分析，揭示了Oracle在面对不同查询条件时，如何优化查询执行路径。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle是根据COST来选择合适的执行计划的，为了深入了解其机制，我做了一个测试过程如下。
-- Create table
create table TEST
(
  EN_NAME VARCHAR2(6),
  BS_CODE VARCHAR2(10),
  DDATE   DATE,
  IID     NUMBER
)；
-- Create/Recreate indexes
create index TEST_DATE on TEST (DDATE)；
create index TEST_ID on TEST (IID)；
--在IID和时间DDATE建普通索引
--插入了11万数据
INSERT INTO test t(iid,ddate)
SELECT trunc(dbms_random.value(0,100)),SYSDATE -trunc(dbms_random.value(0,100)) FROM dual
CONNECT BY ROWNUM < 110001
--删除IID为84的大部分记录，仅仅剩下23行
Delete from test where iid=84 and rownum < 1089
--为了保证信息的准确性，我重建索引，降低HWL，分析表
Alter index test_id rebuild;
Alter index teset_date rebuild;

alter table test shrink space cascade;

analyse table test analyze statistics;
SQL>  exec show_space('TEST','APS2');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             269
Total Blocks............................             288
Total Bytes.............................       2,359,296
Total MBytes............................               2
Unused Blocks...........................               3
Unused Bytes............................          24,576
Last Used Ext FileId....................              10
Last Used Ext BlockId...................           8,073
Last Used Block.........................              29

PL/SQL procedure successfully completed.
--运行SQL
SELECT * FROM TEST WHERE IID=84
SQL> SELECT * FROM TEST WHERE IID=84;
23 rows selected.
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1089 | 11979 |    73   (6)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |  1089 | 11979 |    73   (6)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        277  consistent gets
居然是TABLE ACCESS FULL？注意COST 73 ,277的一致读
根据常识，当访问小于表15%的数据的时候，走索引会获得更好的效率。为什么这里会走FULL SCAN呢？
那么再运行一次走索引的，如下：
SQL> SELECT * FROM test WHERE  iid=84;

23 rows selected.
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |  1089 | 11979 |   269   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |  1089 | 11979 |   269   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | TEST_ID |  1089 |       |     3   (0)| 00:00:01 |
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         13  consistent gets
COST 269,13的一致读。

这样就很清楚，优化器认为全表扫描的成本较小，所以它选择FULL SCAN,虽然效率不一定会优于走索引。
--下面我来简单说明一下COST是如何计算的呢
从索引访问说起，先收集一些基本信息
SQL> SELECT t.blevel,t.leaf_blocks FROM User_Indexes t WHERE index_name='TEST_ID';

    BLEVEL LEAF_BLOCKS
---------- -----------
1        213
2级索引，有213个叶子块。
根据公式：
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
blevel = 1
leaf_block=213
effective index selectivity = 0.01
有效索引选择率，即基于谓词所希望返回函数的比例，这里是0.01，因为我们采用的0~99的随机IID，虽然我们删除了大量IID为84的数据，但是统计信息里面还是认为没一个单独值的密度为0.01（除非你创建合适的CUBE）。
clustering_factor 集群因素，是一个用于衡量索引有序度和表的混乱度值，关于什么是集群因素，请参考文章：
http://valen.blog.ccidnet.com/bl ... -itemid-207620.html
或者看Cost Based Oracle Fundamentals一书，4.2.3
这里我只能用SQL计算一个值
SQL> SELECT COUNT(*) FROM (
  2  SELECT COUNT(1),iid,blk FROM (
  3  sELECT t.*,dbms_rowid.rowid_block_number(ROWID) blk from test t ORDER BY iid)
  4  GROUP BY iid,blk ORDER BY iid
  5  )
  6  /

  COUNT(*)
----------
     26414
effective table selectivity = 0.01 数据密度
那么COST=1+CEIL(213*0.01)+CEIL(26414*0.01)= 269
非常完美的一致！
再看FULL SCAN的计算方式
传统的计算方式，COST = FULL BLOCK / adjusted MULTPLEBLOCK READ
我的db_file_multiblock_read_count为16，这里算出
Adjusted db_file_multiblock_read_count 为3.64，偏低。但这并不重要，暂不研究。
最后，我们设置10053事件来追踪优化器的行为
SQL> alter session set events '10053 trace name context forever,level 2';

Session altered.

打开trace文件,在里面找到一段我们感兴趣的内容
***********************
Table Stats::
  Table: TEST  Alias: TEST
    #Rows: 108902  #Blks:  306  AvgRowLen:  16.00
Index Stats::
  Index: TEST_DATE  Col#: 3
    LVLS: 1  #LB: 289  #DK: 200  LB/K: 1.00  DB/K: 134.00  CLUF: 26804.00
  Index: TEST_ID  Col#: 4
    LVLS: 1  #LB: 213  #DK: 100  LB/K: 2.00  DB/K: 265.00  CLUF: 26594.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#4): IID(NUMBER)
    AvgLen: 2.00 NDV: 100 Nulls: 0 Density: 0.01 Min: 0 Max: 99
  Table: TEST  Alias: TEST
    Card: Original: 108902  Rounded: 1089  Computed: 1089.02  Non Adjusted: 1089.02
  Access Path: TableScan
    Cost:  64.56  Resp: 64.56  Degree: 0
      Cost_io: 61.00  Cost_cpu: 30493681
      Resp_io: 61.00  Resp_cpu: 30493681
  Access Path: index (AllEqRange)
    Index: TEST_ID
    resc_io: 269.00  resc_cpu: 2363417
    ix_sel: 0.01  ix_sel_with_filters: 0.01
    Cost: 269.28  Resp: 269.28  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 64.56  Degree: 1  Resp: 64.56  Card: 1089.02  Bytes: 0

优化器得出结论：Best:: AccessPath: TableScan

相同的方法，也可以解释，优化器为什么使用这个索引，不使用那个索引。

原文地址：http://blog.sina.com.cn/s/blog_445f0e620100x98y.html