SQL性能优化调整 Oracle 数据库oracle执行SQL查询语句的步骤l 编译：在进行编译时，服务器进程将SQL语句的正文放入 共享池的库高速缓存中并将完成以下处理。 1.首先在共享池中搜索是否有相同的SQL语句，如果没有 就进行后续的处理。 2.检查该SQL语句的语法是否正确 3.通过查看数据字典来检查表和列的定义 4.对所操作的对象加编译锁，以便在编译语句期间这些对 象的定义不能被改变 5.检查锁引用对象的权限 6.生成执行该SQL语句所需要的优化的执行条件 7.将SQL语句的执行计划装入共享的SQL区 l 执行：oracle服务器进程开始执行SQL语句是因为它已获 得了执行SQL语句所需要的全部资源和信息。 l 提取数据：oracle服务器进程所选择所需的数据行，并在 需要时将其排序，最后将结果返回给用户 1. 选用适合的ORACLE优化器lRULE (基于规则) lCOST (基于成本) lCHOOSE (选择性) 缺省情况下,ORACLE采用CHOOSE优化器, 为 了避免那些不必要的全表扫描(full table scan) , 但 尽量避免使用CHOOSE优化器,而直接采用基于规 则或者基于成本的优化器。2. 访问Table的方式 ORACLE 采用两种访问表中记录的方式: a. 全表扫描 , 顺序地访问表中每条记录b. 通过ROWID访问表 采用基于ROWID的访问方式情况,提高访问表 的效率。实例：ORACLE采用索引(INDEX)实现了数据和 存放数据的物理位置(ROWID)之间的联系 。3.共享SQL语句为了不重复解析相同的SQL语句,在第一次解 析之后, ORACLE将SQL语句存放在内存中. 这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内 存可以被所有的数据库用户共享。 A. 字符级的比较 B. 两个语句所指的对象必须完全相同 C. 两个SQL语句中必须使用相同的名字的绑定变量 4-1. 选择最有效率的表名顺序 ORACLE的解析器按照从右到左的顺序处理FROM 子句中的表名, FROM子句中包含多个表的情况下 ,你必须选择记录条数最少的表作为基础表 例如: 表 TAB1 16,384条记录 表 TAB2 1条记录 l 选择TAB2作为基础表 (最好的方法)select count(*) from tab1,tab2 执行时间0.96秒 l 选择TAB2作为基础表 (不佳的方法)select count(*) from tab2,tab1 执行时间26.09 秒4-2. 3个以上的表连接查询 如果有3个以上的表连接查询, 那就需要选择交叉表 (intersection table)作为基础表, 交叉表是指那个 被其他表所引用的表.例如: EMP表描述了LOCATION表和CATEGORY表的交集 l SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN5. WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原 理,表之间的连接必须写在其他WHERE条件之前, 那些可 以过滤掉最大数量记录的条件必须写在WHERE子句的末 尾. l 例如: l (低效,执行时间156.3秒) lSELECT FROM EMP E lWHERE SAL 50000 lAND JOB = MANAGER lAND 25 50000 lAND JOB = MANAGER; 6.SELECT子句中避免使用 * SELECT子句中列出所有的COLUMN时,使用 动态SQL列引用 * 是一个方便的方法.不幸 的是,这是一个非常低效的方法. 实际上 ,ORACLE在解析的过程中, 会将* 依次转 换成所有的列名, 这个工作是通过查询数据 字典完成的, 这意味着将耗费更多的时间. 7.使用oracle自带函数提高效率例子：使用DECODE函数来减少处理时间，可以避免重复扫 描相同记录或重复连接相同的表.例如: l SELECT COUNT(*)，SUM(SAL) l FROM EMP l WHERE DEPT_NO = 0020 l AND ENAME LIKE SMITH%; l SELECT COUNT(*)，SUM(SAL) l FROM EMP l WHERE DEPT_NO = 0030 l AND ENAME LIKE SMITH%; l你可以用DECODE函数高效地得到相同结果 lSELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, l COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, l SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, l SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SAL lFROM EMP WHERE ENAME LIKE SMITH%;8.用TRUNCATE替代DELETE l 当删除表中的记录时,在通常情况下, 回滚段 (rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复 到删除之前的状态(准确地说是恢复到执行删除命 令之前的状况) l 而当运用TRUNCATE时, 回滚段不再存放任何可 被恢复的信息.当命令运行后,数据不能被恢复.因 此很少的资源被调用,执行时间也会很短 9.尽量多使用COMMIT l 只要有可能,在程序中尽量多使用COMMIT, 这样 程序的性能得到提高,需求也会因为COMMIT所释 放的资源而减少。 l COMMIT所释放的资源:a. 回滚段上用于恢复数据的信息.b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE为管理上述3种资源中的内部花费 注：在使用COMMIT时必须要注意到事务的完整性 。 10.用Where子句替换HAVING子句 l避免使用HAVING子句, HAVING 只会在检 索出所有记录之后才对结果集进行过滤. 这 个处理需要排序,总计等操作. 如果能通过 WHERE子句限制记录的数目,那就能减少 这方面的开销。 lHAVING 中的条件一般用于对一些集合函 数的比较,如COUNT() 等等. 除此而外,一般 的条件应该写在WHERE子句中 11.通过内部函数提高SQL效率l SELECT H.EMPNO,E.ENAME,H.HIST_ TYPE,T.TYPE_DESC,COUNT (*) l FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H l WHERE H.EMPNO = E.EMPNO l AND H.HIST_TYPE = T.HIST_TYPE l GROUP BY H.EMPNO,E.ENAME,H.HIST_ TYPE,T.TYPE_DESC;lFUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 lAS l TDESC VARCHAR2(30); l CURSOR C1 IS l SELECT TYPE_DESC l FROM HISTORY_TYPE l WHERE HIST_TYPE = TYP; lBEGIN l OPEN C1; l FETCH C1 INTO TDESC; l CLOSE C1; l RETURN (NVL(TDESC,?); lEND; l lFUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2 lAS l ENAME VARCHAR2(30); l CURSOR C1 IS l SELECT ENAME l FROM EMP l WHERE EMPNO=EMP; lBEGIN l OPEN C1; l FETCH C1 INTO ENAME; l CLOSE C1; l RETURN (NVL(ENAME,?); lEND; l lSELECT H.EMPNO,LOOKUP_EMP(H.EMPNO), lH.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*) lFROM EMP_HISTORY H lGROUP BY H.EMPNO , H.HIST_TYPE;12.使用表的别名(Alias) l当在SQL语句中连接多个表时, 请使用表的 别名并把别名前缀于每个Column上.这样一 来,就可以减少解析的时间并减少那些由 Column歧义引起的语法错误. lColumn歧义指的是由于SQL中不同的表具 有相同的Column名,当SQL语句中出现这 个Column时,SQL解析器无法判断这个 Column的归属 13.用表连接替换EXISTS l SELECT ENAME l FROM EMP E l WHERE EXISTS (SELECT X l FROM DEPT l WHERE DEPT_NO = E.DEPT_NO l AND l DEPT_CAT = A);(更高效) l SELECT ENAME l FROM DEPT D, l EMP E l WHERE l E.DEPT_NO = D.DEPT_NO l AND l DEPT_CAT = A ;14.用EXISTS替换DISTINCT EXISTS 使查询更为迅速,因为RDBMS 核心模块将在子查询的条件一旦满足后,立刻 返回结果 l 低效: l SELECT DISTINCT DEPT_NO,DEPT_NAME l FROM DEPT D,EMP E l WHERE D.DEPT_NO = E.DEPT_NO；l 高效: l SELECT DEPT_NO,DEPT_NAME l FROM DEPT D l WHERE EXISTS (SELECT X l FROM EMP E l WHERE E.DEPT_NO = D.DEPT_NO);15.识别低效执行的SQL语句 l 使用TKPROF 工具来查询SQL性能状态 SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个 跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统。 l 用EXPLAIN PLAN 分析SQL语句 EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在 不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是 怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称。16.用索引提高效率 l 索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE 使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表 扫描要快. 当ORACLE找出执行查询和Update语句的