Oracle排序（转）

柔情只为你懂 2022-04-14 06:20 296阅读 0赞

\[b\]1、概念\[/b\]  
服务器首先在sort\_area\_size指定大小的内存区域里排序，如果所需的空间超过sort\_area\_size，排序会在临时表空间里进行。在专用服务器模式下，排序空间在PGA中，在共享服务器模式下，排序空间在UGA中。如果没有建立large pool，UGA处于shared pool中，如果建立了large pool，UGA就处于large pool中，而PGA不在sga中，它是与每个进程对应单独存在的。  
  
\[align=center\]  
\[img\]http://dl2.iteye.com/upload/attachment/0090/1692/4535ade0-732f-348c-a3be-255e7ec3dfeb.jpg\[/img\]  
\[/align\]  
　　PGA：program global area,为单个进程(服务器进程或后台进程)保存数据和控制信息的内存区域。PGA与进程一一对应，且只能被起对应的进程读写，PGA在用户登录数据库创建会话的时候建立。  
　　有关排序空间自动管理的两个参数：  
　　Pga\_aggregate\_target: 10M-4000G，等于分配给oracle instance的所有内存减去SGA后的大小。  
　　Workarea\_size\_policy: auto/manual，只有Pga\_aggregate\_target已定义时才能设置为auto。  
　　这两个参数会取代所有的\*\_area\_size参数。  
　　措施：  
　　尽可能避免排序;尽可能在内存中排序;分配合适的临时空间以减少空间分配调用。  
  
\[b\]2、需要进行排序的操作：\[/b\]  
　　A、创建索引;  
　　B、涉及到索引维护的并行插入  
　　C、order by或者group by(尽可能对索引字段排序)  
　　D、Distinct  
　　E、union/intersect/minus  
　　F、sort-merge join  
　　G、analyze命令(仅可能使用estamate而不是compute)  
  
\[b\]3、诊断和措施\[/b\]  
　　Select \* from v$sysstat where name like ‘%sort%’;  
　　Sort(disk):要求Io去临时表空间的排序数目  
　　Sort(memory)：完全在memory中完成的排序数目  
　　Sort(rows)：被排序的行数合计  
　　Sort(disk)/ Sort(memory)<5%,如果超过5%，增加sort\_area\_size的值。  
　　SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)\*100 ratio FROM v$sysstat disk,v$sysstat mem WHERE mem.NAME='sorts (memory)' AND disk.NAME='sorts (disk)';  
  
\[b\]4、监控临时表空间的使用情况及其配置\[/b\]  
　　Select tablespace\_name,current\_users,total\_extents,used\_extents,extent\_hits,max\_used\_blocks,max\_sort\_blocks FROM v$sort\_segment ;  
Column  
Description  
CURRENT\_USERS  
Number of active users  
TOTAL\_EXTENTS  
Total number of extents  
USED\_EXTENTS  
Extents currently allocated to sorts  
EXTENT\_HITS  
Number of times an unused extent was found in the pool  
MAX\_USED\_BLOCKS  
Maximum number of used blocks  
MAX\_SORT\_BLOCKS  
Maximum number of blocks used by an individual sort  
  
临时表空间的配置：  
A、initial/next设置为sort\_area\_size的整数倍，允许额外的一个block作为segment的header  
B、pctincrease=0  
C、基于不同的排序需要建立多个临时表空间  
D、将临时表空间文件分散到多个磁盘上  
http://tech.it168.com/a2009/0504/274/000000274713.shtml