越来越不懂cost咋算了

最新推荐文章于 2021-09-22 12:03:06 发布

转载最新推荐文章于 2021-09-22 12:03:06 发布 · 176 阅读

本文通过具体案例解析了Oracle数据库中SQL执行计划的成本计算方法，包括不考虑CPU成本与考虑CPU成本时的成本差异，并详细分析了索引扫描、位图转换等操作的成本估算过程。

explain plan for
select /*+ no_cpu_costing */* from vodka.bpm_actinst where col2=:1 and actor=:2;
-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                      |    2 | 594 | 221 |
| 1 |  TABLE ACCESS BY INDEX ROWID    | BPM_ACTINST          |    2 | 594 | 221 |
| 2 | BITMAP CONVERSION TO ROWIDS |                      |    |    |    |
| 3 | BITMAP AND                   |                      |    |    |    |
| 4 |    BITMAP CONVERSION FROM ROWIDS|                      |    |    |    |
|*  5 |    INDEX RANGE SCAN          | BPM_ACT_INST_ACTOR_IND |  1338 |    |    9 |
| 6 |    BITMAP CONVERSION FROM ROWIDS|                      |    |    |    |
|*  7 |    INDEX RANGE SCAN          | BPM_ACTINST_COL2_IND |  1338 |    | 191 |
-------------------------------------------------------------------------------------------

explain plan for
select * from vodka.bpm_actinst where col2=:1 and actor=:2;

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                      |    2 | 594 | 202 (1)| 00:00:03 |
| 1 |  TABLE ACCESS BY INDEX ROWID    | BPM_ACTINST          |    2 | 594 | 202 (1)| 00:00:03 |
| 2 | BITMAP CONVERSION TO ROWIDS |                      |    |    |          |       |
| 3 | BITMAP AND                   |                      |    |    |          |       |
| 4 |    BITMAP CONVERSION FROM ROWIDS|                      |    |    |          |       |
|*  5 |    INDEX RANGE SCAN          | BPM_ACT_INST_ACTOR_IND |  1338 |    |    9 (0)| 00:00:01 |
| 6 |    BITMAP CONVERSION FROM ROWIDS|                      |    |    |          |       |
|*  7 |    INDEX RANGE SCAN          | BPM_ACTINST_COL2_IND |  1338 |    | 192 (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------------------

计算cpu的cost比不计算cpu的cost要小。真是伤不起。

----------------索引部分的cost
select (112060/599)+3 from dual;-----------（leaf_block/distint)+LEVEL
(112060/599)+3
--------------
190.078464
select (47540/7499)+2 from dual;-----------（leaf_block/distint)+LEVEL
(47540/7499)+2
--------------
8.33951193
select （191+9）*1.1 from dual;------------1.1是位图索引固定的比例
200*1.1
----------
   220

--------------回表的cost

select 15608540-5572000 from dual;-----------总行数-空值
15608540-5572000
----------------
      10036540

-----------------------总选择率
1/(7499*599)

----------------回表读取的块数
10036540/(7499*599)=2.2-------------这里我们毛估一下，没有用80/20原理

-----------------总cost
220+2.2=222.2

再算上cpu的cost 应该比这个还大点才对。不知道计算cpu的时候，哪里出了问题，ORACLE真是越来越搞不懂了。

TABLE_NAME                NUM_ROWS    BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZED,'MM/DD/YYYYHH24:
------------------------- ---------- ---------- ----------- --------------------------------------
BPM_ACTINST                15608540    658314       297 06/19/2011 03:48:58

name                                                          NUM_ROWS distinct LEAF_BLOCKS       cf    level alfbpkey
------------------------------------------------------------ ---------- ---------- ----------- ---------- ---------- ----------
BPM_ACTINST_COL2_IND                                        15868480       599    112060 6062780       3       187
BPM_ACT_INST_ACTOR_IND                                        9937520    7499    47540 8278480       2       6

22 rows selected.

COLUMN_NAME       NUM_DISTINCT  NUM_NULLS NUM_BUCKETS DENSITY
-------------------- ------------ ---------- ----------- ----------
ACTOR                      7499 5572000          1 .000133351
COL2                         599       0          1 .001669449

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-700568/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-700568/