聊聊Oracle Optimizer相关的几个参数（中）

最新推荐文章于 2024-06-18 16:10:07 发布
最新推荐文章于 2024-06-18 16:10:07 发布
阅读量164 收藏
点赞数
文章标签： 数据库 操作系统 前端 ViewUI

 

我们继续来介绍可以影响Oracle Optimizer工作的若干参数。

 

4、优化器参数2——cursor_sharing

 

Cursor_sharing是Oracle优化器的另一个重要参数，可能也是我们日常讨论较多的一个参数。最开始的cursor_sharing提出，是建立在Oracle希望可以“一蹴而就”的解决hard parse，提高library cache中SQL执行计划重用概率的“美好愿望”下。在相当长的时间里，很多DBA和开发人员都将这个参数视为“金手指”，认为只要开启了这个参数，硬解析比例就会降低，性能瓶颈就会消除。

 

但是无论是从理论上，还是实际使用效果上，cursor_sharing都没有实现当时的目标。

 

笔者在之前的Blog中，对cursor_sharing参数的含义、取值和效果乃至评价都有比较具体的阐述。有兴趣的读者可以参见下面列表系列：

 

《浅谈cursor_sharing取值对SQL共享的影响》

http://space.itpub.net/17203031/viewspace-705196

http://space.itpub.net/17203031/viewspace-705289

 

 

SQL> show parameter cursor_shar

 

NAME                                 TYPE        VALUE

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

cursor_sharing                       string      EXACT

 

 

简单的说，cursor_sharing参数对使用绑定变量的SQL语句是没有效果和影响的。如果该参数设置为非EXACT，那些没有使用绑定变量的SQL语句的where后面取值都会被默认的替换，并且进行一定程度的执行计划共享。

 

但是，无论是cursor_sharing取值为familiar还是force，都存在其在游标共享问题的不合理性。特别是其对所有SQL执行计划处理的“一刀切”策略，是很有问题的。

 

在使用过程中，cursor_sharing也有各种问题，所以很快的大家就放弃了这种参数优化的思路。转回到书写绑定变量和避免bind peeking等方案上来。

 

进入11g之后，Adaptive Cursor Sharing技术的提出，意味着Oracle重新思考解决绑定变量的bind peeking问题。Cursor_sharing有传言在下一个版本中一些取值会去除。

 

笔者认为：cursor_sharing是一种不成熟的尝试。在一些特定场合下，可能存在使用的价值。但是作为数据架构师和开发人员，还是老老实实的写绑定变量稳妥。运维DBA在面对没有绑定变量的系统时候，也要慎用该参数。

 

5、优化参数3——db_file_multiblock_read_count

 

这个参数的作用，同Oracle进行物理IO的方式有关。Oracle进行的IO分为Logical IO和Physical IO两种。不严格的说：进行物理IO的次数比例越高，系统性能瓶颈问题出现的多。

 

但是进行IO也是有不同之处的。进行一次IO的数据块读取量不同，可能会影响到Optimizer特别是CBO的成本计算。

 

在Oracle进行FTS（Full Table Scan）和Index Fast-Full Scan的时候，进行读取的数据块都是连续方式访问的。因为作为数据段Data Segment和索引段Index Segment，都是由一系列内部连续数据块组成的分区extent序列构成的。在进行这样的操作时，获取的数据块大都是相邻的。

 

如果我们进行一次物理IO时候，能够多获取到一些数据块，那么进行物理IO的绝对次数就会变少。这样相应的成本就会降低。

 

 

SQL> show parameter db_file_mu

 

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count        integer     16

 

 

SQL> select value, display_value from v$parameter where name='db_file_multiblock_read_count';

 

VALUE      DISPLAY_VA

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

16                                     16

 

 

默认情况下，笔者实验环境上的该参数为16，表示一次进行读取的时候最多读取到16个数据块。

 

我们首先回顾一下在默认情况下，进行FTS操作的成本计算。

 

 

 

 

