您现在的位置是:首页 >其他 >ORACLE-SQL性能优化(2)网站首页其他
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):
- 在共享池中查找SQL语句
- 检查语法
- 检查语义和相关的权限
- 合并(MERGE)视图定义和子查询
- 确定执行计划
SQL语句的处理过程
绑定(BIND):
- 在语句中查找绑定变量
- 赋值(或重新赋值)
SQL语句的处理过程
执行(EXECUTE):
- 应用执行计划
- 执行必要的I/O和排序操作
提取(FETCH):
- 从查询结果中返回记录
- 必要时进行排序
- 使用ARRAY FETCH机制
共享游标:好处
- 减少解析
- 动态内存调整
- 提高内存使用率
书写可共享的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’