Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Purpose

When getting ORA-04030 errors, it is important to see the memory usage on the database for comparison to Operating System usage. The scripts can be used on any platform. NOTE: Explicitly setting SGA_MAX_SIZE with 11g Automatic Memory Management (AMM) can lead to confusion in the internally calculated SGA size. On 11g with AMM, the SGA size and PGA_AGGREGATE_TARGET can show up differently in various V$ tables.

Software Requirements/Prerequisites

Connect to a DBA privileged account

Configuring the Sample Code

The ORA-4030 diagnostic scripts will spool output. This can be changed to meet your environment needs.

Running the Sample Code

Download the script. in the zip file attached by clicking here.

Video - Video showing use of 4030 Diagnostic Scripts (4:00)

Caution

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Sample Code

Download the scripts in the ZIP file attached to ensure there are no formatting issues with the script. on various platforms

Sample Code Output

Sample Output for TopUser.sql

Top PGA User

PID: 15 SPID: 4664
User Info: SYSTEM
Program: ORACLE.EXE (MMON)
PGA Used: 6123026
PGA Allocated: 6802494
PGA Freeable: 0
Maximum PGA: 8768574

PL/SQL procedure successfully completed.

NAME                                        VALUE                UNIT
------------------------------------------- -------------------- ------------
aggregate PGA target parameter 209,715,200 bytes
aggregate PGA auto target    164,155,392 bytes
global memory bound                                   67,108,864 bytes
total PGA inuse                                       27,320,320 bytes
total PGA allocated 41,569,280 bytes
maximum PGA allocated                                102,458,368 bytes
total freeable PGA memory                                      0 bytes
process count                                                 27
max processes count                                           35
PGA memory freed back to OS                                    0 bytes
total PGA used for auto workareas                              0 bytes
maximum PGA used for auto workareas                   11,233,280 bytes
total PGA used for manual workareas                            0 bytes
maximum PGA used for manual workareas                    530,432 bytes
over allocation count                                          0
bytes processed                                   47,961,919,488 bytes
extra bytes read/written                                       0 bytes
cache hit percentage                                         100 percent
recompute count (total)                                  123,970

Sample Output for WorkAreaOverview.sql

Name                                     Total        Units
---------------------------------------- ------------ ----------
aggregate PGA target parameter           200          MBytes
aggregate PGA auto target                159          MBytes
global memory bound 64          MBytes
total PGA inuse 26          MBytes
total PGA allocated                       40          MBytes
maximum PGA allocated                     86          MBytes
total freeable PGA memory                  0          MBytes
process count                             27
max processes count                       35
PGA memory freed back to OS                0          MBytes
total PGA used for auto workareas          3          MBytes
maximum PGA used for auto workareas        9          MBytes
total PGA used for manual workareas        0          MBytes
maximum PGA used for manual workareas      0          MBytes
over allocation count                      0
bytes processed                       25,238          MBytes
extra bytes read/written                   0          MBytes
cache hit percentage                     100          percent
recompute count (total)               66,967

19 rows selected.

PGA Size                  Optimal          One-Pass         Multi-Pass
------------------------- ---------------- ---------------- ----------------
2kb <= PGA < 4kb                   105,113                0                0
64kb <= PGA < 128kb                    172                0                0
128kb <= PGA < 256kb                    42                0                0
256kb <= PGA < 512kb                   124                0                0
512kb <= PGA < 1024kb               25,608                0                0
1mb <= PGA < 2mb                        44                0                0
2mb <= PGA < 4mb                        86                0                0
4mb <= PGA < 8mb                         2                0                0
8mb <= PGA < 16mb                        4                0                0

9 rows selected.

                 Optimal                  One                   Multi
Optimal Count    PCT One-Pass Count   PCT Multi-Pass Count PCT
---------------- ------- ---------------- ---- ---------------- -----
          25,787     100                0    0                0     0

                                                                 Temporary
SID Operation     Expected Size Actual Mem   Maximum Mem Passes Segment Size
--- ------------- ------------- ------------ ------------ ------ -------------
140 HASH-JOIN             2,370        1,161        1,161      0
140 HASH-JOIN             2,262        1,189        1,189    0
140 SORT (v2)             1,180        1,180        1,180    0

Sample Output for TotalMemoryUsed.sql

Total Memory
----------------
41,320,448
502,202,368
----------------
543,522,816

NOTE: This uses views available with 10g and higher. If running an older release of Oracle, please use

col TTL format 999,999,999,999 heading "Total Memory"

select (sga_ttl + pga_ttl) TTL from
(select sum(value) sga_ttl from v$sga) ,
(select sum(pga_alloc_mem) pga_ttl from v$process)
/

References

