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

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.数据访问路径111)全表扫描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查看与分析简介17四、应用程序优化经验171.ZLHIS优化历程回顾与总结172.应用程序优化原则18本次培训的背景医大二院性能诊断过程中发现应用程序中较多SQL存在性能问题和优化空间。目前已知的反映过ZLHIS存在性能问题的用户：医大二院，遵义医院，广西容县人民医院，重庆市三院一、 Oracle相关知识目标：仅介绍SQL调优涉及的Oracle相关知识，达到了解的程度，不要求深入理解。1. 连接Oracle的两种模式专用服务器一个客户端连接对应一个服务器进程共享服务器多个客户端连接对应一个服务器进程，服务器端存在一个进程调度器来管理。Oracle缺省用的是专用服务器模式，Oracle可以同时支持共享服务器和专用服务器模式，可以指定一个会话使用专用服务器，另一个会话使用共享服务器。MTS具有以下一些缺点：1) 共享服务器的代码路径比专用服务器长，所以它天生就比专用服务器慢。2) 存在人为死锁的可能，因为它是串行的，只要一个连接阻塞，则该服务器进程上的所有用户都被阻塞，并且极可能死锁。3) 存在独占事务的可能，因为如果一个会话的事务运行时间过长，它独占共享资源，其它用户只能等待，而专用服务器，每个客户端是一个会话。4) 共享服务器模式限制了某些数据库特性，例如：不能单独启动和关闭实例，不能进行介质恢复，不能使用Log Miner，并且SQL_TRACE没有意义(因为是共享而不是当前会话的)。MTS减少的内存实际上是专用服务器模式下每个用户连接到操作系统进程所需的内存，但它却使用SGA的Large_Pool来分配UGA，拆东墙补西墙，所减少的内存是很少的。如果用户会话的连接和断开很频繁，数据库进程的创建和删除的开销会非常大，这种情况最好采用共享服务器模式(否则，应该使用连接池技术)。如果客户端一次连接终身使用(会话生命周期内)，使用共享服务器模式的意义不大。因为大部分时间，一个会话就连接到一个服务器进程，无法共享服务器进程。2. 文件类型参数文件，跟踪文件，警告文件，数据文件，临时文件，控制文件，日志文件，密码文件，修改跟踪文件，数据泵文件，DMP文件，闪回日志文件，平面文件3. 存储结构逻辑结构：表空间，段，区，块4. 内存结构这里省略了Oracle进程介绍，因为我们主要介绍与SQL调优相关的Oracle知识。SGA：日志缓冲区每3秒一次，事务提交，切换日志文件，缓冲区1/3满或达1MB数据块缓冲区多池，多块尺寸，LRU、LRUW和接触计数散列存储桶，散列链，散列锁存器共享池字典缓冲区（Data dictionary cache），包括关于数据库文件、表、索引、列、用户、权限以及其它数据库对象的信息。 在语法分析阶段，Server Process访问数据字典中的信息以解析对象名和对存取操作进行验证。将数据字典信息缓存在内存中有助于缩短响应时间。库高速缓冲区（Library cache ），包括游标，SQL和PL/SQL语句，执行计划共享池的管理LRU算法，FreeList管理Free块，Bucket与Chunk共享池过大带来的问题，绑定变量与共享池大池回收型的内存空间，用途：共享服务器模式时分配UGA，并行查询的消息缓冲，RMAN缓冲区Java池专用服务器：每个JAVA类的共享部分共享服务器：每个JAVA类的共享部分，UGA中的JAVA会话状态部分PGA：进程全局区，一个进程或线程专用的内存，其它进程或线程不能访问。主要包括排序数据，Hash连接数据，位图合并数据等，PGA中的结构不需要由latches来保护，因为其它的进程是不能进入到这里面来访问的。所以，后面要讲到的表联接方式Hash连接是最快速的一种连接方式。workarea_size_policy 与pga_aggregate_targetsort_area_size，hash_area_size，bitmap_merge_area_sizeUGA：用户全局区，与特定的用户会话相关，如果使用共享服务器就在SGA中分配（Large Pool）， 如果使用专用服务器，就在PGA中分配。包括以下内容：1) 游标缓存2) 包的状态信息，特定的变量3) Java会话状态4) 可用的ROLES5) 被ENABLE的跟踪事件6) 起作用的NLS参数设置7) 打开的DBLINK8) 会话的入口控制5. 锁DML锁：TX锁，TM锁（确保修改表内容时结构不会变），死锁DDL锁，排它DDL锁，共享DDL锁（保护结构不变），可中断解析锁闩锁（栓锁，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，Redo 2) Insert，Update，Delete，Select（没有输错）产生的Redo3) 索引对Redo的影响4) 触发器对Redo的影响5) 能不能关掉Redo讨论：为什么大量的私有同义词会带来性能问题？一条SQL查询语句，会用到Oracle哪些内存结构？ 二、 SQL知识及优化经验1. SQL语句处理过程1) SQL语句处理的四个基本步骤a. 分析检查语法，验证SQL合法性，即书写正确验证语义，验证表和列符合定义，并且有权限访问，相同的环境通过散列计算，查找是否有完全相同的SQL，并且引用相同的对象b. 优化（选择执行计划）根据规则选择一个可以在数据库中用来执行语句的最佳计划这是最消耗CPU资源的步骤，甚至所花时间可能比SQL执行的时间还长。目前Oracle支持两种优化器基于规则的优化器（RBO），根据一组固定的规则来确定执行计划，它不考虑对象尺寸，列的基数，数据的分布，排序区尺寸及其它因素。基于成本的优化器（CBO），根据收集到的关于对象的统计信息来确定执行计划，它主要考虑对象的行数，尺寸，甚至CPU，IO性能等各种因素。它将生成许多执行计划（甚至上千个），每个执行计划按规则和参数赋予一个成本数，选择最低成本的执行计划。c. 行资源生成（生成执行计划代码）建立执行计划树，保存在共享池中我们通过PL/SQL Developer的F5功能看到的执行计划就是行资源生成器根据优化结果产生的输出。它是一棵树状的数据结构，存储在共享池中。d. 语句执行根据执行计划树执行SQL语句的过程，注意，不是提取数据，仅标识结果集。除了这四个基本步骤外，之前还有打开游标，之后有关闭游标这两个步骤。2) SQL语句分析的四种情况a. 硬分析（hard parse）：一条SQL语句从分析到优化，到行资源生成，到执行的整个过程。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_cursor查看缓存游标。d. 不分析：同样，使用会话高速缓存的游标来执行SQL语句，但是使用静态SQL或批量绑定方式，一次分析多次执行。方式：静态游标，存储过程、函数、包等其中的静态SQL，DBMS_SQL（一次parse，多次bind_variable和execute），Forall in execute immediate using，SQL移出触发器使用包，相对于在应用程序中直接使用SQL，带来的好处就是减少了分析。讨论：最消耗CPU资源的步骤是什么，为什么？ora92_ora_14372_体检.log为什么会产生硬解析?2. 绑定变量1) 使用绑定变量的好处a. 性能使用共享SQL，减少了硬解析所需的CPU资源，提高速度b. 可伸缩性减少硬解析所需闩锁占用，提高了并发能力减少共享池中的SQL数量，从而提高了查找是否存在共享SQL的速度，减少了对有限的闩锁资源的占用时间减少共享池中的SQL数量，从而减少共享池内存占用避免频繁调用的SQL语句产生大量SQL来把其它SQL语句挤出共享池，导致其它SQL语句再次调用时的硬解析c. 代码编写方便使代码更容易编写，特别是单引号的转换与拼接d. 代码安全使代码更安全，防止SQL注入讨论：和使用绑定变量相比，使用包，以及应用程序中的查询放在存储过程中进行，带来哪些好处？为什么？在OLTP系统中，如果不使用绑定变量，为什么共享池越大，性能越差？2) 哪些是可以共享的SQL语句字符级的比较完全相同，包括大小写，空格，注释两个语句所指的对象必须完全相同两个SQL语句中必须使用相同的名字的绑定变量CURSOR_SHARING参数3) ZLHIS中使用绑定变量的方法通过ADO的Command对象执行带参数的SQL来达到使用绑定变量的目的应用程序调用公共方法：OpenSQLRecord，ExecuteProcedure，ShowSQLSelect怎样在对in操作使用变量绑定：/post/9689/2044754) ZLHIS中使用绑定变量的注意事项a. 传入的绑定变量一定要是明确的数据类型，不是明确或对应类型的变量，要用类型转换函数；如日期类型要用Cdate(注意Format返回是String类型)，同时SQL语句中不用再用To_Date；表格取值(TextMatrix)要用Val或cLng之类函数；字段取值也要明确转换，象rsData!ID实际是Field类型。b. 传入OpenSQLRecord函数的绑定变量个数一定要与SQL中标明的个数足够，可以多不能少，即使在某种情况下这个变量不使用c. Select子句中的字段也可以用？参数，但注意该字段类型会为adVarChar类型，除非与明确的其它类型运算，如：Select ID，?+100 as 字段 Fromd. 传入的日期参数参与运算会出错，如“Where 登记时间=?+1/24”，可以强行用To_Date(?)+1/24方式，但当不是OraOLEDB连接时，时间精度会丢失，所以，最好采用字符传入再用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语句中的这些字符串被处理为绑定变量，那么字符串本身不存在解析问题，不需要使用“”的形式。OpenSQLRecord是使用绑定变量执行SQL，因此字符串参数中的“”不需要处理为“”。ExecuteProcedure是自动处理了绑定变量，但不是一定使用，而且因为不同部件、版本的原因可能并没有处理绑定变量，因此字符串参数中的“”需要处理为“”。h. 在目前的ODBC连接方式下，暂不支持Group by 子句中使用绑定变量i. 经在Oracle 8i，9i，10g上测试，使用绑定变量字符参数长度的限制：ODBC连接，=8001，否则报错：MicrosoftODBC driver for Oracle错误参数1: 数据被截断OLEDB连接， Row source 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(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。如果不使用并行操作，最好的驱动表是那些应用了where 限制条件后，可以返回较少行数据的的表，所以大表也可能称为驱动表，关键看限制条件。对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并行功能。当然，有时对查询使用并行操作并不一定会比查询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU，多个硬盘控制器)，所以具体问题具体对待。一条SQL中连接的表的数量太多，是SQL速度慢的常见原因。一条SQL语句，连接11个表嵌套循环的例子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比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动表，当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。3) 排序合并连接Sort Merge Join， SMJ，与嵌套循环、散列连接不同，它没有驱动表的概念一般情况，执行非相等连接时才发生。排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个row source都已经预先排序，则这种连接方法的效率也是很高的。4) 笛卡儿连接查询中的表没有连接条件，这通常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，除此之外，我们要尽量使用笛卡儿乘积5) 反连接Not IN，从一个表中返回不在另一个数据源中的数据行。注意：Not IN并非一定比外部连接、Not Exists差6) 全外部连接TABLE1 FULL OUTER JOIN TABLE2 ON （连接条件）返回两个表的每一行，9i新语法，以前是使用Union连接两个外部连接结果。总结一下，三种常用连接方式的适用场合和优点：排序合并连接(Sort Merge Join， SMJ)：a) 对于非等值连接，这种连接方式的效率是比较高的。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有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。哈希连接(Hash Join， HJ)：a) 这种方法使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。b) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。c) 只能用于等值连接中7. 分析函数Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。它的好处主要是简化SQL，并且提供更好的性能。主要用于OLAP应用。8i时只能在企业版中使用，标准版和个人版不支持，9i开始所有版本都支持。1) 语法FUNCTION_NAME(，)OVER( ORDER BY 表达式 )2) 几个常用函数ROLLUP，CUBE多级汇总函数如果是ROLLUP(A， B， C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A， B， C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。RANK ，DENSE_RANK行的相对排序（与ORDER BY搭配）ROW_NUMBER排序的组中行的偏移8. SQL优化经验1) SELECT子句中，应避免使用*，减少查系统字典转换列名的过程2) RBO模式下，From子句中，应按返回的记录行数从大到小的顺序从左到右排列表名，选择记录条数最少的表作为驱动表3) RBO模式下，Where子句中，应将表联接的条件放在最前面，能够使返回的记录行数最少的条件放到最后4) 如果可以用Union All则不用Union，减少排序和重复记录筛选5) 在应用程序中连接数据获取时，如果可能， 最好使用Union方式一次返回多个结果集，减少数据访问次数6) 一般情况，能够用表联接的就不用Exists，能够用Exists的就不用In7) 将In子句优化为表联接时注意用Distinct去除重复数据8) 能够使用表联接的，不要在Select子句中使用标量子查询9) 尽可能减少多表连接的数量，使用子查询或者应用程序中对SQL拆分执行10) 尽可能将Having子句中的条件放到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扩展/sql/sql_tuning1.htmb.使用函数避免Or方式一： 3109 consistent getsSelect * From 收费价目 Where (Sysdate Between 执行日期 And 终止日期) Or (Sysdate = 执行日期 And 终止日期 Is Null)方式二： 3 db block gets， 1328 consistent gets， 125 physical readsSelect *From 收费价目Where Sysdate Between 执行日期 And 终止日期UnionSelect * From 收费价目 Where Sysdate = 执行日期 And 终止日期 Is Null;方式三：2449 consistent getsSelect * From 收费价目 Where Sysdate Between 执行日期 And Nvl(终止日期， To_Date(3000-01-01， yyyy-mm-dd)三、 SQL分析诊断方法和工具1. AutoTrace使用介绍1) 创建执行计划环境创建plan_table表为临时表，并且授权给所有人可用，再建立plustrace并授权%oracle_home% RDBMSADMINutlxplan.sql修改为创建临时表create global TEMPORARY table PLAN_TABLE ( )on commit preserve ROWS;再执行%oracle_home% RDBMSADMINutlxplan.sqlgrant all privilege on plan_table to public;create public synonym plan_table for plan_table;2) 创建AutoTrace环境D:oracleora81sqlplusadminplustrce.sqlgrant plustrace to public;3) 使用AutoTrace在SQL_PLUS中使用:SQL alter session set timed_STATISTICS=true;SQL set autotrace on;SQL set autotrace traceonly;( on explan，on statistics，traceonly explan，traceonly statistics)SQL set autotrace off;2. SQL Trace的几种方法首先，修改系统参数或会话：timed_STATISTICS=true1) SQL_Trace当前会话alter session set sql_trace=true;alter session set sql_trace=false;指定会话select sid，serial#，username from v$sessionexec dbms_system.set_sql_trace_in_session(Sid， Serial#，true)exec dbms_system.set_sql_trace_in_session(Sid， Serial#，false)2) 事件跟踪10046事件可以设置以下四个级别:1 - 启用标准的SQL_TRACE功能，等价于sql_trace4 - Level 1 加上绑定值(bind values)8 - Level 1 + 等待事件跟踪12 - Level 1 + Level 4 + Level 8当前会话alter session set events 10046 trace name context forever ，level 12alter session set events 10046 trace name context off;指定会话SQL exec dbms_system.set_ev(Sid， Serial#，10046，8，zl);3) SQL Trace的几种工具PL/SQL Developer中的SQL TraceZL9Comlib中的10046公用函数ZL SQLTRACE图形化工具3. ZL SQLTRACE工具使用介绍4. Trace文件分析5. StatsPack查看与分析简介四、 应用程序优化经验1. ZLHIS优化历程回顾与总结回顾我们所做的应用程序优化，从中分析和总结经验1) 2004年5月，ZLHIS10在医大二院启用初期，为了便于性能检测和分析，SQLTEST和SQLLOG产生了（如果那时知道Oracle的SQL Trace，可能不会产生）。历史见证：SQL_C7047X_20050310.log2006年6月，在技术中心的要求下，周韬开发了SQLTRACE工具，SQL性能分析更加方便和直观。重视性能，利用工具进行检测和分析，是性能优化的基础。2) 2004年上半年某月，赵元礼专门安排陈福容对所有报表的SQL进行了优化，主要是索引的合理使用，以及IN方式优化为Exists方式SQL执行计划的调整来实现性能优化3) 2004年8月，重庆九龙坡三院使用VPN方式，主要业务因速度太慢无法开展，而之前使用方联软件能正常进行，在用户的强烈要求下，我们进行了挂号，费用程序和打印部件的SQL调用次数优化，以及应用程序访问数据的合理性优化。通过客户端内存缓存，数据访问合理性调整，减少SQL往返调用来实现性能优化4) 在10.13中对主要部件进行了绑定变量修改，10.15又进行了打印部件和存储过程调用的绑定变量修改。通过绑定变量的使用，减少硬解析从而减少CPU资源耗用来实现整体性能优化5) 2005年11月，在重庆市三院和广西容县人民医院的要求下，周韬进行了收费项目输入匹配的SQL优化。通过简化庞大，复杂的SQL语句来实现性能优化6) 2005年底，周韬检查程序发现一批存储过程对汇总表（病人费用汇总和病人未结费用）的更新使用了低效的索引。通过屏蔽低效索引，选择高效索引来实现性能优化7) 2005年10月左右，贵阳一家大量使用门诊记帐的医院反映，记帐、结帐时选