oracle优化（oracle优化器）

本篇文章给大家谈谈oracle优化，以及oracle优化器对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

• 1、讲解Oracle优化器的优化方式和优化模式
• 2、【基于ORACLE数据库的SQL语句优化分析】 数据库查询语句的优化
• 3、Oracle性能优化:droptable与dbcache
• 4、oracle 对于多个大表关联操作如何优化速度？

讲解Oracle优化器的优化方式和优化模式

Oracle在执行一个SQL之前 首先要分析一下语句的执行计划 然后再按执行计划去执行 分析语句的执行计划的工作是由优化器(Optimizer)来完成的 不同的情况 一条SQL可能有多种执行计划 但在某一时点 一定只有一种执行计划是最优的 花费时间是最少的 相信你一定会用Pl/sql Developer Toad等工具去看一个语句的执行计划 不过你可能对Rule Choose First rows All rows这几项有疑问 因为我当初也是这样的 那时我也疑惑为什么选了以上的不同的项 执行计划就变了?

优化器的优化方式

Oracle的优化器共有两种的优化方式 即基于规则的优化方式(Rule Based Optimization 简称为RBO)和基于代价的优化方式(Cost Based Optimization 简称为CBO)

A RBO方式 优化器在分析SQL语句时 所遵循的是Oracle内部预定的一些规则 比如我们常见的 当一个where子句中的一列有索引时去走索引

B CBO方式 依词义可知 它是看语句的代价(Cost)了 这里的代价主要指Cpu和内存 优化器在判断是否用这种方式时 主要参照的是表及索引的统计信息 统计信息给出表的大小 有少行 每行的长度等信息 这些统计信息起初在库内是没有的 是你在做 *** yze后才出现的 很多的时侯过期统计信息会令优化器做出一个错误的执行计划 因些我们应及时更新这些信息 在Oracle 及以后的版本 Oracle列推荐用CBO的方式

我们要明了 不一定走索兄则蠢引就是优的 比如一个表只有两行数据 一次IO就可以完成全表的检索 而此时走索引时则需要两次IO 这时对这个表做全表扫描(full table scan)是最好的

优化器的优化模式(Optermizer Mode)

优化模式包括Rule Choose First rows All rows这四种方式 也就是我们以上所提及的 如下我解释一下

Rule:不用多说 即走基于规则的方式

Choolse:这是我们应观注的 默认的情况下Oracle用的便是这种方式 指的是当一个表或或索引有统计信息 则走CBO的方式 如果表或索引没统计信息 表又羡陪不是特别的小 而且相应的列有索引时 那么就走盯闭索引 走RBO的方式

First Rows:它与Choose方式是类似的 所不同的是当一个表有统计信息时 它将是以最快的方式返回查询的最先的几行 从总体上减少了响应时间

All Rows:也就是我们所说的Cost的方式 当一个表有统计信息时 它将以最快的方式返回表的所有的行 从总体上提高查询的吞吐量 没有统计信息则走基于规则的方式

如何设定选用哪种优化模式

◆A Instance级别

我们可以通过在init ora文件中设定OPTIMIZER_MODE=RULE OPTIMIZER_MODE=CHOOSE OPTIMIZER_MODE=FIRST_ROWS OPTIMIZER_MODE=ALL_ROWS去选用 所提的四种方式 如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式

◆B Sessions级别

通过SQL ALTER SESSION SET OPTIMIZER_MODE= ;来设定

◆C 语句级别

这些需要用到Hint 比如:

SQL SELECT /*+ RULE */ a userid b name b depart_name FROM tf_f_yhda a tf_f_depart b WHERE a userid=b userid;

  为什么有时一个表的某个字段明明有索引 当观察一些语的执行计划确不走索引呢？如何解决呢？

◆A 不走索引大体有以下几个原因

你在Instance级别所用的是all_rows的方式

你的表的统计信息(最可能的原因)

你的表很小 上文提到过的 Oracle的优化器认为不值得走索引

◆B 解决方法

可以修改init ora中的OPTIMIZER_MODE这个参数 把它改为Rule或Choose 重起数据库 也可以使用 中所提的Hint

删除统计信息

SQL *** yze table table_name delete statistics;

表小不走索引是对的 不用调的

其它相关

◆A 如何看一个表或索引是否是统计信息

SQLSELECT * FROM user_tables WHERE table_name=table_name AND num_rows is not null; SQLSELECT * FROM user_indexes WHERE table_name=table_name AND num_rows is not null;

◆B 假如我们先用CBO的方式 就应当及时去更新表和索引的统计信息 以免生形不切合实的执行计划

SQL ANALYZE TABLE table_name PUTE STATISTICS; SQL ANALYZE INDEX index_name ESTIMATE STATISTICS;

