診斷並解決 ORA-4030 錯誤 (文件 ID 1548826.1)

適用於:

用途

如何診斷 ORA-4030 錯誤

排錯步驟

診斷並解決 ORA-4030 錯誤

ORA-4030 意味著什麼？

你可能在日誌檔案中或者螢幕上看到這個錯誤: ORA-04030 'out of process memory when trying to allocate %s bytes (%s,%s)'

該錯誤意味著 Oracle Server 程式無法從作業系統分配更多記憶體。該記憶體由 PGA（Program Global Area）組成，其內容取決於伺服器配置。對於專用的伺服器程式，記憶體包含堆疊以及用於儲存使用者會話資料、遊標資訊和排序區的 UGA（User Global Area）。在多執行緒配置中（共享伺服器），UGA 被分配在 SGA（System Global Area）中，所以在這種配置下 UGA 不是造成 ORA-4030 錯誤的原因。

因此，ORA-4030 表示程式需要更多記憶體（堆疊 UGA 或 PGA）來執行其任務。

是什麼導致了該錯誤？

由於發生了這個錯誤，您因此無法從作業系統分配記憶體。這個錯誤可能是程式本身導致的，例如程式需要過多的記憶體，或者一些其他原因導致作業系統記憶體被耗盡，例如 SGA 太大或系統虛擬記憶體（實體記憶體 + 交換空間）中要容納的程式過多。許多作業系統會對單個程式能夠獲取的記憶體量加以限制，以便自我保護。所以我們就會有下列問題：

問題：

• 是否仍然有足夠的可用記憶體？

• 是否設定了作業系統限制？

• 是否設定了 Oracle 限制？

• 哪個程式需要的記憶體過多？

• 如何收集有關程式實際正在執行的任務的資訊？

下面我們將討論這些內容。

其他主題：

• 有關避免此錯誤的一般建議

是否仍然有足夠的可用記憶體？

要回答這個問題，我們需要使用作業系統特定的工具來檢查記憶體使用情況。

1. OpenVMS 系統：show memory 會提供關於實體記憶體和頁面檔案使用情況的資訊：

   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        226160      201088      249984
     DISK$BOBBIE_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS      462224      405296      499968
   
   作為一般準則，頁面檔案中的可用空間總和應不低於整個空間總和的一半。
   交換檔案應幾乎不使用，可用空間的大小應大致等於總空間的大小。

2. Windows 系統：在“工作管理員”的效能選項卡中檢查記憶體使用情況。

3. Unix 系統：每種 unix 系統通常都有自己的工具來檢查系統上的全域性記憶體使用情況，如 top、vmstat……並且在不同的 OS 上，記憶體管理的運作方式各不相同。

• top 通常會顯示實體記憶體和交換空間的統計資訊

• swapon -s 顯示交換空間使用情況

• vmstat 顯示可用實體記憶體

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 配額和頁面檔案配額。請參閱 OpenVMS 部分，瞭解使用了哪些配額以及如何修改配額。根據程式型別以及啟動的方式，其所用的配額可能不是 Oracle中的那部分配額。show 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:   LOGIN
   Flags:
   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:            

2. Windows 系統：在 Microsoft Windows 作業系統中，Oracle 各個程式是在一個程式中以多執行緒實施的。對於 32 位的系統，可定址的記憶體量為 2Gb（包括堆疊、PGA 和 SGA）。此限制可以增加到 3Gb 或更高。有關更多資訊，請參閱“Oracle Database and the Windows NT memory architecture, Technical Bulletin”。對於 64 位的系統這個限制就高多了。Oracle 程式使用的總記憶體（不包括程式堆疊和程式碼）可透過此查詢進行確定。

3. Unix 系統：使用 limit/ulimit shell builtin 命令。請注意，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

   
   有的問題可能是因為限制設定得過低造成的，所以需要進行相應的增加。也有的問題可能是由於我們需要使用的太多造成的。
   
   請注意：其他 OS 引數設定（例如 maxuproc）可能會導致問題
   
   例如，”ORA-4030 (QERHJ HASH-JOI,KLLCQAS:KLLSLTBA)”
   Status: 92,Closed, Not a Bug
   
   ***來自於 bug - "Increased MAXUPROC from 1000 to 2000, restarted the listener and ORA-4030 errors were resolved"

是否設定了 Oracle 限制？

從 Oracle Version 9i 開始我們引入了引數 PGA_AGGREGATE_TARGET，該引數嘗試限制一個例項可以分配的 PGA 總量。“Automatic PGA Memory Managment”部分提供了關於此問題的更多資訊。以下查詢可用於查詢分配給所有會話的 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 角度看來所用於 Oracle 程式的 PGA 和 UGA 大小。

SQL> col name format a30

SQL> 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 Server 程式。通常，對於伺服器程式而言，Oracle 和作業系統之間基本都可以就記憶體的使用情況達成一致。透過以下命令，您可以查詢作業系統中程式的記憶體使用情況。

