ORACLESQL性能优化(这个很全的)课件

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

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

3、 析和优化CBO上。调优领域调优领域：应用程序级调优： * SQL语句调优 * 管理变化调优2. 实例级调优 * 内存 * 数据结构 * 实例配置3. 操作系统交互 * I/O * SWAP * Parameters本课程内容只讲解讨论应用程序级：Oracle SQL语句调优及管理变化调优调整的方法调整业务功能调整数据设计调整流程设计调整SQL语句调整物理结构调整内存分配调整I/O调整内存竞争调整操作系统不同调整产生相应性能收益调整的角色SQL语句优化是提高性能的重要环节开发人员不能只注重功能的实现，不管性能如何开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法必

4、需遵守既定的开发规范未经过SQL语句优化的模块不要上线SQL语句优化的过程定位有问题的语句检查执行计划检查执行过程中优化器的统计信息分析相关表的记录数、索引情况改写SQL语句、使用HINT、调整索引、表分析有些SQL语句不具备优化的可能，需要优化处理方式达到最佳执行计划什么是好的SQL语句？尽量简单，模块化易读、易维护节省资源内存CPU扫描的数据块要少少排序不造成死锁SQL共享原理 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORA

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

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

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

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

9、 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 a Query 取查询出来的行9、Close the Cursor 关闭游标 为什么要bind variables?字符级的比较:SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 与SELECT * FROM USER_F

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

11、语句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;

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

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

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

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

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

17、NTERSECT的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两个查询结果集合时,这两个结果集合会以UN

18、ION-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

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

20、IQUE SCAN ON EMP_NO_IDX至少要包含组合索引的第一列如果索引是建立在多个列上, 只有在它的第一个列(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 s

21、elect * from multiindexusage where 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

22、0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引避免在索引列上使用函数WHERE子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描举例:低效：SELECT FROM DEPTWHERE SAL * 12 25000;高效:SELECT FROM DEPTWHERE SAL 25000/12;避免使用前置通配符WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用. SELECT USER_NO,USER_NAME,ADDRESSFR

23、OM USER_FILESWHERE USER_NO LIKE %109204421; 在这种情况下，ORACLE将使用全表扫描.避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.举例: 低效: (这里,不使用索引) SELECT FROM DEPT WHERE DEPT_CODE NOT = 0; 高效: (这里,使用了索引) SELECT FROM DEPT WHERE DEPT_CODE 0;避免在索引列上使用 IS NULL和IS NOT NULL避免在索引中

24、使用任何可以为空的列，ORACLE将无法使用该索引 对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停

25、用该索引.任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。避免出现索引列自动转换当比较不同数据类型的数据时, 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 因为内部发生的类

26、型转换, 这个索引将不会被用到! 在查询时尽量少用格式转换如用 WHERE a.order_no = b.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的

27、工作量.类比，工程实施使用DECODE来减少处理时间例如: SELECT COUNT(*)，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_COUN

28、T, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SALFROM 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 TA

29、B_COLUMNS WHERE VERSION = 604) 高效 SELECT 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表查到的数据比较少或该表的过滤条件

30、比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：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

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

32、me =Beill Cliton; 系统优化器对基于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%; 在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符

33、如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用： 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

34、 != SYDNEY AND 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来代

35、替NOT IN，尽管二者都使用了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,这是很浪费时间的操

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

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

38、 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列使用索引，而第一种查询则不能使用

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

