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

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调优应在

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

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

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

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

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

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

9、nt 分析语句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 a Query 取查询出来的行9、Close the Cursor 封锁游标 .为什么要bind variables?字符级的比较:SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 与SE

10、LECT * FROM USER_FILES 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 = 32021484095.绑

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

12、 :n_var1, :n_var2;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语句，提高系统的可用性。在多数情况下，Or

14、acle运用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。假设在SQL语句的where子句中写的SQL代码不合理，就会呵斥优化器删去索引而运用全表扫描，普通就这种SQL语句就是所谓的劣质SQL语句。.sql 语句的编写原那么和优化 在编写SQL语句时我们应清楚优化器根据何种原那么来运用索引，这有助于写出高性能的SQL语句。SQL语句的编写原那么和SQL语句的优化，请跟我一同窗习以下几方面：.Tunning Tip的各个方面1.不要让Oracle做得太多；2.给优化器更明确的命令； 3.减少访问次数；4.细节上的影响；.1.不要让Oracle做得太多.防止复杂的多表关联select f

15、rom user_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 )?很难优化，随着数据量的添加性能的风险很大。.防止运用 * 当他想在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)操作被执行而且它只能在前面的嵌入排序终了后才干开场执行. 嵌入的排序的深度会大

17、大影响查询的效率.通常, 带有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)当S

18、QL语句需求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

19、TRAN_DATE = 31-DEC-95 UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95.2. 给优化器更明确的命令.自动选择索引假设表中有两个以上包括两个索引，其中有一个独一性索引，而其他是非独一性在这种情况下，ORACLE将运用独一性索引而完全忽略非唯一性索引举例:SELECT ENAMEFROM EMPWHERE EMPNO = 2326 AND DEPTNO = 20 ;这里，只需EMPNO上的索引是独一性的，所以EMPNO索引将用来检索记录TABLE

20、 ACCESS BY ROWID ON EMP INDEX UNIQUE 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

21、.SQL set autotrace traceonlySQL select * 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 SEL

22、ECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明显, 当仅援用索引的第二个列时,优化器运用了全表扫描而忽略了索引.防止在索引列上运用函数WHERE子句中，假设索引列是函数的一部分优化器将不运用索引而运用全表扫描举例:低效：SELECT FROM DEPTWHERE SAL * 12 25000;高效:SELECT FROM DEPTWHERE SAL 25000/12;.防止运用前置通配符WHERE子句中, 假设索引列所对应的值的第一个字符由通配符(WILDCARD)开场, 索引将不被采用.

23、 SELECT USER_NO,USER_NAME,ADDRESSFROM 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;.防

24、止在索引列上运用 IS NULL和IS NOT NULL防止在索引中运用任何可以为空的列，ORACLE将无法运用该索引 对于单列索引，假设列包含空值，索引中将不存在此记录. 对于复合索引，假设每个列都为空，索引中同样不存在此记录.假设至少有一个列不为空，那么记录存在于索引中假设独一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有一样A,B值123,null的记录(插入). 然而假设一切的索引列都为空，ORACLE将以为整个键值为空而空不等于空. 因此他可以插入1000条具有一样键值的记录,当然它们都是空!由于空值不存在于索

25、引列中,所以WHERE子句中对索引列进展空值比较将使ORACLE停用该索引.任何在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_NU

26、MBER(USER_NO) = 109204421 由于内部发生的类型转换, 这个索引将不会被用到! .在查询时尽量少用格式转换如用 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语句, 估算索引的利用率, 绑定变量 , 读数据块

27、等等. 由此可见, 减少访问数据库的次数 , 就能实践上减少ORACLE的任务量.类比，工程实施.运用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(

28、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 WHERE ENAME LIKE SMITH%;.减少对表的查询在含有子查询的SQL语句中,要特别留意减少对表的查询.例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION =

29、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) FROM TAB_COLUMNS WHERE VERSION = 604) .4. 细节上的影响.WHERE子句中的衔接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表该当是前往行数能够最少的表，有过滤条件的

30、子句应放在WHERE子句中的最后。如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大减少查询范围，那么将最具有选择性部分放在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子句中运用函或表达式，假设要运用的话，最好一致运用

31、一样的表达式或函数，这样便于以后运用合理的索引。.Order by语句 ORDER BY语句决议了Oracle如何将前往的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数参与列中象联接或者附加等。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。处理这个问题的方法就是重写order by语句以运用索引，也可以为所运用的列建立另外一个索引，同时应绝对防止在order by子句中运用表达式。.联接列 对于有联接的列，即使最后的联接值为一个静态值，优化器是不会运用索引的。 sele

32、ct * from employss where first_name|last_name =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

