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

最新推荐文章于 2024-05-29 13:15:59 发布
最新推荐文章于 2024-05-29 13:15:59 发布
阅读量179 收藏
点赞数
文章标签： 数据库

 

最后我们介绍几个可能影响到Optimizer的系统参数。

 

7、优化参数5——optimizer_index_caching

 

参数optimizer_index_caching的含义在于一个估算值，就是告诉优化器当进行索引路径检索的时候，特别是nested loop连接时索引探知（index probe）的成本。该参数的取值范围是0-100，描述使用索引的时候，索引在buffer cache中的比例。这个参数的选取会影响到nested loop和in-list循环的成本估算值。

 

默认情况下，Oracle这个参数取值为0。

 

 

SQL> show parameter optimizer_index_cach

 

NAME                                 TYPE        VALUE

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

optimizer_index_caching              integer     0

 

 

如果这个取值设置为100，就表示在使用索引路径的时候，所检索的索引块在buffer cache中都已经缓存住。注意，这个参数只是一个估计值，是我们进行控制成本公式使用的。真正在运行的时候，索引块在buffer cache中的比例是不能预知的！！

 

显而易见，在控制这个参数的前提下，Oracle在计算索引路径执行计划的时候，成本值就自然有所降低。进而，最后CBO选择出的执行计划就更加倾向于索引路径和Nested Loop连接方式。

 

我们通过下面简单的实验去验证。我们首先看看非连接条件下的索引路径成本计算，选取上面参数实验t_obj和t_tables作为实验环境。

 

 

--补充索引构建

SQL> create index idx_t_obj_owner on t_obj(owner);

Index created

 

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

PL/SQL procedure successfully completed

 

 

分别在optimizer_index_caching两个极端情况下进行试验。

 

 

--在取值为0的时候进行试验；

SQL> alter session set optimizer_index_caching=0;

Session altered

 

SQL> explain plan for select * from t_obj where wner='SCOTT';

Explained

 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

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

|   0 | SELECT STATEMENT            |                 |    25 |  2325 |     2

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ           |    25 |  2325 |     2

|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJ_OWNER |    25 |       |     1

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

14 rows selected

 

--修改参数

SQL> alter session set optimizer_index_caching=100;

Session altered

 

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

VALUE

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

100

 

 

SQL> explain plan for select * from t_obj where wner='SCOTT';

Explained

 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

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

|   0 | SELECT STATEMENT            |                 |    25 |  2325 |     2

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ           |    25 |  2325 |     2

|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJ_OWNER |    25 |       |     1

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

14 rows selected

 

 

虽然都是进行索引路径，但是对最后成本值影响不大。究其原因，可能是笔者选择的选择率过小的原因。

 

下面实验nested loop路径成本情况。

 

 

SQL> alter session set optimizer_index_caching=0;

Session altered

 

SQL> explain plan for select /*+ use_nl(a, b)*/* from t_obj a, t_tables b where a.owner=b.owner and a.object_name=b.table_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"."OBJECT_NAME"="B"."TABLE_NAME")

 

16 rows selected

 

 

当进行强制的nested loop路径选择之后，我们在index_caching参数为0的时候，成本为3229，主要消耗在进行nested loop连接路径上。那么，当我们修改为100时，执行计划如何变化呢？

 

 

SQL> alter session set optimizer_index_caching=100;

Session altered

 

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

Explained

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 709597304

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

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

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

|   0 | SELECT STATEMENT            |                  |  1606 |   467K|   789

|   1 |  TABLE ACCESS BY INDEX ROWID| T_TABLES         |     1 |   205 |     1

|   2 |   NESTED LOOPS              |                  |  1606 |   467K|   789

|   3 |    TABLE ACCESS FULL        | T_OBJ            | 50387 |  4576K|   157

|*  4 |    INDEX RANGE SCAN         | IDX_T_TABLES_CMP |     1 |       |     0

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

Predicate Information (identified by operation id):

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

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

 

16 rows selected

 

 

效果非常明显，成本值只有789。说明该参数在成本路径计算中的作用和分量。但是，再次强调：参数只是控制和影响CBO进行决策的手段，它们只能影响到最后执行计划的决策选择，真正的执行时间还要看实际的环境和数据情况而定。

 

 

8、优化参数6——optimizer_index_cost_adj

 

该参数也是用来调整索引路径成本的。简单的说，optimizer_index_cost_adj参数就是索引路径偏好的“缩放灯”。参数取值范围为0-10000，默认为100。

 

 

SQL> show parameter optimizer_index_cost

 

NAME                                 TYPE        VALUE

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

optimizer_index_cost_adj             integer     100

 

 

当Oracle依据统计量（数据表、索引对象）计算出index access路径成本之后，会根据这个参数进行一定比例的放大和缩小。例如，如果该参数设置为10，表示会对索引路径执行计划的成本进行10%的缩小。

 

应用该参数，我们可以控制索引路径的偏好度，放大或者缩小CBO成本计算公式中索引路径成本的计算值。进而控制实际选择出的执行计划。

 

下面我们根据实验来体会效果，我们同样适用两个SQL语句在三个参数取值下的情况进行实验。

 

