ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录（INDEX fast full scan 的成本计算）

oracle实验记录（INDEX fast full scan 的成本计算）

原创 Linux操作系统 作者：fufuh2o 时间：2009-09-03 22:55:21 0 删除 编辑


现在环境

实验下INDEX FAST FULL SCAN 的计算
我们知道INDEX FAST FULL SCAN 是可以用db_file_multiblock_read_count  影响 采取scattered read的
SQL> show parameter db_file_m

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
db_file_multiblock_read_count        integer     16
SQL> alter system set "_cache_stats_monitor"=false;

系统已更改。

SQL> alter system set "_optimizer_cache_stats"=false;

系统已更改。

SQL> alter system set "_optimizer_cost_model"=choose;

系统已更改。

以上是前面实验时候修改的参数，实际VALUE为 上面修改回来的值

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


SQL> select count(*) from t1;

  COUNT(*)
----------
     10000

SQL> create index t1_ind on t1(a);


SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select count(*) from t1;

执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |     6   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL> select count(*) from t1 where a>6000;

执行计划
----------------------------------------------------------
Plan hash value: 2264155217

--------------------------------------------------------------------------------

| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |        |     1 |     3 |     6   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |        |     1 |     3 |            |          |

|*  2 |   INDEX FAST FULL SCAN| T1_IND |  4000 | 12000 |     6   (0)| 00:00:01 |

--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A">6000)


可以看到INDFFS COST 与FTS COST 一样（计算方式也应该一样）~~~~~~~~~~~~~~~~~~~~~~*****************************


****************
select count(*) from t1**********************************FTS时候
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 bjn=52816 hint_alias="T1"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 1271 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  20  AvgRowLen:  3.00
Index Stats::
  Index: T1_IND  Col#: 1
    LVLS: 1  #LB: 21  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1    
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  6.11  Resp: 6.11  Degree: 0
      Cost_io: 6.00  Cost_cpu: 1642429
      Resp_io: 6.00  Resp_cpu: 1642429
  Best:: AccessPath: TableScan
         Cost: 6.11  Degree: 1  Resp: 6.11  Card: 10000.00  Bytes: 0

Final - All Rows Plan:  Best join order: 1
  Cost: 6.1077  Degree: 1  Card: 10000.0000  Bytes: 0
  Resc: 6.1077  Resc_io: 6.0000  Resc_cpu: 1642429
  Resp: 6.1077  Resp_io: 6.0000  Resc_cpu: 1642429

 

 

 


~~~~~~~~~~~~~~~~~~~
QUERY BLOCK TEXT
****************
select count(*) from t1 where a>6000*****************************index fast full scan 时
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 bjn=52816 hint_alias="T1"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 1271 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  20  AvgRowLen:  3.00
Index Stats::
  Index: T1_IND  Col#: 1
    LVLS: 1  #LB: 21  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
  Table: T1  Alias: T1    
    Card: Original: 10000  Rounded: 4000  Computed: 4000.40  Non Adjusted: 4000.40
  Access Path: TableScan~~~~~~~~~~~~~可以看到 表SCAN 时候成本
    Cost:  6.14  Resp: 6.14  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2142429
      Resp_io: 6.00  Resp_cpu: 2142429
  Access Path: index (index (FFS))~~~~~~~~~~~~采用INDX时候路径 成本  比FTS 成本小 6.12<6.14 所以OPTIMIZER 选择INDEX FAST FULL SACN
    Index: T1_IND
    resc_io: 6.00  resc_cpu: 1849550
    ix_sel: 0.0000e+000  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  6.12  Resp: 6.12  Degree: 1
      Cost_io: 6.00  Cost_cpu: 1849550
      Resp_io: 6.00  Resp_cpu: 1849550
  Access Path: index (IndexOnly)
    Index: T1_IND
    resc_io: 10.00  resc_cpu: 871414
    ix_sel: 0.40004  ix_sel_with_filters: 0.40004
    Cost: 10.06  Resp: 10.06  Degree: 1
  Best:: AccessPath: IndexFFS  Index: T1_IND~~~~~~~~~~~~~~~~~~~~~~~~~OPTIMIZER最后选择了最好的执行方式INDEX FFS
         Cost: 6.12  Degree: 1  Resp: 6.12  Card: 4000.40  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  T1[T1]#0
