ORACLE SQL性能优化(全).ppt

1、ORACLE培训-SQL性能优化,老方块内部培训班使用,内容概述,课程主要讨论： SQL语句执行的过程、ORACLE优化器 ，表之间的关联，如何得到SQL执行计划，如何分析执 行计划等内容，从而由浅到深的方式了解SQL优化的过 程，使大家逐步掌握SQL优化。,目录,优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项,一、优化基础知识,概述,性能管理 性能问题 调整的方法 SQL优化机制 应用的调整 SQL语句的处理过程 共享SQL区域 SQL语句处理的阶段 共享游标 SQL编码标准 Oracle 优化器介绍 SQL Tunn

2、ing Tips 优化Tools,性能管理,尽早开始 设立合适目标 边调整边监控 相关人员进行合作 及时处理过程中发生的意外和变化 80/20定律,SQL 优化衡量指标,随着软件技术的不断发展，系统性能越来越重要。 系统性能主要用：系统响应时间和并发性来衡量。 造成SQL语句性能不佳大致有两个原因： 开发人员只关注查询结果的正确性，忽视查询语句的效率。 开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。 * 前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。 SQL调优是一个系统工程，熟悉SQL语法、掌握各种内嵌函数、分 析函数的用法只是编

3、写高效SQL的必要条件。 * 后者从分析SQL语句执行原理入手，指出SQL调优应在优化SQL解 析和优化CBO上。,调优领域,调优领域： 应用程序级调优： * SQL语句调优 * 管理变化调优 2. 实例级调优 * 内存 * 数据结构 * 实例配置 3. 操作系统交互 * I/O * SWAP * Parameters 本课程内容只讲解讨论应用程序级： Oracle SQL语句调优及管理变化调优,调整的方法,调整业务功能 调整数据设计 调整流程设计 调整SQL语句 调整物理结构 调整内存分配 调整I/O 调整内存竞争 调整操作系统,不同调整产生相应性能收益,调整的角色,SQL语句优化是提高性能

4、的重要环节,开发人员不能只注重功能的实现，不管性能如何 开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法 必需遵守既定的开发规范 未经过SQL语句优化的模块不要上线,SQL语句优化的过程,定位有问题的语句 检查执行计划 检查执行过程中优化器的统计信息 分析相关表的记录数、索引情况 改写SQL语句、使用HINT、调整索引、表分析 有些SQL语句不具备优化的可能，需要优化处理方式 达到最佳执行计划,什么是好的SQL语句？,尽量简单，模块化 易读、易维护 节省资源 内存 CPU 扫描的数据块要少 少排序 不造成死锁,SQL共享原理,ORACLE将执行过的SQL语句存放

5、在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。 当你执行一个SQL语句(有时被称为一个游标)时, 如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了 SQL的执行性能并节省了内存的使用。,SQL共享原理,为了不重复解析相同的SQL语句，在第一次解析之后， Oracle将SQL语句存放在内存中。这块位于系统全局区域 SGA(systemglobal area)的共享池(shared buffer poo1)中的 内存可以被所有的数据库用户共享。因此，当你执行一个SQL 语句(有时被称为

6、一个游标)时，如果它和之前执行过的语句完全 相同，Oracle就能很快获得已经被解析的语句以及最好的执行 方案。Oracle的这个功能大大地提高了SQL的执行性能并节省 了内存的使用。 可惜的是，Oracle只对简单的表提供高速缓冲 (cache bufferiIlg)，这个功能并不适用于多表连接查询。数据 库管理员必须在启动参数文件中为这个区域设置合适的参数， 当这个内存区域越大，就可以保留更多的语句，当然被共享的 可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle 会首先在这块内存中查找相同的语句。,SQL共享的三个条件,当前被执行的语句和共享池中的语句必须完全相同 （包

7、括大小写、空格、换行等） 两个语句所指的对象必须完全相同 （同义词与表是不同的对象） 两个SQL语句中必须使用相同的名字的绑定变量(bind variables),共享SQL语句,注意： Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句必 须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个 条件： 字符级的比较。 当前被执行的语句和共享池中的语句必须完全相同。 例如： SELECT * FROM ATABLE；和下面每一个SQL语句都不同： SELECT *from ATABLE Select * From Atable； 语句所指对象必须完全相同 即两条SQL语

