ORA-4030 PGA Usage Diagnostic Script

REM  Locate the top PGA user

set lines 75
set pages 999
set serveroutput on

spool topuser.out

select * from gv$version;

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

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'
/

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

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;

select * from v$pgastat;

col time form a30
col name form a30
select a.BEGIN_INTERVAL_TIME time, b.*
from DBA_HIST_SNAPSHOT a, DBA_HIST_PGASTAT b
where a.SNAP_ID=b.SNAP_ID
and b.name='total PGA allocated'
order by a.BEGIN_INTERVAL_TIME desc
/

show parameter parallel_execution_message_size

show parameter memory

show parameter pga



spool off
clear col



#collect AIX info

ls -al $ORACLE_HOME/bin/oracle >> /tmp/support.txt
oslevel -s
whoami >> /tmp/support.txt
ulimit -a >> /tmp/support.txt
svmon -O unit=MB >> /tmp/support.txt
/usr/sbin/lsps -a >> /tmp/support.txt
/usr/sbin/lsattr -HE -l sys0 -a realmem >> /tmp/support.txt
ipcs -m >> /tmp/support.txt

opatch lsinventory -detail

#collect Linux info

arch
cat /etc/issue
whoami
ulimit -a
df -h /dev/shm
ipcs -ma
cat /etc/sysctl.conf
cat /proc/meminfo
cat /proc/swaps
cat /proc/vmstat
opatch lsinventory -detail


Posted

in

by

Tags:

Comments

2 responses to “ORA-4030 PGA Usage Diagnostic Script”

  1. maclean Avatar
    maclean

    ORA-00600: internal error code, arguments: [723], [67108952], [pga heap], [], [], [], [], []

    In trace file….

    ERROR: PGA size limit exceeded: 3224886936 > 3221225472
    ————————————————————————————–
    HEAP DUMP heap name=”pga heap” desc=0x685f220
    extent sz=0x20c0 alt=184 het=32767 rec=0 flg=2 opc=1
    parent=(nil) owner=(nil) nex=(nil) xsz=0x400fff0
    EXTENT 0 addr=0x2b50136010
    Chunk 2b50136020 sz= 65432 free ” ”
    Chunk 2b50145fb8 sz= 67108936 freeable “qesmmCheckPgaL ” ds=0x2a97c2b1e8
    EXTENT 1 addr=0x2b4c126010

    Call stack functions
    ———————————
    ksmapg <- kghgex <- kghfnd <- kghalo<- kghgex <- kghfnd <- kghprmalo <- kghalp <- qesmmCheckPgaLimit

    Changes
    #In init.ora/spfile.ora, the below event have been set to limit the PGA memory leak.
    event = 10261 trace name context forever,level 3145728

    Event 10261 "Limit the size of the PGA heap

    Event 10261
    Text: Limit the size of the PGA heap

    Description:
    This event is useful for PGA memory leaks (and UGA if the UGA is in the PGA). The event causes Oracle to raise an ORA-600 if the PGA tries to grow above the specified size. A PGA heapdump is produced but additional information can be dumped for other heaps by setting an event on ORA-600 within the target process.

    Level:
    The limit is one kilobyte times the level of the event. If the pga grows bigger than this we signal an internal error.

    Cause

    The ORA-600 [723] error was reported because the Event 10261 was set for 3145728k (Approximately 3.2 GB)

    In pfile/spfile, the below event
    event = 10261 trace name context forever,level 3145728

    Solution
    ORA-600 [723] error has been reported because EVENT 10261 has been set to ~3.2Gb. The process getting the error exceeded ~3.2Gb in memory usage; so oracle reported the error and terminated the process.

    event = 10261 trace name context forever,level 3145728
    This is expected because, when event set with the above values, when a process reaches ~3.2GB of PGA memory, it will abort the process, report ORA-600 [723] and generate a trace file accordingly.

    Please unset this event

    OR

    User can ignore this error.

  2. 曾凡坤 Avatar
    曾凡坤

    select case when low_optimal_size < 1024*1024
    then to_char(low_optimal_size/1024,'999999') || 'kb 0
    order by low_optimal_size
    /

    这脚本不全啊

Leave a Reply

Your email address will not be published. Required fields are marked *