***********************
Best so far: Table#: 0  cost: 6.1213  card: 4000.4000  bytes: 12000
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 6.1213  Degree: 1  Card: 4000.0000  Bytes: 12000
  Resc: 6.1213  Resc_io: 6.0000  Resc_cpu: 1849550
  Resp: 6.1213  Resp_io: 6.0000  Resc_cpu: 1849550


*********可以看到此例中FTS IO COST 与INDEX FAST FULL SCAN  IO COST 部分是一样的CPU COST（计算方式应该一样），要比 FTS时候CPU COST小些


SQL> select leaf_blocks from dba_indexes where index_name='T1_IND';

LEAF_BLOCKS
-----------
         21   ~~~~~~~~~~~~~~~~~应该是用LEAF BLOCK 代入公式计算的~~~~~~~~~(USED LEAF BLOCK)

Cost = (
#SRds +
#MRds * mreadtim / sreadtim +**************io cost
#CPUCycles / (cpuspeed * sreadtim)********cpu cost
计算*使用 默认信息**************************
MBRC=db_file_multiblock_read_count=16
sreadtim=IOSEEKTIM+db+block_size/IOTFRSPEED=12
mreadtime=IOSEEKTIM+db_file_multiblock_read_count*db+block_size/IOTFRSPEED=42

 

SQL> select leaf_blocks from dba_indexes where index_name='T1_IND';

LEAF_BLOCKS
-----------
         21

SQL> select pname,pval1 from aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     1270.63969
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

SQL> select ceil((21/16)*(42/12)) from dual;

CEIL((21/16)*(42/12))
---------------------
                    5
又由于 _table_scan_cost_plus_one           = true   加1 所以IO COST=6

***********************************
SQL> create table t2 (a int ,b char(2000),c char(2000));

表已创建。

SQL> declare
  2  begin
  3  for i in 1..100 loop
  4  insert into t2 values(i,'a','a');
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> execute dbms_stats.gather_table_stats('SYS','T2');

PL/SQL 过程已成功完成。

SQL> select blocks from user_tables where table_name='T2';

    BLOCKS
----------
       100

SQL> create index t2_ind on t2(a);

索引已创建。

SQL> select leaf_blocks from dba_indexes where index_name='T2_IND';

LEAF_BLOCKS
-----------
          1

SQL> execute dbms_stats.gather_index_stats('SYS','T2_IND');

PL/SQL 过程已成功完成。

SQL> select leaf_blocks from dba_indexes where index_name='T2_IND';

LEAF_BLOCKS
-----------
          1

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select a from t2;

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   300 |    24   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T2   |   100 |   300 |    24   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select a from t2 where a>60;

执行计划
----------------------------------------------------------
Plan hash value: 2876512201

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    40 |   120 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T2_IND |    40 |   120 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A">60)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

可以看到OPTIMIZER 并没有使用INDEXFFS~~~原因很简单 INDEX RANGE SCAN COST更低
但从10053中TRACE中可以看出OPTIMIZER 做出了INDEXFFS的COST 并做了对比

Table Stats::
  Table: T2  Alias: T2
    #Rows: 100  #Blks:  100  AvgRowLen:  4005.00
Index Stats::
  Index: T2_IND  Col#: 1
    LVLS: 0  #LB: 1  #DK: 100  LB/K: 1.00  DB/K: 1.00  CLUF: 100.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 3.00 NDV: 100 Nulls: 0 Density: 0.01 Min: 1 Max: 100
  Table: T2  Alias: T2    
    Card: Original: 100  Rounded: 40  Computed: 40.40  Non Adjusted: 40.40
  Access Path: TableScan
    Cost:  24.05  Resp: 24.05  Degree: 0~~~~~~~~~~~~~FTS时候COST
      Cost_io: 24.00  Cost_cpu: 732144
      Resp_io: 24.00  Resp_cpu: 732144
  Access Path: index (index (FFS))
    Index: T2_IND
    resc_io: 2.00  resc_cpu: 24121
    ix_sel: 0.0000e+000  ix_sel_with_filters: 1
  Access Path: index (FFS)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~INDEX FFS时候COST
    Cost:  2.00  Resp: 2.00  Degree: 1
      Cost_io: 2.00  Cost_cpu: 24121
      Resp_io: 2.00  Resp_cpu: 24121
  Access Path: index (IndexOnly)~~~~~~~~~~~~~~~~~~~INDEX RANGE SCAN时COST（INDEX COST计算会在后面实验于FTS公式不一样）
    Index: T2_IND
    resc_io: 1.00  resc_cpu: 15321
    ix_sel: 0.40404  ix_sel_with_filters: 0.40404
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: T2_IND
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 40.40  Bytes: 0


因为INDEX FFSCOST  Cost_io: 2.00 
按照公式来算
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +**************io cost
#CPUCycles / (cpuspeed * sreadtim)********cpu cost
计算*使用 默认信息**************************
MBRC=db_file_multiblock_read_count=16
sreadtim=IOSEEKTIM+db+block_size/IOTFRSPEED=12
mreadtime=IOSEEKTIM+db_file_multiblock_read_count*db+block_size/IOTFRSPEED=42
SQL> select pname,pval1 from aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     1270.63969
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

SQL> select ceil((1/16)*(42/12)) from dual;

CEIL((1/16)*(42/12))
--------------------
                   1
 _table_scan_cost_plus_one           = true   加1   所以IO COST=2

 


SQL> alter system set "_table_scan_cost_plus_one"=false;~~~去掉加一

系统已更改。

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select a from t2 where a>60;

执行计划
----------------------------------------------------------
Plan hash value: 2876512201

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    40 |   120 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T2_IND |    40 |   120 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A">60)


SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 3.00 NDV: 100 Nulls: 0 Density: 0.01 Min: 1 Max: 100
  Table: T2  Alias: T2    
    Card: Original: 100  Rounded: 40  Computed: 40.40  Non Adjusted: 40.40
  Access Path: TableScan
    Cost:  23.05  Resp: 23.05  Degree: 0
      Cost_io: 23.00  Cost_cpu: 732144
      Resp_io: 23.00  Resp_cpu: 732144
  Access Path: index (index (FFS))
    Index: T2_IND
    resc_io: 1.00  resc_cpu: 24121
    ix_sel: 0.0000e+000  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  1.00  Resp: 1.00  Degree: 1~~~~~~~~~~~~~~~~~~~~~~可以看 到IOCOST 部分已经为1  但是CPU 部分比 INDEX RANGSCAN 要高
      Cost_io: 1.00  Cost_cpu: 24121
      Resp_io: 1.00  Resp_cpu: 24121
  Access Path: index (IndexOnly)
    Index: T2_IND
    resc_io: 1.00  resc_cpu: 15321~~~~~~~~~~~~~~~这CPU 部分 COST小，所以ORACLE 还是选择了INDEX RANG SCAN
    ix_sel: 0.40404  ix_sel_with_filters: 0.40404
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: T2_IND
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 40.40  Bytes: 0

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

0
0
上一篇： oracle实验记录 (高速缓存命中率与成本)
下一篇： oracle实验记录（分区全表扫描（全区扫描） FTS 时候的成本计算）
请登录后发表评论 登录
全部评论
fufuh2o

注册时间：2009-06-26

  • 博文量
    182
  • 访问量
    471658

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593