【接确保Oracle数据库sql语句高效执行的优化总结(一)】

2.4 10053和10046事件

2.4.1 10053事件

我们在查看一条SQL的执行计划的时候，只能看到CBO 最终告诉我们的执行计划结果，但是不知道CBO 是根据什么来做的。 如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。无法进行分析判断。

10053事件就提供了这样的功能。它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。

对于10053事件的trace文件，我们只能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件。

10053事件有两个级别：

? Level2：2级是1级的一个子集，它包含以下内容：

Column statistics Single Access Paths Join Costs Table Joins Considered Join Methods Considered (NL/MS/HA)

…… …… 余下全文

Oracle数据库SQL基础用法与优化总结

前言................................................................................................................................. 2

基础用法.......................................................................................................................... 2

一、 SELECT语句 ....................................................................................... 2 二、

三、

四、

五、

六、

七、

八、

九、

十、 多表关联.............................................................................................. 2 子查询 ................................................................................................. 3 级联查询(Hierachical Querys) ................................................................ 5 INSERT语句........................................................................................ 6 UPDATE语句 ...................................................................................... 7 DELETE语句....................................................................................... 7 集合操作符 .......................................................................................... 8 DISTINCT............................................................................................ 8 聚集查询的用法 ..................................................... 错误！未定义书签。

…… …… 余下全文

Oracle数据库中SQL语句优化研究

中国电信股份有限公司石嘴山分公司 任凤

摘要： 简要介绍数据库中SQL 语句优化技术，并结合具体的事例，探讨了基于Oracle 数据库的SQL 语句优化。

关键词： Oracle 数据库；SQL 语句；优化

1、引言

Oracle 数据库是当前应用最广泛的大型数据库管理系统, 它在各个领域的使用不断增长, 其查询性能直接关系到系统的运行效率, 对其查询优化方法的研究更具有现实意义。数据库SQL 查询优化的一个基本原则就是: 通过尽可能少的磁盘I/ O访问获取所需要的数据。要评价查询优化性能, 需要在数据库优化前后比较其评价指标: 响应时间和吞吐量之间的权衡、数据库的命中率以及内存的使用效率, 并以此来衡量优化的效果和指导优化的方向。优质的查询语句可以使应用系统的性能得到大大提高。拙劣的应用SQL 语句、大小不合适的SGA 内存结构、效率差的SQL 语句、执行计划过度的文件I/O及访问数据库资源的紊乱等都会影响数据库系统的性能和执行效率。本文将从数据库设计的角度及SQL语句优化技术的两方面进行详细地阐述如何提高数据库的性能和执行效率。

一、 数据库设计的优化

…… …… 余下全文

oracle sql语句优化

（1） 选择最有效率的表名顺序 ( 只在基于规则的优化器中有效 ) ：

ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名， FROM 子句中写在最后的表 ( 基础表

driving table) 将被最先处理，在 FROM 子句中包含多个表的情况下 , 你必须选择记录条数最少的表作为基础表。如果有 3 个以上的表连接查询 , 那就需要选择交叉表 (intersection table) 作为基础表 , 交叉表是指那个被其他表所引用的表 .

（2） WHERE 子句中的连接顺序．：

ORACLE 采用自下而上的顺序解析 WHERE 子句 , 根据这个原理 , 表之间的连接必须写在其他 WHERE条件之前 , 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾 .

（3） SELECT 子句中避免使用 ? * ? ：

ORACLE 在解析的过程中 , 会将 '*' 依次转换成所有的列名 , 这个工作是通过查询数据字典完成的 , 这意味着将耗费更多的时间

（4） 减少访问数据库的次数：

…… …… 余下全文

操作符优化

IN 操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。

但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

推荐方案：在业务密集的SQL当中尽量不采用IN操作符。

NOT IN操作符

此操作是强列推荐不使用的，因为它不能应用表的索引。

推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

操作符（不等于）

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 推荐方案：用其它相同功能的操作运算代替，

如 a0 改为 a>0 or a a’’ 改为 a>’’

IS NULL 或IS NOT NULL操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。 推荐方案：

…… …… 余下全文

1． 基本的Sql编写注意事项尽量少用IN操作符，基本上所有的IN

操作符都可以用EXISTS代替。不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS比NOT IN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。

2． 不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫

描，可以用“<” or “>”代替。 3． Where子句中出现IS NULL或者IS NOT NULL时，Oracle会

停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。

4． 当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会

被使用。

5． 对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，

可以分开连接或者使用不作用在列上的函数替代。

6． 如果索引不是基于函数的，那么当在Where子句中对索引列使

…… …… 余下全文

oracle sql语句优化

（1）选择最有效率的表名顺序 ( 只在基于规则的优化器中有效 ) ：

ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名， FROM 子句中写在最后的表 ( 基础表 driving table) 将被最先处理，在 FROM 子句中包含多个表的情况下 , 你必须选择记录条数最少的表作为基础表。如果有 3 个以上的表连接查询 , 那就需要选择交叉表 (intersection table) 作为基础表 , 交叉表是指那个被其他表所引用的表 .

（2）WHERE 子句中的连接顺序．：

ORACLE 采用自下而上的顺序解析 WHERE 子句 , 根据这个原理 , 表之间的连接必须写在其他 WHERE 条件之前 , 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾 .

（3）SELECT 子句中避免使用 ? * ? ：

ORACLE 在解析的过程中 , 会将 '*' 依次转换成所有的列名 , 这个工作是通过查询数据字典完成的 , 这意味着将耗费更多的时间

（4）减少访问数据库的次数：

ORACLE 在内部执行了许多工作 : 解析 SQL 语句 , 估算索引的利用率 , 绑定变量 , 读数据块等；

…… …… 余下全文

抓取oracle数据库耗费资源的sql语句

oracle数据库连接业务系统，而有些sql语句的执行严重影响了oracle的性能，就如同mysql的慢查询一样，mysql可以开启慢查询日志定位这些造成数据库性能下降的语句，而oracle同样可以做到，而且有过之而无不及。

下面举例说明

首先用root用户登陆核心应用系统，su - oracle

执行命令 topas

可以查看到如下信息：

解释三个地方

1.Idle%：是cpu的空闲率

2.PID：进程唯一标示符

3.CPU%：该进程所占用的cpu的百分比

从上述信息可以定位进程为1454604的进程占用资源比较高，查询该进程的sql语句情况。

登陆核心系统oracle数据库，执行以下语句

select

c.spid,a.p1,a.p1raw,a.p2,a.event,b.sql_text,b.SQL_FULLTEXT,b.SQL_ID

2. from v$session a,v$sql b,v$process c

3. where a.wait_class<>'Idle' and a.sql_id=b.sql_id and a.PADDR=c.addr 4. order by event; 1.

…… …… 余下全文