8、句操作的数据库对象必须同一。 语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同 的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变 量以相同的值： 第一组 select pin，name from people where pin = ：blk1.pin； select pin，name from people where pin =：blk1.pin； 第二组 select pin，name from people where pin =：blk1.ot_jnd； select pin，name from people where pin = ：blk1.o

9、v_jnd；,SQL语句的处理过程,共享SQL区域,Sql 处理过程,SQL PARSE与共享SQL语句,当一个Oracle实例接收一条sql后 1、Create a Cursor 创建游标 2、Parse the Statement 分析语句 3、Describe Results of a Query 描述查询的结果集 4、Define Output of a Query 定义查询的输出数据 5、Bind Any Variables 绑定变量 6、Parallelize the Statement 并行执行语句 7、Run the Statement 运行语句 8、Fetch Rows of

10、 a Query 取查询出来的行 9、Close the Cursor 关闭游标,为什么要bind variables?,字符级的比较: SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 与 SELECT * FROM USER_FILES WHERE USER_NO = 10004321; 检查： select name,executions from v$db_object_cache where name like select * from user_files%,什么叫做重编译问题,什么叫做重编译？ 下面这个语句每执行一次就需要在S

11、HARE POOL 硬解析一 次，一百万用户就是一百万次，消耗CPU和内存，如果业务 量大，很可能导致宕库 如果绑定变量，则只需要硬解析一次，重复调用即可 select * from dConMsg where contract_no = 32013484095139,绑定变量解决重编译问题,未使用绑定变量的语句 sprintf(sqlstr, insert into scott.test1 (num1, num2) values (%d,%d),n_var1, n_var2); EXEC SQL EXECUTE IMMEDIATE :sqlstr ; EXEC SQL COMMIT; 使用绑

12、定变量的语句 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语

13、句的处理过程,解析(PARSE)：,SQL语句的处理过程,在共享池中查找SQL语句 检查语法 检查语义和相关的权限 合并(MERGE)视图定义和子查询 确定执行计划,绑定(BIND)：,SQL语句的处理过程,在语句中查找绑定变量 赋值(或重新赋值),执行(EXECUTE)：,SQL语句的处理过程,应用执行计划 执行必要的I/O和排序操作,提取(FETCH)：,从查询结果中返回记录 必要时进行排序 使用ARRAY FETCH机制,共享游标：好处,减少解析 动态内存调整 提高内存使用率,书写可共享的SQL,绑定变量和共享游标,ORACLE 优化器模式 概述,Oracle的优化器共有3种模式：RUL

14、E (基于规则)、COST (基于成本)、CHOOSE(基于选择)。 设置缺省的优化器的方法，是在启动参数文件中针对 OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、 CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语 句级别或是会话级别对其进行覆盖。 为了使用基于成本的优化器(CBO，CostBased Optimizer)， 必须经常运行analyze命令，以增加数据库中的对象统计信息 (object statistics)的准确性。 如果数据库的优化器模式设置为基于选择，那么实际的优化 器模式将和是否运行过analyze命令有关。如

15、果数据表已经被 analyze过，优化器模式将自动切换成CBO，反之，数据库将采用 RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化 器。为避免那些不必要的全表扫描，必须尽量避免使用 CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。,访问数据表的方式, 全表扫描 全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。 通过ROWID访问表 ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)

16、之间的联系 通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。,数据库性能,影响数据库系统性能的要素： 主机CPU,RAM,存储系统; OS参数配置,ORACLE参数配置; 应用方面：数据库设计及SQL编程的质量 一个性能优秀的应用系统需要： 良好的硬件配置；正确合理的数据库及中间件参数配置；合理的数据库设计；良好的sql编程；运行期的性能优化,SQL Tunning 的重点,SQL: insert, update, delete, select; 主要关注的是select 关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置,SQL优化的一般性原

