论文部分内容阅读
摘 要 本文从SQL语句优化角度出发，诊断ORACLE数据库中SQL语句优化的影响因素，并在最后提出优化建议。
关键词 数据库 SQL语句优化 影响因素
中图分类号：TP392 文献标识码：A
数据库系统本身是一个较为复杂的系统，影响其性能的因素也是多方面的。在不断探索中，SQL语句的执行效率逐渐得到重视，实验证明，SQL语句的执行效率在提升系统性能中占有重要地位。在Oracle数据库中，如何优化SQL语句很值得我们深入探讨。
1 影响SQL语句优化的因素
优化器可以完成部分工作，当人为改变优化器的执行计划时同样要遵循这部分基础。总的来说，Oracle优化器制定执行计划的选择基础也是影响执行计划的主要因素，这些因素都可能在特定情况下改变执行计划的内容，而这些因素都显式或隐式的由SQL语句的书写方式和执行情况影响，主要有以下几点：
1.1 表访问方式
表的访问方式主要有三种，全表扫描、Rowid访问、索引扫描。这里，通过Rowid访问表的速度是最快的，因为它不用顺序的读取每一块数据块去查找符合条件的记录，而是直接根据该记录在数据磁盘中的位置获取记录信息；其次是索引访问，它能获取到该行记录的Rowid值再查找进行判断；最后是全表扫描，它只能通过读取全部数据块进行查找，极大增加了系统由于逻辑读取进行的消耗。在实际应用中Rowid的值需要手动获取，因此并不可能在每个查询条件中都根据Rowid进行获取，所以使用最多的是索引扫描对表进行访问。
1.2 表连接顺序
当有两个或两个以上的表进行连接时，Oracle选择一种合适的顺序进行连接。在Oracle数据库中，无论进行连接的表一共有多少个，每次进行连接的表都是两个，连接成一张表之后再与下一张表进行连接，直至完成所有表的连接，所以每次连接的效率都只与当前连接的两个表有关。连接顺序之所以会影响执行效率是因为连接的两个表有驱动表和连接表之分。驱动表是主表，是作为连接操作的外层循环的表。连接表是与驱动表进行连接的表，在连接中处于内存循环的位置。CBO对驱动表的每一行都对连接表进行一次全扫描，所以Oracle优化器分别计算每个驱动表和连接表的记录条数，并选择最小时间复杂度的顺序在执行计划中被执行。
1.3 表连接方式
表的连接方式主要有三种：嵌套连接、合并连接和哈希连接。嵌套连接是使用驱动表的每一行与连接表的所有满足条件的行进行连接，这种连接方式使用于任何连接条件，当连接条件中有高选择性索引或限制性搜索时效率比较高。合并连接是分别取出两个连接表中满足查询条件的所有记录再对其排序然后进行笛卡尔连接，这种连接方式主要用于不等价连接，且在没有索引时效率较嵌套连接高。哈希连接是通过将驱动表中的连接列建立一个哈希函数，并对于连接表中满足条件的每一行计算哈希值进行匹配，这种连接方式只用于等价连接，且没有索引时比嵌套循环和合并连接效率高，尤其是当数据量较大时。三种连接方式均有各自匹配的情况，但一般情况来说哈希连接效率是最高的，其次是嵌套连接，而合并连接因为要对两个表都进行排序操作会增加由于排序引起的资源消耗。优化器根据返回的每个表的结果集的大小计算最合适的连接方式并在执行计划中被安排执行。
1.4 查询条件的筛选率
Oracle根据系统统计信息计算出SQL语句中每个蹄选条件所能返回的记录条数并计算所占的比率，然后选择每次选择蹄选率最大的语句最先执行。与此同时将返回的结果集作为数据源传递给下一个查询条件，下一个查询条件对此数据源执行新的蹄选操作，直至所有的查询条件执行完毕返回最终的记录集。因此具有较高筛选率的查询条件在执行计划中总是被最先安排执行。
1.5 查询条件的数据类型
优化器根据查询条件中传入的值隐式转换成表中字段所能匹配的类型。当传入的值和该字段在数据库中创建时的类型不同时，Oracle尝试进行将其进行类型转换，如果转换不成功则报错。所以Oracle可以识别隐式和显式的类型转换，但每次的转换都会耗费一定的CPU和内存资源。
1.6 排序操作
排序操作需消耗系统大量内存和CPU，尤其是中间过程的排序，大部分都在内存中直接排序并存储中间结果，当某个排序过程不是必须的或是可以被其他易操作的执行步骤代替时，Oracle的CBO就会节省系统资源和时间的消耗使用其他方式代替。当Oracle在执行路径中发现结果集已经按照一定顺序排好或者无需排序时就会省略排序过程直接下一步的操作，所以当排序不是必须的选择或已经进行过排序的结果集时，Oracle优化器在选择执行计划时忽略排序的步骤。
2 SQL语句优化的建议
ADDM工具和STA工具配合的使用使特定SQL语句优化方案的使用更为方便，使用以上两种工具进行优化的主要方法如下：
（1）将系统参数statistics—level设置为typica或all。这个参数的设置决定了收集的统计信息的内容，typical或all级别收集的信息包含在ADDM所需的统计信息内容中。
（2）获取两次快照的ID。当数据库中设置为自动获取快照时，此步骤可以省略，转为直接使用数据库对快照信息的分析结果。
（3）用户授权。优化建议的获取需要advisor的权限，需确保当前执行的用户拥有此权限。
（4）创建优化任务。创建优化任务是使用存储过程完成的，将传送过来的SQL语句和用户名作为参数，调用DBMS-SQLTUNE包的CREATE-TUNING-TASK方法为该任务创建一个任务名，同时使用包屮的EXECUTE-TUNING-TASK方法执行该任务。执行完成后系统根据该SQL语句和统计信息生成优化建议，并把执行结果存放在任务日志中。
（5）根据任务名查看优化建议。DBMS-SQLTUNE包提供了一个方法REPORT_TUNING_TASK （任务名）方法可以查看与任务名匹配的结果，结果中包含三部分。第一部分是此次优化任务的基本信息，如任务名称、执行时问、范围和执行的语句等；第二部分是关于此次任务所找到的问题和优化建议；第三部分是原有的执行计划以及采用优化建议以后的执行计划的对比。当该任务需要再次被运行时，需要删除在系统中存储的该任务名相关的信息，DBMS_SQLTUNE包提供了一个DROP_TUNING-TASK （任务名）的方法除同名的任务，以便于下次执行。
参考文献
[1] 郭霞.基于Oracle数据库的SQL语句优化分析[J].电脑知识与技术，2011，7（21）：5063-5065.
[2] 杨宇红，石红春.数据库管理中SQL查询优化技术的应用[J].信息与电脑，2011，1：108.
[3] 王跃.Oracle数据库中SQL语句的优化与分析[J].计算机光盘软件与应用，2011，8：162-163.
关键词 数据库 SQL语句优化 影响因素
中图分类号：TP392 文献标识码：A
数据库系统本身是一个较为复杂的系统，影响其性能的因素也是多方面的。在不断探索中，SQL语句的执行效率逐渐得到重视，实验证明，SQL语句的执行效率在提升系统性能中占有重要地位。在Oracle数据库中，如何优化SQL语句很值得我们深入探讨。
1 影响SQL语句优化的因素
优化器可以完成部分工作，当人为改变优化器的执行计划时同样要遵循这部分基础。总的来说，Oracle优化器制定执行计划的选择基础也是影响执行计划的主要因素，这些因素都可能在特定情况下改变执行计划的内容，而这些因素都显式或隐式的由SQL语句的书写方式和执行情况影响，主要有以下几点：
1.1 表访问方式
表的访问方式主要有三种，全表扫描、Rowid访问、索引扫描。这里，通过Rowid访问表的速度是最快的，因为它不用顺序的读取每一块数据块去查找符合条件的记录，而是直接根据该记录在数据磁盘中的位置获取记录信息；其次是索引访问，它能获取到该行记录的Rowid值再查找进行判断；最后是全表扫描，它只能通过读取全部数据块进行查找，极大增加了系统由于逻辑读取进行的消耗。在实际应用中Rowid的值需要手动获取，因此并不可能在每个查询条件中都根据Rowid进行获取，所以使用最多的是索引扫描对表进行访问。
1.2 表连接顺序
当有两个或两个以上的表进行连接时，Oracle选择一种合适的顺序进行连接。在Oracle数据库中，无论进行连接的表一共有多少个，每次进行连接的表都是两个，连接成一张表之后再与下一张表进行连接，直至完成所有表的连接，所以每次连接的效率都只与当前连接的两个表有关。连接顺序之所以会影响执行效率是因为连接的两个表有驱动表和连接表之分。驱动表是主表，是作为连接操作的外层循环的表。连接表是与驱动表进行连接的表，在连接中处于内存循环的位置。CBO对驱动表的每一行都对连接表进行一次全扫描，所以Oracle优化器分别计算每个驱动表和连接表的记录条数，并选择最小时间复杂度的顺序在执行计划中被执行。
1.3 表连接方式
表的连接方式主要有三种：嵌套连接、合并连接和哈希连接。嵌套连接是使用驱动表的每一行与连接表的所有满足条件的行进行连接，这种连接方式使用于任何连接条件，当连接条件中有高选择性索引或限制性搜索时效率比较高。合并连接是分别取出两个连接表中满足查询条件的所有记录再对其排序然后进行笛卡尔连接，这种连接方式主要用于不等价连接，且在没有索引时效率较嵌套连接高。哈希连接是通过将驱动表中的连接列建立一个哈希函数，并对于连接表中满足条件的每一行计算哈希值进行匹配，这种连接方式只用于等价连接，且没有索引时比嵌套循环和合并连接效率高，尤其是当数据量较大时。三种连接方式均有各自匹配的情况，但一般情况来说哈希连接效率是最高的，其次是嵌套连接，而合并连接因为要对两个表都进行排序操作会增加由于排序引起的资源消耗。优化器根据返回的每个表的结果集的大小计算最合适的连接方式并在执行计划中被安排执行。
1.4 查询条件的筛选率
Oracle根据系统统计信息计算出SQL语句中每个蹄选条件所能返回的记录条数并计算所占的比率，然后选择每次选择蹄选率最大的语句最先执行。与此同时将返回的结果集作为数据源传递给下一个查询条件，下一个查询条件对此数据源执行新的蹄选操作，直至所有的查询条件执行完毕返回最终的记录集。因此具有较高筛选率的查询条件在执行计划中总是被最先安排执行。
1.5 查询条件的数据类型
优化器根据查询条件中传入的值隐式转换成表中字段所能匹配的类型。当传入的值和该字段在数据库中创建时的类型不同时，Oracle尝试进行将其进行类型转换，如果转换不成功则报错。所以Oracle可以识别隐式和显式的类型转换，但每次的转换都会耗费一定的CPU和内存资源。
1.6 排序操作
排序操作需消耗系统大量内存和CPU，尤其是中间过程的排序，大部分都在内存中直接排序并存储中间结果，当某个排序过程不是必须的或是可以被其他易操作的执行步骤代替时，Oracle的CBO就会节省系统资源和时间的消耗使用其他方式代替。当Oracle在执行路径中发现结果集已经按照一定顺序排好或者无需排序时就会省略排序过程直接下一步的操作，所以当排序不是必须的选择或已经进行过排序的结果集时，Oracle优化器在选择执行计划时忽略排序的步骤。
2 SQL语句优化的建议
ADDM工具和STA工具配合的使用使特定SQL语句优化方案的使用更为方便，使用以上两种工具进行优化的主要方法如下：
（1）将系统参数statistics—level设置为typica或all。这个参数的设置决定了收集的统计信息的内容，typical或all级别收集的信息包含在ADDM所需的统计信息内容中。
（2）获取两次快照的ID。当数据库中设置为自动获取快照时，此步骤可以省略，转为直接使用数据库对快照信息的分析结果。
（3）用户授权。优化建议的获取需要advisor的权限，需确保当前执行的用户拥有此权限。
（4）创建优化任务。创建优化任务是使用存储过程完成的，将传送过来的SQL语句和用户名作为参数，调用DBMS-SQLTUNE包的CREATE-TUNING-TASK方法为该任务创建一个任务名，同时使用包屮的EXECUTE-TUNING-TASK方法执行该任务。执行完成后系统根据该SQL语句和统计信息生成优化建议，并把执行结果存放在任务日志中。
（5）根据任务名查看优化建议。DBMS-SQLTUNE包提供了一个方法REPORT_TUNING_TASK （任务名）方法可以查看与任务名匹配的结果，结果中包含三部分。第一部分是此次优化任务的基本信息，如任务名称、执行时问、范围和执行的语句等；第二部分是关于此次任务所找到的问题和优化建议；第三部分是原有的执行计划以及采用优化建议以后的执行计划的对比。当该任务需要再次被运行时，需要删除在系统中存储的该任务名相关的信息，DBMS_SQLTUNE包提供了一个DROP_TUNING-TASK （任务名）的方法除同名的任务，以便于下次执行。
参考文献
[1] 郭霞.基于Oracle数据库的SQL语句优化分析[J].电脑知识与技术，2011，7（21）：5063-5065.
[2] 杨宇红，石红春.数据库管理中SQL查询优化技术的应用[J].信息与电脑，2011，1：108.
[3] 王跃.Oracle数据库中SQL语句的优化与分析[J].计算机光盘软件与应用，2011，8：162-163.