lishixinzhi/Article/program/Oracle/201311/18622

[img]

【基于ORACLE数据库的SQL语句优化分析】 数据库查询语句的优化

【摘　要】　随着数据库应用范围及规模的不断扩大，数据库的性能问题逐渐显现，优化数据库有助于维持系统的稳定性以及运行的高效性。本文主要依据笔者在实际工作中的精坦敏拍英，对SQL语句优化的目的、SQL语句优化技术及原则进行全面分析和阐述。

【关键词】　ORACLE数据库；SQL语句；优化

1前言

随着现代化信息技术的迅猛发展，互联网应用的日益普及，数据库技术的影响力越来越大。作为信息系统管理的核心，数据库的主要操作就是查询，数据库的应用效率在很大程度上是由查询速度决定的，特别是对于规模较大的数据库而言，查询速度十分关键。查询速度在SQL语句中占有很大比重，所以，通过对查询语句进行优化有助于促进应用系统性能及效率的进一步提升。

2SQL语句优化分析

2.1SQL语句优化的目的

对于一个数据库而言，在确保设计无误的前提下，要想避免出现性能问题必须确保其拥有合理的SQL语句拿唤结构。最简单的数据库寻找数据路径是对SQL语句进行调整，ORACLE数据库性能提升的主要途径就是对SQL语句进行适当的调整。从本质上讲，SQL语句优化就是确保所使用的语句可以被优化器识别，对索引进行有效利用以便控制表扫描的I/O次数，有效防止出现表搜索。用高性能的SQL语句替代低性能的SQL语句，确定最佳的数据查找路径，尽可能使CPU时间与I/O时间保持平衡是进行优化的主要目的。在对SQL语句进行优化的过程中，以系统需求为依据确定最有可能实现性能提升的语句并进行优化。

2.2SQL语句优化技术及原则

当数据量积累到一定程度之后，对于数据库全表SQL语句进行一次扫描，若查询策略较好，一般只用几秒钟，但如果SQL语句性能较低，就需要用几分钟甚至更多时间。从这点不难看出，SQL语句性能对于查询速度具有极大的影响，所以，对于应用系统而言，不仅能满足功能的实现，还要保证SQL语句的质量。

（1）采取适宜的索引。为达到优化查询的目的，一项重要工作就是确定相适应的索引，并严格依照原则加以使用，与此同时，为有效控制I/O竞争，不可以在同一个磁盘中同时建立索引和用户表空间。

语句1：SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE CUS_NO NOT IN

(SELECT CUS_NO FROM SERVICE);

语句2： SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE NOT EXISTS

(SELECT * FROM SERVICE WHERE SERVICE.CUS_NO=CUSTOMER.CUS_NO);

上述两个语句可以达到一致的查询结果，对二者进行对比，当执行语句1时，由于ORACLE未利用CUSTOMER 表上CUS_NO索引，所以就会扫描整表，在执行语句2的过让羡程中，ORACLE所扫描的只是CUSTOMER 表子查询中的联合查询，并且使用了CUS_NO索引，因此，在执行效率方面明显优于前者。

（2）避免在SELECT子句中出现“*”。ORACLE在进行解析时，需要按照一定顺序对“*”进行转换，该项转换工作的进行需要对数据库的数据字典进行查询，势必需要花费较多的时间，这样就会导致较低的效率，所以，要避免在SELECT子句中出现“*”。

（3）如果必要可以利用COMMIT提交事务。ORACLE能够自动提交DDL语句，而诸如DML等类型的语句的提交则是通过手动方式或者回滚事务实现的。在编写应用程序的过程中，在操作诸如insert、delete以及update 等较为复杂的语境的时候，利用COMMIT提交事务可以讲会话中持有的锁加以释放，将存在于缓存中的未经修改的数据块进行清除，进而将系统资源予以释放，促进系统性能的进一步提升，因此，如果有必要，可以利用COMMIT对相关事务进行提交。

（4）联合查询连接顺序的确定。如果查询操作涉及到多个表，基础表应当是交叉表，所谓交叉表具体是指被其他表引用的表。连接执行效果在很大程度上受到FROM语句中表的顺序的影响，对于FROM中所包含的表，ORACLE解析器进行处理的顺序是由右至左，SQL语句中所选择的基础表会因优化器的不同而有所区别，在使用CBO的情况下，优化器会对SQL语句中各个表的物理大小以及索引状态进行检查，在此基础上确定一个花费最小的执行路径；在使用RBO的情况下，如果全部的连接条件均有索引与之相对应，那么，FROM子句中位置最后面的表就是基础表。

