用智能优化限制来提高Oracle性能

原创 2007年10月15日 16:39:00
Oracle SQL运行时间的最主要的组成部分是花在为执行准备新的SQL语句上的时间。不过,如果了解了可执行计划产生的内在机制,你就可以控制Oracle花费在评估表的连接顺序的时间,并在总体上提高查询的性能。
  
  准备为执行提供的SQL语句
  
  
  在一个SQL语句进入Oracle库的cache之后、而真正被执行之前,将会依次发生如下事件:
  
  语法检查——检查该SQL语句的拼写和词序是否正确。
  语义解析——Oracle根据数据词典(data dictionary)来验证所有的表格(table)和列(column)。
  已保存纲要检查——Oracle检查词典以确认对应该SQL语句是否已存在已保存的纲要(Stored Outline)。
  产生执行计划——Oracle根据一种罚值(cost-based)优化算法和数据词典中的统计数据来决定如何生成最优执行计划。
  产生二进制代码——Oracle在执行计划的基础上生成可执行的二进制代码。
  一旦开始准备执行SQL语句,上述的过程很快就会执行,这是因为Oracle可以识别出同样的SQL语句并对同样的SQL语句重复使用对应的可执行代码。然而,对产生ad hoc SQL的系统以及SQL中嵌入文本值(literal value)的情况,SQL执行计划的生成时间就会变得相当长,而且以前的执行计划也常常不能被再次利用。对那些牵涉到许多表格的查询,Oracle可能要花上很长的时间来决定把连接这些表格的顺序。
  
  评估连接表格的顺序
  生成可执行计划的时间往往是SQL的准备过程中最大的开销组成部分,尤其是在处理有多个表的连接的查询的情况下。当Oracle评估表的连接顺序时,它必须考虑每一种可能的排序。例如,当有六个表格需要连接时,Oracle需要考虑720种(6的排列数,即6×5×4×3×2×1=720)可能的连接排序。当需要连接的表的数量超过10时,这个排列问题将变得非常突出:如果需要连接的表格有15个,那么需要考虑的可能的查询排列顺序超过一万亿种(精确值为1,307,674,368,000)。
  
  在optimizer_search_limit参数中设置限制
  你可以通过optimizer_search_limit参数来控制上述问题的发生,该参数用来指定优化器评估的表格连接顺序的最大数目。利用这个参数,就可以防止优化器在评估所有可能的表格连接顺序中所花费的多余时间。如果查询中的表的数量少于或者等于optimizer_search_limit,那么优化器检查所有的可能表的连接方式。
  
  例如,涉及了五个表的查询一共有120种(5!=5×4×3×2×1=120)可能的连接顺序,所以如果参数optimizer_search_limit的值设置为5(默认值),那么优化器就会考虑所有的这120种可能的连接顺序。optimizer_search_limit参数还用来控制启动开始连接指示(star join hint)的阈值。当查询所涉及的表格数量少于参数optimizer_search_limit的设定值,开始连接指示将被设置。
  另一个工具:optimizer_max_permutations参数
  optimizer_max_permutations初始参数用来设定优化器优化范围的上界(即最多考虑多少种表格连接顺序),它依赖于初始参数optimizer_search_limit。参数optimizer_max_permutations的默认值为8000。
  
  
  
  参数optimizer_search_limit 和optimizer_max_permutations一同用来设置优化器所考虑的排列数的上限。优化器不断的产生可能的表的连接的排列,直到排列数达到参数optimizer_search_limit或者optimizer_max_permutations为止。一旦优化器停止产生新的可能连接排列,它将会从中选择出耗费最小的排列。
  
  用已排序指示来指定一种连接排序
  你可以设定优化器评估的排列数的上限。但是对复杂的情况下,即使允许的排列数很大,优化器也很可能在远远没有找到一个比较合适的排列之间就已经停止优化了。你不妨回头看看我前面举的那个例子(15个需要连接的表有超过一万亿种排列)。如果设定优化器考虑80,000种排列,那么这仅仅考虑了所有可能性的0.000006%,优化器极可能没有达到最佳的排列。
  
  在Oracle SQL中解决这个问题的最好方法就是手工指定一种表格连接顺序。这里需要遵循的大原则就是表格连接顺序应该使得查询计划尽快得以建立,通常在SQL语句中使用WHERE限制子句。
  
  下面以一个对名为emp的表格的并行查询为例,例子中的代码强制查询计划执行一个嵌套循环连接(nested loop join)。注意,我使用了已排序指示来引导优化器来评估WHERE子句中给出的表格的连接顺序。
  
  select /*+ ordered use_nl(bonus) parallel(e, 4) */
  e.ename,
  hiredate,
  b.comm.
  from
  emp e,
  bonus b
  where
  e.ename = b.ename
  ;
  
  上面的例子要求优化器按照SQL语句中FROM子句指定的顺序连接表格,FROM子句中第一个的表格指定为驱动表格(driving table)。已排序指示常常与其它指示联合使用以确保多个表格按照适当的顺序连接起来。在遇到涉及四个以上表格的数据仓库查询时常常也是这样处理。
  
  下面另给出一个例子,在这个例子中,我们使用一个已排序指示(ordered hint)来把表格按照一个特定的顺序(先是emp,然后是dep和sal,最后是bonus)连接起来。进一步改进执行计划,我指定emp表格到dept表格的连接使用hash连接,sal表格到bonus表格使用嵌套循环连接。
  
  select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
  from
  emp,
  dept,
  sal,
  bonus
  where . . .
  
  对实际应用的建议
  在实际应用场合下,减小optimizer_max_permutations参数并使用已保存的优化计划或者已保存纲要(这样在查询涉及到许多表格时,就可以避免重新解析查询所花费的实际)会更有效率。一旦找到最好的表格连接顺序,你可以手工指定表格的连接顺序(通过已排序指示)并保存纲要,这样就永久保存该表格连接顺序。
  
  
  
  当执行一个新的查询时,你可以首先把optimizer_search_limit设置为该查询所涉及的表格数,这样优化器将从所有的连接顺序中找出最佳的那种。以后执行该查询时,你就可以在WHERE子句中按照最佳连接顺序排列表格名称,并设置已保存指示和已保存纲要,这样就可以按照最佳顺序连接表格而无需重复评估各种可能排序。这样查询的速度将会得到显著的提高。
  
  已排序指示的优先级高于optimizer_search_limit和 optimizer_max_permutations参数。如果设置了已排序指示,那么表格就会按照查询命令中的FROM子句给出的顺序连接,这样这个过程就没有优化器优化表格的连接顺序这一步骤了。
  
  作为Oracle的专业人士,你应该知道SQL语句进入库cache中有一个明显的起始延时。但是聪明的Oracle数据库管理员以及Oracle开发者能改变表格的搜索限制参数或者利用已排序指示来手工指定表格的连接顺序,这样可以极大的降低优化以及执行新查询所花费的时间。
 

