优化排序操作

优化排序操作

[1]排序进程

  如果可以在小于参数sort_area_size的值(以字节为单位)的区域中进行排序，服务器则会在内存中执行该操作。

 

如果排序需要的空间大于此值：

1)将数据分为较小的片段(称为排序存储片)，并对每个片段单独排序。

2)服务器进程将片段写入磁盘上的临时段中，当服务器处理其他的排序存储片时，这些段   可以保留中间的排序存储片数据。

3)将已排序的片段合并在一起以生成最终结果。如果sort_area_size的大小不足以一次合并所有的存储片，则这些存储片的子集将并入多次合并过程。

 

[2]排序区域和参数

当与专用服务器连接时，是PGA的一部分。

 

参数：SORT_AREA_SIZE

      SORT_AREA_RETAINE_SIZE

      --当排序完成并且排序区域仍包含待提取的排序行时，排序区域可以收缩到

       SORT_AREA_RETAINED_SIZE参数指定的大小。

      --当从排序空间提取出最后一行之后，内存将被释放回UGA以便同一oracle服务器进程使用

      --该参数的缺省值等于SORT_AREA_SIZE参数。

 

 [3]优化排序

 --如果处理的数据以前曾进行过排序，则不必再进行排序。

--如果排序操作太大，则过小的排序区会导致磁盘交换的性能开销，请确保尽可能在

内存中进行排序操作。

   --使用大内存块进行排序，可能会导致页面调试和交换并降低系统总体性能。

   --如果使用永久表空间代替临时表空间进行磁盘排序，那么频繁地分配和解除分配临时段，可能会导致栓锁争用和性能问题。

   

    优化目标：

    --避免不必要的排序操作

    --优化内存排序和磁盘开销

    --消除分配和解除分配临时段的空间分配调用。

 

[4]排序进程和临时空间

a)创建临时表空间

create temporary tablespace temp tempfile ‘$HOME/ORADATA/u06/temp01.dbf’ size 200M

a)       临时表空间的优点

为排序指定临时表空间，可以有效地消除在分配和解除分配排序空间时，所涉及的连续繁杂的空间管理操作。

临时表空间：

--不能包含任何永久对象

--可以包含临时对象

临时表空间必须具有临时文件，这些文件的优点是，不需要作为备份策略的一部分，因而在备份进程中可以节省时间。

每个用户都有临时表空间，缺省情况下，临时表空间被设置为system，除非dba分配了缺省临时表空间，当用户创建临时表或需要排序区域时，无法指定临时存储区域。临时存储区域是在分配给该用户的临时表空间中创建的。

 

[5]临时空间段

--在使用表空间的第一个排序操作期间创建

--数据库关闭时即删除

--需要时可增长

--组成区，每个区都可由不同的排序操作使用

 

[6]要排序的操作

a)索引创建

在创建B树前，服务器进程必须先对已索引的值排序

b)order by 或group by子句

服务器进程必须对order by或group by子句中的值进行排序。

c)distinct

对distinct关键字，排序必须删除重复值。

d)UNION、INTERSECT、MINUS操作符

服务器需要对它们正在删除重复值的表进行排序。

e)排序合并联接

如果没有可用的索引，等值联接请求需要：

执行二张表的全表扫描

分别对每个行源排序

将已排序的源合并到一起，即将一个源中的每一行与其他源的每个匹配行组合。

 

[7]避免排序

--用nosort创建索引

对于使用sql*loader的单cpu计算机，在为其中预排序数据创建索引时，使用nosort子句。此子句仅对插入到表中的数据有效：

SQL>create index emp on emp(emp_id) nosort;

 

--UNION ALL

使用UNION ALL,而不是使用UNION;此子句不消除重复值，因此不必排序。

 

--嵌套循环联接

使用索引访问进行等值联接请求：

 

--索引和order by

为ordery by语句经常引用的列创建索引。因为索引是有序的，因此服务器将使用索引而不是排序操作。创建索引的方式必须与ordery by子句匹配。

 

--analyze for columns

只收集有关的列的统计信息，例如，在联接条件中涉及的统计信息：

analyze…for cloumns或analyze…for all indexed columns

 

 

[8]诊断工具

v$sysstat视图显示在内存中和磁盘上的执行排序数量，及排序行数。

SELECT * FROM v$sysstat WHERE NAME LIKE '%sort%';

Sort(disk):临时段中需要I/O的排序数量。

Sort(memory)：全部在内存中执行的排序数量。

Sort(rows):监视期间排序的总行数。

 

在oltp系统中，磁盘排序数量与内存排序数量之比应小于5%。如果比率大于5%，应增大

sort_area_size  pga_aggregate_target的值。

 

[9]监视临时表空间

SELECT * FROM v$sort_segment;

此视图包含例程中临时表空间的每个排序段的信息

current_users:活动用户的数量

total_extents:区的总数

used_extents:当前分配给排序的区

extent_hits:在池中发现未使用的区的次数

max_used_blocks:已用块的最大数量

max_sort_blocks:单个排序使用的块的最大数量

 

临时表空间配置：临时表空间的缺省存储参数都应用于排序段，除非它们有无限的区