SQL及应用程序优化培训.doc

《SQL及应用程序优化培训.doc》由会员分享，可在线阅读，更多相关《SQL及应用程序优化培训.doc（19页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL及应用程序优化培训一、Oracle相关知识31.两种连接Oracle的模式32.文件类型33.存储结构34.内存结构简介35.锁46.Undo和Redo4二、SQL知识及优化经验41.SQL语句处理过程41)SQL语句处理的四个基本步骤42)SQL语句分析的四种情况52.绑定变量51)使用绑定变量的好处52)哪些是可以共享的SQL语句63)ZLHIS中使用绑定变量的方法64)ZLHIS中使用绑定变量的注意事项63.优化器71)优化器模式72)RBO与CBO的优缺点73)RBO主要规则84)CBO成本计算规则85)CBO相关知识96)Hints94.执行计划相关知识105.数据访问路径11

2、1)全表扫描112)通过RowID访问113)索引扫描114)群扫描126.表间连接方式121)嵌套循环122)散列连接133)排序合并连接134)笛卡儿连接135)反连接136)全外部连接137.分析函数148.SQL优化经验14三、SQL分析诊断方法和工具151.AutoTrace使用介绍151)创建执行计划环境152)创建AutoTrace环境163)使用AutoTrace162.SQL Trace的几种方法161)SQL_Trace162)事件跟踪163)SQL Trace的几种工具163.ZL SQLTRACE工具使用介绍174.Trace文件分析175.StatsPack查看与分析

3、简介17四、应用程序优化经验171.ZLHIS优化历程回顾与总结172.应用程序优化原则18本次培训的背景医大二院性能诊断过程中发现应用程序中存在较多SQL存在性能问题和优化空间。目前已知的反映过ZLHIS存在性能问题的用户：医大二院，遵义医院，广西容县人民医院，重庆市三院一、 Oracle相关知识仅介绍SQL调优涉及的Oracle相关知识，不要求深入理解。1. 两种连接Oracle的模式专用服务器，共享服务器2. 文件类型参数文件，跟踪文件，警告文件，数据文件，临时文件，控制文件，日志文件，密码文件，修改跟踪文件，数据泵文件，DMP文件，闪回日志文件，平面文件3. 存储结构逻辑结构：表空间，

4、段，区，块4. 内存结构简介SGA：日志缓冲区每3秒一次，事务提交，切换日志文件，缓冲区1/3满或达1MB数据块缓冲区多池，多块尺寸，LRU、LRUW和接触计数散列存储桶，散列链，散列锁存器共享池字典缓冲区（Data dictionary cache），包括关于数据库文件、表、索引、列、用户、权限以及其它数据库对象的信息。 在语法分析阶段，Server Process访问数据字典中的信息以解析对象名和对存取操作进行验证。将数据字典信息缓存在内存中有助于缩短响应时间。库高速缓冲区（Library cache ），包括游标，SQL和PL/SQL语句，执行计划共享池的管理LRU算法，FreeList

5、管理Free块，Bucket与Chunk共享池过大带来的问题，绑定变量与共享池大池回收型的内存空间，用途：共享服务器模式时分配UGA，并行查询的消息缓冲，RMAN缓冲区Java池专用服务器：每个JAVA类的共享部分共享服务器：每个JAVA类的共享部分，UGA中的会话状态部分PGA：PGA与UGA（会话状态）workarea_size_policy 与pga_aggregate_targetsort_area_size，hash_area_size，bitmap_merge_area_size5. 锁DML锁：TX锁，TM锁（确保修改表内容时结构不会变），死锁DDL锁，排它DDL锁，共享DDL锁

6、（保护结构不变），可中断解析锁闩锁（栓锁，Latch）Latch free等待事件，两种Latch请求方式：Willing-To-Wait和No-Wait共享池：shareed pool，library cache，row cache objects，数据块缓冲区：cache buffers chains，cache buffers lru chain6. Undo和Redo1) 什么是Undo，Redo2) Insert，Update，Delete，Select（没有输错）产生的Redo3) 索引对Redo的影响4) 触发器对Redo的影响5) 能不能关掉Redo二、 SQL知识及优化经验1

7、. SQL语句处理过程1) SQL语句处理的四个基本步骤a. 分析检查语法，验证SQL合法性，即书写正确验证语义，验证表和列符合定义，并且有权限访问，相同的环境通过散列计算，查找是否有完全相同的SQL，并且引用相同的对象b. 优化（选择执行计划）根据规则选择一个可以在数据库中用来执行语句的最佳计划这是最消耗CPU资源的步骤，甚至所花时间可能比SQL执行的时间还长。目前Oracle支持两种优化器基于规则的优化器（RBO），根据一组固定的规则来确定执行计划，它不考虑对象尺寸，列的基数，数据的分布，排序区尺寸及其它因素。基于成本的优化器（CBO），根据收集到的关于对象的统计信息来确定执行计划，它主要

