oracle實驗記錄（INDEX fast full scan 的成本計算）

現在環境

實驗下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

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

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

| 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部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613946/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄（INDEX fast full scan 的成本計算）

相關文章