实验记录下分区全表扫描(全区扫描) 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
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pst
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
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
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
--------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------
------------------
| 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-614011/