33、%cliton%; 在很多情况下能够无法防止这种情况，但是一定要心中有底，通配符如此运用会降低查询速度。然而当通配符出如今字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了运用： select * from employee where last_name like c%; .用Where子句交换HAVING子句防止运用HAVING子句, HAVING 只会在检索出一切记录之后才对结果集进展过滤. 这个处置需求排序,总计等操作. 假设能经过WHERE子句限制记录的数目,那就能减少这方面的开销.例如: 低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCA

34、TION 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 顺序 WHERE GROUP HAVING.用NOT EXISTS 替代 NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (由于它对子查询中的表执行了一个全表遍历).

35、运用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 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 2要比1的执行性能

36、好很多。由于1中对emp进展了full table scan,这是很浪费时间的操作。而且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

38、 * 12 25000; 高效: SELECT FROM DEPT 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; 虽然这两种查询的结果一样，但是第二种查询方案会比第一种

39、查询方案更快些。第二种查询允许Oracle对salary列运用索引，而第一种查询那么不能运用索引。.假设有其它方法，不要运用子查询。.外部联接+的用法 外部联接+按其在=的左边或右边分左联接和右联接。假设不带+运算符的表中的一个行不直接匹配于带+预算符的表中的任何行，那么前者的行与后者中的一个空行相匹配并被前往。利用外部联接+，可以替代效率非常低下的 not in 运算，大大提高运转速度。例如，下面这条命令执行起来很慢：select a.empno from emp a where a.empno not in(select empno from emp1 where job=SALE);利用

40、外部联接，改写命令如下: select a.empno from 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 ) 用来存放可以被恢复

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

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

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

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

45、,取消了RBO的支持。.优化器与执行方案Rule:即走基于规那么的方式Choose:默许的情况下Oracle用的便是这种方式。当一个表或或索引有统计信息,那么走CBO的方式,假设表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式前往查询的最先的几行,从总体上减少了呼应时间All Rows:all_rows是oracle优化器默许的方式,它将选择一种在最短时间内前往一切数据的执行方案,它将基于整体本钱的思索. first_rows_n:first_rows_

46、n是根据本钱而不是基于硬编码的规那么来选择执行方案.n可以是1,10,100,1000或者直接用first_rows(n) hint指定恣意正数.这里的n是我们想获取结果集的前n条记录,这种需求在很多分页语句的需求中会碰到. .用EXPLAIN PLAN 分析SQL语句EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 经过分析,我们就可以知道ORACLE是怎样样衔接表,运用什么方式扫描表(索引扫描或全表扫描)以及运用到的索引称号.他需求按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式陈列的, 最

47、内部的操作将被最先解读, 假设两个操作处于同一层中,带有最小操作号的将被首先执行.NESTED LOOP是少数不按照上述规那么处置的操作, 正确的执行途径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处置.Autotrace 解读Current mode:对于修正的数据从数据段中读Read-consistent mode: 读一致性方式Physical block:物理块如8192字节Recursive calls:嵌套调用次数.运用TKPROF 工具SQL trace 工具搜集正在执行的SQL的性能形状数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例

48、如解析次数.执行次数,CPU运用时间等.这些数据将可以用来优化他的系统.设置SQL TRACE在会话级别: 有效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 EXPL

49、AIN仅仅显示优化器执行计划的AUTOTRACE报告SET AUTOTRACE ON STATISTICS仅仅显示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 t

50、o 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可显示其执行方案。.优化器与执行方案SQL select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in (ACCOUNT

51、ING,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) 最起码要处理全表扫描问题改动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%以上的任务。.在设计和开发时调整良好设计的系统可防止在运用生命周期中产生

59、性能问题。系统设计人员和运用开发人员必需了解Oracle的查询处置机制以便写出高效的SQL语句。“有效的运用设计讨论了他的系统中各种可用的配置，以及每种配置更适宜哪种类型的运用。“优化器讨论了Oracle的查询优化器，以及如何写语句以获取最快的结果。 .在设计和开发时调整当设计他的系统时，运用以下优化性能的准那么： - 消除客户机效力器运用中不用要的网络传输。运用存储过程。 - 运用适宜他系统的Oracle效力器选件例如，并行查询或分布式数据库。 - 除非他的运用有特殊的需求，否那么运用缺省的Oracle锁。 - 利用数据库记住运用模块，以便能以每个模块为根底来追踪性能。 - 选择他的数据块的

60、最正确大小。 - 原那么上来说大一些的性能较好。 - 分布他的数据，使得一个节点运用的数据本地存贮在该节点中。 .目录 优化根底知识 性能调整综述 有效的运用设计 SQL语句的处置过程 Oracle的优化器 Oracle的执行方案 本卷须知.有效的运用设计将最常用的运用分为2种类型：联机事务处置类型(OLTP)，决策支持系统(DSS)。 联机事务处置(OLTP) 该类型的运用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供应成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主要目的是可用性、速度、并发性和可恢复性。