NOTE:399497.1 - FAQ: ORA-4030 [Video]

显示附件附件

Video showing use of 4030 Diagnostic Scripts (552.21 KB)

Zip file for standarized scripts (2.1 KB)

显示相关信息 相关内容

产品

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

关键字

AUTOMATIC MEMORY MANAGEMENT; DIAGNOSTIC; DYNAMIC ADV DIAGNOSTIC TOOLS; DYNAMIC ADV TRAINING; MEMORY USAGE; PGA

错误

ORA-4030

返回页首

TOPUser.SQL

REM Locate the top PGA user

set lines 75
set pages 999
set serveroutput on

spool topuser.out

declare a1 number;
            a2 number;
            a3 varchar2(30);
            a4 varchar2(30);
            a5 number;
            a6 number;
            a7 number;
            a8 number;
            blankline varchar2(70);

cursor code is select pid, spid, substr(username,1,20) "USER" , substr(program,1,30) "Program",
PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
from v$process where pga_alloc_mem=
(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');

begin
blankline:=chr(13);
open code;
fetch code into a1, a2, a3, a4, a5, a6, a7, a8;

dbms_output.put_line(blankline);
dbms_output.put_line('               Top PGA User');
dbms_output.put_line(blankline);

dbms_output.put_line('PID:   '||a1||'             '||'SPID:   '||a2);
dbms_output.put_line('User Info:           '||a3);
dbms_output.put_line('Program:            '||a4);
dbms_output.put_line('PGA Used:            '||a5);
dbms_output.put_line('PGA Allocated:        '||a6);
dbms_output.put_line('PGA Freeable:             '||a7);
dbms_output.put_line('Maximum PGA:            '||a8);

end;
/

set lines 132
col value format 999,999,999,999,999

select * from v$pgastat;

spool off

TotalMemoryUsed.SQL

REM
REM Investigate memory from the database side
REM

col TTL format 999,999,999,999 heading "Total Memory"

break on report
compute sum on report of TTL

select bytes TTL from v$sgainfo where name='Maximum SGA Size'
union
select value from v$pgastat where name='total PGA allocated'
/

WorkAreaOverview.SQL

set lines 132
set pages 999

spool workareaoverview.out

REM overview of PGA usage

col name format a40 head "Name"
col value format 999,999,999 head "Total"
col unit format a10 head "Units"
col pga_size format a25 head "PGA Size"
col optimal_executions format 999,999,999,999 head "Optimal"
col onepass_executions format 999,999,999,999 head "One-Pass"
col multipasses_executions format 999,999,999,999 head "Multi-Pass"
col optimal_count format 999,999,999,999 head "Optimal Count"
col optimal_perc format 999 head "Optimal|PCT"
col onepass_count format 999,999,999,999 head "One-Pass Count"
col onepass_perc format 999 head "One|PCT"
col multipass_count format 999,999,999,999 head "Multi-Pass Count"
col multipass_perc format 999 head "Multi|PCT"

col sid format 999,999 Head "SID"
col operation format a30 head "Operation"
col esize format 999,999,999 head "Expected Size"
col mem format 999,999,999 head "Actual Mem"
col "MAX MEM" format 999,999,999 head "Maximum Mem"
col pass format 999,999 head "Passes"
col tsize format 999,999,999,999,999 head "Temporary|Segment Size"

spool workareaoverview.out

SELECT name, decode(unit, 'bytes', trunc(value/1024/1024), value) value ,
decode(unit, 'bytes', 'MBytes', unit) unit FROM V$PGASTAT
/

REM Review workarea buckets to see how efficient memory is utilized
REM Ideal to see OPTIMAL EXECUTIONS vs. ONE-PASS and Multi-PASS

select case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') || 'kb <= PGA < ' ||
(HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
else to_char(low_optimal_size/1024/1024,'999999') || 'mb <= PGA < ' ||
(high_optimal_size+1)/1024/1024|| 'mb'
end pga_size,
optimal_executions,
onepass_executions,
multipasses_executions
from v$sql_workarea_histogram where total_executions <> 0
order by low_optimal_size
/

REM Review workarea buckets as percentages overall
REM      this script. assuming 64K optimal size

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
       onepass_count, round(onepass_count*100/total, 2) onepass_perc,
       multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
       (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
               sum(OPTIMAL_EXECUTIONS) optimal_count,
               sum(ONEPASS_EXECUTIONS) onepass_count,
               sum(MULTIPASSES_EXECUTIONS) multipass_count
        FROM   v$sql_workarea_histogram
        WHERE low_optimal_size > 64*1024)
/

REM   Review current activity in Work Areas

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2
/

spool off
clear col

ORA-4030 PGA Usage Diagnostic Script [Video] [ID 1087789.1]