oracle實驗記錄（分割槽全表掃描（全區掃描） FTS 時候的成本計算）

實驗記錄下分割槽全表掃描（全區掃描） FTS 時候的成本計算

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

create table t1 (
part_col not null,
id not null,
small_vc,
padding
)
partition by range(part_col) (
partition p0200 values less than ( 200),
partition p0400 values less than ( 400),
partition p0600 values less than ( 600),
partition p0800 values less than ( 800),~~~~~~~~~~~~~~~`這個建立表的指令碼來自COST-BASED ORACLE
partition p1000 values less than (1000)
)
nologging
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 5000
)
select
trunc(sqrt(rownum-1)),
rownum-1,
lpad(rownum-1,10),
rpad('x',50)
from
generator v1,
generator v2
where
rownum <= 1000000
/

begin
dbms_stats.gather_table_stats(
  user,
  't1',
  cascade => true,
  estimate_percent => null,
  method_opt => 'for all columns size 1'
);
end;
.
/

SQL> select table_name,blocks from user_tables where table_name='T1';

TABLE_NAME BLOCKS
------------------------------ ----------
T1 10527

SQL> select partition_name ,blocks from user_tab_partitions where table_name='T1
';

PARTITION_NAME                     BLOCKS
------------------------------ ----------
P0200                                 419
P0400                                1264
P0600                                2106
P0800                                2948
P1000                                3790

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

會話已更改。

SQL> select * from t1;

執行計劃
----------------------------------------------------------
Plan hash value: 589593414

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

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

art| Pstop |

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

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

| 0 | SELECT STATEMENT | | 1000K| 66M| 2323 (1)| 00:00:28 |
| |

| 1 | PARTITION RANGE ALL| | 1000K| 66M| 2323 (1)| 00:00:28 |
1 | 5 |

| 2 | TABLE ACCESS FULL | T1 | 1000K| 66M| 2323 (1)| 00:00:28 |
1 | 5 |

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

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

SQL> select     count(*)
2 from       t1
3 where      part_col between 250 and 350
4 ;

執行計劃
----------------------------------------------------------
Plan hash value: 3488358399

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

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

Pstart| Pstop |

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

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

| 0 | SELECT STATEMENT | | 1 | 4 | 280 (1)| 00:00:04 |

| |

| 1 | SORT AGGREGATE | | 1 | 4 | | |

| |

| 2 | PARTITION RANGE SINGLE| | 61502 | 240K| 280 (1)| 00:00:04 |~~~~~~~~~~~~分割槽掃描
~
2 | 2 |

|* 3 | TABLE ACCESS FULL | T1 | 61502 | 240K| 280 (1)| 00:00:04 |

2 | 2 |

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

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

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

3 - filter("PART_COL">=250 AND "PART_COL"<=350)

計算*使用預設資訊**************************

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> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter db_file_mu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
已選擇9行。
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

MDRS=BLOCKS/MBRC
#SRds =0(single-block reads),因為是 FTS 都是多塊讀取
最早的公式變換一下(除開)
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +**************io cost
#CPUCycles / (cpuspeed * sreadtim)********cpu cost

計算IO 部分COST

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

CEIL((10527/16)*(42/12))
------------------------
2303

table _table_scan_cost_plus_one 為TRUCE 所以 IO COST =2304 與下面TRACE中一樣

分割槽COST ~~~可以看到掃描分割槽時候 ~用的是分割槽的BLOCKS資訊該分割槽BLOCKS 為1264
SQL> select ceil((1264/16)*(42/12)) from dual;

CEIL((1264/16)*(42/12))
-----------------------
277

table _table_scan_cost_plus_one 為TRUCE 所以 IO COST =278 與下面TRACE中一樣

trac中
select count(*) from t1

SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
    Card: Original: 1000000 Rounded: 1000000 Computed: 1000000.00 Non Adjusted: 1000000.00
Access Path: TableScan
    Cost: 2322.69 Resp: 2322.69 Degree: 0
      Cost_io: 2304.00 Cost_cpu: 284967399~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Resp_io: 2304.00 Resp_cpu: 284967399
Best:: AccessPath: TableScan
         Cost: 2322.69 Degree: 1 Resp: 2322.69 Card: 1000000.00 Bytes: 0

Current SQL statement for this session:
select count(*)
from t1
where part_col between 250 and 350

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T1 Alias: T1 Partition [1]
    #Rows: 120000 #Blks: 1264 AvgRowLen: 70.00
    #Rows: 120000 #Blks: 1264 AvgRowLen: 70.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): PART_COL(NUMBER) Part#: 1
    AvgLen: 4.00 NDV: 200 Nulls: 0 Density: 0.005 Min: 200 Max: 399
Column (#1): PART_COL(NUMBER)
    AvgLen: 4.00 NDV: 200 Nulls: 0 Density: 0.005 Min: 200 Max: 399
Table: T1 Alias: T1
    Card: Original: 120000 Rounded: 61502 Computed: 61501.51 Non Adjusted: 61501.51
Access Path: TableScan
    Cost: 280.46 Resp: 280.46 Degree: 0
      Cost_io: 278.00 Cost_cpu: 37523962~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Resp_io: 278.00 Resp_cpu: 37523962
Best:: AccessPath: TableScan
         Cost: 280.46 Degree: 1 Resp: 280.46 Card: 61501.51 Bytes: 0

~~上例子為在一個分割槽中，現在跨分割槽計算下

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

會話已更改。

SQL> select     count(*)
2 from       t1
3 where      part_col between 150 and 250
4 ;

執行計劃
----------------------------------------------------------
Plan hash value: 2744578615

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

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

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

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

| 0 | SELECT STATEMENT | | 1 | 4 | 373 (1)| 00:00:05

| | |

| 1 | SORT AGGREGATE | | 1 | 4 | |
| | |

| 2 | PARTITION RANGE ITERATOR| | 102K| 398K| 373 (1)| 00:00:05~~~~~~~~~~可以看到PSTART，PSTOP為2個分割槽

| 1 | 2 |

|* 3 | TABLE ACCESS FULL | T1 | 102K| 398K| 373 (1)| 00:00:05

| 1 | 2 |

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

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

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

3 - filter("PART_COL"<=250 AND "PART_COL">=150)

SQL> select ceil(((419+1264)/16)*(42/12)) from dual;

CEIL(((419+1264)/16)*(42/12))
-----------------------------
369

table _table_scan_cost_plus_one 為TRUCE 所以 IO COST =370與下面TRACE中一樣

SINGLE TABLE ACCESS PATH
Column (#1): PART_COL(NUMBER)
    AvgLen: 4.00 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 0 Max: 999
Table: T1 Alias: T1
    Card: Original: 1000000 Rounded: 102100 Computed: 102100.10 Non Adjusted: 102100.10
Access Path: TableScan
    Cost: 373.01 Resp: 373.01 Degree: 0
      Cost_io: 370.00 Cost_cpu: 45968791
      Resp_io: 370.00 Resp_cpu: 45968791
Best:: AccessPath: TableScan
         Cost: 373.01 Degree: 1 Resp: 373.01 Card: 102100.10 Bytes: 0

Final - All Rows Plan: Best join order: 1
Cost: 373.0148 Degree: 1 Card: 102100.0000 Bytes: 408400
Resc: 373.0148 Resc_io: 370.0000 Resc_cpu: 45968791
Resp: 373.0148 Resp_io: 370.0000 Resc_cpu: 45968791

所以分割槽COST 就是用的分割槽的BLOCKS 計算~~~如果跨分割槽就是多個分割槽BLOCKS相加

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-614011/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄（分割槽全表掃描（全區掃描） FTS 時候的成本計算）

相關文章