Rapid PGA size increase_ora-01503 ora-00600-CSDN博客

Rapid PGA size increase

A couple of days ago, one of our customers experienced a rapid PGA increase and the system got rebounced by PMON. We suggested dumping the PGA heap while the problem reoccurs. But the problem is that the PGA increase is too rapid(upto 3.6G in less than 2 min), so it is a bit hard to get the PGA dump manually.

Fortunately, in situation like this, Oracle provides a way of getting automatic PGA heap dump.

1. First, let me set the 10261 diagnostic event to limit the size of the PGA heap. For instance, following command will limit the size of the PGA heap to 100000KB.

alter system set events '10261 trace name context forever, level 100000';

2. With the 10261 event set, the process would fail with ORA-600 [723] when the size of the PGA heap reaches 100000KB. As you already know, the 10261 event is used as a band-aid for the ORA-4030 error.

-- make big pga
declare
 type varchar2_array is table of varchar2(32767) index by pls_integer;
 vc  varchar2_array;
 v  varchar2(32767);
begin
 for idx in 1 .. 10000 loop
 v := rpad('x',32767,'x');
 vc(idx) := v;
 end loop;
end;
/

ERROR at line 1:
ORA-00600: internal error code, arguments: [723], [41000], [pga heap], [], [],
[], [], []

3. Now, let me set the 600 diagnostic event to get the heap dump when the process hits ORA-600 error. With the combination of 10261 and 600 event, the process would record the PGA heap dump automatically when the PGA heap size reaches the limit.

alter system set events '600 trace name heapdump level 0x20000001';

4. When the PGA heap size reaches the limit(100000KB in this case) again, I would have the complete PGA heap dump including all the recursive subheaps by virtue of the dump level 0×20000001.

DDE: Problem Key 'ORA 600 [723]' was flood controlled (0x2) (incident: 44800)
ORA-00600: internal error code, arguments: [723], [41000], [pga heap], [], [], [], [], []
****** ERROR: PGA size limit exceeded: 102450812 > 102400000 *****
******************************************************
HEAP DUMP heap name="pga heap"  desc=11AFB098
 extent sz=0x206c alt=92 het=32767 rec=0 flg=2 opc=1
 parent=00000000 owner=00000000 nex=00000000 xsz=0xfff8 heap=00000000
 fl2=0x60, nex=00000000
EXTENT 0 addr=39150008
  Chunk 39150010 sz=    24528    free      "               "
  Chunk 39155fe0 sz=    40992    freeable  "koh-kghu call  "  ds=0D4D9A60
EXTENT 1 addr=39140008
  Chunk 39140010 sz=    24528    free      "               "
  Chunk 39145fe0 sz=    40992    freeable  "koh-kghu call  "  ds=0D4D9A60
...

5. The last step is to analyze the heap dump. For instance, I have my own library to analyze the heap dump.

select * from table(tpack.heap_file_report('C:\oracle\diag\rdbms\ukja1106\ukja1106\trace\ukja1106_ora_3640.trc'));

TYPE     HEAP_NAME        ITEM             ITEM_COUNT  ITEM_SIZE  HEAP_SIZE      RATIO
-------- ---------------- ---------------- ---------- ---------- ---------- ----------
HEAP     pga heap                                   0      97.14      97.14        100
HEAP     top call heap                              0        .18        .18        100
HEAP     top uga heap                               0        .31        .31        100
CHUNK    pga heap         free                   1554       36.2       97.1       37.3
CHUNK    pga heap         recreate                  9          0       97.1          0
CHUNK    pga heap         perm                     14          0       97.1          0
CHUNK    pga heap         freeable               1597       60.7       97.1       62.5
CHUNK    top uga heap     recreate                  1          0         .3       19.9
CHUNK    top uga heap     free                      5          0         .3          0
CHUNK    top uga heap     freeable                  4         .2         .3       79.9
CHUNK    top call heap    free                      3         .1         .1       65.5
CHUNK    top call heap    recreate                  2          0         .1          1
CHUNK    top call heap    freeable                  1          0         .1       33.3
CHUNK    top call heap    perm                      1          0         .1          0
OBJECT   pga heap         kews sqlstat st           1          0       97.1          0
OBJECT   pga heap         pesom.c:Proces            3          0       97.1          0
...

6. It is also possible to write the script to monitor the V$SESSTAT view(session pga memory) and execute the heap dump when the value hits some specific threshold. For instance, I have my own library which is used like following.

col report_id new_value report_id

select tpack_server.create_report('Heap Dump') as report_id from dual;

exec tpack_server.add_parameter('&report_id', 'dump_level', '0x20000001');
exec tpack_server.add_parameter('&report_id', 'get_whole_contents', 0);

exec tpack_server.add_condition('&report_id', 'STAT', 'session pga memory', '>100000000', 'SUM');

exec tpack_server.register_report('&report_id');

-- start server
exec tpack_server.start_server;

When the PGA heap size hits the limit(100000000B), it would execute the predefined procedure which executes the heap dump.

Fri Jun 11 06:19:10 GMT+00:00 2010 : Session 142 got! sum=659645392, name = session pga memory
...
Fri Jun 11 06:27:50 GMT+00:00 2010 : executing report 1:142:1973827792 for session 142
Fri Jun 11 06:27:55 GMT+00:00 2010 : executing report = begin tpack.heap_dump(  dump_level=>'0x20000001', get_whole_contents=>0,  session_id => 142); end;
...

Also note that the combination of 10261 and 600 event is just a temporary solution and the most important thing is to analyze the heap dump very carefully to make the rapid PGA increase never happen again!

Written by Dion Cho

June 14, 2010 at 2:10 am

Posted in Troubleshooting