Oracle 11g R2 全表扫描成本计算（非工作量模式-noworkload）

数据库版本Oracle11gR2
SQL> select * from v$version where rownum=1;BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production创建手动管理的表空间，blockssize 8k
SQL> create tablespace test datafile
"/u01/app/oracle/oradata/ROBINSON/datafile/test.dbf" size 50m autoextend on maxsize 200m
uniform size 1m segment space management manual blocksize 8k; 2    3Tablespace created.创建测试用户test,默认表空间 test
SQL> create user test identified by oracle default tablespace test;User created.为了简便，授权DBA给test
SQL> grant dba to test;Grant succeeded.创建测试表test
SQL> create table test as select * from dba_objects where 1=0 ;Table created.设置pctfree 99
SQL> alter table test pctfree 99 pctused 1;Table altered.SQL> insert into test select * from dba_objects where rownum<2;1 row created.确保一行一个block
SQL> alter table test minimize records_per_block;Table altered.SQL> insert into test select * from dba_objects where rownum<1000;999 rows created.SQL> commit;Commit complete.收集表统计信息
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS（ownname => "TEST",
tabname => "TEST",
estimate_percent => 100,
method_opt => "for all columns size 1",
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
）;
END;
/ 2    3    4    5    6    7    8    9   10PL/SQL procedure successfully completed.SQL> select owner,blocks from dba_tables where owner="TEST" and table_name="TEST";OWNER                              BLOCKS
------------------------------ ----------
TEST                                 1000SQL> show parameter db_file_multiblock_read_countNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16全表扫描的成本等于220
SQL> select count（*） from test;Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost （%CPU）| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   220   （0）| 00:00:03 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 1000 |   220   （0）| 00:00:03 |
-------------------------------------------------------------------成本的计算方式如下：
Cost = （
       #SRds * sreadtim +
       #MRds * mreadtim +
       CPUCycles / cpuspeed
       ） / sreadtime

#SRds - number of single block reads
#MRds - number of multi block reads
#CPUCyles - number of CPU cyclessreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second注意：如果没有收集过系统统计信息，那么Oracle采用非工作量统计，www.linuxidc.com 如果收集了，Oracle采用工作量统计的计算方法
SQL> select pname, pval1 from sys.aux_stats$ where sname="SYSSTATS_MAIN";PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     2696.05568
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM9 rows selected.我这里因为MBRC 为0，所以CBO采用了非工作量（noworkload）来计算成本#SRds=0,因为是全表扫描，单块读为0
#MRds=表的块数/多块读参数=1000/16mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
SQL> select （select pval1 from sys.aux_stats$ where pname = "IOSEEKTIM"） +
2         （select value
          from v$parameter
         where name = "db_file_multiblock_read_count"） *
       （select value from v$parameter where name = "db_block_size"） /
       （select pval1 from sys.aux_stats$ where pname = "IOTFRSPEED"） "mreadtim"
3    4    5    6    7    from dual; mreadtim
----------
        42sreadtim=ioseektim+db_block_size/iotfrspeed
SQL> select （select pval1 from sys.aux_stats$ where pname = "IOSEEKTIM"） +
       （select value from v$parameter where name = "db_block_size"） /
       （select pval1 from sys.aux_stats$ where pname = "IOTFRSPEED"） "sreadtim"
from dual; 2    3    4 sreadtim
----------
        12

CPUCycles 等于 PLAN_TABLE里面的CPU_COSTSQL> explain plan for select count（*） from test;Explained.SQL> select cpu_cost from plan_table; CPU_COST
----------
   7271440cpuspeed 等于 CPUSPEEDNW= 2696.05568那么COST=1000/16*42/12+7271440/2696.05568/12/1000SQL> select ceil（1000/16*42/12+7271440/2696.05568/12/1000） from dual;CEIL（1000/16*42/12+7271440/2696.05568/12/1000）
----------------------------------------------
                                           219手工计算出来的COST用四舍五入等于219，和我们看到的220有差别，www.linuxidc.com 这是由于隐含参数_tablescan_cost_plus_one参数造成的SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = USERENV （"Instance"）
   AND y.inst_id = USERENV （"Instance"）
   AND x.indx = y.indx
   AND x.ksppinm LIKE "%_table_scan_cost_plus_one%"
/ 2    3    4    5    6    7NAME                           VALUE      DESCRIB
------------------------------ ---------- ------------------------------
_table_scan_cost_plus_one      TRUE       bump estimated full table scan
                                           and index ffs cost by one
根据该参数的描述，在table full scan和index fast full scan的时候会将cost+1
那么我把改参数禁止了试一试SQL> alter session set "_table_scan_cost_plus_one"=false;Session altered.SQL> set autot trace
SQL> select count（*） from test;Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost （%CPU）| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   219   （0）| 00:00:03 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 1000 |   219   （0）| 00:00:03 |
-------------------------------------------------------------------这次得到的Cost等于219，与计算值正好匹配，现在更改db_file_multiblock_read_count参数SQL> alter session set db_file_multiblock_read_count=32;Session altered.这个时候 sreadtim=12SQL> select （select pval1 from sys.aux_stats$ where pname = "IOSEEKTIM"） +
       （select value from v$parameter where name = "db_block_size"） /
       （select pval1 from sys.aux_stats$ where pname = "IOTFRSPEED"） "sreadtim"
from dual; 2    3    4 sreadtim
----------
        12mreadtim=74

SQL> select （select pval1 from sys.aux_stats$ where pname = "IOSEEKTIM"） +
       （select value
2    3            from v$parameter
4           where name = "db_file_multiblock_read_count"） *
5         （select value from v$parameter where name = "db_block_size"） /
6         （select pval1 from sys.aux_stats$ where pname = "IOTFRSPEED"） "mreadtim"
7    from dual; mreadtim
----------
        74那么cost等于SQL> select ceil（1000/32*74/12+7271440/2696.05568/12/1000） from dual;CEIL（1000/32*74/12+7271440/2696.05568/12/1000）
----------------------------------------------
                                           193
SQL> set autot trace
SQL> select count（*） from test;Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost （%CPU）| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   193   （0）| 00:00:03 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 1000 |   193   （0）| 00:00:03 |
-------------------------------------------------------------------与计算的Cost相匹配，从实验种可以得出，在11gR2中，全表扫描计算Cost的方式依然和9i/10g一样，没有变化。相关链接 http://www.linuxidc.com/Linux/2011-07/38575.htmOracle 11g R2 全表扫描成本计算（工作量模式-workload）一次500行SQL的优化相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）