您现在的位置是:首页 >其他 >ORACLE-SQL性能优化(2)网站首页其他

ORACLE-SQL性能优化(2)

2301_76957510 2024-06-17 11:26:22
简介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参数的各种声明进行选择,如RULECOST

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语句优化的总体策略。以这些

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

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

整数据表的PCTFREEPCTUSED参数;大数据表删除,用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 BYSQL语句会启动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’

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。