Oracle11gR2 全表扫描成本计算(非工作量模式-noworkload)

本文主要是介绍Oracle11gR2 全表扫描成本计算(非工作量模式-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    3

Tablespace 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   10

PL/SQL procedure successfully completed.

SQL> select owner,blocks from dba_tables where owner='TEST' and table_name='TEST';

OWNER                              BLOCKS
------------------------------ ----------
TEST                                 1000

SQL> show parameter db_file_multiblock_read_count

NAME                                 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 cycles

sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second

注意:如果没有收集过系统统计信息,那么Oracle采用非工作量统计,如果收集了,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
SREADTIM

9 rows selected.

我这里因为MBRC 为0,所以CBO采用了非工作量(noworkload)来计算成本

#SRds=0,因为是全表扫描,单块读为0
#MRds=表的块数/多块读参数=1000/16

mreadtim=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
----------
        42

sreadtim=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_COST

SQL> explain plan for select count(*) from test;

Explained.

SQL> select cpu_cost from plan_table;

  CPU_COST
----------
   7271440

cpuspeed 等于 CPUSPEEDNW= 2696.05568

那么COST=1000/16*42/12+7271440/2696.05568/12/1000

SQL>  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有差别,这是由于隐含参数_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    7

NAME                           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=12

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

mreadtim=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一样,没有变化。

这篇关于Oracle11gR2 全表扫描成本计算(非工作量模式-noworkload)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/182404

相关文章

Java设计模式---迭代器模式(Iterator)解读

《Java设计模式---迭代器模式(Iterator)解读》:本文主要介绍Java设计模式---迭代器模式(Iterator),具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,... 目录1、迭代器(Iterator)1.1、结构1.2、常用方法1.3、本质1、解耦集合与遍历逻辑2、统一

Java 线程安全与 volatile与单例模式问题及解决方案

《Java线程安全与volatile与单例模式问题及解决方案》文章主要讲解线程安全问题的五个成因(调度随机、变量修改、非原子操作、内存可见性、指令重排序)及解决方案,强调使用volatile关键字... 目录什么是线程安全线程安全问题的产生与解决方案线程的调度是随机的多个线程对同一个变量进行修改线程的修改操

Redis Cluster模式配置

《RedisCluster模式配置》:本文主要介绍RedisCluster模式配置,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录分片 一、分片的本质与核心价值二、分片实现方案对比 ‌三、分片算法详解1. ‌范围分片(顺序分片)‌2. ‌哈希分片3. ‌虚

RabbitMQ工作模式中的RPC通信模式详解

《RabbitMQ工作模式中的RPC通信模式详解》在RabbitMQ中,RPC模式通过消息队列实现远程调用功能,这篇文章给大家介绍RabbitMQ工作模式之RPC通信模式,感兴趣的朋友一起看看吧... 目录RPC通信模式概述工作流程代码案例引入依赖常量类编写客户端代码编写服务端代码RPC通信模式概述在R

SQL Server身份验证模式步骤和示例代码

《SQLServer身份验证模式步骤和示例代码》SQLServer是一个广泛使用的关系数据库管理系统,通常使用两种身份验证模式:Windows身份验证和SQLServer身份验证,本文将详细介绍身份... 目录身份验证方式的概念更改身份验证方式的步骤方法一:使用SQL Server Management S

Redis高可用-主从复制、哨兵模式与集群模式详解

《Redis高可用-主从复制、哨兵模式与集群模式详解》:本文主要介绍Redis高可用-主从复制、哨兵模式与集群模式的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝... 目录Redis高可用-主从复制、哨兵模式与集群模式概要一、主从复制(Master-Slave Repli

一文带你搞懂Redis Stream的6种消息处理模式

《一文带你搞懂RedisStream的6种消息处理模式》Redis5.0版本引入的Stream数据类型,为Redis生态带来了强大而灵活的消息队列功能,本文将为大家详细介绍RedisStream的6... 目录1. 简单消费模式(Simple Consumption)基本概念核心命令实现示例使用场景优缺点2

Nginx location匹配模式与规则详解

《Nginxlocation匹配模式与规则详解》:本文主要介绍Nginxlocation匹配模式与规则,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、环境二、匹配模式1. 精准模式2. 前缀模式(不继续匹配正则)3. 前缀模式(继续匹配正则)4. 正则模式(大

Linux系统配置NAT网络模式的详细步骤(附图文)

《Linux系统配置NAT网络模式的详细步骤(附图文)》本文详细指导如何在VMware环境下配置NAT网络模式,包括设置主机和虚拟机的IP地址、网关,以及针对Linux和Windows系统的具体步骤,... 目录一、配置NAT网络模式二、设置虚拟机交换机网关2.1 打开虚拟机2.2 管理员授权2.3 设置子

SpringBoot如何通过Map实现策略模式

《SpringBoot如何通过Map实现策略模式》策略模式是一种行为设计模式,它允许在运行时选择算法的行为,在Spring框架中,我们可以利用@Resource注解和Map集合来优雅地实现策略模式,这... 目录前言底层机制解析Spring的集合类型自动装配@Resource注解的行为实现原理使用直接使用M