- 浏览: 241914 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
huangtut:
popdyc 写道请问作者有没有遇到过Intellij 插件开 ...
IntelliJ 9 插件开发 -
popdyc:
请问作者有没有遇到过Intellij 插件开发中用Runtim ...
IntelliJ 9 插件开发 -
ggd543:
好文,收藏
IntelliJ 9 插件开发 -
fengwei199181:
不错哦。
Groovy 学习 (整理修改自精通Groovy) -- Groovy 介绍与插件的安装 -
zhengweisincere:
我的评论呢?
通过分析SQL语句的执行计划优化SQL(总结)
【IT168 技术文档】消耗在准备新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制,你能够控制Oracle花费在评估连接顺序的时间数量,并且能在大体上提高查询性能。
准备执行SQL语句
当SQL语句进入Oracle的库缓存后,在该语句准备执行之前,将执行下列步骤:
1) 语法检查:检查SQL语句拼写是否正确和词序。
2) 语义分析:核实所有的与数据字典不一致的表和列的名字。
3) 轮廓存储检查:检查数据字典,以确定该SQL语句的轮廓是否已经存在。
4) 生成执行计划:使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。
5) 建立二进制代码:基于执行计划,Oracle生成二进制执行代码。
一旦为执行准备好了SQL语句,以后的执行将很快发生,因为Oracle认可同一个SQL语句,并且重用那些语句的执行。然而,对于生成特殊的SQL语句,或嵌入了文字变量的SQL语句的系统,SQL执行计划的生成时间就很重要了,并且前一个执行计划通常不能够被重用。对那些连接了很多表的查询,Oracle需要花费大量的时间来检测连接这些表的适当顺序。
评估表的连接顺序
在SQL语句的准备过程中,花费最多的步骤是生成执行计划,特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时,它必须考虑到表之间所有可能的连接。例如:六个表的之间连接有720(6的阶乘,或6 * 5 * 4 * 3 * 2 * 1 = 720)种可能的连接线路。当一个查询中含有超过10个表的连接时,排列的问题将变得更为显著。对于15个表之间的连接,需要评估的可能查询排列将超过1万亿(准确的数字是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参数也控制着调用带星号的连接提示的阀值。当查询中的表的数目比optimizer_search_limit小时,带星号的提示将被优先考虑。
另一个工具:参数optimizer_max_permutations
初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限,且依赖于初始参数optimizer_search_limit。optimizer_max_permutations的默认值是80,000。
参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限:除非(表或组合数目)超过参数optimizer_search_limit 或者 optimizer_max_permutations设定的值,否则优化器将生成所有可能的连接组合。一旦优化器停止评估表的连接组合,它将选择成本最低的组合。
使用ordered提示指定连接顺序
你能够设定优化器所执行的评估数目的上限。但是即使采用有很高价值的排列评估,我们仍然拥有使优化器可以尽早地放弃复杂的查询的重要机会。回想一下含有15个连接查询的例子,它将有超过1万亿种的连接组合。如果优化器在评估了80,000个组合后停止,那么它才仅仅评估了0.000006%的可能组合,而且或许还没有为这个巨大的查询找到最佳的连接顺序。
在Oracle SQL中解决此问题的最好的方法是手工指定表的连接顺序。为了尽快创建最小的解决方案集,这里所遵循的规则是将表结合起来,通常优先使用限制最严格的WHERE子句来连接表。
下面的代码是一个查询执行计划的例子,该例子在emp表的关联查询上强制执行了嵌套的循环连接。注意,我已经使用了ordered提示来直接最优化表的评估顺序,最终它们表现在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子句中的第一个表指定了驱动表。ordered提示通常被用来与其它的提示联合起来来保证采用正确的顺序连接多个表。它的用途更多的是在扭转连接表数在四个以上的数据仓库的查询方面。
另外一个例子,下面的查询使用ordered提示按照指定的顺序来连接表:emp、dept、sal,最后是bonus。我通过指定emp到dept使用哈希连接和sal到bonus使用嵌套循环连接,来进一步精炼执行计划。
select /**//*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
from
emp,
dept,
sal,
bonus
where . . .
实践建议
实际上,更有效率的做法是在产品环境中减小optimizer_max_permutations参数的大小,并且总是使用稳定的优化计划或存储轮廓来防止出现耗时的含有大量连接的查询。一旦找到最佳的连接顺序,您就可以通过增加ordered提示到当前的查询中,并保存它的存储轮廓,来为这些表手工指定连接顺序,从而使其持久化。
当你打算使用优化器来稳定计划,则可以照下面的方法使执行计划持久化,临时将optimizer_search_limit设置为查询中的表的数目,从而允许优化器考虑所有可能的连接顺序。然后,通过重新编排WHERE子句中表的名字,并使用ordered提示,与存储轮廓一起使变更持久化,来调整查询。在查询中包含四个以上的表时,ordered提示和存储轮廓将排除耗时的评估SQL连接顺序解析的任务,从而提高查询的速度。
一旦检测到最佳的连接顺序,我们就可以使用ordered提示来重载optimizer_search_limit和optimizer_max_permutations参数。ordered提示要求表按照它们出现在FROM子句中的顺序进行连接,所以优化器没有加入描述。
作为一个Oracle专业人员,你应该知道在SQL语句第一次进入库缓存时可能存在重大的启动延迟。但是聪明的Oracle DBA和开发人员能够改变表的搜索限制参数或者使用ordered提示来手工指定表的连接顺序,从而显著地减少优化和执行新查询所需的时间。
准备执行SQL语句
当SQL语句进入Oracle的库缓存后,在该语句准备执行之前,将执行下列步骤:
1) 语法检查:检查SQL语句拼写是否正确和词序。
2) 语义分析:核实所有的与数据字典不一致的表和列的名字。
3) 轮廓存储检查:检查数据字典,以确定该SQL语句的轮廓是否已经存在。
4) 生成执行计划:使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。
5) 建立二进制代码:基于执行计划,Oracle生成二进制执行代码。
一旦为执行准备好了SQL语句,以后的执行将很快发生,因为Oracle认可同一个SQL语句,并且重用那些语句的执行。然而,对于生成特殊的SQL语句,或嵌入了文字变量的SQL语句的系统,SQL执行计划的生成时间就很重要了,并且前一个执行计划通常不能够被重用。对那些连接了很多表的查询,Oracle需要花费大量的时间来检测连接这些表的适当顺序。
评估表的连接顺序
在SQL语句的准备过程中,花费最多的步骤是生成执行计划,特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时,它必须考虑到表之间所有可能的连接。例如:六个表的之间连接有720(6的阶乘,或6 * 5 * 4 * 3 * 2 * 1 = 720)种可能的连接线路。当一个查询中含有超过10个表的连接时,排列的问题将变得更为显著。对于15个表之间的连接,需要评估的可能查询排列将超过1万亿(准确的数字是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参数也控制着调用带星号的连接提示的阀值。当查询中的表的数目比optimizer_search_limit小时,带星号的提示将被优先考虑。
另一个工具:参数optimizer_max_permutations
初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限,且依赖于初始参数optimizer_search_limit。optimizer_max_permutations的默认值是80,000。
参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限:除非(表或组合数目)超过参数optimizer_search_limit 或者 optimizer_max_permutations设定的值,否则优化器将生成所有可能的连接组合。一旦优化器停止评估表的连接组合,它将选择成本最低的组合。
使用ordered提示指定连接顺序
你能够设定优化器所执行的评估数目的上限。但是即使采用有很高价值的排列评估,我们仍然拥有使优化器可以尽早地放弃复杂的查询的重要机会。回想一下含有15个连接查询的例子,它将有超过1万亿种的连接组合。如果优化器在评估了80,000个组合后停止,那么它才仅仅评估了0.000006%的可能组合,而且或许还没有为这个巨大的查询找到最佳的连接顺序。
在Oracle SQL中解决此问题的最好的方法是手工指定表的连接顺序。为了尽快创建最小的解决方案集,这里所遵循的规则是将表结合起来,通常优先使用限制最严格的WHERE子句来连接表。
下面的代码是一个查询执行计划的例子,该例子在emp表的关联查询上强制执行了嵌套的循环连接。注意,我已经使用了ordered提示来直接最优化表的评估顺序,最终它们表现在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子句中的第一个表指定了驱动表。ordered提示通常被用来与其它的提示联合起来来保证采用正确的顺序连接多个表。它的用途更多的是在扭转连接表数在四个以上的数据仓库的查询方面。
另外一个例子,下面的查询使用ordered提示按照指定的顺序来连接表:emp、dept、sal,最后是bonus。我通过指定emp到dept使用哈希连接和sal到bonus使用嵌套循环连接,来进一步精炼执行计划。
select /**//*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
from
emp,
dept,
sal,
bonus
where . . .
实践建议
实际上,更有效率的做法是在产品环境中减小optimizer_max_permutations参数的大小,并且总是使用稳定的优化计划或存储轮廓来防止出现耗时的含有大量连接的查询。一旦找到最佳的连接顺序,您就可以通过增加ordered提示到当前的查询中,并保存它的存储轮廓,来为这些表手工指定连接顺序,从而使其持久化。
当你打算使用优化器来稳定计划,则可以照下面的方法使执行计划持久化,临时将optimizer_search_limit设置为查询中的表的数目,从而允许优化器考虑所有可能的连接顺序。然后,通过重新编排WHERE子句中表的名字,并使用ordered提示,与存储轮廓一起使变更持久化,来调整查询。在查询中包含四个以上的表时,ordered提示和存储轮廓将排除耗时的评估SQL连接顺序解析的任务,从而提高查询的速度。
一旦检测到最佳的连接顺序,我们就可以使用ordered提示来重载optimizer_search_limit和optimizer_max_permutations参数。ordered提示要求表按照它们出现在FROM子句中的顺序进行连接,所以优化器没有加入描述。
作为一个Oracle专业人员,你应该知道在SQL语句第一次进入库缓存时可能存在重大的启动延迟。但是聪明的Oracle DBA和开发人员能够改变表的搜索限制参数或者使用ordered提示来手工指定表的连接顺序,从而显著地减少优化和执行新查询所需的时间。
发表评论
-
MySQL 手册
2009-09-03 14:59 832来源于MySQL官方网站的5.0 ,中文手册。 因为是翻译于英 ... -
SQL Server,Oracle,DB2数据库SQL语句比较
2009-03-22 13:14 14781.1.1 取前n条记录 SQL Server ... -
异常是否会引起oracle事物回滚
2009-02-23 16:21 2179原理上说,异常是不会终止事务的。但是如果异常传递到了调用环境中 ... -
oracle 数据迁移 imp/exp 和 物化视图 的区别
2009-02-23 13:31 4181使用ON PREBUILT MATERIALIZED VIEW ... -
DB2 函数大全
2009-02-22 14:31 1062函数名 函数 ... -
oracle sql 转换成 db2 sql
2009-02-22 13:28 35221、Oracel中的decode DB2解决方案:用case ... -
通过分析SQL语句的执行计划优化SQL(总结)
2008-12-03 10:23 4088做DBA快7年了,中间感悟 ... -
Oracle中的Hash Join祥解
2008-12-02 13:41 1898、 hash join概念 ... -
oracle分区表总结(转)
2008-11-27 15:25 1306在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应 ... -
oracle 索引
2008-11-27 14:04 1446概述 索引在各种关 ... -
常见Oracle HINT的用法
2008-11-27 12:42 13231. /*+ALL_ROWS*/ 表明对语句块选择基于开销的 ... -
start with ... connect by用法简介
2008-11-25 13:32 2247通过START WITH . . . CONNECT BY . ... -
oracle 常用 函数
2008-11-25 10:54 8801、add_months()用于从一个日期值增加或减少一些月份 ... -
oracle 常用 系统查询
2008-11-19 09:32 1042查索引 DBA_INDEXES 或 ALL_INDEXE ... -
SQL语句性能调整原则
2008-11-04 22:33 857【IT168 技术文档】一、问题的提出 在应用系统开发初 ... -
Oracle性能调优原则
2008-11-04 22:25 833【IT168 技术文档】任何事情都有它的源头,要解决问题,也得 ... -
新增oracle监听器后 PL/SQL 显示 没有监听器
2008-11-02 23:10 2182oracle的监听器启动不了,于是删除重新建了一个监听器。数据 ... -
oracle Listener
2008-11-02 22:20 5084Oracle数据库监听配置 ... -
oracle sql 优化
2008-09-05 10:28 1066基本的Sql编写注意事项 ... -
转:sqlserver常用函数
2007-06-18 22:48 2516SQL常用字符串函数 一、字符转换函数1、ASCII()返回字 ...
相关推荐
消耗在准备新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制,你能够控制Oracle花费在评估连接顺序的时间...本文介绍了使用智能优化器提高Oracle性能的方法。
如何使用 Oracle Real User Experience Insight 构建业务智能解决方案 (PDF) Oracle SOA 套件 Oracle SOA 套件 (PDF) Oracle SOA 套件 – Oracle BPEL 流程管理器 (PDF) Oracle SOA 套件 11g (PDF) Oracle Tuxedo ...
【极限数据库管理与新一代技术】 ...综上所述,Oracle Database 12c通过其先进的极限数据库管理技术,实现了全面的自动化和智能化,显著提升了数据库管理的效率和性能,降低了风险,使得数据库运维变得更加高效和轻松。
- **预配置与严格测试**:提供完整、预配置的系统,经过严格的测试,确保极限性能。 - **数据库与硬件一体解决方案**:作为一体机解决方案,Exadata提供了即插即用的体验,并由Oracle提供整体的技术支持和服务。 - *...
Benchmark Factory for Databases是一种负载测试解决方案,模拟高并发用户访问场景,评估应用系统的性能极限和稳定性。 #### TOAD DATA MODELER™ Toad Data Modeler是专业级别的数据库设计工具,支持实体关系图的...
"Exadata数据库云一体机的关键技术点" 一、云计算定义与特征 根据NIST的定义,云计算是一种模型,能够方便地按需从网络访问共享...Exadata数据库一体机是一种高性能、高度优化的数据库解决方案,具有广泛的应用前景。
- **结果分析(Result Analysis)**:使用分析器查看测试结果,识别性能问题。 3. **LoadRunner的关键技术** - **协议支持**:LoadRunner支持多种协议,如HTTP/HTTPS、FTP、SMTP、Oracle、WebServices等,能覆盖...
- **压力测试**:模拟大量用户同时访问的情况,检验系统的性能极限。 - **用户体验测试**:邀请部分用户试用系统,收集反馈意见进行优化。 #### 5. 结论 通过对烟草销售管理系统的开发与实施,不仅可以提高烟草行业...
4. **数据库技术**:MySQL、Oracle、SQL Server等数据库系统的优化和管理技巧,以及NoSQL数据库的初步介绍,可能对数据库设计和性能调优有所讨论。 5. **移动开发**:随着智能手机的普及,iOS和Android开发成为热门...
- **压力测试**: 模拟高并发场景,评估系统性能极限。 #### 五、文档管理与版本控制 **5.1 文档规范** - **版本管理**: 明确记录文档的每次变更及原因。 - **审查流程**: 定期对文档进行评审,确保内容的准确性。...
8. **硬件技术**: Pentium 4处理器的性能提升,硬盘容量的扩大,以及USB设备的广泛应用,都推动了个人电脑性能的提升。硬件发展趋势和技术规格的解析也是读者关心的内容。 9. **人工智能**:虽然AI尚未像今天这般...