ORACLE-SQL性能优化(2）

绑定变量解决重编译问题

未使用绑定变量的语句

sprintf(sqlstr, "insert into scott.test1 (num1, num2) values (%d,%d)",n_var1, n_var2);

EXEC SQL EXECUTE IMMEDIATE :sqlstr ; EXEC SQL COMMIT;

使用绑定变量的语句

strcpy(sqlstr, "insert into test (num1, num2) values (:v1, :v2)"); EXEC SQL PREPARE sql_stmt FROM :sqlstr;

EXEC SQL EXECUTE sql_stmt USING :n_var1, :n_var2; EXEC SQL COMMIT;

绑定变量的注意事项

注意：

1、不要使用数据库级的变量绑定参数cursor_sharing来强

制绑定，无论其值为 force 还是similar

2、有些带> < 的语句绑定变量后可能导致优化器无法正确

使用索引

SQL语句的处理过程

SQL语句的四个处理阶段

SQL语句的处理过程

解析(PARSE)：

1. 在共享池中查找SQL语句
2. 检查语法
3. 检查语义和相关的权限
4. 合并(MERGE)视图定义和子查询
5. 确定执行计划

SQL语句的处理过程

绑定(BIND)：

1. 在语句中查找绑定变量
2. 赋值(或重新赋值)

SQL语句的处理过程

执行(EXECUTE)：

1. 应用执行计划
2. 执行必要的I/O和排序操作

提取(FETCH)：

1. 从查询结果中返回记录
2. 必要时进行排序
3. 使用ARRAY FETCH机制

共享游标：好处

1. 减少解析
2. 动态内存调整
3. 提高内存使用率

书写可共享的SQL

绑定变量和共享游标

ORACLE 优化器模式 概述Oracle的优化器共有3种模式：RULE (基于规则)、COST (基于成本)、CHOOSE(基于选择)。

设置缺省的优化器的方法，是在启动参数文件中针对OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、

CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语句级别或是会话级别对其进行覆盖。

为了使用基于成本的优化器(CBO，Cost—Based Optimizer)，必须经常运行analyze命令，以增加数据库中的对象统计信息(object statistics)的准确性。

如果数据库的优化器模式设置为基于选择，那么实际的优化器模式将和是否运行过analyze命令有关。如果数据表已经被

analyze过，优化器模式将自动切换成CBO，反之，数据库将采用RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化

器。为避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。

访问数据表的方式

① 全表扫描

全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。

② 通过ROWID访问表

ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系通常索引提供了快速访问ROWID的方法，因此那些基于索

引列的查询就可以得到性能的提高。

数据库性能

 影响数据库系统性能的要素：

 主机CPU,RAM,存储系统;

 OS参数配置,ORACLE参数配置;

 应用方面：数据库设计及SQL编程的质量

 一个性能优秀的应用系统需要：

 良好的硬件配置；正确合理的数据库及中间件参数配置；合理的数据库设计；

良好的sql编程；运行期的性能优化

SQL Tunning 的重点

 SQL: insert, update, delete, select; 主要关注的是select

 关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置

SQL优化的一般性原则

 目标：

 减少服务器资源消耗（主要是磁盘IO）； 设计方面：

 尽量依赖oracle的优化器，并为其提供条件； 合适的索引，索引的双重效应，列的选择性；

 编码方面：

 利用索引，避免大表FULL TABLE SCAN；

 合理使用临时表；

 避免写过于复杂的sql，不一定非要一个sql解决问题； 在不影响业务的前提下减小事务的粒度；

优化概括

课程Oracle数据库SQL语句优化的总体策略。以这些

优化策略为指导，通过经验总结，我们可以不断地丰富优化方案，进而指导我们进行应用系统的数据库性能优化。以下枚举几则被证明行之有效的优化方案：

● 创建表的时候。应尽量建立主键，尽量根据实际需要调

整数据表的PCTFREE和PCTUSED参数；大数据表删除，用truncate table代替delete。

● 合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。

优化概括

● 查询尽量用确定的列名，少用*号。select count(key)from tab where key> 0性能优于select count(*)from tab；

尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多or运算的查询，建议分成多个查询，用union all联结起来；多表查询的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从

右到左，所以记录少的表放在右边。

● 尽量多用commit语句提交事务，可以及时释放资源、解

锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的

数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：alter table．．．cache；

● 在Oracle中动态执行SQL，尽量用execute方式，不用dbms_sql包。

** SQL Tunning Tips **

sql 语句的编写原则和优化

 随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功

能就可，而是要写出高质量的SQL语句，提高系统的可用性。

sql 语句的编写原则和优化

 在编写SQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。

 SQL语句的编写原则和SQL语句的优化，请跟我一起学习以下几方面：

Tunning Tip的各个方面

1.不要让Oracle做得太多；2.给优化器更明确的命令；3.减少访问次数；

4.细节上的影响；

1.不要让Oracle做得太多

避免复杂的多表关联

select .

fromdm,user_files uf,  df_money_files cw_charge_record cc

where

uf.user_no = dm.user_no

and dm.user_no = cc.user_no and ..

and not exists(select .)

避免使用 ‘ * ‘

当你想在SELECT子句中列出所有的COLUMN时,使用动态

SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低

效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转

换成所有的列名, 这个工作是通过查询数据字典完成的, 这意

味着将耗费更多的时间；

避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.

例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.

通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写.

用EXISTS替换DISTINCT

例如:低效:

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效:

SELECT DEPT_NO,DEPT_NAME FROM DEPT D

WHERE EXISTS ( SELECT ‘X’

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

用UNION-ALL 替换UNION ( if possible)

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以

UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.举例:

低效：

SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ UNION

SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’

高效:

SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ UNION ALL

SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’