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

2023-10-10 18:58
文章标签 模式 扫描 全表 工作量 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。

相关文章

在JS中的设计模式的单例模式、策略模式、代理模式、原型模式浅讲

在JS中的设计模式的单例模式、策略模式、代理模式、原型模式浅讲

1. 单例模式（Singleton Pattern） 确保一个类只有一个实例，并提供一个全局访问点。 示例代码： class Singleton {constructor() {if (Singleton.instance) {return Singleton.instance;}Singleton.instance = this;this.data = [];}addData(value)
阅读更多...
模版方法模式template method

模版方法模式template method

学习笔记，原文链接 https://refactoringguru.cn/design-patterns/template-method 超类中定义了一个算法的框架， 允许子类在不修改结构的情况下重写算法的特定步骤。 上层接口有默认实现的方法和子类需要自己实现的方法
阅读更多...
【iOS】MVC模式

【iOS】MVC模式

MVC模式 MVC模式MVC模式demo MVC模式 MVC模式全称为model(模型)view(视图)controller(控制器)，他分为三个不同的层分别负责不同的职责。 View：该层用于存放视图，该层中我们可以对页面及控件进行布局。Model：模型一般都拥有很好的可复用性，在该层中，我们可以统一管理一些数据。Controlller：该层充当一个CPU的功能，即该应用程序
阅读更多...
迭代器模式iterator

迭代器模式iterator

学习笔记，原文链接 https://refactoringguru.cn/design-patterns/iterator 不暴露集合底层表现形式 （列表、 栈和树等） 的情况下遍历集合中所有的元素
阅读更多...
《x86汇编语言：从实模式到保护模式》视频来了

《x86汇编语言：从实模式到保护模式》视频来了

《x86汇编语言：从实模式到保护模式》视频来了 很多朋友留言，说我的专栏《x86汇编语言：从实模式到保护模式》写得很详细，还有的朋友希望我能写得更细，最好是覆盖全书的所有章节。 毕竟我不是作者，只有作者的解读才是最权威的。 当初我学习这本书的时候，只能靠自己摸索，网上搜不到什么好资源。 如果你正在学这本书或者汇编语言，那你有福气了。 本书作者李忠老师，以此书为蓝本，录制了全套视频。 试
阅读更多...
利用命令模式构建高效的手游后端架构

利用命令模式构建高效的手游后端架构

在现代手游开发中，后端架构的设计对于支持高并发、快速迭代和复杂游戏逻辑至关重要。命令模式作为一种行为设计模式，可以有效地解耦请求的发起者与接收者，提升系统的可维护性和扩展性。本文将深入探讨如何利用命令模式构建一个强大且灵活的手游后端架构。 1. 命令模式的概念与优势 命令模式通过将请求封装为对象，使得请求的发起者和接收者之间的耦合度降低。这种模式的主要优势包括： 解耦请求发起者与处理者
阅读更多...
springboot实战学习（1）(开发模式与环境)

springboot实战学习（1）(开发模式与环境)

目录 一、实战学习的引言 （1）前后端的大致学习模块 （2）后端 （3）前端 二、开发模式 一、实战学习的引言 （1）前后端的大致学习模块 （2）后端 Validation：做参数校验Mybatis：做数据库的操作Redis：做缓存Junit：单元测试项目部署：springboot项目部署相关的知识 （3）前端 Vite：Vue项目的脚手架Router：路由Pina：状态管理Eleme
阅读更多...
状态模式state

状态模式state

学习笔记，原文链接 https://refactoringguru.cn/design-patterns/state 在一个对象的内部状态变化时改变其行为， 使其看上去就像改变了自身所属的类一样。 在状态模式中，player.getState()获取的是player的当前状态，通常是一个实现了状态接口的对象。 onPlay()是状态模式中定义的一个方法，不同状态下（例如“正在播放”、“暂停
阅读更多...
软件架构模式：5 分钟阅读

软件架构模式：5 分钟阅读

原文： https://orkhanscience.medium.com/software-architecture-patterns-5-mins-read-e9e3c8eb47d2 软件架构模式：5 分钟阅读 当有人潜入软件工程世界时，有一天他需要学习软件架构模式的基础知识。当我刚接触编码时，我不知道从哪里获得简要介绍现有架构模式的资源，这样它就不会太详细和混乱，而是非常抽象和易
阅读更多...
使用Spring Boot集成Spring Data JPA和单例模式构建库存管理系统

使用Spring Boot集成Spring Data JPA和单例模式构建库存管理系统

引言 在企业级应用开发中，数据库操作是非常重要的一环。Spring Data JPA提供了一种简化的方式来进行数据库交互，它使得开发者无需编写复杂的JPA代码就可以完成常见的CRUD操作。此外，设计模式如单例模式可以帮助我们更好地管理和控制对象的创建过程，从而提高系统的性能和可维护性。本文将展示如何结合Spring Boot、Spring Data JPA以及单例模式来构建一个基本的库存管理系统
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2