oracle排序系列二
上一期讲了oracle在什么情况下需要排序，这次我们把注意力集中到与排序相关的几个内存组件
PGA:
The Process Global Area，它是属于私有内存段，段内的内容只对本进程可见，这不同于sga的共享内存段。
pga的内存结构不需要latch来保护，因为不会有其他进程同时访问。PGA包括两个部分，fixed pga,variable pga。
fixed pga保存一些数据结构和指向variable pga的指针。PGA也是通过freelist和bucket来分配和管理。
UGA：
User Global Area，它包含以下一些信息
The persistent and runtime areas for open cursors
State information for packages, in particular package variables
Java session state
The roles that are enabled
Any trace events that are enabled
The NLS parameters that are in effect
Any database links that are open
The session's mandatory access control (MAC) label for Trusted Oracle
和PGA一样，它也分为fixed,variable两个部分，同样通过freelist和bucket分配和管理内存。
CGA：
Call Global Area,跟PGA不一样，CGA只是短暂存在的，它只存在于每一次调用周期，当
Parse an SQL statement
Execute an SQL statement
Fetch the outputs of a SELECT statement
的时候它会被使用到
在分析语句的时候产生的递规调用将会使用到CGA,包括用于检查语义，生成执行计划，PL/SQL中的递规调用也会用到它，DML的触发器递规
调用同样会需要它。Java Call Memory也是在CGA中，这也是ORACLE内存管理中唯一一个用到垃圾收集（garbage collection）的内存区域。
关于PGA,UGA,CGA的详细解释请参考Steve Adamas的<<
racle8i internal services for waits, latches, locks>>
sort_area_size：
排序空间的最大限制，在完成排序阶段后，oracle将释放内存并保留sort_area_retained_size用于fetch阶段，当最后一条记录被fetch回
客户端后，oracle将会释放sort_area_retained_size。
sort_area_retained_size：
在UGA中分配的排序空间，当oracle发生排序时，先会从UGA中分配内存直到达到sort_area_retained_size的限制，然后会继续在PGA中分配
内存一直到sort_area_size限制，如上面所说的，sort_area_retained_size将会在fetch阶段被保留，在fetch完成后释放给OS或PGA(区别
在于是否UGA是PGA的subheap)
在9iR2版本以前，UGA,CGA一直是PGA的subheap，当它们被释放后将会释放给PGA而不是释放给OS，这是由于PGA的内存分配是通过malloc,brk
来实现的，但是从9iR2版本开始，我们有了选择，_use_realfree_heap参数给我们机会去改变PGA内存的分配方式，当_use_realfree_heap为
true时，PGA的内存分配将会通过mmap来实现，这样当调用munmap的时候将不必将内存返回给进程而直接返回给OS.下面我们来验证一下
alter system set "_use_realfree_heap"=true;
ALTER SESSION SET EVENTS
'immediate trace name heapdump level 1';
HEAP DUMP heap name="pga heap" desc=0xc6a6400
extent sz=0x206c alt=92 het=32767 rec=0 flg=2 opc=2
parent=(nil) owner=(nil) nex=(nil) xsz=0xfff8
EXTENT 0 addr=0xb72c0008
******************************************************
******************************************************
HEAP DUMP heap name="top call heap" desc=0xc6a8c00
extent sz=0x206c alt=100 het=32767 rec=0 flg=2 opc=2
parent=(nil) owner=(nil) nex=(nil) xsz=0xfffc
EXTENT 0 addr=0xb72b0004
Chunk b72b000c sz= 32780 perm "perm " alo=40
Chunk b72b8018 sz= 31672 free " "
Chunk b72bfbd0 sz= 1072 recreate "callheap " latch=(nil)
ds c6a8300 sz= 1072 ct= 1
Total heap size = 65524
******************************************************
******************************************************
HEAP DUMP heap name="top uga heap" desc=0xc6a8d20
extent sz=0xffdc alt=100 het=32767 rec=0 flg=3 opc=3
parent=(nil) owner=(nil) nex=(nil) xsz=0xfffc
EXTENT 0 addr=0xb72e0004
Chunk b72e000c sz= 65524 free " "
EXTENT 1 addr=0xb72d0004
Chunk b72d000c sz= 48 free " "
Chunk b72d003c sz= 65476 recreate "session heap " latch=(nil)
ds b72c7628 sz= 65476 ct= 1
Total heap size = 131048
当_use_realfree_heap为true时，pga,uga,cga为独立的heap
strace -p xxxx -o 1.txt
mmap2(0xb7271000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xb7271000
mmap2(0xb7281000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xb7281000
mmap2(0xb7291000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xb7291000
mmap2(NULL, 1048576, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE, 7, 0xf1) = 0xb7121000
mmap2(0xb7121000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xb7121000
mmap2(0xb7131000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xb7131000
mmap2(0xb7141000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xb7141000
mmap2(0xb7151000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xb7151000
mmap2(0xb7161000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xb7161000
同时可以看到内存分配是通过mmap来实现的
alter system set "_use_realfree_heap"=false;
ALTER SESSION SET EVENTS
'immediate trace name heapdump level 1';
HEAP DUMP heap name="pga heap" desc=0xc6a6400
extent sz=0x206c alt=92 het=32767 rec=0 flg=3 opc=3
parent=(nil) owner=(nil) nex=(nil) xsz=0x206c
EXTENT 0 addr=0xc74a7b8
Chunk c74a7c0 sz= 8292 free " "
EXTENT 1 addr=0xc748740
Chunk c748748 sz= 4148 free " "
Chunk c74977c sz= 4144 freeable "session heap " ds=0xc73332c
当_use_realfree_heap为true时，uga,cga为pga的subheap
strace -p xxxx -o 2.txt
brk(0xc7fb000) = 0xc7fb000
brk(0xc823000) = 0xc823000
brk(0xc852000) = 0xc852000
brk(0xc881000) = 0xc881000
brk(0xc8b1000) = 0xc8b1000
brk(0xc8e0000) = 0xc8e0000
brk(0xc90f000) = 0xc90f000
brk(0xc93f000) = 0xc93f000
brk(0xc96e000) = 0xc96e000
brk(0xc99d000) = 0xc99d000
同时可以看到内存分配是通过brk来实现的
我们再来看一下在_use_realfree_heap不同设置下uga,pga的内存使用情况
_use_realfree_heap=true
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory 1.8679962158203125M
session uga memory max 49.53220367431640625M
session pga memory 4.808185577392578125M
session pga memory max 62.308185577392578125M
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory 49.78192901611328125M
session uga memory max 49.78192901611328125M
session pga memory 52.808185577392578125M
session pga memory max 62.308185577392578125M
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .898193359375M
session uga memory max 49.78192901611328125M
session pga memory 3.808185577392578125M
session pga memory max 62.308185577392578125M
可以看到排序结束后UGA,PGA都直接返回给OS
_use_realfree_heap=false
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .699710845947265625M
session uga memory max .91107177734375M
session pga memory 3.50872802734375M
session pga memory max 3.50872802734375M
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory 49.081577301025390625M
session uga memory max 49.081577301025390625M
session pga memory 51.68599700927734375M
session pga memory max 51.68599700927734375M
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .61719512939453125M
session uga memory max 49.081577301025390625M
session pga memory 51.68599700927734375M
session pga memory max 51.68599700927734375M
可以看到排序结束后UGA将会释放给PGA，而PGA也不会释放给OS,直到OS内存不足时将会释放出来
再来看一下sort_area_retained_size，sort_area_size设置对排序的影响
_use_realfree_heap=false
SQL 10G>alter session set workarea_size_policy=manual;
Session altered.
SQL 10G>alter session set sort_area_size=102400000;
Session altered.
SQL 10G>alter session set sort_area_retained_size=1024;
Session altered.
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .646923065185546875M
session uga memory max 49.081577301025390625M
session pga memory 60.620296478271484375M
session pga memory max 60.620296478271484375M
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .62007904052734375M
session uga memory max 49.081577301025390625M
session pga memory 60.620296478271484375M
session pga memory max 60.620296478271484375M
可以看到当sort_area_retained_size设置为1024时（实际上sort_area_retained_size的最小值为2*blocksize，
设置成1024将会被置为2*blocksize），它从UGA中先分配2*blocksize（这里是16k)的内存，然后从PGA中分配。
SQL 10G>alter session set sort_area_retained_size=102400000;
Session altered.
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory 49.0844573974609375M
session uga memory max 49.0844573974609375M
session pga memory 60.620296478271484375M
session pga memory max 60.620296478271484375M
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .61717987060546875M
session uga memory max 49.0844573974609375M
session pga memory 60.620296478271484375M
session pga memory max 60.620296478271484375M
如果设置sort_area_retained_size=102400000=sort_area_size时，那么可以看到UGA将会被一直使用
下面是_use_realfree_heap=true时的情况，情况一样，除了整个排序结束后内存是返回给OS而不是进程
_use_realfree_heap=true
SQL 10G>alter session set workarea_size_policy=manual;
Session altered.
SQL 10G>
SQL 10G>
SQL 10G>
SQL 10G>alter session set sort_area_size=102400000;
Session altered.
SQL 10G>
SQL 10G>
SQL 10G>
SQL 10G>alter session set sort_area_retained_size=1024;
Session altered.
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .46117401123046875M
session uga memory max .58603668212890625M
session pga memory 59.558185577392578125M
session pga memory max 59.558185577392578125M
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .46117401123046875M
session uga memory max .58603668212890625M
session pga memory .870685577392578125M
session pga memory max 59.558185577392578125M
SQL 10G>alter session set sort_area_retained_size=102400000;
Session altered.
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory 49.78192901611328125M
session uga memory max 49.78192901611328125M
session pga memory 52.745685577392578125M
session pga memory max 59.558185577392578125M
SQL 10G>/
NAME MEM
---------------------------------------------------------------- -----------------------------------------
session uga memory .898193359375M
session uga memory max 49.78192901611328125M
session pga memory 3.808185577392578125M
session pga memory max 59.558185577392578125M
sort_area_retained_size同时会对排序性能产生比较大的影响，不恰当的sort_area_retained_size设置将会导致排序性能严重下降。
当sort_area_size大于语句排序空间需求而sort_area_retained_size设置小于语句排序空间需求时就会产生initial run，导致使用
到临时表空间来存放initial run，这样会降低排序的性能。
SQL 10G>alter session set events'10032 trace name context forever,level 1';
Session altered.
SQL 10G>alter session set sort_area_retained_size=102400000;
Session altered.
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
SQL 10G>alter session set sort_area_retained_size=1024;
Session altered.
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
---- Sort Parameters ------------------------------
sort_area_size 102400000
sort_area_retained_size 102400000
sort_multiblock_read_count 2
max intermediate merge width 2837
---- Sort Statistics ------------------------------
Input records 398608
Output records 398608
Total number of comparisons performed 4977796
Comparisons performed by in-memory sort 4977796
Total amount of memory used 50689024
Uses version 2 sort
Does not use asynchronous IO
---- End of Sort Statistics -----------------------
sort_area_size 102400000
sort_area_retained_size 16384
sort_multiblock_read_count 2
max intermediate merge width 2837
*** 2005-10-13 18:09:07.352
---- Sort Statistics ------------------------------
Initial runs 1
Input records 398608
Output records 398608
Total disk blocks used 5527
Total number of comparisons performed 4977796
Comparisons performed by in-memory sort 4977796
Temp segments allocated 1
Extents allocated 44
Total amount of memory used 50689024
Uses version 2 sort
Does not use asynchronous IO
当使用自动管理PGA时，sort_area_retained_size将会被设置成等于sort_area_size
SQL 10G>show parameter work
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters string
workarea_size_policy string AUTO
SQL 10G>show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1G
SQL 10G>alter session set events'10032 trace name context forever,level 1';
Session altered.
SQL 10G>set autotrace trace;
SQL 10G>select * from testsort order by 1,2,3;
398608 rows selected.
---- Sort Parameters ------------------------------
sort_area_size 50689024
sort_area_retained_size 50689024
sort_multiblock_read_count 1
max intermediate merge width 3092
---- Sort Statistics ------------------------------
Input records 398608
Output records 398608
Total number of comparisons performed 4977796
Comparisons performed by in-memory sort 4977796
Total amount of memory used 50689024
Uses version 2 sort
Does not use asynchronous IO
下一次我们将分享自动管理PGA