SQL> explain plan for select * from t where wner='SYS';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 43272 |  3887K|   158   (3)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 43272 |  3887K|   158   (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OWNER"='SYS')

 

SQL> explain plan for select * from t where object_id between 10 and 50000;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 44865 |  4030K|   159   (4)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 44865 |  4030K|   159   (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"<=50000 AND "OBJECT_ID">=10)

 

 

 

注意，两个进行FTS的执行计划，Oracle优化器“估算出”的成本值为158和159。如果我们调整db_file_multiblock_read_count参数，观察一下两个SQL的执行计划变化。

 

 

SQL> alter session set db_file_multiblock_read_count=300;

Session altered

 

SQL> select value, display_value from v$parameter where name='db_file_multiblock_read_count';

 

VALUE      DISPLAY_VA

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

128                                  128

 

 

我们尝试将这个参数设置为300，但是最后还是调整为最大值128。这个参数虽然可以设置，但是最后还要受到服务器体系结构和物理配置的影响。

 

此时两个SQL的执行计划如下。

 

 

SQL> explain plan for select * from t where wner='SYS';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 43300 |  3890K|   126   (4)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 43300 |  3890K|   126   (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OWNER"='SYS')

 

SQL> explain plan for select * from t where object_id between 10 and 50000;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 44849 |  4029K|   127   (4)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 44849 |  4029K|   127   (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"<=50000 AND "OBJECT_ID">=10)

 

13 rows selected

 

 

当我们调整该参数之后，进行FTS操作计算出的成本变小。说明在计算执行计划的过程中，如果db_file_multiblock_read_count参数设置变大，成本公式中成本值就会变小。

 

可以看出，该参数设置的较大，可以让FTS操作成本估算的结果变小。进而，最终的生成执行计划更倾向于FTS操作。

 

 

6、优化参数4——optimizer_mode

 

optimizer_mode也是Oracle早期使用的一个参数，主要是针对SQL执行计划生成的目标导向。默认情况下，该参数取值为all_rows，表明执行计划的生成是以最小成本消耗和吞吐量为标准。在all_rows的取值情况下，我们通常是对所有的结果集合进行一次性的处理操作。

 

与all_rows相对应的optimizer_mode参数是first_rows系列取值。该取值要求优化器以快速响应fast response作为执行计划的生成依据。执行SQL的时候，Oracle会争取将结果集合先返回给前端。

 

在实际使用中，单表操作情况下不同的optimizer_mode取值在执行计划上没有什么差异。

 

我们首先准备实验数据环境。

 

 

SQL> create table t_obj as select * from dba_objects;

Table created

 

SQL> create table t_tables as select * from dba_tables;

Table created

 

SQL> create index idx_t_obj_cmp on t_obj(owner,object_name);

Index created

 

SQL> create index idx_t_tables_cmp on t_tables(owner,table_name);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T_OBJ',cascade => true);

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,'T_TABLES',cascade => true);

PL/SQL procedure successfully completed

 

 

当进行单表操作的时候，first_rows与all_rows取值差异不是很大。

 

--设置optimizer_mode参数为all_rows

SQL> alter session set optimizer_mode='all_rows';

Session altered

 

SQL> select value from v$parameter where name='optimizer_mode';

VALUE

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

ALL_ROWS

 

SQL> explain plan for select * from t_obj;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

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

|   0 | SELECT STATEMENT  |       | 50387 |  4576K|   157   (3)| 00:00:02 |

|   1 |  TABLE ACCESS FULL| T_OBJ | 50387 |  4576K|   157   (3)| 00:00:02 |

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

8 rows selected

 

--设置参数取值为optimizer_mode为first_rows

SQL> alter session set optimizer_mode='first_rows';

Session altered

 

SQL> select value from v$parameter where name='optimizer_mode';

VALUE

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

FIRST_ROWS

 

SQL> explain plan for select * from t_obj;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

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

|   0 | SELECT STATEMENT  |       | 50387 |  4576K|   157   (3)| 00:00:02 |

|   1 |  TABLE ACCESS FULL| T_OBJ | 50387 |  4576K|   157   (3)| 00:00:02 |

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

 

8 rows selected

 

 

单表操作下，执行计划是没有什么差异的。但是，在进行表连接操作的情况下，执行计划有很大差异。

 

当参数为all_rows的时候，一般连接Oracle都尽量使用hash join连接方式，不使用index连接键。

 

 

SQL> alter session set optimizer_mode='all_rows';

Session altered

 

SQL> explain plan for select * from t_tables a, t_obj b where a.owner=b.owner and a.table_name=b.object_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2309375240

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

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

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

|   0 | SELECT STATEMENT   |          |  1606 |   467K|   171   (4)| 00:00:03 |

|*  1 |  HASH JOIN         |          |  1606 |   467K|   171   (4)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T_TABLES |  1606 |   321K|    12   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T_OBJ    | 50387 |  4576K|   157   (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - access("A"."OWNER"="B"."OWNER" AND

              "A"."TABLE_NAME"="B"."OBJECT_NAME")

 

16 rows selected

 

 

虽然在连接键上有索引，Oracle使用hash join连接方式。Hash Join方式往往会忽视索引路径。

 

如果我们将参数设置为first_rows，也就是尽量将先获取的结果返回。Oracle会更加倾向与嵌套循环Nested Loop和索引连接Index Join。

 

 

SQL> alter session set optimizer_mode='first_rows';

Session altered

 

SQL> select value from v$parameter where name='optimizer_mode';

VALUE

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

FIRST_ROWS

 

SQL> explain plan for select * from t_tables a, t_obj b where a.owner=b.owner and a.table_name=b.object_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2368859676

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

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

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

|   0 | SELECT STATEMENT            |               |  1606 |   467K|  3229   (1

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ         |     1 |    93 |     2   (0

|   2 |   NESTED LOOPS              |               |  1606 |   467K|  3229   (1

|   3 |    TABLE ACCESS FULL        | T_TABLES      |  1606 |   321K|    12   (0

|*  4 |    INDEX RANGE SCAN         | IDX_T_OBJ_CMP |     1 |       |     1   (0

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

Predicate Information (identified by operation id):

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

   4 - access("A"."OWNER"="B"."OWNER" AND "A"."TABLE_NAME"="B"."OBJECT_NAME")

 

16 rows selected

 

 

根据原始的设计，all_rows取值适合于批作业、计算、选择等常规业务场景。而first_rows主要适合在Web/Windows页面分页显示场景。

 

 

在我们使用SQL的时候，默认还是将这个参数设置为all_rows，这样适应性更好，执行计划获取更好。如果一些个别需要要求first_rows，可以使用hint进行特殊设置。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-720870/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-720870/

ciqu9915
关注
从零开始学自然语言处理-十万字长文带你深入学习自然语言处理全流程
wenyusuran的专栏
09-12 3080
前言 内容会涉及自然语言处理的各个方面知识内容和具体操作：包括但不仅限于词法分析，句法分析，语义分析，文本聚类，文本分类，情感分析，文本摘要生成，主题模型，词嵌入，文本语义相似度，自然语言推理，机器翻译，语言模型，信息抽取，关系预测，对话，指代消解等等。 针对中文的自然语言处理，我们需要先将段落和句子切分为词语，这是最基础的一步操作，分词属于词法分析的基础部分。 以下是我为大家准备的几个精品专栏，喜欢的小伙伴可自行订阅，你的支持就是我不断更新的动力哟！ MATLAB-30天带你从入门到精通 MAT
TiDB的设计哲学——Make It Work! Make It Right! Make It Fast!
作一个独立连续的思考者
02-13 1432
一、最初的三个基本信念 本次分享题目是《TiDB 的架构演进哲学》，既然讲哲学那肯定有故事和教训，否则哲学从哪儿来呢？但从另外的角度来说，一般大家来讲哲学就先得有信念。有一个内容特别扯的美剧叫做《美国众神》，里面核心的一条思路是“你相信什么你就是什么”。其实人类这么多年来，基本上也是朝这条线路在走的，人类对于未知的东西很难做一个很精确的推导，这时信念就变得非常重要了。 图 1 最初的基本信...
optimizer_相关参数
rgb_rgb的博客
10-18 1929
SQL> show parameter optimizer_ NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_
早餐｜第十九期 · Model Optimizer 参数介绍 （一）
OpenVINOCC的博客
09-28 733
早餐｜第十九期 · Model Optimizer 参数介绍 （一） 爱学习的 OpenVINO 中文社区 内容来源| 曹慧燕 内容排版| 晏杨 各位小伙伴们大家好， 今日的 OpenVINO 早餐已献上。 前面铺垫了一系列模型优化器的内容， 这次我们来看看如何正式使用模型优化器吧~ 作者介绍 ○ 曹慧燕 英特尔 IOTG Edge AI 工程师 正餐部分 视频放映 【持续更新】吃 OpenVINO 早餐，玩转深度学习
聊聊Oracle Optimizer相关的几个参数（下）
ciqu9915的博客
04-11 234
最后我们介绍几个可能影响到Optimizer的系统参数。 7、优化参数5——optimizer_index_caching 参数optimizer_index_caching的含义在于一个估算值，就是告诉优...
【optimizer详解】
m0_52087081的博客
03-06 428
深度学习optimizer详解
浅谈optimizer_mode优化器模式
ciqu9915的博客
08-16 284
Oracle query optimizer（查询优化器）是我们接触最多的一个数据库组件。查询优化器最主要的工作就是接受输入的SQL以及各种环境参数、配置参数，生成合适的SQL执行计划（Execution Plan）。 ...
2024大数据面试题汇总(完善中。。。)
最新发布
为人性僻 语不惊人
06-18 1397
自己汇总的面试题，涉及到大数据的常用组件，将持续更新... ... 部分图片不全，后期继续完善 更新记录: 2024-6-18 初版0.1.0 :hadoop，hbase，doris，hive，mysql，es 2024-6-26 1.0.0 : java，spark，redis，kafka，flink，kafka，数据仓库
玩转MySQL：如何正确应用索引
Java_LingFeng的博客
11-05 2291
至此，MySQL索引应用篇，也就是索引中篇就结束了，相信大家认真看完本篇之后，对于索引的掌握性、熟练程度绝对会更上一层楼，因为本章中从索引的优劣分析，到建立索引的原则、索引失效的情景、使用索引的正确姿势、MySQL对于索引的优化机制等各方面，对索引进行了进一步阐述。
MySQL
lyw4631的博客
04-14 1124
文章目录基础架构：一条SQL语句查询是如何执行的连接器查询缓存分析器优化器执行器小结一个问题日志系统：一条SQL更新语句是如何执行的事务隔离深入显出索引全局锁和表锁行锁功过事务到底是隔离的还是不隔离的？普通索引和唯一索引，应该怎么选？MySQL为什么有时候会选错索引？如何给字符串字段加索引MySQL会“抖”一下？表数据删掉一半，表文件大小不变？count(*)慢吗日志和索引相关问题“order by”如何工作？如何正确显示随机信息以下SQL语句逻辑相同，性能差异巨大只差一行，执行也慢？幻读是什么，有什么问题
pytorch基础（十五）-----------优化器optimizer
m0_55769743的博客
01-27 3408
pytorch的优化器的作用：管理并更新模型中可学习参数的值， 使得模型输出更接近真实标签（更新采用是梯度下降） 一、optimizer的基本参数 defaults：优化器超参数 state：参数的缓存，如momentum的缓存 param_groups：管理的参数组 _step_count：记录更新次数，学习率调整中使用（比如要在第n次迭代后降低学习率） 二、optimizer的基本使用方法 zero_grad()：清空所有参数的梯度，因为张量不会自动清零会自动累加，在梯度求导之前需要进行梯度清零 ste
Oracle db_file_mulitblock_read_count参数
haiross的专栏
07-17 1205
Oracle db_file_mulitblock_read_count参数      Oracle DB_FILE_MULTIBLOCK_READ_COUNT是Oracle比较重要的一个全局性参数，可以影响系统级别及sessioin级别。主要是用于设置最小化表扫描时Oracle一次按顺序能够读取的数据块数。通常情况下，我们看到top events中的等待事件db file sc
pytorch优化器（optimizer）中params参数详细介绍
m0_50501767的博客
04-07 4507
（1）如果我们只是想优化一个网络，那么我们就把一整个网络看做一个param_groups，params参数的赋值为model.parameters()。params就是网络中需要优化的网络参数，在这里需要注意的是传入的网络参数必须使可以迭代的对象。a.我们将多个网络的参数合并到一起，当成一个网络的参数来进行优化一般的赋值方式为。因为这里我们把整个网络看做一个param_groups，所以这里我们的执行结果为：1。代码的执行结果为：1。这样我们就可以把多个网络参数合并成一个网络参数进行优化。
关于db_file_multiblock_read_count的研究
cuihuang1302的博客
12-15 146
初始化参数db_file_multiblock_read_count 影响Oracle在执行全表扫描时一次读取的block的数量。 db_file_multiblock_read_count的设置要受OS最大IO能...
oracle的参数文件：pfile和spfile
BLACKHEART的博客
11-28 4191
oracle的参数文件：pfile和spfile
PyTorch 模型参数和optimizer
便纵有千种风情
04-11 4914
网络参数 ( parameters()和named_parameters()) model为网络， 打印层结构： ret=[*model.modules()] for layer in ret: #print(type(layer)) print(layer.__class__) 大概会输出这样的一个效果： <class ‘main.ResNet’> ...
Oracle 优化参数 optimizer_mode 介绍
one_dollar
12-05 4747
Oracle 在执行SQL语句时，有两种优化方法：即基于规则的RBO和基于代价的CBO。 在SQL执教的时候，到底采用何种优化方法，就由Oracle参数 optimizer_mode 来决定。SQL> show parameter optimizer_modeNAME                                 TYPE        VALUE------------
Tensorflow 中优化器optimizer参数 adam认识
h363924219的博客
05-20 9737
一、先理解优点 Adam优化器 2014年12月，Kingma和Lei Ba两位学者提出了Adam优化器，结合AdaGrad和RMSProp两种优化算法的优点。对梯度的一阶矩估计（First Moment Estimation，即梯度的均值）和二阶矩估计（SecondMoment Estimation，即梯度的未中心化的方差）进行综合考虑，计算出更新步长。 主要包含以下几个显著的优点： 实现简单，计算高效，对内存需求少 参数的更新不受梯度的伸缩变换影响 超参数具有很好的解释性，且通常无需调整或仅需很少的微
Oracle优化器的optimizer_mode参数
weixin_34064653的博客
09-29 723
optimizer_mode参数   optimizer_mode是oracle 11g的一个优化器参数，在某些时候可以影响优化器的行为，是个不可忽视的细节参数。 SQL> show parameter optimizer;optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_dy...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值