首先，我们选择默认的optimizer_index_cost_adj取值下的情况。

 

 

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

VALUE

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

100

 

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

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  |       | 22351 |  2029K|   156   (2)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T_OBJ | 22351 |  2029K|   156   (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

13 rows selected

 

SQL> explain plan for select * from t_obj where wner='SCOTT';

Explained

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

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

|   0 | SELECT STATEMENT            |                 |    25 |  2325 |     2

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ           |    25 |  2325 |     2

|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJ_OWNER |    25 |       |     1

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

 

14 rows selected

 

 

下面，我们缩小参数值到10，也就是缩小索引路径成本到原来的10%。

 

 

SQL> alter session set optimizer_index_cost_adj=10;

Session altered

 

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

Explained

 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

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

|   0 | SELECT STATEMENT            |                 | 22351 |  2029K|    65

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ           | 22351 |  2029K|    65

|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJ_OWNER | 22351 |       |     5

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SYS')

 

14 rows selected

 

SQL> explain plan for select * from t_obj where wner='SCOTT';

Explained

 

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

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

|   0 | SELECT STATEMENT            |                 |    25 |  2325 |     1

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ           |    25 |  2325 |     1

|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJ_OWNER |    25 |       |     1

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

 

14 rows selected

 

 

我们看到了实验现象：对owner=’SYS’而言，当不进行索引路径成本缩放的时候索引路径的成本是要高于全表扫描FTS的，所以CBO选择了FTS作为路径计算。但是，当我们将参数设置为10，也就是进行10%比例的缩放时，索引路径的成本就下降到了65，小于FTS的156成本值。结果CBO在这种情况下选择了索引路径。

 

当owner=’SCOTT’语句中，成本也有一定程度的降低。因为取值本身就很小了，所以减小效果不明显。

 

下面我们将参数调节到另一个极端，参数放大到10000。

 

 

SQL> alter session set optimizer_index_cost_adj=10000;

Session altered

 

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

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  |       | 22351 |  2029K|   156   (2)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T_OBJ | 22351 |  2029K|   156   (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

13 rows selected

 

--强制的索引路径；

SQL>  explain plan for select /*+ index(t_obj IDX_T_OBJ_OWNER)*/* from t_obj where wner='SYS';

 

Explained

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

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

|   0 | SELECT STATEMENT            |                 | 22351 |  2029K| 64906

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ           | 22351 |  2029K| 64906

|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJ_OWNER | 22351 |       |  5283

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SYS')

 

14 rows selected

 

 

上面的实验中，我们将索引路径的成本放大了100倍。原有的owner=’SYS’语句重新选择了FTS路径。如果我们强迫它去走索引路径，结果成本计算出的64906，恰恰是之前650成本值的近100倍。

 

那么，owner=’SCOTT’结果情况如何呢？

 

 

SQL> explain plan for select * from t_obj where wner='SCOTT';

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  |       |    25 |  2325 |   156   (2)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T_OBJ |    25 |  2325 |   156   (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

 

13 rows selected

 

 

owner=’SCOTT’选择率极低，但是在放大100倍的效应下，CBO也放弃了索引路径，选择了FTS作为最终执行计划。

 

9、结论

 

Oracle Optimizer本质上，就是一系列计算公式比较。我们提供数据对象的统计量，外加适当的参数作为引导，最后可以帮助CBO去生成更好的执行计划。

 

 

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

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

ciqu9915
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
optimizer_相关参数
rgb_rgb的博客
10-18 1873
SQL> show parameter optimizer_ NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_
oracle 中的参数 optimizer_mod的各个参数的意义
weixin_33920401的博客
12-13 108
1. 我们可以先通过语句show parameter optimizer_mode --看ORACLE处于何种模式，Oracle 7以来缺省的设置应是"choose",即如果对已分析的表查询的话选择CBO,是否选择RBO.如果该参数设置为"rule",则不论表是否分析过,一概选用RBO,除非在语句中用hint强制. 2.analyze table xxx compute ...
聊聊Oracle Optimizer相关的几个参数（中）
ciqu9915的博客
04-10 145
我们继续来介绍可以影响Oracle Optimizer工作的若干参数。 4、优化器参数2——cursor_sharing Cursor_sharing是Oracle优化器的另一个重要参数，可能也是我们日常讨...
pytorch优化器（optimizer）中params参数详细介绍
m0_50501767的博客
04-07 3635
（1）如果我们只是想优化一个网络，那么我们就把一整个网络看做一个param_groups，params参数的赋值为model.parameters()。params就是网络中需要优化的网络参数，在这里需要注意的是传入的网络参数必须使可以迭代的对象。a.我们将多个网络的参数合并到一起，当成一个网络的参数来进行优化一般的赋值方式为。因为这里我们把整个网络看做一个param_groups，所以这里我们的执行结果为：1。代码的执行结果为：1。这样我们就可以把多个网络参数合并成一个网络参数进行优化。
pytorch基础（十五）-----------优化器optimizer
m0_55769743的博客
01-27 3321
pytorch的优化器的作用：管理并更新模型中可学习参数的值， 使得模型输出更接近真实标签（更新采用是梯度下降） 一、optimizer的基本参数 defaults：优化器超参数 state：参数的缓存，如momentum的缓存 param_groups：管理的参数组 _step_count：记录更新次数，学习率调整中使用（比如要在第n次迭代后降低学习率） 二、optimizer的基本使用方法 zero_grad()：清空所有参数的梯度，因为张量不会自动清零会自动累加，在梯度求导之前需要进行梯度清零 ste
验证oracle服务安装成功,聊聊Oracle的OS验证登录
weixin_30346025的博客
04-05 339
用户名、密码是我们最常见的安全验证策略方法，也是我们在各种应用系统软件中采用的最多的安全保护策略。对Oracle而言，以最大限度保护系统数据安全，是不可推卸的天然职责。从最早版本到今天，Oracle在用户名和密码安全策略方面不断的加以改进，比如，进入11g之后，Default Profile要求定期进行用户密码修改操作，用户密码加密算法强化，以及对dba_users等数据字典视图进行优化改进。今天...
Oracle 浅谈optimizer_mode优化器模式
雨花石
10-10 1551
Oracle query optimizer（查询优化器）是我们接触最多的一个数据库组件。查询优化器最主要的工作就是接受输入的SQL以及各种环境参数、配置参数，生成合适的SQL执行计划（Execution Plan）。   Query Optimizer一共经历了两个历史阶段：RBO和CBO。RBO时代，Oracle执行计划是通过一系列固化的规则进行执行计划生成。而CBO时代，则是利用系统
聊聊Oracle 11g中的char类型使用
卉鱼程序人生
04-18 653
在Oracle数据表和程序类型中，字符类型可能是使用最多的一种了。从历史上看，Oracle首先推出了固定长度char类型字段，之后才推出了变长度类型varchar2。目前，主流Oracle应用开发都已经普遍接受了varchar2作为数据表字段类型表示。如果存在超长字符，都考虑使用CLOB这类大对象进行存储。但是，我们在实际领域中，还是会在各种遗留系统中发现很多char类型字段。而且连带这些遗留系统的
Oracle optimizer性能优化手册 chm
05-19
数据库开发,Oracle教程　Oracle optimizer性能优化手册 chm，主要是一些Oracle数据库在性能优化方面的技术文章，编译成CHM格式，方便大家阅读。
Linux下ZendOptimizer的安装与配置方法
10-30
Linux下ZendOptimizer的安装与配置方法
Understanding Optimizer Statistics (oracle 性能优化白皮书英文版)
10-31
ORACLE 统计信息（Statistics）的查询，收集，还原，锁定，导入，导出
SQL Optimizer for Oracle - Basic.ppt
10-27
SQL相关资料
Centos环境下安装Zend optimizer 3.3.9的方法
09-15
主要介绍了Centos环境下安装Zend optimizer 3.3.9的方法,简单说明了CentOS服务器环境下安装Zend optimizer 3.3.9的相关下载、解压、安装、配置等操作步骤与相关注意事项,需要的朋友可以参考下
[解决]windows mysql8.0.x误删除root，解决办法
最新发布
weixin_46626971的博客
05-29 97
b. 确保mysql安装路径下的data文件夹下的ibdata1文件有写入权限。，这在mysql8.0之后的版本已经不在试用了，所以改为输入。：a. 很多解决办法是输入。
电脑同时配置两个版本mysql数据库常见问题
xiao_kelai的博客
05-21 508
在每一个命令中，mysql要选择是那个，如果都配置在环境变量中，要修改名字，不然可能会出错，在搞mysql57版本的时候就用mysql57.exe 和 mysqld57.exe ,注意每条命令，不要在创建的时候用混了，到时候又得卸载重来。例如 mysql -u root -p 改为 mysql57 -u root -p 这个时在57版本中。win登录的话，复制密码后，输入mysql57 -P3305 -u root -p 之后可以点击右键粘贴密码。
Linux文件操作——实现cp指令以及模拟修改配置文件
shion1114的博客
05-23 348
cp 1.c 2.c功能是将(内容相同，实现拷贝)main函数的参数由编译结果可知，总共有三个输入参数，同时输出每个参数字符串内容。
Mongodb分布式id
littleschemer的博客
05-25 679
分布式ID是指在分布式系统中用于唯一标识每个元素的数字或字符串。本文主要讲解mongodb分布式id的解决方案，并从实际测试代码验证产生的id在不同集合是否也是保证全局唯一。
数据访问与Spring Data JPA
无远弗届
05-25 590
假设我们要处理一个用户表（users），可以创建一个User@Entity // 标识这是一个实体类 public class User {@Id // 标识这是主键字段 @GeneratedValue(strategy = GenerationType . AUTO) // 主键生成策略 private Long id;// Getter 和 Setter ... }@Entity // 标识这是一个实体类 public class User {
optimizer的可选参数有哪些
05-31
以下是Keras中可选的optimizer及其参数： 1. SGD（随机梯度下降）：lr（学习率）、momentum（动量参数）、decay（学习率衰减参数）、nesterov（是否使用Nesterov Momentum）。 2. RMSprop（均方根传播）：lr（学习...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值