扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Oracle动态采样学习
  • 245

0
分享到

Oracle动态采样学习

2024-04-02 19:04:59 245人浏览 泡泡鱼
摘要

     动态采样(Dynamic Sampling)是在oracle 9i Release 2中开始引入的一个技术，引入它的目的是为了应对数据库对象没有分析（统计信息缺失）的

     动态采样(Dynamic Sampling)是在oracle 9i Release 2中开始引入的一个技术，引入它的目的是为了应对数据库对象没有分析（统计信息缺失）的情况下，优化器生成更好的执行计划。简单的说，在数据库段（表、索引、分区）对象没有分析的情况下，为了使CBO优化器得到足够多的信息以保证优化器做出正确执行计划而发明的一种技术。它会分析一定数量段对象上的数据块获取CBO需要的统计信息。动态采样技术仅仅是统计信息的一种补充，它不能完全替代统计信息分析。


注意：动态采样在Oracle 11g之前称为 Dynamic Sampling, ORACLE 12c之后改名为Dynamic Statistic.

Oracle11G R2 默认的采样级别：

sql> show parameter optimizer_dynamic_sampling

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_dynamic_sampling           integer     2


SQL> show parameter Dynamic Statistic

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_dynamic_sampling           integer     2


动态采样的级别有11个级别：请自行查看官方文档

Http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30101



动态采样实验：


1、创建测试表test

SQL> create table test as select * from dba_objects;          


Table created.


SQL> select count(1) from test;


  COUNT(1)

----------

     86259


2、不使用动态采样，查看执行计划

SQL> set autotrace traceonly explain;

SQL> select * from test;   


Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   100K|    19M|   336   (1)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST |   100K|    19M|   336   (1)| 00:00:05 |

--------------------------------------------------------------------------


Note

-----

   - dynamic sampling used for this statement (level=2)


从上面可以看出，次数优化器估计表test的行数显示为100K，我们再看下面使用动态采样的执行计划，优化器会估算多少行：


3、使用动态采样，查看执行计划（下面是直接查询的，因为在11G 是默认启用动态采样的）

SQL> select * from test;


Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 72258 |    14M|   336   (1)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST | 72258 |    14M|   336   (1)| 00:00:05 |

--------------------------------------------------------------------------


Note

-----

   - dynamic sampling used for this statement (level=2)

   

   

如果启用动态采样(默认情况下，动态采样级别为2)，优化器根据动态采样得到一些数据信息猜测、估计表TEST的记录行数为86259，已经接近实际记录行数72258了。比不做动态采样分析要好很多了。



如果我们将动态采样的级别提高为3，如下所示，发现优化器根据动态采样得到的信息比默认（默认情况下，动态采样级别为2）情况获得的信息更准确。优化器估计表TEST的行数为92364，比72258又接近实际情况一步了。


SQL> select * from test;


Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 92364 |    18M|   336   (1)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST | 92364 |    18M|   336   (1)| 00:00:05 |

--------------------------------------------------------------------------


Note

-----

   - dynamic sampling used for this statement (level=2)



4、在Tom大师的这篇文章中提到，在没有动态采样的情况下，如果删除了该表数据，CBO优化器估算的结果集和没有删除之前是一样的。

    这是因为当一个表的数据被删除后，这个表所分配的extent和block是不会自动回收的（高水位线不变），所以CBO如果没有采样数据块做分析，只是从数据字典中获取extend等信息，就会误认为任然还有那么多数据。下面我们把test表数据清空，看看执行计划如何


SQL> delete from test;


86259 rows deleted.


SQL> commit;


SQL> select * from test;    ----不使用动态采样

Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   100K|    19M|   336   (1)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST |   100K|    19M|   336   (1)| 00:00:05 |

--------------------------------------------------------------------------


SQL> select * from test;                   -----使用动态采样


Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |   207 |   335   (0)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST |     1 |   207 |   335   (0)| 00:00:05 |

--------------------------------------------------------------------------


Note

-----

   - dynamic sampling used for this statement (level=2)


从上面的查看可以看出，不采用动态采样和采用动态采样的区别；


5、我们对test表收集下统计信息：再次查询，该表的执行计划就会少了：dynamic sampling 

SQL> select * from test;


Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |   207 |   335   (0)| 00:00:05 |

|   1 |  TABLE ACCESS FULL| TEST |     1 |   207 |   335   (0)| 00:00:05 |

--------------------------------------------------------------------------


SQL> 



第二种情况：当表TEST即使被分析过，如果查询脚本里面包含临时表，就会使用动态采样技术。因为临时表是不会被分析，它是没有统计信息的。如下所示：


SQL> drop table test;


SQL> create table test as select * from dba_objects;


SQL> exec dbms_stats.gather_table_stats(ownname =>'SYS',tabname =>'TEST',cascade=>TRUE);


SQL> create global temporary table tmp (object_type varchar2(19));


SQL> insert into tmp select distinct object_type from dba_objects;


44 rows created.


SQL> commit;


然后查看下面查询语句的执行计划：


SQL> select t.owner,l.object_type from test t inner join tmp l on t.object_type=l.object_type;

