SQL和PL/SQL的性能优化之五---排序、分组与集合
1、排序操作---可能需要ORACLE排序数据的操作包括:
A、创建一个索引
B、通过GROUP BY,UNIQUE或DISTINCT关键字对数据进行分组或聚合
C、ORDER BY
D、使用排序-合并方法联结表或结果集
E、使用集合操作UNION,INTERSECT或MINUS
F、执行特定的子查询
查询可能需要可观的资源,具体说明如下:
A、CPU总是要消耗的,需要CPU的数量和需要排序的结果集大小成正比。
B、ORACLE分配一块内存用来排序。这块内存来自程序全局区(PGA)。可用的PGA总量通常取决于MEMORY_TARGET或PGA_AGGREGATE_TARGET的值。
C、如果内存区域不足于完成排序,ORACLE会分配一个或多个临时段到临时表空间。这就叫作磁盘排序。磁盘排序会增加额外的开销,包括在临时段中分配空间和把数据写入临时表空间以及磁盘读出的IO消耗。
如果供排序使用的内存量足以使排序完成,则性能将是最佳的--最优排序;如果没有足够内存,则需要排序的次数越多,排序中所包含的IO就越多,性能就越差。
通过联结V$SQL_WORKAREA和V$SQL,我们可以发现拥有排序最多的SQL语句:
WITH sql_workarea as
(select sql_id||'-'||child_number sql_child, operation_type opert, last_execution last_exec, round(active_time/1000000,2) seconds,
optimal_executions||'/'||onepass_executions||'/'||multipasses_executions olm,
' '||substr(sql_text,1,155) sql_text, rank() over(order by active_time desc) ranking
from v$sql_workarea join v$sql using(sql_id,child_number))
select sql_child, seconds, opert, last_exec, olm, sql_text
from sql_workarea where ranking <= 2
order by ranking;
----如果你要求使用MEMSTATS参数,DBMS_XPLAN.DISPLAY_CURSOR将显示与排序和散列操作相关的统计信息
select * from table(dbms_xplan.display_cursor(:sql_id, :child_number, 'MEMSTATS'));
10032跟踪事件可以用来得到关于会话排序的详细统计信息。要调用此跟踪,需要提交如下的命令:
ALTER session set events '10032 trace name context forvevr, level 1';
跟踪的结果文件(通常在USER_DUMP_DEST所指的位置)包含排序参数和发生在会话执行期间的每一个排序的统计信息。
----使用索引规避排序
如果在ORDER BY子句里的列相同的列上,ORACLE可以直接从索引来按照要求的顺序获取记录,因此也避免了排序操作。
当检索第一条记录时,使用索引而不是排序会带来更好的性能。然而,当检索所有记录时,排序通常比索引查找更出色。
相对于使用排序操作,使用索此对记录排序需要的内存更少;如果内存很紧张,索此查找的效率可能比排序操作更优。
2、分组(Grouping)与聚合(Aggregate)
A、聚合操作(如SUM和AVERAGE)必须处理输入数据的每一行记录。(一般全表扫描)
select sum(quantity-sold) from sales;
如果被聚合列上存在索引,对这个索引的快速全扫描通常会更高效。
B、如果在相关列上存在索引,MAX和MIN操作并不需要读取每一行记录。(3-5个逻辑读)。然而如果我们同时要查最大值和最小值,则ORACLE要花费很高的逻辑读来扫描整个索此项。实际上分别提交MAX和MIN查询,然后将结果合并到一起是一种更好的方法(两个子查询)
C、在ORACLE 10G前,GROUP BY是通过按照GROUP BY中的列对记录进行排序实现的。10G后,HASH GROUP BY 操作通过在读取表时将聚合计算在散列表中避免了排序。理论上HASH GROUP BY 应该比SORT GROUP BY 速度快很多,且消耗更少的内存。然而,HASH GROUP BY 在初始实现有不少严重的BUG,甚至包含错误的结果(!),低劣的性能,或过高的内存消耗等,随着补丁及新版本发布,问题似乎解决了。然而,如果遇到了和HASH GROUP BY 相关的问题,可尝试关闭HASH GROUP BY 。可以通过将参数_GBY_HASH_AGGREGATION_ENABLE设置为FALSE。
10G以前,GROUP BY 会按顺序返回记录,ORDER BY 子句不起作用,10G以后,ORDER BY 会抑制HASH GROUP BY 方法的使用。因此可能会降低性能
D、永远不应该用HAVING 替代HWERE,HAVING是在聚合完成之后对记录进行排除,参与聚合的记录数越少,效果越好。HAVING子句只和GROUP函数配合使用。
3、集合操作
A、UNION 与UNION ALL
如果你不需要在UNION操作中对结果去重,可以用UNION ALL替代UNION。这样可以避免潜在的昂贵的排序操作。
B、INTERSECT(类似排序合并联结)--返回同时出现在两张表或两个结果集里的记录。考虑将其转化为表联结;这样可以用上更加高效的嵌套循环联结和散列联结。
C、MINUS--返回所有出现在第一个SELECT结果后又不包含在第二个SELECT结果集中的记录。建议你将语句转化成使用NOT IN或NOT EXISTS的子查询,这样可以使用返联结。