（5）IN用EXISTS取代。在对数个基础表查询过程中，一般需要进行表的连接。因为利用IN的子查询过程中，ORACLE的扫描对象是全表，因此，出于提高查询效率目的的考虑，应当将IN用EXISTS取代。

（6）在索引列中不使用计算。当通过对函数进行引用在WHERE子句中进行计算的时候，假如索引列只是函数的一部分，优化器就会针对全表进行扫描，而不会使用索引，所以，在索引列中不能使用函数。

3结语

综上所述，随着现代化信息技术的迅猛发展，互联网应用的日益普及，数据库技术的影响力越来越大。在信息量迅速激增的形势下，数据库优化调整成为当前所面临的一大关键性问题，特别是对规模较大的数据库而言，及时进行优化的意义更加倍重大。对于数据库的运行性能而言，最主要的影响因素主要体现在以下几点：数据库系统架构的设计是否合理，资源配置是否科学以及SQL语句编写效率等。笔者从事的是电信企业的运营分析工作，每天都要从数据库取各种数据，可以说是离不开数据库，所以在实践中，我觉得严格遵守SQL语句优化原则及方法，并在实践中及时总结经验教训，可以实现对系统响应时间的有效控制，促进运行效率的提升。

参考文献

[1] 许开宇,胡文骅. 如何提高ORACLE数据库应用程序的性能[J]. 计算机应用与软件. 2002(10)

[2] 郑耀,吴建岚. 基于Oracle数据库的语句优化策略[J]. 信息与电脑(理论版). 2011(07)

[3] 高攀,施蔚然. 基于Oracle数据库的SQL语句优化[J]. 电脑编程技巧与维护. 2010(22)

[4] 钟小权,叶猛. Oracle数据库的SQL语句优化[J]. 计算机与现代化. 2011(03)

作者简介：

王勇军，男，（1981.1-），吉林通化人，就职于中国联合网络通信有限公司长春市分公司，通信工程师，本科，研究方向：SQL使用

（作者单位：中国联合网络通信有限公司长春市分公司）

Oracle性能优化:droptable与dbcache

们都知道drop table truncate table时都会先做一次checkpoint 将被删除对象的脏块写入磁盘

客户有一套系统 Oracle 需要做数据迁移 由于种种原因 采用的是逻辑迁移的方式 由于库比较大 超过了 T 而停机时间又有限 因此在正式迁移之前需要做大量的测试 测试的目的 一方面是看迁移流程上是否存在问题 另一方面是看迁移的时候 哪个地方会存在性能瓶颈 并进行优化 同时估算实施迁移时间

第一次测试后 需要把测试产生的大量用户及其对象全部删除 删除用的是drop user username cascade 不幸的是这种方式删除得相当地慢 一个 多个表的用户 删除了 个半小时才删除了 多个表 为什么这么慢?有没有办法提高速度?

drop table既晌乱然要做checkpoint 那么在db cache非常大的情况下 这需要消耗的时间是比较长的 如果能够减少这个时间无疑将大幅提高速度 首先尝试做一次checkpoint 将buffer cache全部刷新出去

view plaincopy to clipboardprint?

alter system checkpoint;

alter session set events immediate trace name flush_cache level ;

alter system checkpoint;

alter session set events immediate trace name flush_cache level ;

发现没什么效春谨饥果

由于db_cache_size有 GB左右 db_keep_cache_size有 G左右扒返 重新设置参数 将db_keep_cache_size设为 将db_cache_size设为 M 重启一下数据库 重新执行删除用户的操作 操作很快完成

lishixinzhi/Article/program/Oracle/201311/17079

oracle 对于多个大表关联操作如何优化速度？

1、首先要建立适当的索引。sql在索引字段不要加函数，保证索引起效。如果是复合索引注意在sql的顺序。如果已经存在索引，建议你先重建索引先，因为大数据表的索引维护到了一个阶段就是乱的，一般建议重建。建立好的一般可以获得几十倍的速告虚闹度提升。

2、最大数据量的表放在最前，最小的表放在最后面。sql是从最后面开始反向解析誉清的。

3、其次是要把最有效袜罩缩小范围的条件放到sql末尾去。尤其是主键或者索引字段的条件。

4、保证你sql的算法合理性。保证复杂度和空间度的合理性。

5、必要时候使用存储过程。提升30%-40%的速度

6、建议你分页读取不要一下读完所有的数据。（使用rownum），一下子数据太多会使得内存不够用的。

如果这些都做了还不满意的话，可以考虑建立几个表空间，然后按照一个算法将各个表的数据，平均的放在各个表空间内（分表分区），在select的时候数据库就会使用多线程到各个表空间索引数据，这个一般不是上千万级的表是不用的。 也不是所有人都会用。

关于oracle优化和oracle优化器的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。