40、re 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会将数据恢复到删除之前的状态(准确地说是恢复到执行删

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

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

43、CLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。 选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。 选择数据的搜索路径， 根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。 运行“执行计划”优化器与执行计划Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的 Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化

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

45、e用的便是这种方式。当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间All Rows:all_rows是oracle优化器默认的模式,它将选择一种在最短时间内返回所有数据的执行计划,它将基于整体成本的考虑. first_rows_n:first_rows_n是根据成本而不是基于硬编码的规则来选择执行计划.n可以是1,10,100,1000或者直接用first_r

46、ows(n) hint指定任意正数.这里的n是我们想获取结果集的前n条记录,这种需求在很多分页语句的需求中会碰到. 用EXPLAIN PLAN 分析SQL语句EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.NESTED LOOP是少数不

47、按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.Autotrace 解读Current mode:对于修改的数据从数据段中读Read-consistent mode: 读一致性模式Physical block:物理块（如8192字节）Recursive calls:嵌套调用次数使用TKPROF 工具SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.设置SQL TRACE在会话级别: 有效

48、ALTER SESSION SET SQL_TRACE TRUE设置SQL TRACE 在整个数据库有效, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录再使用TKPROF对TRACE文件进行分析分析结果更加准确、清楚在SQLPLUS 配置AUTOTRACEAUTOTRACE 参数解 释SET AUTOTRACE OFF不能获得AUTOTRACE报告. 这是默认的.SET AUTOTRACE ON EXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SET AUTOTRACE ON STATISTICS仅仅显示

49、SQL语句执行的统计结果的AUTOTRACE报告SET AUTOTRACE ON包括上面两项内容的AUTOTRACE报告SET AUTOTRACE TRACEONLY与SET AUTOTRACE ON类似,所有的统计和数据都在，但不可以打印在SQLPLUS 配置AUTOTRACE1、 首先创建PLUSTRACE角色并且赋给public：Sql $ORACLE_HOME/sqlplus/admin/plustrce.sql2、 赋权限给用户Sql grant plustrace to public（预赋权的用户名）;3、以SYSTEM用户创建PLAN_TABLE表 Sql $ORACLE_HOM

50、E/rdbms/admin/utlxplan.sqlSql create public synonym plan_table for plan_table;Sql grant all on plan_table to public;在每个用户下设置AUTOTRACE可显示其执行计划。优化器与执行计划SQL select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in (ACCOUNTING,RESEARCH,SALES,OPERATIONS);Execution Plan- 0 SELECT S

51、TATEMENT 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) 最起码要解决全表扫描问题改变where条件的次序一般没有用目录 优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项性能调整综述谁来调整系统? 什么时候调整? 建立有效调整的目标 在设计和开发时的调整 谁来调整系

52、统应用设计人员必须传达应用系统的设计，使得每个人都清楚应用中的数据流动. 应用开发人员必须传达他们选择的实现策略，使得语句调整的过程中能快速、容易地识别有问题的应用模块和可疑的SQL语句. 数据库管理人员必须仔细地监控系统活动并提供它们的资料，使得异常的系统性能可被快速得识别和纠正. 硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员能有效地设计和管理系统。 谁来调整系统与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传达了系统的特性并提供了它们的资料，调整就能相对的容易和更快一些。 事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据库

53、管理员很少有合适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实施阶段才介入数据库，这就给调整工作带来许多负面的影响，因为在设计阶段的缺陷是不能通过DBA的调整而得以解决，而设计阶段的缺陷往往对数据库性能造成极大的影响。 在真正成熟的开发环境下，开发人员作为纯代码编写人员时，对性能的影响最小，此时大部分的工作应由应用设计人员完成，而且数据库管理员往往在前期的需求管理阶段就介入，为设计人员提供必要的技术支持。 调整并不是数据库管理员的专利，相反大部分应该是设计人员和开发人员的工作，这就需要设计人员和开发人员具体必要的数据库知识，这样才能组成一个高效的团队，然而事实上往往并非如此。 谁

54、来调整系统与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传达了系统的特性并提供了它们的资料，调整就能相对的容易和更快一些。 事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据库管理员很少有合适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实施阶段才介入数据库，这就给调整工作带来许多负面的影响，因为在设计阶段的缺陷是不能通过DBA的调整而得以解决，而设计阶段的缺陷往往对数据库性能造成极大的影响。 在真正成熟的开发环境下，开发人员作为纯代码编写人员时，对性能的影响最小，此时大部分的工作应由应用设计人员完成，而且数据库管理员往往在前期的需求管理阶段就介入