1. OpenVMS 系統：show system 將顯示關於程式和資源的總體使用情況。頻繁呼叫頁面失敗的程式通常會使用大量虛擬記憶體。“Pages”列指示物理頁的使用情況。show process/continious 命令顯示物理（工作集）和虛擬記憶體的使用情況。
   $ show system/pag  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 系統：在 Microsoft windows 系統中， Oracle 是透過在單個程式中使用多個執行緒來實施的。直到現在，尚未找到可以檢視每個執行緒的記憶體使用情況的方法。但是，我們可以檢查 Oracle 和作業系統是否就 Oracle 所使用的記憶體達成一致。從作業系統的角度看，我們可以使用工作管理員。單擊檢視按鈕並選擇選擇列(S)...，確保已選中虛擬記憶體大小(V)。oracle.exe 的虛擬記憶體大小  列中顯示的大小應與 SGA、總 PGA 記憶體以及程式堆疊和程式碼大小的總和相同。以下查詢可用於獲取 Oracle 所檢視的記憶體大小，但是不包括程式堆疊和程式碼大小：
   

   SQL> 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

   在我的系統中，這個值要比透過工作管理員所看到的 VM值小約 30 Mb。
     當您確定 Oracle 就是那個正在大量使用記憶體的程式時，此查詢會顯示使用記憶體最多的會話

3. Unix 系統：top 是一個非常有用的工具，您可以自定義列顯示和基於關鍵字排序。ps 命令在大部分系統上都可用，但具體使用方法不盡相同。例如，在 Linux 系統上，“ps -AF --sort resident”會列出具有最大駐留集大小的所有程式。另請參閱“UNIX: Determining the Size of an Oracle Process”。

如何收集有關程式實際正在執行的任務的資訊

這部分將只討論 Oracle Server 程式。透過前面介紹的方法，您應該可以確定一個或多個 Oracle Server 程式導致了記憶體消耗。請記住，並非總是出現 ORA-4030 的程式導致了記憶體消耗，這個程式可能只是無法申請到需要的記憶體而已。

對於不斷增加記憶體使用的程式，我們可以在其執行時進行檢視以下方面的資訊：

• 您可以使用以下查詢檢查 v$sqlarea 從而找到程式正在執行的 SQL：

SQL> select

           sql_text

         from

            v$sqlarea a, v$session s

         where a.address = s.sql_address and

                  s.sid =<SID>;

• 我們可以做heapdump，並將結果提交給 Oracle 技術支援：

           SQL> select PID from v$process p, v$session s where p.addr=s.paddr and sid=<SID>;

SQL> oradebug setorapid <PID>
SQL> oradebug unlimit
SQL> oradebug dump errorstack 3
SQL> oradebug dump heapdump 536870917
SQL> oradebug tracefile_name (shows the path and filename information)
SQL> oradebug close_trace

如果問題間歇出現或某一程式由於報錯太快而導致無法進行檢查，且這個程式最有可能是記憶體消耗的原因，那麼，在程式發生錯誤時我們可以使用以下事件來獲取 heapdump：
SQL> alter session set events '4030 trace name heapdump level 536870917';

或者在資料庫初始化檔案中設定此事件並重新啟動例項。

- init.ora: event="4030 trace name heapdump level 536870917"
- spfile: 執行: SQL> ALTER SYSTEM SET EVENT='4030 trace name heapdump level 536870917' scope=spfile;

對於 低於 9.2.0.5 的版本，請使用級別 5，而非級別 536870917。
Oracle技術支援工程師可使用該heapdump查詢過多記憶體分配的原因。

 

有關避免此錯誤的一般建議

1. 如上所述，一些操作需要大量的記憶體。對於排序問題，減少 SORT_AREA_SIZE 會有所幫助。Oracle Server 程式會將 PGA 中的 SORT_AREA_SIZE 位元組分配給排序操作。如果完成搜尋需要更多記憶體，伺服器程式將會使用temporary segment。這意味著，減少 SORT_AREA_SIZE 會對需要大量排序操作的查詢效能產生影響。

2. 對於 9i 及更高版本，透過將引數 WORKAREA_SIZE_POLICY 設定為 AUTO，以及在初始化檔案中指定 PGA_AGGREGATE_TARGET 的大小，即可啟用自動 SQL 執行記憶體管理功能。使用自動 PGA 記憶體管理將有助於減少發生 ORA-4030 錯誤的可能性。請注意，OpenVMS 作業系統在Oracle 9i版本上不支援 PGA_AGGREGATE_TARGET，但是在 Oracle 10g 版本上是支援的。有關更多詳細資訊，請參閱以下文件：
   
     "Performance Issues After Increasing Workload",
     "Automatic PGA Memory Managment",
    "Top Oracle 9i init.ora Parameters Affecting Performance"

3. PL/SQL 程式也可分配大量記憶體，因此可能需要重寫應用程式的某些部分。儘管 PL/SQL 表非常容易使用，但它確實需要在 PGA 中分配記憶體。

4. 檢視 optimizer 策略，一些訪問路徑可能會因排序操作、較多行上的函式使用等原因而需要更多記憶體。

5. 在某些作業系統上（例如 Microsoft Windows），可能要降低 SGA 的大小以便於 PGA 獲得更大的記憶體。

6. 確保您的作業系統和 Oracle 限制設定合理。

7. 確保有足夠的可用記憶體（實體記憶體和交換空間）。

參考