ORACLE-SQL性能优化课件.ppt

1、1ORACLEORACLE培训-SQL-SQL性能优化2一二请在这里输入您的主要叙述内容整体概述三请在这里输入您的主要叙述内容请在这里输入您的主要叙述内容3课程主要讨论：SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步掌握SQL优化。4n 优化基础知识n 性能调整综述n 有效的应用设计n SQL语句的处理过程n Oracle的优化器n Oracle的执行计划n 注意事项56n性能管理n性能问题n调整的方法nSQL优化机制n应用的调整nSQL语句的处理过程共享SQLSQL区域SQLSQL语句

2、处理的阶段共享游标SQLSQL编码标准nOracle 优化器介绍nSQL Tunning Tipsn优化Tools7 尽早开始 设立合适目标 边调整边监控 相关人员进行合作 及时处理过程中发生的意外和变化 80/2080/20定律8随着软件技术的不断发展，系统性能越来越重要。系统性能主要用：系统响应时间和并发性来衡量。造成SQL语句性能不佳大致有两个原因：开发人员只关注查询结果的正确性，忽视查询语句的效率。开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。*前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。SQL调优是一个系统工程，熟悉SQ

3、L语法、掌握各种内嵌函数、分 析函数的用法只是编写高效SQL的必要条件。*后者从分析SQL语句执行原理入手，指出SQL调优应在优化SQL解 析和优化CBO上。9调优领域：应用程序级调优：*SQL语句调优 *管理变化调优2.实例级调优 *内存 *数据结构 *实例配置3.操作系统交互 *I/O *SWAP *Parameters本课程内容只讲解讨论应用程序级：Oracle SQL语句调优及管理变化调优10 调整业务功能 调整数据设计 调整流程设计 调整SQL语句 调整物理结构 调整内存分配 调整I/O 调整内存竞争 调整操作系统111213n开发人员不能只注重功能的实现，不管性能如何n开发人员不能

4、把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法n必需遵守既定的开发规范n未经过SQL语句优化的模块不要上线14n定位有问题的语句n检查执行计划n检查执行过程中优化器的统计信息n分析相关表的记录数、索引情况n改写SQL语句、使用HINT、调整索引、表分析n有些SQL语句不具备优化的可能，需要优化处理方式n达到最佳执行计划15n尽量简单，模块化n易读、易维护n节省资源内存CPU扫描的数据块要少少排序n不造成死锁16 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游

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

6、并节省了内存的使用。可惜的是，Oracle只对简单的表提供高速缓冲(cache bufferiIlg)，这个功能并不适用于多表连接查询。数据库管理员必须在启动参数文件中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle会首先在这块内存中查找相同的语句。18当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）两个语句所指的对象必须完全相同（同义词与表是不同的对象）两个SQL语句中必须使用相同的名字的绑定变量(bind variables)19注意：Oracle对两者采取的是一种严

7、格匹配策略，要达成共享。SQL语句必须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个条件：字符级的比较。当前被执行的语句和共享池中的语句必须完全相同。例如：SELECT*FROM ATABLE；和下面每一个SQL语句都不同：SELECT *from ATABLESelect *From Atable；语句所指对象必须完全相同 即两条SQL语句操作的数据库对象必须同一。语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变量以相同的值：第一组 select pin，name from people whe

8、re 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.ov_jnd；20共享SQL区域21Sql 处理过程22SQL PARSE与共享SQL语句当一个Oracle实例接收一条sql后1、Create a Cursor 创建游标2、Parse the Statement 分析语句3、Describe Results of a

9、Query 描述查询的结果集4、Define Output of a Query 定义查询的输出数据5、Bind Any Variables 绑定变量6、Parallelize the Statement 并行执行语句7、Run the Statement 运行语句8、Fetch Rows of a Query 取查询出来的行9、Close the Cursor 关闭游标 23为什么要bind variables?n字符级的比较:SELECT*FROM USER_FILES WHERE USER_NO=10001234;与SELECT*FROM USER_FILES WHERE USER_NO

