ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录(分区全表扫描(全区扫描) FTS 时候的成本计算)

oracle实验记录(分区全表扫描(全区扫描) FTS 时候的成本计算)

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

 


实验记录下分区全表扫描(全区扫描) 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426966