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的模式专用服务器，共享服务器2. 文件类型参数文件，跟踪文件，警告文件，数据文件，临时文件，控制文件，日志文件，密码文件，修改跟踪文件，数据泵文件，DMP文件，闪回日志文件，平面文件3. 存储结构逻辑结构：表空间，段，区，块4. 内存结构简介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中的会话状态部分PGA：PGA与UGA（会话状态）workarea_size_policy 与pga_aggregate_targetsort_area_size，hash_area_size，bitmap_merge_area_size5. 锁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，Redo2) Insert，Update，Delete，Select（没有输错）产生的Redo3) 索引对Redo的影响4) 触发器对Redo的影响5) 能不能关掉Redo二、 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资源的高消耗？2. 绑定变量1) 使用绑定变量的好处a. 性能使用共享SQL，减少了硬解析所需的CPU资源，提高速度b. 可伸缩性减少硬解析所需闩锁占用，提高了并发能力减少共享池中的SQL数量，从而提高了查找是否存在共享SQL的速度，减少了对有限的闩锁资源的占用时间减少共享池中的SQL数量，从而减少共享池内存占用避免频繁调用的SQL语句产生大量SQL来把其它SQL语句挤出共享池，导致其它SQL语句再次调用时的硬解析c. 代码编写方便使代码更容易编写，特别是单引号的转换与拼接d. 代码安全使代码更安全，防止SQL注入讨论：l 和使用绑定变量相比，使用包，以及应用程序中的查询放在存储过程中进行，带来哪些好处？为什么？l 不使用绑定变量，为什么共享池越大，性能越差？2) 哪些是可以共享的SQL语句字符级的比较完全相同，包括大小写，空格，注释两个语句所指的对象必须完全相同两个SQL语句中必须使用相同的名字的绑定变量CURSOR_SHARING参数3) ZLHIS中使用绑定变量的方法通过ADO的Command对象执行带参数的SQL来达到使用绑定变量的目的应用程序调用公共方法：OpenSQLRecord，ExecuteProcedure，ShowSQLSelect怎样在对in操作使用变量绑定：4) 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连接，<=32512，否则报错：ORA-01460：未实现或无理的转换请求j. 不必使用绑定变量的情况l 无变化条件的SQL调用。l 使用不定个数变量的IN子句的SQLl 使用不定个数Union方式组合的SQLl 字段通过变量组合生成的SQL3. 优化器1) 优化器模式CHOOSE如果以下条件全部成立则使用RBO，否则使用CBO（ALL_ROWS）a. 查询中引用的对象没有统计数据b. 查询中没有要求CBO的引用对象，例如：IOT，分区表，非默认并行度的表c. 未使用要求CBO的结构，例如：域索引d. 查询不包含CBO的提示，例如：访问路径提示（全、散列等），查询转换（合并、重写），连接排列，连接操作（嵌套循环，散列等），并行执行等。RULE查询中的下列条件都为真时使用RBOe. 查询中引用的对象都不要求CBOf. 未使用要求CBO的结构g. 查询不包含CBO提示ALL_ROWS以数据的吞吐量为主要目标，生成执行计划时，优化器根据统计数据分析当前SQL查询返回的数据量，达到一定的百分比，则采用全表扫描，即使有索引。FIRST_ROWS，FIRST_ROWS_1 | 10 | 100 | 1000全表扫描与其它访问表的方式相比，被认为具有无限大的成本，只有没有索引时才会选择。2) RBO与CBO的优缺点RBO的缺点：a. RBO不能根据情况选择最优执行计划，典型的就是不能根据情况决定该用索引还是全表扫描。b. 需要程序员编写SQL时遵循严格的规则：表顺序，条件顺序c. 不能利用散列连接d. 不能使用位图索引，函数索引，分区索引，反向码索引e. 不能使用索引组织表f. 随着Oracle的版本升级，RBO不支持的特性越来越多CBO的缺点：a. 执行计划不稳定，随着环境（硬件，数据等）的变化而不同b. 需要收集正确的对象统计信息，往往没有收集统计信息或不正确的收集方法导致错误的执行计划，发生“严重”的性能问题c. 执行计划的数量随着多表连接数量的增加而按排列函数增长，硬解析过程中最优执行计划的生成会消耗更多的时间。从ORACLE 10G开始，缺省使用CBO，并且ORACLE宣布不再为RBO的BUG提供修补服务。3) RBO主要规则基于规则的优化器使用的执行路径与各个路径对应的等级：RBO Path 1: Single Row by Rowid(等级最高)RBO Path 2: Single Row by Cluster JoinRBO Path 3: Single Row by Hash Cluster Key with Unique or Primary KeyRBO Path 4: Single Row by Unique or Primary KeyRBO Path 5: Clustered JoinRBO Path 6: Hash Cluster KeyRBO Path 7: Indexed Cluster KeyRBO Path 8: Composite IndexRBO Path 9: Single-Column IndexesRBO Path 10: Bounded Range Search on Indexed ColumnsRBO Path 11: Unbounded Range Search on Indexed ColumnsRBO Path 12: Sort Merge JoinRBO Path 13: MAX or MIN of Indexed ColumnRBO Path 14: ORDER BY on Indexed ColumnRBO Path 15: Full Table Scan(等级最低)4) CBO成本计算规则Cost=物理IO+逻辑IO/1000+数据链路访问远程数据库IO*1.5表：blocks，num_rows索引：avg_leaf_blocks_per_key，avg_data_blocks_per_key，clustering_factor调整参数：Db_file_multiblock_read_count（8：6.589）optimizer_index_caching ，optimizer_index_cost_adj5) CBO相关知识a. 影响执行计划的几个重要参数optimizer_index_caching ，optimizer_index_cost_adjhash_join_enabled，optimizer_dynamic_sampling（9iR2）optimizer_features_enable，optimizer_max_permutationsb. 收集系统统计信息dbms_stats.gather_system_statsdbms_stats.import_system_statsc. 收集对象统计信息Analyze，sys.dbms_utility，sys.dbms_stats6) Hints什么是HintsHints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：1) 使用的优化器的类型2) 基于代价的优化器的优化目标，是all_rows还是first_rows。3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。4) 表之间的连接类型5) 表之间的连接顺序6) 语句的并行程度除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示，则最好对表和索引进行分析。如何使用hints:Hints只应用在它们所在sql语句块(statement block，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。我们可以使用注释(comment)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面使用hints的语法：DELETE|INSERT|SELECT|UPDATE /*+ hint text hinttext. */orDELETE|INSERT|SELECT|UPDATE -+ hint text hinttext.注解：a. DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。b. “+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。c. hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。d. text 是其它说明hint的注释性文本，如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。例子：l 指定优化器模式SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_idFROM employeesWHERE department_id = 20;l 使用该表上指定的索引对表进行索引扫描SELECT /*+ INDEX(A sex_index) use sex_index because there are fewmale patients */ A.name, A.height, A.weightFROM patients AWHERE A.sex = m;l 按from 字句中表的顺序从左到右的连接SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantityFROM customers c, order_items l, orders oWHERE c.cust_last_name = :b1AND o.customer_id = c.customer_idAND o.order_id = l.order_id;4. 执行计划相关知识Recursive SQL概念 有时为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为'recursive calls'或'recursive SQL statements'。如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不比关心这些recursive SQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。当然DML语句与SELECT都可能引起recursive SQL。简单的说，我们可以将触发器视为recursive SQL。Row Source(行源) 用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。Driving Table(驱动表) 该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动行源(driving row source)更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有有限制条件(如等值限制)，则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source，后面会给出具体说明。在我们后面的描述中，一般将该表称为连接操作的row source 1。Probed Table(被探查表) 该表又称为内层表(INNER TABLE)。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表(实际上应该为返回较大row source的表)且相应的列上应该有索引。5. 数据访问路径1) 全表扫描为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描和索引快速全扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。由于HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有改善，一般我们需要使用truncate命令来使HWM值归为0。幸运的是oracle 10G后，可以人工收缩HWM的值。由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据被交换出内存。2) 通过RowID访问Rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。在ORACLE8以前的版本中，ROWID由FILE 、BLOCK、ROW NUMBER构成。随着oracle8中对象概念的扩展，ROWID发生了变化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER构成。利用DBMS_ROWID可以将rowid分解成上述的各部分，也可以将上述的各部分组成一个有效的rowid。 Rowid对访问一个表中的给定的行提供了最快的访问方法，通过ROWID可以直接定位到相应的数据块上，然后将其读到内存。3) 索引扫描索引唯一扫描，索引范围扫描，索引全扫描，索引快速全扫描，索引连接我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫描可以由2步组成：(1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经缓存到内存（数据块缓冲区）中，所以第1步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多大表进行索引扫描，取出的数据如果大于总量的5% - 10%，使用索引扫描会效率下降很多。因为它一次IO只能读一个块，并且要访问大量的索引块。4) 群扫描对群表进行数据访问的一种方法。6. 表间连接方式1) 嵌套循环Nested Loops (NL)，包括固有连接，外部连接（尽可能避免不必要的外部连接）这个连接方法有驱动表(外部表)的概念。其实，该连接过程就是一个多层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将小表或返回较小row source的表作为驱动表(用于外层循环)的原因。使用这种方法，决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确，将会导致语句的性能很差、很差。连接过程：Row source1的Row 1 - - Probe -> 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速度慢的常见原因。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(<PARTITION BY 表达式,> <ORDER BY 表达式 <ASC | DESC> <NULLS FIRST | NULLS LAST>> <WINDOWING子句>)2) 几个常用函数ROLLUP，CUBE自动汇总函数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扩展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 *