17、则,目标： 减少服务器资源消耗（主要是磁盘IO）； 设计方面： 尽量依赖oracle的优化器，并为其提供条件； 合适的索引，索引的双重效应，列的选择性； 编码方面： 利用索引，避免大表FULL TABLE SCAN； 合理使用临时表； 避免写过于复杂的sql，不一定非要一个sql解决问题； 在不影响业务的前提下减小事务的粒度；,优化概括,课程Oracle数据库SQL语句优化的总体策略。以这些 优化策略为指导，通过经验总结，我们可以不断地丰富优 化方案，进而指导我们进行应用系统的数据库性能优化。 以下枚举几则被证明行之有效的优化方案： 创建表的时候。应尽量建立主键，尽量根据实际需要调整数据表的P

18、CTFREE和PCTUSED参数；大数据表删除，用truncate table代替delete。 合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。,优化概括, 查询尽量用确定的列名，少用*号。select count(key)from tab where key 0性能优于select count(*)from tab； 尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多 or运算的查询，建议分成多个查询，用unio

19、n all联结起来；多表查询 的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从 右到左，所以记录少的表放在右边。 尽量多用commit语句提交事务，可以及时释放资源、解 锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的 数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可 以常驻内存：alter tablecache； 在Oracle中动态执行SQL，尽量用execute方式，不用 dbms_sql包。,* SQL Tunning Tips *,sql 语句的编写原则和优化,随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优

20、化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。 在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。,sql 语句的编写原则和优化,在编写SQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。 SQL语句的编写原则和SQL语句的优化，请跟

21、我一起学习以下几方面：,Tunning Tip的各个方面,1.不要让Oracle做得太多； 2.给优化器更明确的命令； 3.减少访问次数； 4.细节上的影响；,1.不要让Oracle做得太多,避免复杂的多表关联,select from user_files uf, df_money_files dm, cw_charge_record cc where uf.user_no = dm.user_no and dm.user_no = cc.user_no and and not exists(select ) ? 很难优化，随着数据量的增加性能的风险很大。,避免使用 * ,当你想在SELECT

22、子句中列出所有的COLUMN时,使用动态 SQL列引用 * 是一个方便的方法.不幸的是,这是一个非常低 效的方法. 实际上,ORACLE在解析的过程中, 会将* 依次转 换成所有的列名, 这个工作是通过查询数据字典完成的, 这意 味着将耗费更多的时间； 只提取你所要使用的列； 使用别名能够加快解析速度；,避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的 SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次 排序. 例如,一个UNION查询,其中每个查询都带有GRO

23、UP BY子句 , GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个 查询需要执行一次排序, 然后在执行UNION时, 又一个唯一 排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入 排序结束后才能开始执行. 嵌入的排序的深度会大大影响查 询的效率. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以 用其他方式重写.,例如: 低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 高效: SELECT DEPT_NO

24、,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);,用EXISTS替换DISTINCT,用UNION-ALL 替换UNION ( if possible),当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以 UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 举例: 低效： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95 UNION

25、 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,2. 给优化器更明确的命令,自动选择索引,如果表中有两个以上（包括两个）索引，其中有

26、一个唯一性 索引，而其他是非唯一性 在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯 一性索引 举例: SELECT ENAME FROM EMP WHERE EMPNO = 2326 AND DEPTNO = 20 ; 这里，只有EMPNO上的索引是唯一性的，所以EMPNO索 引将用来检索记录 TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX,至少要包含组合索引的第一列,如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. SQL cre