8、考虑对象的行数，尺寸，甚至CPU，IO性能等各种因素。它将生成许多执行计划（甚至上千个），每个执行计划按规则和参数赋予一个成本数，选择最低成本的执行计划。c. 行资源生成（生成执行计划代码）建立执行计划树，保存在共享池中我们通过PL/SQL Developer的F5功能看到的执行计划就是行资源生成器根据优化结果产生的输出。它是一棵树状的数据结构，存储在共享池中。d. 语句执行根据执行计划树执行SQL语句的过程，注意，不是提取数据，仅标识结果集。除了这四个基本步骤外，之前还有打开游标，之后有关闭游标这两个步骤。2) SQL语句分析的四种情况a. 硬分析（hard parse）：一条SQL语句从分

9、析到优化，到行资源生成，到执行的整个过程。b. 软分析（soft parse）：通过利用共享池中相同的SQL，跳过优化步骤，直接根据已生成的的执行计划树进行语句执行。c. 更软的软分析（soft soft parse）：使用会话高速缓存的游标来执行SQL语句，跳过优化步骤（仍然包含分析步骤），使用动态SQL，每分析一次，执行一次。方式：open cursor for using，execute immediate using系统参数session_cached_cursors决定了可以会话可以缓存的游标数量，缺省值：Oracle 9i及以下为0，Oracle 10G为20。v$open_cur

10、sor查看缓存游标。d. 不分析：同样，使用会话高速缓存的游标来执行SQL语句，但是使用静态SQL或批量绑定方式，一次分析多次执行。方式：静态游标，存储过程、函数、包等其中的静态SQL，DBMS_SQL（一次parse，多次bind_variable和execute），Forall in execute immediate using，SQL移出触发器使用包，相对于在应用程序中直接使用SQL，带来的好处就是减少了分析。讨论：为什么说硬解析带来CPU资源的高消耗？2. 绑定变量1) 使用绑定变量的好处a. 性能使用共享SQL，减少了硬解析所需的CPU资源，提高速度b. 可伸缩性减少硬解析所需闩锁

11、占用，提高了并发能力减少共享池中的SQL数量，从而提高了查找是否存在共享SQL的速度，减少了对有限的闩锁资源的占用时间减少共享池中的SQL数量，从而减少共享池内存占用避免频繁调用的SQL语句产生大量SQL来把其它SQL语句挤出共享池，导致其它SQL语句再次调用时的硬解析c. 代码编写方便使代码更容易编写，特别是单引号的转换与拼接d. 代码安全使代码更安全，防止SQL注入讨论：l 和使用绑定变量相比，使用包，以及应用程序中的查询放在存储过程中进行，带来哪些好处？为什么？l 不使用绑定变量，为什么共享池越大，性能越差？2) 哪些是可以共享的SQL语句字符级的比较完全相同，包括大小写，空格，注释两个

12、语句所指的对象必须完全相同两个SQL语句中必须使用相同的名字的绑定变量CURSOR_SHARING参数3) ZLHIS中使用绑定变量的方法通过ADO的Command对象执行带参数的SQL来达到使用绑定变量的目的应用程序调用公共方法：OpenSQLRecord，ExecuteProcedure，ShowSQLSelect怎样在对in操作使用变量绑定：4) ZLHIS中使用绑定变量的注意事项a. 传入的绑定变量一定要是明确的数据类型，不是明确或对应类型的变量，要用类型转换函数；如日期类型要用Cdate(注意Format返回是String类型)，同时SQL语句中不用再用To_Date；表格取值(Te

13、xtMatrix)要用Val或cLng之类函数；字段取值也要明确转换，象rsData!ID实际是Field类型。b. 传入OpenSQLRecord函数的绑定变量个数一定要与SQL中标明的个数足够，可以多不能少，即使在某种情况下这个变量不使用c. Select子句中的字段也可以用？参数，但注意该字段类型会为adVarChar类型，除非与明确的其它类型运算，如：Select ID，?+100 as 字段 Fromd. 传入的日期参数参与运算会出错，如“Where 登记时间=?+1/24”，可以强行用To_Date(?)+1/24方式，但当不是OraOLEDB连接时，时间精度会丢失，所以，最好采用

14、字符传入再用To_Date函数转换。e. 使用IN条件的参数，如“类别 IN(C,D,E)”，如果条件字段不需要利用索引，则可以转换为“Instr(,C,D,E,？)0”的方式以使用绑定为变量。f. 在Oracle存储过程中，入参可以使用一种特殊的数字类型PLS_Integer，一般用于临时变量或计数器，功能和Number一样。但ADO不支持这种数据类型，无法通过SQL参数化来使用绑定变量。g. 一个SQL语句(包括查询或过程)，如果字符串中包含“”符号，如果该SQL语句全部由Oracle来解析执行，那么“”应该处理为“”，如果该SQL语句中的这些字符串被处理为绑定变量，那么字符串本身不存在解

