Oracle优化器的optimizer_mode参数-阿里云开发者社区

开发者社区> superdba> 正文

Oracle优化器的optimizer_mode参数

简介: optimizer_mode参数   optimizer_mode是oracle 11g的一个优化器参数,在某些时候可以影响优化器的行为,是个不可忽视的细节参数。 SQL> show parameter optimizer; optimizer_capture_sql_plan_baselines.
+关注继续查看

optimizer_mode参数

  optimizer_mode是oracle 11g的一个优化器参数,在某些时候可以影响优化器的行为,是个不可忽视的细节参数。



SQL> show parameter optimizer;
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

此参数的简单介绍如下,
optimizer

  oracle优化器在解析sql前先查看这个参数的值。参数optimizer_mode 决定取出多少行后开始向用户返回第一批数据,当值为all_rows时,获取完所有的数据后再开始向用户返回数据。
  这么看来,貌似感觉和执行计划没多大关系,但是oracle优化器在选择执行计划的时候却是会考虑这个参数的,而这里主要体现在btree索引的特性。

Let's make a test

  我们来做一个测试。执行一个sql语句,从t1表中取出object_id列为1-3000的数据,结果以object_id列的值排序。测试情况如下。

SQL> show parameter optimizer_mode;
optimizer_mode string ALL_ROWS
SQL> select * from t1 where object_id between 1 and 3000 order by object_id;
2998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2148421099
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2959 | 283K| | 404 (1)| 00:00:05 |
| 1 | SORT ORDER BY | | 2959 | 283K| 400K| 404 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 2959 | 283K| | 336 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"<=3000 AND "OBJECT_ID">=1)
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1235 consistent gets
          0 physical reads
          0 redo size
     120248 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
       2998 rows processed

  我们可以看到,当前optimizer_mode的值为all_rows,优化器选择了全表扫描TABLE ACCESS FULL。那么我们一般的认识是,数据量总量较小或者查询量的总量占比较大的情况下,优化器认为全表扫描的性能较索引检索性能更优。

接下来修改当前会话的optimizer_mode的值为first_rows_10

SQL> alter session set optimizer_mode='first_rows_10';
Session altered.

然后再次测试刚才的SQL语句

SQL> select * from t1 where object_id between 1 and 3000 order by object_id;
2998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1057374866
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1176 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 12 | 1176 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_1 | 2959 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=3000)
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
         53 consistent gets
          7 physical reads
          0 redo size
     276790 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2998 rows processed

呃,,我们发现,调整完这个参数后,优化器立马就选择了索引。

那么,怎么去解释呢?
  这里就需要考虑到btree索引的特性了,因为索引的有序性,也就是获取到的数据是根据索引列排好序的。
  因此,当进行全表扫描时,由于列值杂乱,所以必须等到所有的数据全部获取完毕后才能进行排序,再开始返回第一批数据;而通过索引返回的数据都是有序的,取到第十行时就可以直接返回给用户。
  由于索引的这个小特性,加上optimizer_mode参数对数据操作的影响,间接地影响了优化器的选择行为。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【RAC】asm_diskgroups 参数
初始化参数asm_diskgroups指定了在ASM实例启动时,或执行alter diskgroup all mount语句时,被挂载的磁盘组的名字,即当ASM实例启动的时候,会挂载这个参数指定的所有磁盘组,或当执行alter diskgroup all mount/dismount语句时,也会挂载这个参数指定的所有磁盘组。
603 0
Oracle优化器的optimizer_mode参数
optimizer_mode参数   optimizer_mode是oracle 11g的一个优化器参数,在某些时候可以影响优化器的行为,是个不可忽视的细节参数。 SQL> show parameter optimizer; optimizer_capture_sql_plan_baselines.
2788 0
利用OptionParser在命令中加入参数
手动导入一天的数据 # -*- coding:utf-8 -*- #__author__ = 'ecaoyng' # date : 20170627 from optparse import...
833 0
oracle 安装要用到的内存相关参数详解
在这里,对oracle在/etc/sysctl 要用到的参数值做个简要的解释和说明。 (1)kernel.shmmax:该参数定义了共享内存段的最大尺寸(以字节为单位)。
664 0
+关注
superdba
Super, not a DBA, not a programmer. May be a singer, poet, ITer, and so on.
18
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载