Execution Plan

----------------------------------------------------------

Plan hash value: 19574435


---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    26 |   338   (1)| 00:00:05 |

|*  1 |  HASH JOIN         |      |     1 |    26 |   338   (1)| 00:00:05 |

|   2 |   TABLE ACCESS FULL| TMP  |     1 |    11 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TEST | 86260 |  1263K|   336   (1)| 00:00:05 |

---------------------------------------------------------------------------


Predicate InfORMation (identified by operation id):

---------------------------------------------------


   1 - access("T"."OBJECT_TYPE"="L"."OBJECT_TYPE")


Note

-----

   - dynamic sampling used for this statement (level=2)


SQL> 

从上面可以看到 虽然是对tmp表执行的而是全表扫描，但是优化器只是估算了1行数据



6、动态采样还有一个独特能力，可以对不同列之间的相关性做统计。

   表统计信息都是相对独立的。当查询涉及列之间的相关性时,统计信息就显得有些不足了,请看Tom大师的例子


6.1、创建一个特殊的表t，然后对字段flag1、flag2创建索引t_idx，然后分析收集统计信息

SQL> create table t as select decode(mod(rownum,2),0,'N', 'Y') flag1, decode(mod(rownum,2),0,'Y', 'N') flag2, a.* from all_objects a;

SQL> create index t_idx on t(flag1, flag2);

 

SQL> begin

    dbms_stats.gather_table_stats(user, 'T',      

          method_opt =>'for all indexed columns size 254');

    end;

    /

 

PL/SQL procedure successfully completed.


6.2、查看表的行数：

SQL> select num_rows, num_rows/2, num_rows/2/2 from user_tables  where table_name='T';

 

  NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2

---------- ---------- ------------

     84396      42198        21099


6.3、看看对flag1过滤条件的SQL语句的执行计划：

SQL> select * from t where flag1='N';


Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 42937 |  4276K|   342   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T    | 42937 |  4276K|   342   (1)| 00:00:05 |

--------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   1 - filter("FLAG1"='N')


从上面的执行计划可以看出：CBO优化器猜测、估计的行数42937， 相当接近42198记录数了


6.4、看看对flag2过滤条件的SQL语句的执行计划：

SQL> select * from t where flag2='N';


Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 41459 |  4129K|   342   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T    | 41459 |  4129K|   342   (1)| 00:00:05 |

--------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   1 - filter("FLAG2"='N')


从上面的执行计划可以看出：CBO优化器猜测、估计的行数41459， 相当接近42198记录数了



6.5、如果条件flag1 = 'N' and flag2 = 'N',我们根据逻辑推理判断这样的记录肯定是不存在的，这也是苦心构造这个特例的初衷。下面看看CBO优化器怎么探测、预测的


SQL> select * from t where flag1 = 'N' and flag2 = 'N';


Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 21093 |  2101K|   342   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T    | 21093 |  2101K|   342   (1)| 00:00:05 |

--------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   1 - filter("FLAG2"='N' AND "FLAG1"='N')


从上面看：CBO估计的记录数为12468，和实际情况相差非常远。其实是CBO优化器这样估算来的：


flag1=‘N' 的记录数占总数的1/2

flag2= 'N' 的记录数占总数的1/2


6.6、根据NUM_ROWS/2/2 =12468.这样显然是不合理的。下面我们通过提升动态采样级别，来看看动态采样是否能避免CBO的错误：

SQL> select * from t where flag1 = 'N' and flag2 = 'N';


Execution Plan

----------------------------------------------------------

Plan hash value: 470836197


-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |     6 |   612 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     6 |   612 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - access("FLAG1"='N' AND "FLAG2"='N')


Note

-----

   - dynamic sampling used for this statement (level=2)



注意：

①：采样级别越高，采样的数据块越多，得到的分析数据就越接近于真实，但同时伴随着资源消耗的开销也增加了。这时一个需要权衡考虑的东西。ORACLE 10 g & 11g的默认采样级别都为2,一般使用在会话中使用dynamic_sampling提示来修改动态采样级别。


②：凡事有利必有弊，动态采样也不是神器。它采样的数据块越多，系统开销就越大，这样会增加SQL硬解析的时间，如果是数据库仓库（DW、OLAP）环境，SQL执行时间相当长，硬解析时间只占整个SQL执行时间的一小部分，那么可以适当的提高动态采样级别，这样是有利于优化器获取更加正确的信息。一般设置为3或4比较合适。


③：在并发比较严重的OLTP系统中，每秒中有成千上万的SQL语句执行，它要求SQL语句短小、执行时间短，所以在OLTP系统中应该减低动态采样级别或不用动态采样。


您可能感兴趣的文档:

--结束END--

本文标题: Oracle动态采样学习