55、，为设计人员提供必要的技术支持。 调整并不是数据库管理员的专利，相反大部分应该是设计人员和开发人员的工作，这就需要设计人员和开发人员具体必要的数据库知识，这样才能组成一个高效的团队，然而事实上往往并非如此。 什么时候调整系统多数人认为当用户感觉性能差时才进行调整，这对调整过程中使用某些最有效的调整策略来说往往是太迟了。此时，如果你不愿意重新设计应用的话，你只能通过重新分配内存(调整SGA)和调整I/O的办法或多或少地提高性能。Oracle提供了许多特性，这些特性只有应用到正确地设计的系统中时才能够很大地提高性能。 应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间，应用设计人

56、员应考虑哪些Oracle 特性可以对系统有好处，并使用这些特性。 通过良好的系统设计，你就可以在应用的生命周期中消除性能调整的代价和挫折。下图说明在应用的生命周期中调整的相对代价和收益，最有效的调整 时间是在设计阶段。在设计期间的调整能以最低的代价给你最大的收益。 什么时候调整系统图: 在应用生命周期中调整的代价 什么时候调整系统图: 在应用生命周期中调整的收益 调整的目标不管正在设计或维护系统，应该建立专门的性能目标，它使你知道何时要作调整。调整你的系统的最有效方法如下： 当设计系统时考虑性能 调整操作系统的硬件和软件 识别性能瓶颈 确定问题的原因 采取纠正的动作 当你设计系统时，制定专门的

57、目标；例如，响应时间小于秒。当应用不能满足此目标时，识别造成变慢的瓶颈（例如，I/O竞争），确定原因，采取纠正动作。在开发期间，你应测试应用研究，确定在采取应用之前是否满足设计的性能目标。 调整的目标调整通常是一系列开销。一旦确定了瓶颈，可能要牺牲一些其它方面的指标来达到所要的结果。例如，如果I/O有问题，你可能需要更多内存或磁盘。如果不可能买，你可能要限制系统的并发性，来获取所需的性能。如果你已经明确地定义了性能的目标，那用什么来交换高性能的决策就变的很容易的，因为已经确定了哪些方面是最重要的，如果我的目标为高性能，可能牺牲一些空间资源。 随着应用的越来越庞大，硬件性能的提高，全面的调整应用

58、逐渐变成代价高昂的行为，在这样情况下，要取得最大的投入/效率之比，较好的办法是调整应用的关键部分，使其达到比较高的性能，这样从总体上来说，整个系统的性能也是比较高的。这也就是有名的20/80原则，调整应用的20%(关键部分)，能解决80%的问题。 SQL 调整的目标去掉不必要的大型表的全表扫描。缓存小型表的全表扫描。校验优化索引的使用。检验优化的连接技术。以上目标任务将占据SQL调整90%以上的工作。在设计和开发时调整良好设计的系统可防止在应用生命周期中产生性能问题。系统设计人员和应用开发人员必须了解Oracle的查询处理机制以便写出高效的SQL语句。“有效的应用设计”讨论了你的系统中各种可用

59、的配置，以及每种配置更适合哪种类型的应用。“优化器”讨论了Oracle的查询优化器，以及如何写语句以获取最快的结果。 在设计和开发时调整当设计你的系统时，使用下列优化性能的准则： - 消除客户机服务器应用中不必要的网络传输。使用存储过程。 - 使用适合你系统的Oracle服务器选件（例如，并行查询或分布式数据库）。 - 除非你的应用有特殊的需要，否则使用缺省的Oracle锁。 - 利用数据库记住应用模块，以便能以每个模块为基础来追踪性能。 - 选择你的数据块的最佳大小。 - 原则上来说大一些的性能较好。 - 分布你的数据，使得一个节点使用的数据本地存贮在该节点中。 目录 优化基础知识 性能调整

60、综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项有效的应用设计将最常用的应用分为2种类型：联机事务处理类型(OLTP)，决策支持系统(DSS)。 联机事务处理(OLTP) 该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供给成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主要目标是可用性、速度、并发性和可恢复性。 当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与cluster 表，因为这些结构会使插入和更新操作变慢。