27、ate table multiindexusage ( inda number , indb number , descr varchar2(10); Table created. SQL create index multindex on multiindexusage(inda,indb); Index created. SQL set autotrace traceonly SQL select * from multiindexusage where inda = 1; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 T

28、ABLE ACCESS (BY INDEX ROWID) OF MULTIINDEXUSAGE 2 1 INDEX (RANGE SCAN) OF MULTINDEX (NON-UNIQUE) SQL select * from multiindexusage where indb = 1; Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引,避免在索引列上使用函数,WHERE子句中，如

29、果索引列是函数的一部分优化器将不 使用索引而使用全表扫描 举例: 低效： SELECT FROM DEPT WHERE SAL * 12 25000; 高效: SELECT FROM DEPT WHERE SAL 25000/12;,避免使用前置通配符,WHERE子句中, 如果索引列所对应的值的第一个字符由通 配符(WILDCARD)开始, 索引将不被采用. SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO LIKE %109204421; 在这种情况下，ORACLE将使用全表扫描.,避免在索引列上使用NOT,通常，我

30、们要避免在索引列上使用NOT, NOT会产生在和在 索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就 会停止使用索引转而执行全表扫描. 举例: 低效: (这里,不使用索引) SELECT FROM DEPT WHERE DEPT_CODE NOT = 0; 高效: (这里,使用了索引) SELECT FROM DEPT WHERE DEPT_CODE 0;,避免在索引列上使用 IS NULL和IS NOT NULL,避免在索引中使用任何可以为空的列，ORACLE将无法使用该 索引 对于单列索引，如果列包含空值，索引中将不存在此记 录. 对于复合索引，如果每个列都为空，索引中同样

31、不存在此 记录.如果至少有一个列不为空，则记录存在于索引中 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记 录的A,B值为(123,null) , ORACLE将不接受下一条具有相同 A,B值（123,null）的记录(插入). 然而如果所有的索引列都为 空，ORACLE将认为整个键值为空而空不等于空. 因此你可以 插入1000条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行 空值比较将使ORACLE停用该索引. 任何在where子句中使用is null或is not null的语句优化器是 不允许使用索引的。,避免出现索引列自动转

32、换,当比较不同数据类型的数据时, ORACLE自动对列进行简单 的类型转换. 假设EMP_TYPE是一个字符类型的索引列. SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = 109204421 这个语句被ORACLE转换为: SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE TO_NUMBER(USER_NO) = 109204421 因为内部发生的类型转换, 这个索引将不会被用到!,在查询时尽量少用格式转换,如用 WHERE a.order_no = b

33、.order_no 不用 WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1),3.减少访问次数,减少访问数据库的次数,当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 ORACLE的工作量. 类比，工程实施,使用DECODE来减少处理时间,例如: SELECT COUNT(*)

34、，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%; 你可以用DECODE函数高效地得到相同结果 SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_

35、SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SAL FROM EMP WHERE ENAME LIKE SMITH%;,减少对表的查询,在含有子查询的SQL语句中,要特别注意减少对表的查询. 例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效 SELEC

36、T TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604),4. 细节上的影响,WHERE子句中的连接顺序,ORACLE采用自下而上的顺序解析WHERE子句,根据这个原 理, 当在WHERE子句中有多个表联接时，WHERE子句中排 在最后的表应当是返回行数可能最少的表，有过滤条件的子 句应放在WHERE子句中的最后。 如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子

37、句中的最后： select * from emp e,dept d where d.deptno 10 and e.deptno =30 ; 如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。 select * from emp e,dept d where e.deptno =30 and d.deptno 10 ;,WHERE子句 函数、表达式使用,最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。,Order by语句,ORDER BY语句决定了Oracle如何将返回的查询结果排序。Orde

38、r by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。,联接列,对于有联接的列，即使最后的联接值为一个静态值，优化器 是不会使用索引的。 select * from employss where first_name|last_name =Beill Cliton; 系统优化器对基

39、于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写，Oracle系统就可以采用基 于last_name创建的索引。 select * from employee where first_name =Beill and last_name =Cliton;,带通配符（%）的like语句,通配符（%）在搜寻词首出现，Oracle系统不使用 last_name的索引。 select * from employee where last_name like %cliton%; 在很多情况下可能无法避免这种情况，但是一定要心中有底 ，通配符如此使用会降低查询速度。然而当通配符出现在

40、字 符串其他位置时，优化器就能利用索引。在下面的查询中索 引得到了使用： select * from employee where last_name like c%;,用Where子句替换HAVING子句,避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果 集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限 制记录的数目,那就能减少这方面的开销. 例如: 低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY AND

