【SQL 性能优化】参数设置-阿里云开发者社区

开发者社区> yangyi_dba> 正文

【SQL 性能优化】参数设置

简介: QL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.
+关注继续查看

QL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> show parameter optimizer_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      CHOOSE
optimizer_secure_view_merging        boolean     TRUE
SQL>
---optimizer_features_enable
如果在升级数据库后还想保持优化器在旧版本上的原有行为,可以设置optimizer_features_enable为旧版本号,不过建议尽快将应用程序调整为适合新版本数据库。可以看看这个参数的取值:
SQL> alter session set optimizer_features_enable ='ddd';
ERROR:
ORA-00096: invalid value ddd for parameter optimizer_features_enable, must be from among
10.2.0.4.1, 10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1, 10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0,
9.2.0.8, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6,
8.0.5, 8.0.4, 8.0.3, 8.0.0
optimizer_features_enable 是动态的,可以在系统级和语句级修改。也可以在语句级使用提示修改

optimizer_features_enable(enable)
optimizer_features_enable('10.2.0.5')
注意:optimizer_features_enable 不仅能禁用新版本的特性也能禁用bug 修复。

--optimizer_mode
从这里的提示可以看出优化模式的选择值。
SQL> alter session set optimizer_mode ='dd';
ERROR:
ORA-00096: invalid value dd for parameter optimizer_mode, must be from among
first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows,choose, rule
每个值的含义如下:
Rule:基于规则的方式,忽略CBO和统计数据并且完全基于基本数据字典信息生成执行计划。
Choose:允许ORACLE选择最合适的优化器目标,默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,那么ORACLE将使用RULE模式
First Rows:基于成本的优化器模式,当一个表有统计信息时,它将以最快的速度返回查询的最先几行,从总体上减少了响应时间,但是会造成总体查询速度的下降或者是消耗更多的资源,通常会选择索引扫描而不是全表扫描,所以这种模式最适用于在线系统,因为在这样的系统中终端用户希望以最快的速度看到一些结果
All Rows:基于成本的优化器模式,当一个表有统计信息时,它将确保总体查询时间最短,但是它可能在收到第一条记录的操作上花费更长的时间。这种模式通常选择全表扫描,所以这种模式最适用于批量查询,没有统计信息则走RULE模式。
一般来讲,
1 需要获取所有记录更重要,应该将参数设置为all_rows。比如报表系统和 OLAP,数据仓库系统和缓存数据的中间层组件中。
2 如果前几行更重要或者对响应时间的要求非常高的系统,使用first_rows_n这里的n可取(1,10,100,1000)。
  optimizer_mode 是动态的,可以在系统级和语句级设置。也可以使用提示(all_rows,first_rows(n))在语句级更改。

---optimizer_dynamic_sampling 动态采样
优化器可以在语句解析阶段进行信息统计并动态收集。注意:动态信息统计信息既不存储在数据字典也不在其他地方,真正重用它们的是共享游标本身。该参数指定了动态采样的的方式和时间。
1 如果optimizer_features_enable设置为10.0.0或者以上,默认为2,
2 9.2.0则为1,
3 9.0.1或者以下则为0,即禁用了动态采样。
可以在系统级别和会话级别来修改这个参数。也可以通过提示dynamic_sampling()在语句级应用动态采样。
1 设置为所有表设置采样层级:dynamic_sampling(N)
2 为某个表指定采样层级:dynamic_sampling(TNAME N)
层级  含义                                                                     数据块的数量
0     禁止动态采样                                                                0
1     对于没有对象统计信息的表在下面三种情况下使用动态采样。                      32
      1 表没有索引。2 是连接的一部分(包括子查询和不可合并视图)。3块数多于采样数。  
 
2      对于没有对象统计信息的表使用动态采样。                                     64

3     对符合层级2标准的表已及已经使用了评估条件选择性猜测的表使用动态采样                             32

4     对符合层级3标准的表以及where子句中引用了两个或更多字段的表使用动态采样。                  32
 
5     同等级4                                                                     64
6     同等级4                                                                     128
7     同等级4                                                                     256
8     同等级4                                                                     1024
9     同等级4                                                                     4096
10    同等级4                                                                      所有               


optimizer_index_caching
这个参数影响嵌套循环连接的探测索引的代价,0-100表示在使用嵌套循环或这in-list迭代时将索引缓存在buffer cache的百分比。例如,设置为100,则优化器认为100%能在内存中找到索引数据,会按照这个设定来计算cost和选择执行计划。

optimizer_index_cost_adj

和optimizer_index_caching一样,这个参数也是cbo用来计算cost的,这个参数可以用来调整使用索引的代价,默认值是100,范围是1-10000,它表示索引扫描和全表扫描的比值。例如设置为10,意味着使用通过索引路径访问是正常通过索引路径访问的10%(oracle 10g performace tuning guide),也即可以设置索引参与计算代价的不同值。
optimizer_secure_view_merging
这个参数控制视图合并,默认值是true,在不影响安全问题的情况下允许视图合并,如果设置为false,则在任何情况下允许视图合并。

 

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

相关文章
SQL优化二(SQL性能调优)
一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知识太大了,几乎可以用一本书来介绍。另外,博主对SQL优化也是刚刚接触,也有很多不了解的地方,说的不对的地方,还请大家指正,共勉!   二、oracle服务器,所谓oracle服务器指的是一个数据库管理系统,它包括一个oracle实例(动态)和一个oracle数据库(静态)。
1202 0
新网域名,万网域名的邮箱mx设置
域名的设置下面我们以万网 (http://www.net.cn/) 和 新网 (http://www.xinnet.com/) 为例来讲述设置的全过程,其他的域名服务商的所提供域名设置与此类似,学习到域名和 DNS 的相关知识。
1276 0
2.4 PyCharm设置参数
在运行脚本或者代码的时候,可能会需要传入一个或多个参数,直接在命令后面添加较麻烦,那么怎样在 PyCharm 里面预先设置好参数呢? 首先我们打开一个 python 文件,使用 "ctrl+/" 注释原有的内容,添加新的需要传参代码,如下: 然后我们会发现 sys 是有...
1509 0
MySQL SSD 参数优化
SSD硬盘:慢查询日志可以设置0.5秒,如果超过0.5秒。0.5秒在SSD上最少走了50个IO,就有可能没有用到索引。0.5秒还是有点问题:如果从8000W中找一条记录,如果加上order 等计算耗时,比较小。
3400 0
PostgreSQL 垃圾回收参数优化之 - maintenance_work_mem , autovacuum_work_mem
PostgreSQL 垃圾回收参数优化之 - maintenance_work_mem , autovacuum_work_mem
1853 0
+关注
yangyi_dba
数据库相关技术专家
972
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载