10、=10004321;n检查：select name,executionsfrom v$db_object_cachewhere name like select*from user_files%24什么叫做重编译问题什么叫做重编译？下面这个语句每执行一次就需要在SHARE POOL 硬解析一次，一百万用户就是一百万次，消耗CPU和内存，如果业务量大，很可能导致宕库如果绑定变量，则只需要硬解析一次，重复调用即可select*from dConMsg where contract_no=3201348409513925绑定变量解决重编译问题未使用绑定变量的语句sprintf(sqlstr,inse

11、rt 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;26绑定变量的注意事项注意：1、不要使用数据库

12、级的变量绑定参数cursor_sharing来强制绑定，无论其值为 force 还是similar2、有些带 0性能优于select count(*)from tab；尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多or运算的查询，建议分成多个查询，用union all联结起来；多表查询的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从右到左，所以记录少的表放在右边。尽量多用commit语句提交事务，可以及时释放资源、解锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：alter tab

13、lecache；在Oracle中动态执行SQL，尽量用execute方式，不用dbms_sql包。41*SQL Tunning Tips*42sql 语句的编写原则和优化 n随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQLSQL语句的优化。对于大量数据，劣质SQLSQL语句和优质SQLSQL语句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQLSQL语句，提高系统的可用性。n在多数情况下，OracleOracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。如果在SQL

14、SQL语句的wherewhere子句中写的SQLSQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQLSQL语句就是所谓的劣质SQLSQL语句。43sql 语句的编写原则和优化 n在编写SQLSQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQLSQL语句。nSQLSQL语句的编写原则和SQLSQL语句的优化，请跟我一起学习以下几方面：44Tunning Tip的各个方面1.不要让Oracle做得太多；2.给优化器更明确的命令；3.减少访问次数；4.细节上的影响；451.不要让Oracle做得太多46避免复杂的多表关联select from user_

15、files uf,df_money_files dm,cw_charge_record ccwhere uf.user_no=dm.user_noand dm.user_no=cc.user_noand and not exists(select)?很难优化，随着数据量的增加性能的风险很大。47避免使用 *当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用*是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将*依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间；只提取你所要使用的列；使用别名能够加快解析

16、速度；48避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUP BY子句,GROUP BY会触发嵌入排序(NESTED SORT);这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.通常,带有UNION,MINUS,INTER

17、SECT的SQL语句都可以用其他方式重写.49例如:低效: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);用EXISTS替换DISTINCT50用UNION-ALL 替换UNION(if possible)当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式

18、被合并,然后在输出最终结果前进行排序.举例:低效：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

19、_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE=31-DEC-95512.给优化器更明确的命令52自动选择索引如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引举例:SELECT ENAMEFROM EMPWHERE EMPNO=2326 AND DEPTNO =20;这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX53至少

20、要包含组合索引的第一列如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.SQL create table multiindexusage(inda number,indb number,descr varchar2(10);Table created.SQL create index multindex on multiindexusage(inda,indb);Index created.SQL set autotrace traceonlySQL select*from multiindexusage where

21、inda=1;Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE 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 很明显,当仅引用索

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

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

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

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

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

27、NAME 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_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL)D0030_SALFROM EMP

28、WHERE ENAME LIKE SMITH%;63减少对表的查询在含有子查询的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)高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER)=(SELECT TAB_NAME,DB_VER)

29、FROM TAB_COLUMNS WHERE VERSION=604)644.细节上的影响65WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。n如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：select*from emp e,dept d where d.deptno 10 and e.deptno=30;n如果dept表返回的记录数较多

30、的话，上面的查询语句会比下面的查询语句响应快得多。select*from emp e,dept d where e.deptno=30 and d.deptno 10;66WHERE子句 函数、表达式使用n最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。67Order by语句 nORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。n仔细检查order

31、 by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。68联接列 对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。select*from employss where first_name|last_name=Beill Cliton;系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。select*from employee where

32、first_name=Beill and last_name=Cliton;69带通配符（%）的like语句 通配符（%）在搜寻词首出现，Oracle系统不使用last_name的索引。select*from employee where last_name like%cliton%;在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：select*from employee where last_name like c%;70用Where子句替换HAVING子句避免使用HAV

33、ING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION REGION!=SYDNEY AND REGION!=PERTH 高效 SELECT REGION，AVG(LOG_SIZE)FROM LOCATION WHERE REGION REGION!=SYDNEY AND REGION!=PERTH GROUP BY REGION 顺序

34、 WHERE GROUP HAVING71用NOT EXISTS 替代 NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并.无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。例如:语句1 SELECT dname,deptno FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp);语句2 SE

35、LECT dname,deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno=emp.deptno);2 2要比1 1的执行性能好很多。因为1 1中对empemp进行了full table scan,full table scan,这是很浪费时间的操作。而且1 1中没有用到empemp的indexindex，因为没有wherewhere子句。而2 2中的语句对empemp进行的是缩小范围的查询。72用索引提高效率索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-t

36、ree结构.通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O.因

37、为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。73避免在索引列上使用计算WHERE子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描 低效：SELECT FROM DEPT WHERE SAL*12 25000;高效:SELECT FROM DEPT WHERE SAL 25000/12;74用=替代 如果DEPTNO上有一个索引。高效:SELECT*FROM EMP WHERE DEPTNO=4 低效:SELECT*FROM EMP WHERE DEPTNO 375通过使用=、=等，避免使用NOT命令n例子：select*