41、REGION != PERTH 高效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGION 顺序 WHERE GROUP HAVING,用NOT EXISTS 替代 NOT IN,在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况 下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS 来代替NOT IN，尽管二

42、者都使用了NOT（不能使用索引而降低速度）， NOT EXISTS要比NOT IN查询效率更高。 例如: 语句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); 语句2 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 2要比1的执行性能好很多。 因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没

43、有用到emp的index， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。,用索引提高效率,索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使 用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表 扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以 提高效率. 另一个使用索引的好处是,它提供了主键(primary key) 的唯一性验证。 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小 表时,使用索引同样能提高效率. 虽然使用索引能得到查询

44、效率的提 高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要 定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也 会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为 此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理, 那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引 是有必要的。,避免在索引列上使用计算,WHERE子句中，如果索引列是函数的一部分优化器将不 使用索引而使用全表扫描 低效： SELECT FROM DEPT WHERE SAL * 12 25000; 高效: SELECT FROM DEPT

45、 WHERE SAL 25000/12;,用= 替代 ,如果DEPTNO上有一个索引。 高效: SELECT * FROM EMP WHERE DEPTNO =4 低效: SELECT * FROM EMP WHERE DEPTNO 3,通过使用=、=等，避免使用NOT命令,例子： select * from employee where salary 3000; 对这个查询，可以改写为不使用NOT： select * from employee where salary3000; 虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列

46、使用索引，而第一种查询则不能使用索引。,如果有其它办法，不要使用子查询。,外部联接+的用法,外部联接+按其在=的左边或右边分左联接和右联接。若不带+运算符的表中的一个行不直接匹配于带+预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。利用外部联接+，可以替代效率十分低下的 not in 运算，大大提高运行速度。例如，下面这条命令执行起来很慢： select a.empno from emp a where a.empno not in (select empno from emp1 where job=SALE); 利用外部联接，改写命令如下: select a.empno f

47、rom emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job=SALE; 这样运行速度明显提高.,尽量多使用COMMIT,事务是消耗资源的，大事务还容易引起死锁 COMMIT所释放的资源: 回滚段上用于恢复数据的信息. 被程序语句获得的锁 redo log buffer 中的空间 ORACLE为管理上述3种资源中的内部花费,用TRUNCATE替代DELETE,当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有 COMMIT事务,ORACLE

48、会将数据恢复到删除之前的状态(准 确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的 信息.当命令运行后,数据不能被恢复.因此很少的资源被调用, 执行时间也会很短.,计算记录条数,和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可 以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO),比如有的表PHONE_NO字段是CHAR型,而且创建有索引， 但在WHERE条件中忘记了加引号，就不会用到索引。 WHERE PHONE_NOWHERE PHONE_NO=139202020

49、22,字符型字段的引号,优化EXPORT和IMPORT,使用较大的BUFFER(比如10MB , 10,240,000)可以提高 EXPORT和IMPORT的速度; ORACLE将尽可能地获取你所指定的内存大小,即使在内存 不满足,也不会报错.这个值至少要和表中最大的列相当,否则 列值会被截断;,* 优化 Tools *,SQL 语句的执行步骤,语法分析 ，分析语句的语法是否符合规范，衡量语句中各表达式的意义。 语义分析 ，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。 视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。 表达式转换， 将复杂的 SQL 表达式转换为较简单

50、的等效连接表达式。 选择优化器，不同的优化器一般产生不同的“执行计划” 选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。 选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。 选择数据的搜索路径， 根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。 运行“执行计划”,优化器与执行计划,Oracle在执行一个SQL之前,首先要分析一下语句的执行计 划,然后再按执行计划去执行。分析语句的执行计划的工作 是由优化器(Optimizer)来完成的 Oracle的优化器共有两种的优化方式

51、,即基于规则的优化方 式(Rule-Based Optimization,简称为RBO)和基于代价的优 化方式(Cost-Based Optimization,简称为CBO)。 A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定 的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走 索引。 B、CBO方式：是看语句的代价(Cost)了,这里的代价主要指Cpu和内存 。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息， 很多的时侯过期统计信息会令优化器做出一个错误的执行计划在 Oracle8及以后的版本,Oracle推荐用CBO的方式。 在Ora

