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。

相关文章

C#原型模式之如何通过克隆对象来优化创建过程

C#原型模式之如何通过克隆对象来优化创建过程

《C#原型模式之如何通过克隆对象来优化创建过程》原型模式是一种创建型设计模式,通过克隆现有对象来创建新对象,避免重复的创建成本和复杂的初始化过程,它适用于对象创建过程复杂、需要大量相似对象或避免重复初... 目录什么是原型模式？原型模式的工作原理C#中如何实现原型模式？1. 定义原型接口2. 实现原型接口3
阅读更多...
大数据spark3.5安装部署之local模式详解

大数据spark3.5安装部署之local模式详解

《大数据spark3.5安装部署之local模式详解》本文介绍了如何在本地模式下安装和配置Spark,并展示了如何使用SparkShell进行基本的数据处理操作,同时,还介绍了如何通过Spark-su... 目录下载上传解压配置jdk解压配置环境变量启动查看交互操作命令行提交应用spark，一个数据处理框架
阅读更多...
Java实现状态模式的示例代码

Java实现状态模式的示例代码

《Java实现状态模式的示例代码》状态模式是一种行为型设计模式,允许对象根据其内部状态改变行为,本文主要介绍了Java实现状态模式的示例代码,文中通过示例代码介绍的非常详细,需要的朋友们下面随着小编来... 目录一、简介1、定义2、状态模式的结构二、Java实现案例1、电灯开关状态案例2、番茄工作法状态案例
阅读更多...
SQL注入漏洞扫描之sqlmap详解

SQL注入漏洞扫描之sqlmap详解

《SQL注入漏洞扫描之sqlmap详解》SQLMap是一款自动执行SQL注入的审计工具,支持多种SQL注入技术,包括布尔型盲注、时间型盲注、报错型注入、联合查询注入和堆叠查询注入... 目录what支持类型how---less-1为例1.检测网站是否存在sql注入漏洞的注入点2.列举可用数据库3.列举数据库
阅读更多...
IDEA常用插件之代码扫描SonarLint详解

IDEA常用插件之代码扫描SonarLint详解

《IDEA常用插件之代码扫描SonarLint详解》SonarLint是一款用于代码扫描的插件,可以帮助查找隐藏的bug,下载并安装插件后,右键点击项目并选择“Analyze”、“Analyzewit... 目录SonajavascriptrLint 查找隐藏的bug下载安装插件扫描代码查看结果总结Sona
阅读更多...
python-nmap实现python利用nmap进行扫描分析

python-nmap实现python利用nmap进行扫描分析

《python-nmap实现python利用nmap进行扫描分析》Nmap是一个非常用的网络/端口扫描工具,如果想将nmap集成进你的工具里,可以使用python-nmap这个python库,它提供了... 目录前言python-nmap的基本使用PortScanner扫描PortScannerAsync异
阅读更多...
在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 不暴露集合底层表现形式 （列表、 栈和树等） 的情况下遍历集合中所有的元素
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2