oracle执行计划查询,利用Oracle执行计划机制提高查询性能

消耗在准备利用Oracle执行计划机制提高查询性能新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制&＃xff0c;你能够控制Oracle花费在评估连接顺序的时间数量&＃xff0c;并且能在大体上提高查询性能。

准备执行SQL语句

当SQL语句进入Oracle的库缓存后&＃xff0c;在该语句准备执行之前&＃xff0c;将执行下列步骤&＃xff1a;

1) 语法检查&＃xff1a;检查SQL语句拼写是否正确和词序。

2) 语义分析&＃xff1a;核实所有的与数据字典不一致的表和列的名字。

3) 轮廓存储检查&＃xff1a;检查数据字典&＃xff0c;以确定该SQL语句的轮廓是否已经存在。

4) 生成执行计划&＃xff1a;使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。

5) 建立二进制代码&＃xff1a;基于执行计划&＃xff0c;Oracle生成二进制执行代码。

一旦为执行准备好了SQL语句&＃xff0c;以后的执行将很快发生&＃xff0c;因为Oracle认可同一个SQL语句&＃xff0c;并且重用那些语句的执行。然而&＃xff0c;对于生成特殊的SQL语句&＃xff0c;或嵌入了文字变量的SQL语句的系统&＃xff0c;SQL执行计划的生成时间就很重要了&＃xff0c;并且前一个执行计划通常不能够被重用。对那些连接了很多表的查询&＃xff0c;Oracle需要花费大量的时间来检测连接这些表的适当顺序。

评估表的连接顺序

在SQL语句的准备过程中&＃xff0c;花费最多的步骤是生成执行计划&＃xff0c;特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时&＃xff0c;它必须考虑到表之间所有可能的连接。例如&＃xff1a;六个表的之间连接有720(6的阶乘&＃xff0c;或6 * 5 * 4 * 3 * 2 * 1 &＃61; 720)种可能的连接线路。当一个查询中含有超过10个表的连接时&＃xff0c;排列的问题将变得更为显著。对于15个表之间的连接&＃xff0c;需要评估的可能查询排列将超过1万亿(准确的数字是1,307,674,368,000)种。

使用optimizer_search_limit参数来设定限制

通过使用optimizer_search_limit参数&＃xff0c;你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数&＃xff0c;我们将能够防止优化器消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值&＃xff0c;优化器将检查所有可能的连接组合。

例如&＃xff1a;有五个表连接的查询将有120(5! &＃61; 5 * 4 * 3 * 2 * 1 &＃61; 120)种可能的连接组合&＃xff0c;因此如果optimizer_search_limit等于5(默认值)&＃xff0c;则优化器将评估所有的120种可能。optimizer_search_limit参数也控制着调用带星号的连接提示的阀值。当查询中的表的数目比optimizer_search_limit小时&＃xff0c;带星号的提示将被优先考虑。

另一个工具&＃xff1a;参数optimizer_max_permutations

初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限&＃xff0c;且依赖于初始参数optimizer_search_limit。optimizer_max_permutations的默认值是80,000。

参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限&＃xff1a;除非(表或组合数目)超过参数optimizer_search_limit 或者 optimizer_max_permutations设定的值&＃xff0c;否则优化器将生成所有可能的连接组合。一旦优化器停止评估表的连接组合&＃xff0c;它将选择成本最低的组合。