52、cle10g中,取消了RBO的支持。,优化器与执行计划,Rule:即走基于规则的方式 Choose:默认的情况下Oracle用的便是这种方式。当一个表或或索引有 统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小, 而且相应的列有索引时,那么就走索引,走RBO的方式 First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息 时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时 间 All Rows:all_rows是oracle优化器默认的模式,它将选择一种在最短时 间内返回所有数据的执行计划,它将基于整体成本的考虑. first_rows

53、_n:first_rows_n是根据成本而不是基于硬编码的规则来选择 执行计划.n可以是1,10,100,1000或者直接用first_rows(n) hint指定任意 正数.这里的n是我们想获取结果集的前n条记录,这种需求在很多分页语 句的需求中会碰到.,用EXPLAIN PLAN 分析SQL语句,EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至 可以在不执行SQL的情况下分析语句. 通过分析,我们就可以 知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫 描或全表扫描)以及使用到的索引名称. 你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PL

54、AN分析的结果是用缩进的格式排列的, 最内部 的操作将被最先解读, 如果两个操作处于同一层中,带有最小 操作号的将被首先执行. NESTED LOOP是少数不按照上述规则处理的操作, 正确的 执行路径是检查对NESTED LOOP提供数据的操作,其中操 作号最小的将被最先处理.,Autotrace 解读,Current mode:对于修改的数据从数据段中读 Read-consistent mode: 读一致性模式 Physical block:物理块（如8192字节） Recursive calls:嵌套调用次数,使用TKPROF 工具,SQL trace 工具收集正在执行的SQL的性能状态数

55、据并记录到 一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解 析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你 的系统. 设置SQL TRACE在会话级别: 有效 ALTER SESSION SET SQL_TRACE TRUE 设置SQL TRACE 在整个数据库有效, 你必须将SQL_TRACE 参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了 生成跟踪文件的目录 再使用TKPROF对TRACE文件进行分析 分析结果更加准确、清楚,在SQLPLUS 配置AUTOTRACE,在SQLPLUS 配置AUTOTRACE,1、 首先创建PLUST

56、RACE角色并且赋给public： Sql $ORACLE_HOME/sqlplus/admin/plustrce.sql 2、 赋权限给用户 Sql grant plustrace to public（预赋权的用户名）; 3、以SYSTEM用户创建PLAN_TABLE表 Sql $ORACLE_HOME/rdbms/admin/utlxplan.sql Sql create public synonym plan_table for plan_table; Sql grant all on plan_table to public; 在每个用户下设置AUTOTRACE可显示其执行计划。,SQ

57、L select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in (ACCOUNTING,RESEARCH,SALES,OPERATIONS); Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF EMP 3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT 4 3 INDEX (UNIQUE SCAN) OF PK_DEPT

58、(UNIQUE) 最起码要解决全表扫描问题 改变where条件的次序一般没有用,优化器与执行计划,目录,优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项,性能调整综述,谁来调整系统? 什么时候调整? 建立有效调整的目标 在设计和开发时的调整,谁来调整系统,应用设计人员必须传达应用系统的设计，使得每个人都清楚应用中的数据流动. 应用开发人员必须传达他们选择的实现策略，使得语句调整的过程中能快速、容易地识别有问题的应用模块和可疑的SQL语句. 数据库管理人员必须仔细地监控系统活动并提供它们的资料，使得异常的系统性能可被快速得识别和纠正. 硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员能有效地设计和管理系统。,谁来调整系统,与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传达了系统的特性并提供了它们的资料，调整就能相对的容易和更快一些。 事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据库管理员很少有合适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实施阶段才介入数据库，这就给调整工作带来许多负面的影响，因为在设计阶段的缺陷是不能通过DBA的调整而得以解决，而设计阶段的缺陷往往对数据库性能造成极大的影响。 在真正成熟的开发环境