几个简单的步骤大幅提高Oracle性能--我优化数据库的三板斧

                                   几个简单的步骤大幅提高Oracle性能--我优化数据库的三板斧  数据库优化的讨论可以说是一个永恒的主题。资深的Oracle优化人...
  • fishinhouse
  • fishinhouse
  • 2006年09月14日 22:08
  • 1452

基于Oracle PLSQL的存储过程性能优化方法案例

【前言】:最近忙着一个项目,Oracle有个JOB基于定时任务处理,经过我多次冥思苦索优化后,该存储过程最终依然需要花费1小时6分。这个JOB处理是为了生成年度、季度、月度三张统计分析报表以供用户查询...
  • shichen2010
  • shichen2010
  • 2015年05月15日 19:25
  • 3829

Oracle update性能优化

当执行批量刷新数据时,以前我写过最好是写成merge into,当然还有一种方式,如下: SQL> create table test1 as select * from dba_objects wh...
  • guogang83
  • guogang83
  • 2017年01月12日 15:37
  • 1631

Oracle使用物化视图提高group by性能

情况介绍 现有表OE.PRODUCT_INFORMATION数据量为800万,求出各个供应商供应产品的数量 无使用物化视图,只在建立SUPPLIER_ID建立索引,执行时间为15s左右SELECT ...
  • u012557814
  • u012557814
  • 2017年01月04日 18:44
  • 1148

Oracle 树查询 性能优化纪实(start with, connect by)

在项目中做权限控制时,需要用组织阶层来控制能够访问的数据, 比如A组织的人可以看到其下属组织的人员数据,或者只有A组织是B组织上级的时候才有看B组织人员数据的权利。 根据需求需要构筑DB的表结构,...
  • congqing2011
  • congqing2011
  • 2011年04月22日 11:52
  • 893

oracle like模糊查询的优化测试

三种like模糊查询的优化: 第一种:like 'a%'这种,直接可走索引 第二种:like '%a'这种,需要用reverse反转函数改写SQL,再建立函数索引,如下:   改写前:    ...
  • zengxuewen2045
  • zengxuewen2045
  • 2016年12月13日 13:26
  • 2838

通向架构师的道路(第二十四天)之Oracle性能调优

前言 这次,我们将在Linux下来动手完成Oracle数据库的安装与使用。 oracle本身是可以免费下载的包括 它的企业版以及被它收购的Weblogic和Sun中的几乎任何东西你都可以拿来下载和...
  • xiangxizhishi
  • xiangxizhishi
  • 2017年07月02日 08:52
  • 659

数据库性能优化3——Oracle SEQUENCE的概念、作用以及创建(使得并发插入主键唯一)

1. Oracle Sequence的概念 Oracle 使用序列 来生成唯一编号,而不是使用 SQL Server 所用的数据类型 uniqueidentifier。无论是哪种情况,主要用途都是为...
  • zhanglu0223
  • zhanglu0223
  • 2013年04月01日 17:41
  • 6260

oracle insert优化

author:skate time:2010/10/19 insert优化 要想提高insert的速度,首先要知道什么影响insert慢,在执行insert的过程中产生redo和undo...
  • lxzo123
  • lxzo123
  • 2011年09月22日 23:12
  • 5461

CSS 优化、提高性能的方法有哪些?

CSS优化主要是4个方面: 加载性能 主要是从减少文件体积,减少阻塞加载,提高并发方面入手 选择器性能 渲染性能 可维护性 较为具体的优化方案: 慎重使用高性属性:浮动、定位; 去除空规则; ...
  • sjinsa
  • sjinsa
  • 2017年05月01日 22:20
  • 1202
收藏助手
不良信息举报
您举报文章:用智能优化限制来提高Oracle性能
举报原因:
原因补充:

(最多只允许输入30个字)