15、析问题，不需要使用“”的形式。OpenSQLRecord是使用绑定变量执行SQL，因此字符串参数中的“”不需要处理为“”。ExecuteProcedure是自动处理了绑定变量，但不是一定使用，而且因为不同部件、版本的原因可能并没有处理绑定变量，因此字符串参数中的“”需要处理为“”。h. 在目前的ODBC连接方式下，暂不支持Group by 子句中使用绑定变量i. 经在Oracle 8i,9i,10g上测试，使用绑定变量字符参数长度的限制：ODBC连接，=8001，否则报错：MicrosoftODBC driver for Oracle错误参数1: 数据被截断OLEDB连接， Row sourc

16、e 2Row source1的Row 2 - - Probe - Row source 2 Row source1的Row 3 - - Probe - Row source 2.Row source1的Row n - - Probe - Row source 2从连接过程来看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题。如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一

17、索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。如果不使用并行操作，最好的驱动表是那些应用了where 限制条件后，可以返回较少行数据的的表，所以大表也可能称为驱动表，关键看限制条件。对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并行功能。当然，有时对查询使用并行操作并不一定会比查询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU，多个硬盘

18、控制器)，所以要具体问题具体对待。一条SQL中连接的表的数量太多，是SQL速度慢的常见原因。2) 散列连接Hash Join，包括固有连接，外部连接这种连接是在oracle 7.3以后引入的，从理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。 较小的row source被用来构建hash table与bitmap，第2个row source被用来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容

19、纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动表，当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。3) 排序合并连接Sort Merge Join, SMJ，与嵌套循环、散列连接不同，它没有驱动表的概念一般情况，执行非相等连接时才发生。排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个row source都已经预先排序，则这种连接方法的效率也是很高的。4) 笛卡儿连接查询中的表没有连接条件，这通常由编写代码疏漏

20、造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，除此之外，我们要尽量使用笛卡儿乘积5) 反连接Not IN，从一个表中返回不在另一个数据源中的数据行。注意：Not IN并非一定比外部连接、Not Exists差6) 全外部连接TABLE1 FULL OUTER JOIN TABLE2 ON （连接条件）返回两个表的每一行，9i新语法，以前是使用Union连接两个外部连接结果。总结一下，三种常用连接方式的适用场合和优点：排序合并连接(Sort Merge Join, SMJ)：a) 对于非等值连接，这种连

21、接方式的效率是比较高的。b) 如果在关联的列上都有索引，效果更好。c) 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。d) 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。嵌套循环(Nested Loops, NL)：a) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。b) NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已

22、经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。哈希连接(Hash Join, HJ)：a) 这种方法使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。b) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。c) 只能用于等值连接中7. 分析函数Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对

23、于每个组只返回一行。它的好处主要是简化SQL，并且提供更好的性能。主要用于OLAP应用。8i时只能在企业版中使用，标准版和个人版不支持，9i开始所有版本都支持。1) 语法FUNCTION_NAME(,)OVER( ORDER BY 表达式 )2) 几个常用函数ROLLUP，CUBE自动汇总函数RANK ,DENSE_RANK行的相对排序（与ORDER BY搭配）ROW_NUMBER排序的组中行的偏移8. SQL优化经验1) SELECT子句中，应避免使用*，减少查系统字典转换列名的过程2) RBO模式下，From子句中，应按返回的记录行数从大到小的顺序从左到右排列表名，选择记录条数最少的表作为

24、驱动表3) RBO模式下，Where子句中，应将表联接的条件放在最前面，能够使返回的记录行数最少的条件放到最后4) 如果可以用Union All则不用Union，减少排序和重复记录筛选5) 在应用程序中连接数据获取时，如果可能， 最好使用Union方式一次返回多个结果集，减少数据访问次数6) 一般情况，能够用表联接的就不用Exists，能够用Exists的就不用In7) 将In子句优化为表联接时注意用Distinct去除重复数据8) 能够使用表联接的，不要在Select子句中使用标量子查询9) 尽可能减少多表连接的数量，使用子查询或者应用程序中对SQL拆分执行10) 尽可能将Having子句中

25、的条件放到Where子句中11) 尽量使用返回结果集最小的索引12) 避免在Where子句中的索引列上使用函数(例如：Upper)，表达式运算，隐式数据类型转换，IS NULL或IS NOT NULL判断，因为这样会禁用索引13) LIKE条件中，如果可能，尽量避免双向匹配，因为这样会禁用索引14) 在Where子句中对索引列使用+ 0或| 来实现禁用该索引15) 对于索引列，用UNION替换OR，如果可以避免OR，则使用函数，见例916) 非必须的情况下，不要使用优化器提示（Hints）17) 慎用触发器，触发器对性能的影响比较大18) 如果使用触发器，最好把SQL语句移出触发器以减少软分析例9：a.Or扩展b.使用函数避免Or方式一： 3109 consistent getsSelect * From 收费价目 Where (Sysdate Between 执行日期 And 终止日期) Or (Sysdate = 执行日期 And 终止日期 Is Null)方式二： 3 db block gets, 1328 consistent gets, 125 physical readsSelect *