SQL和PLSQL的性能优化之五---排序、分组与集合
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查询，然后将结果合并到⼀起是⼀种更好的⽅法（两个⼦查询）
sql语句优化方式
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的⼦查询，这样可以使⽤返联结。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。