本文链接: https://lsjlt.com/news/51948.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • Oracle动态采样学习
         动态采样(Dynamic Sampling)是在ORACLE 9i Release 2中开始引入的一个技术，引入它的目的是为了应对数据库对象没有分析（统计信息缺失）的...
    99+
    2024-04-02
  • PyTorch学习笔记：data.RandomSampler——数据随机采样
    PyTorch学习笔记：data.RandomSampler——数据随机采样 torch.utils.data.RandomSampler(data_source, replacement=...
    99+
    2023-09-05
    pytorch 学习 python
  • Python学习笔记6——动态类型
    参考博客：Python进阶09 动态类型  Python深入06 Python的内存管理 都是非常棒的文章 其实这都是我前两天通过手机看的博客，感觉get到了新知识、新技能，今天早上挖的坑，因为上午有课（电子技术课程设计，嵌入式系统实验室...
    99+
    2023-01-30
    学习笔记 类型 动态
  • 学习oracle
    下载oracle网址http://www.oracle.com/technetwork/index.html根据你的机型选择下载安装不同版本，首先你得注册一个oracle账户才能下载。官网EX Editio...
    99+
    2024-04-02
  • Oracle学习
    001——在现有的表上新建表Create table t2as select ename name,sal salary from emp;当t2诞生时就会有子查询中所查出的数据。如果想改变列的名...
    99+
    2024-04-02
  • 怎样学习Python
    小编给大家分享一下怎样学习Python，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！首先，从基础开始学习，切勿毛躁。刚开始学习Python的时候，我们可能会有些毛躁总觉得这些知识太简单了，没必要去学习，其实这种想法完全是错...
    99+
    2023-06-19
  • 怎样学习golang
    随着云计算、大数据、人工智能等技术的持续发展，对编程语言的需求也不断增加。而Golang作为一门高效、简单、安全的编程语言，在如今的互联网时代受到了广泛的欢迎。那么，在学习Golang这门编程语言时，应该怎样入门呢？首先，我们需要了解Gol...
    99+
    2023-05-22
  • Redis中的动态字符串学习教程
    sds 的用途http://www.gimoo.net/t/1512/ Sds 在 Redis 中的主要作用有以下两个： 实现字符串对象（StringObject）；http://www.gimoo.net...
    99+
    2022-06-04
    字符串 教程 动态
  • vue学习记录之动态组件浅析
    目录动态组件补充：动态调用组件示例总结动态组件 释义：运行时在组件之间动态切换的方法。可以将多个条件组件（使用 v-if、v-else-if、v-else 切换的...
    99+
    2024-04-02
  • Oracle学习（05.13）
          OracleSID   ： 数据库实例名 Oracle数据库的企业管理器：    http://ip:1158/em 查看数据库状态    sqlplus  /  as sysdba;    select st...
    99+
    2017-03-27
    Oracle学习（05.13）
  • 怎样学习.NET Framework
    怎样学习.NET Framework，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。.NET Framework的学习是需要一个循序渐进的过程。我们在这里就为大家总结一下学习.N...
    99+
    2023-06-17
  • PythonOpenCV学习之图像形态学
    目录背景一、图像二值化二、自适应阈值三、腐蚀四、卷积核获取五、膨胀六、开运算七、闭运算八、形态学梯度九、顶帽运算十、黑帽运算总结背景 形态学处理方法是基于对二进制图像进行处理的，卷积...
    99+
    2024-04-02
  • mybatis动态SQL操作之插入学习笔记
    1 import java.util.ArrayList; import java.util.List; import org.apache.ibatis.session.SqlSession; im...
    99+
    2024-04-02
  • 一起来学习C++的动态内存管理
    目录1.new和delete2.new和delete在底层是怎么实现的：2.1new底层的实现： 我们先来new一个test类型的空间。2.2delete底层的实现： 我们执行del...
    99+
    2024-04-02
  • angular学习之深入聊聊状态和动画
    本篇文章带大家深入了解一下angular中的状态和动画，简单介绍一下创建动画的方法，并聊聊关键帧动画、动画回调、可重用动画、交错动画等知识点，希望对大家有所帮助！状态1、什么是状态状态表示的是要进行运动的元素在运动的不同时期所呈现的样式。2...
    99+
    2023-05-14
    状态 动画 Angular
  • oracle学习笔记
    oracle安装1.         安装virtualbox：# yum install gcc kernel-devel ...
    99+
    2024-04-02
  • oracle 函数学习 Oracle10g学习系列（7）
    Oracle函数有字符函数、数学函数、日期函数和转换函数。我只了解了字符函数和数学函数。一、字符函数：lower(char）  将字符串转化为小写的格式SQL> select lo...
    99+
    2024-04-02
  • 怎样学习JavaScript中elisp
    怎样学习JavaScript中elisp，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。对我们 JS 用户而言，学习 Emacs...
    99+
    2024-04-02
  • 【李沐】动手学深度学习 学习笔记
    目录 【内容介绍】动手学深度学习-基于pytorch版本【脉络梳理】预备知识数据操作数据预处理线性代数矩阵计算自动求导 线性神经网络线性回归深度学习的基础优化算法线性回归的从零开始实现线...
    99+
    2023-08-31
    深度学习 学习 python
  • Android学习教程之动态GridView控件使用（6）
    本文实例为大家分享了Android动态GridView控件使用的具体代码，供大家参考，具体内容如下 MainActivity.java代码: package siso.hah...
    99+
    2022-06-06
    android学习 程之 gridview 教程 Android
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作