分析和解決ora-4030錯誤
ORA-4030意味著什么？
這個錯誤意味著Oracle服務器進程不能從操作系統獲得更多的內存。這里的內存指的是PGA(程序全局區)以及由配置決定的它的子項。對于專用的服務器進程，內存包括堆棧區、UGA（用戶全局區）。UGA包括用戶會話數據、游標信息和排序區。在多線程配置中（共享服務器），UGA處于SGA（系統全局區）中，它不會造成ora-4030錯誤。
因此，ora-4030意味著進程需要更多的內存（堆棧、UGA或者PGA）來執行它的工作。
是什么引起了這個錯誤？
這個錯誤表示操作系統不能分配足夠的內存。這個錯誤可能是你的進程本身引起的，例如你的進程需要太多的內存，或者其它的原因引起操作系統內存枯竭，例如SGA區分得太大或者太多的進程競爭系統虛擬內存（物理內存＋交換分區）。許多操作系統會限制某個進程獲得的內存以保證系統穩定。
請按以下步驟檢查你的系統：
· 是否仍有足夠的內存供分配？
· 操作系統是否有限制？
· Oracle數據庫是否有限制？
· 哪一個進程需要過多的內存？
· 如何收集那個（需要過多內存的）進程正在做什么的信息？
這些將在下一節里討論。
進一步討論主題：
· 避免此類錯誤的一般建議
· 參考
是否仍有足夠的內存供分配？
要回答這個問題，我們需要使用操作系統特定的工具來檢測內存使用情況。
1.OpenVMS系統：顯示那些能告訴你物理內存和頁面文件使用情況的信息。
Physical Memory Usage (pages):
Total Free In Use Modified Main Memory (256.00Mb) 32768 24849 7500 419
……
Paging File Usage(blocks):
Free Reservable Total
DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]SWAPFILE.SYS 30720 30720 39936 DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]PAGEFILE.SYS 2261 60201088 249984 DISK$BOBBIE_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS 462224 405296 499968
作為一般的原則，頁面文件中的空閑容量總量應該不低于總容量的一半。交換文件應該幾乎不使用，空閑的容量應該幾乎和總容量一樣。
1.Windows系統：在任務治理器中查看內存使用情況。
2.Unix系統：每一個Unix系統都有自己的工具來檢測全部內存的使用情況，例如top,vmstat…..，并且每一個系統都有所不同。
o top常用來顯示物理內存和交換空間的情況。
o swapon –s 顯示交換空間使用情況
o vmstat 顯示空閑物理內存情況
Sample top output on linux:
在Linux上“top”的輸出例子：
top - 10:17:09 up 1:27, 4 users, load average: 0.07, 0.12, 0.05
Tasks: 110 total, 4 running, 105 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.3% user, 1.6% system, 0.0% nice, 98.0% idle
Mem: 1033012k total, 452520k used, 580492k free, 59440k buffers
Swap: 1052248k total, 0k used, 1052248k free, 169192k cached
.....
假如有足夠的內存，那么請檢查一下是否操作系統有強制限制。假如內存被耗盡了，我們就要找出這些內存被用在了哪里。
操作系統是否有限制？
假如仍有充足的虛擬內存剩余，可能是我們不能使用申請使用的那部分內存。請檢查操作系統是否有限制。
1.OpenVMS系統：要檢查你能使用的物理內存的總量，請檢查工作（頁面）區配額（working set quotas）和頁面文件配額(pagefile quota)。請查詢OpenVMS使用指南確定配額情況和如何修改它們。根據使用進程的不同以及啟動它們方式的不同，配額使用將不同于oracle的統計。PRocess/id=<process id>/quota將顯示對于一個特定的進程還有多少剩余配額可使用。
UAF> show oracle7
Username: ORACLE7 Owner: Oracle7 DBA
Account: SUPPORT UIC: [200,2] ([SUPPORT,ORACLE7])
CLI: DCL Tables: DCLTABLES
Default: DISK$BOBBIE_USER1:[ORACLE7]
LGICMD: LOGINFlags:
Primary days: Mon Tue Wed Thu Fri
Secondary days: Sat Sun
No access restrictions
Expiration: (none) Pwdminimum: 6 Login Fails: 0
Pwdlifetime: (none) Pwdchange: 3-DEC-1997 15:38
Last Login: 27-MAY-2003 14:54 (interactive), 26-MAY-2003 16:15 (non-interactive)
Maxjobs: 0 Fillm: 1200 Bytlm: 180000
Maxacctjobs:0 Shrfillm: 0 Pbytlm: 0
Maxdetach: 0 BIOlm: 500 JTquota: 8192
Prclm: 20 DIOlm: 500 WSdef: 2500
Prio: 4 ASTlm: 4000 WSquo: 4096
Queprio:0 TQElm: 4000 WSextent: 30000
CPU: (none) Enqlm: 18000 Pgflquo: 750000
Authorized Privileges: .....
$ sho proc/id=20200139/quota
24-JUN-2003 12:30:54.39 User: ORACLE7 Process ID: 20200139
Node: BOBBIE Process name: "ORA_BOB901_PMON"
Process Quotas:
Account name: SUPPORT
CPU limit: Infinite Direct I/O limit:100
Buffered I/O byte count quota: 9994816 Buffered I/O limit: 100
Timer queue entry quota: 99 Open file quota:29997
Paging file quota: 145968 Subprocess quota: 10
Default page fault cluster:64 AST quota: 496
Enqueue quota: 49995 Shared file limit: 0
Max detached processes: 0 Max active jobs: 0
2.Windows系統：在微軟的windows操作系統上，oracle進程集作為一個進程的許多線程來運行。
地址空間不能超過2GB（包括堆棧、PGA、SGA）。這個限制可以突破到3ＧＢ或更高。（請看oracle文檔<NOTE:46001.1>）。關于oracle數據庫和Windows NT內存結構的情況，請查詢技術公告板。Oracle進程使用的總的內存情況（不包括進程堆棧和代碼）可以用query查看。
3.Unix系統：使用內置的shell命令： limit/ulimit。注重那些unlimited的不一定意味著無限制，而是可能有著老系統的限制，例如2GB。
Linux系統上輸出的一個例子：
aroelant@aroelant-be:～> ulimit -a
core file size(blocks, -c) 0
data seg size（kbytes, -d) unlimited
file size(blocks, -f) unlimited
max locked memory(kbytes, -l) unlimited
max memory size(kbytes, -m) unlimited
open files(-n) 1024
pipe size(512 bytes, -p) 8
stack size(kbytes, -s) unlimited
cpu time(seconds, -t) unlimited
max user processes(-u) 7168
virtual memory(kbytes, -v) unlimited
有可能是內存限制定得太小了，需要增大它。也可能是我們需索得太多
Oracle數據庫是否有限制？
從oracle 9i以后，有一個參數決定一個oracle實例可以分配到PGA總量。<Note:223730.1>"Automatic PGA Memory Managment in 9i"提供了更多關于這方面的信息。下面的查詢可以用來找出分配給所有會話的PGA區域的內存總量。
SQL> select sum(value)/1024/1024 Mb from v$sesstat s, v$statname n
Where n.STATISTIC# = s.STATISTIC# and name =’session pga memory’;
哪一個進程需要過多的內存？ 某些操作需要大量的內存例如巨大PL/SQL表或者大量的排序操作。在這種情況下，在返回ora-4030錯誤之前進程將運行一段時間。希望我們可以找出內存被分配給哪個進程以及為什么被分配。你可以使用如下的查詢查出oracle數據庫PGA和UGA的運行情況。
SQL>col name format a30SQL>select sid,name,value from v$statname n,v$sesstat s
where n.STATISTIC# = s.STATISTIC# and name like 'session%memory%' order by 3 asc;
這個查詢將顯示列表中的對內存“饑餓”的進程。從操作系統角度來看，確定進程的內存使用量也是一個好主意。總之，不大可能是oracle數據庫的服務器進程使用了過多的內存。一般地，對于服務器進程來說，oracle數據庫和操作系統之間或多或少的可以就內存的使用達成一致。下面的命令答應你從操作系統的角度找出進程的內存使用量。
1.OpenVMS系統：“show system”命令給出進程和資源的使用情況的概覽。那些頻繁調用頁面失敗的進程經常消耗了大量的虛擬內存。“page”列指出物理內存的使用情況。“show process/continious”（原文如此，我懷疑是continuous）命令則給出物理內存（工作頁面區）和虛擬內存的使用情況。
$ show system/page
OpenVMS V7.2-1 on node BOBBIE 13-JUN-2003 09:56:30.44 Uptime 17 18:58:18
Pid Process Name State Pri I/O CPU Page flts Pages
20200101 SWAPPER HIB 16 0 0 00:00:02.45 0 0
20200106 CLUSTER_SERVER HIB 13 104 0 00:00:00.03 87 104
20200107 CONFIGURE HIB 10 21 0 00:00:00.06 77 17
$ sho process/id=xxx/cont:
Process AROELANT 10:00:53
State CUR Working set 131
Cur/base priority 6/4 Virtual pages 11714
Current PC 800D9B28 CPU time 0 00:00:01.28
Current PSL 00000003 Direct I/O 178
Current user SP 7A5227F0 Buffered I/O 962
PID 20200469 Page faults 1312
UIC [SUPPORT,AROELANT] Event flags C0000003 C0000000
2.Windows系統：對于微軟的windows操作系統來說，oracle進程集作為一個進程的許多線程來運行。
到目前為止，我還沒有找到一個方法來查看某個線程的內存使用情況。然而我們可以檢查出oracle是否對操作系統分配的內存感到滿足。從操作系統的角度來看，我們可以使用任務治理器。調出任務治理器，點擊“查看”按鈕，選擇“選擇列”，在彈出的窗口中在“虛擬內存大小”前打上勾。oracle.exe進程使用的虛擬內存大小( VM size)應該和SGA、PGA和進程堆棧以及代碼使用的內存總量相匹配。下面的查詢命令可以給出oracle使用的內存量，然而，這不包括進程堆棧以及代碼使用的內存量。
select sum(bytes)/1024/1024 Mb from (select bytes from v$sgastat union select value bytes from v$sesstat s,v$statname n where n.STATISTIC# = s.STATISTIC# and n.name = 'session pga memory' );MB
----------517.296406
在我的系統上，任務治理器中顯示的虛擬內存大小比上面的查詢出的內存使用量多大約30MB。當你確認是oracle使用了這個內存，這個查詢將給出哪一個會話用得最多。
3.Unix系統：“top”工具是一個很有用的工具，你能夠定制顯示和排序的列。“ps”命令在大多數系統中可以使用，但也有些不能。例如，在Linux上，“ps -AF --sort resident”將列出所有的進程最近的最大常駐內存集（resident set）（注二）。你也可參考<Note:174555.1> "UNIX: Determining the Size of an Oracle Process".
如何收集那個（需要過多內存的）進程正在做什么的信息？
本節將只討論oracle服務器進程。使用前面幾節介紹的方法，你應該可以判定一個或多個oracle服務器進程造成了內存資源的枯竭。記住并不總是由于進程造成了內存資源的枯竭從而導致ORA-4030錯誤。這個錯誤僅僅意味著進程不能獲得它需要的內存資源。
假如進程不斷增長對內存的需求，我們可以在它運行的時候查看一下它的情況。
o 你可以用下面的查詢語句在v$sql_area表中查詢有什么進程正在執行中。
SQL> select sql_text from v$sql_area a, v$session s
where a.address = s.sql_address and s.sid = <SID>;
o We can force a heapdump and have it examined by oracle support services。（這句不知如何譯）
SQL> oradebug unlimitSQL> oradebug setorapid 10 (這是對應 oracle pid, 用“setospid”對應操作系統的進程id)SQL> oradebug dump heapdump 7
假如問題不再發生，或者某些進程太快而不能作這樣的檢查，很有可能這就是引起內存枯竭的原因。我們可以在這個進程引起這個錯誤時使用事件集來獲得一個 heapdump.
SQL> alter session set events '4030 trace name heapdump level 25';
或者在數據庫的init.ora文件中設置這個事件。<Note:21234.1> EVENT: 10261 "Limit the size of the PGA heap" 這個dump能幫助Oracle Support分析并找出引起過多的內存分配的原因。
對于如何避免這個錯誤的一般建議。
o 正如前面提到的一樣，某些操作會需要大量的內存。對于排序操作來說，減少SORT_AREA_SIZE可能有所幫助。Oracle服務器進程會在PGA中分配排序操作需要的SORT_AREA_SIZE字節。假如完成某個查詢需要過多的內存，服務器進程將會使用臨時段。這意味著，當查詢需要大量的排序操作時，更少的SORT_AREA_SIZE可以使得執行更緊湊。
o 對于9i或更高版本的oracle數據庫，可以設備參數WORKAREA_SIZE_POLICY為AUTO來打開自動SQL execution內存治理功能，也可以在初始化文件中指定PGA_AGGREGATE_TARGET的大小。
<Note:262946.1> "Performance Issues After Increasing Workload", <Note:223730.1> "Automatic PGA Memory Managment in 9i", <Note:223299.1> "Top Oracle 9i init.ora Parameters Affecting Performance"
o PL/SQL例程也可能會需要大量內存，因此有必要在你的應用程序中重寫這部分查詢代碼。假如某個PL/SQL表經常被使用，它確實會在PGA中分配一塊內存。
o 再看一下優化策略，由于排序操作可能某些訪問路徑會需要太多的內存，函數調用返回過多的行等等……
o 在某些操作系統上，例如Microsoft windows，SGA的大小應該降低，以便于PGA獲得更大的內存。
o 確信你的操作系統和oracle數據庫的內存限制是適度的。
o 確信有足夠的內存（物理內存和交換空間）。
參考
General:
<Note:237899.1> Resolving ORA-4030 Errors After Upgrading
NT:
<Note:116076.1> Tackling ORA-4030 on WindowsNT
<Note:46001.1> Oracle Database and the Windows NT memory architecture, Technical Bulletin
Unix:
<Note:199746.1> How to Resolve ORA-4030 Errors on UNIX (unix specific but general enough for some suggestions)
UNIX: Determining the Size of an Oracle Process
VMS:
<Note:67033.1> Background process quotas <Note:68663.1> Dedicated server process quotas (SQL*Net V2.3.3, V8.0.X)<Note:70671.1> Process quotas for Bequeath connections (V7, V8)<Note:68849.1> Bequeath listener process quotas (V7, V8)<Note:68226.1> Listener process quotas (SQL*Net V2.3.3, V8.0.X)
@ Internal:
@ <Note:21234.1> EVENT: 10261 "Limit the size of the PGA heap"
@ This event is very usefull. It will cause the process to dump information when the PGA grows above the specified limit
本人注一：工作（頁面）區（working set）：1.為避免過多的調頁所必須激活的用戶頁面的集合。2.為避免系統失效，調頁所需要的實存容量。
本人注二：常駐內存集（resident set）：在虛存系統中，任一時候都存在于主存儲器內的某個程序的頁面 或程序段的全部。