38、from employee where salary 3000;n对这个查询，可以改写为不使用NOT：select*from employee where salary3000;虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。76如果有其它办法，不要使用子查询。77外部联接+的用法 n外部联接+按其在=的左边或右边分左联接和右联接。若不带+运算符的表中的一个行不直接匹配于带+预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。利用外部联接+，可以替代效率十分低下的 not in

39、not in 运算，大大提高运行速度。例如，下面这条命令执行起来很慢：select a.empno from emp a where a.empno not in(select empno from emp1 where job=SALE);n利用外部联接，改写命令如下:select a.empno from emp a,emp1 b where a.empno=b.empno(+)and b.empno is null and b.job=SALE;这样运行速度明显提高.78尽量多使用COMMIT 事务是消耗资源的，大事务还容易引起死锁 COMMIT所释放的资源:回滚段上用于恢复数据的信息.

40、被程序语句获得的锁 redo log buffer 中的空间 ORACLE为管理上述3种资源中的内部花费79用TRUNCATE替代DELETE当删除表中的记录时,在通常情况下,回滚段(rollback segments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.80计算记录条数和一般的观点相反,count(*)比count(1)稍快,当然如果可以通过索引检索,对索引列的计

41、数仍旧是最快的.例如 COUNT(EMPNO)81比如有的表PHONE_NO字段是CHAR型,而且创建有索引，但在WHERE条件中忘记了加引号，就不会用到索引。WHERE PHONE_NO=13920202022WHERE PHONE_NO=13920202022字符型字段的引号82优化EXPORT和IMPORT使用较大的BUFFER(比如10MB,10,240,000)可以提高EXPORT和IMPORT的速度;ORACLE将尽可能地获取你所指定的内存大小,即使在内存不满足,也不会报错.这个值至少要和表中最大的列相当,否则列值会被截断;83*优化 Tools*84SQL 语句的执行步骤语法分析

42、，分析语句的语法是否符合规范，衡量语句中各表达式的意义。语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。表达式转换，将复杂的 SQL 表达式转换为较简单的等效连接表达式。选择优化器，不同的优化器一般产生不同的“执行计划”选择连接方式，ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。选择连接顺序，对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。运行“执行计划”

43、85优化器与执行计划Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的 Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定 的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。B、CBO方式：是看语句的代价(Cost)了,这里的代价主要指Cpu

44、和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息，很多的时侯过期统计信息会令优化器做出一个错误的执行计划在Oracle8及以后的版本,Oracle推荐用CBO的方式。在Oracle10g中,取消了RBO的支持。86优化器与执行计划Rule:即走基于规则的方式Choose:默认的情况下Oracle用的便是这种方式。当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总

45、体上减少了响应时间All Rows:all_rows是oracle优化器默认的模式,它将选择一种在最短时间内返回所有数据的执行计划,它将基于整体成本的考虑.first_rows_n:first_rows_n是根据成本而不是基于硬编码的规则来选择执行计划.n可以是1,10,100,1000或者直接用first_rows(n)hint指定任意正数.这里的n是我们想获取结果集的前n条记录,这种需求在很多分页语句的需求中会碰到.87用EXPLAIN PLAN 分析SQL语句EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句.通过分析,我们就可以知道OR

46、ACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.你需要按照从里到外,从上到下的次序解读分析的结果.EXPLAIN PLAN分析的结果是用缩进的格式排列的,最内部的操作将被最先解读,如果两个操作处于同一层中,带有最小操作号的将被首先执行.NESTED LOOP是少数不按照上述规则处理的操作,正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.88Autotrace 解读Current mode:对于修改的数据从数据段中读Read-consistent mode:读一致性模式Physical block:物理块（如8192

47、字节）Recursive calls:嵌套调用次数89使用TKPROF 工具SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中.这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.设置SQL TRACE在会话级别:有效ALTER SESSION SET SQL_TRACE TRUE设置SQL TRACE 在整个数据库有效,你必须将SQL_TRACE参数在init.ora中设为TRUE,USER_DUMP_DEST参数说明了生成跟踪文件的目录再使用TKPROF对TRACE文件进行分析分析结果更加准确、清楚90在

48、SQLPLUS 配置AUTOTRACEAUTOTRACE 参数解 释SET AUTOTRACE OFF 不能获得AUTOTRACE报告.这是默认的.SET AUTOTRACE ON EXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SET AUTOTRACE ON STATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SET AUTOTRACE ON 包括上面两项内容的AUTOTRACE报告SET AUTOTRACE TRACEONLY与SET AUTOTRACE ON类似,所有的统计和数据都在，但不可以打印91在SQLPLUS 配置AUTOTRACE1、首先创

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

50、elect 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(UNIQUE)最起码要解决