Memory Diagnostics – PGA Part 1

Memory Diagnostics – PGA Part 1

June 19, 2009oraclueLeave a commentGo to comments

Second and very short post related to Oracle memory structures.This time I will focus on PGA .

Oracle offers many diffrents tools ( views, tables, dumps etc.. ) to gather information about thesestructures.This time I will introduce hidden oradebug options.

Reading these files is not difficult. There are also  heap analyzers out there on internet.

Since these dump files looks very familiar I will just focus on commands and syntax.

This is 3 line script to perform these dumps:

oradebug setmypid
oradebug unit_test_nolg &p
oradebug tracefile_name

First command will dump  PGAs information for all processes:

SQL> @od
Statement processed.
Enter value for p: dump_pga_details
Statement processed.

===============================================
PGA memory detail for pid 2, OS pid 13741
===============================================
2072 bytes,   3 chunks: “miscellaneous             ”  PL/SQL
multiple heaps  ds=(nil)  dsprt=(nil)
42928 bytes,  90 chunks: “miscellaneous             “
multiple heaps  ds=(nil)  dsprt=(nil)
155824 bytes,  20 chunks: “permanent memory          “
pga heap        ds=0x9e5fa20  dsprt=(nil)
53120 bytes,   2 chunks: “free memory               “
session heap    ds=0x2aaaabd66998  dsprt=0x9e65120
44608 bytes,   1 chunk : “permanent memory          “
session heap    ds=0x2aaaabd66998  dsprt=0x9e65120
32792 bytes,   1 chunk : “permanent memory          “

———————————————————-
===============================================
PGA memory detail for pid 3, OS pid 13743
===============================================
141976 bytes,  18 chunks: “permanent memory          “
pga heap        ds=0x9e5fa20  dsprt=(nil)
53120 bytes,   2 chunks: “free memory               “
session heap    ds=0x2aaaabd66998  dsprt=0x9e65120
44608 bytes,   1 chunk : “permanent memory          “
session heap    ds=0x2aaaabd66998  dsprt=0x9e65120
32792 bytes,   1 chunk : “permanent memory          “
top call heap   ds=0x9e64f00  dsprt=(nil)
31608 bytes,   1 chunk : “free memory               “
top call heap   ds=0x9e64f00  dsprt=(nil)
31328 bytes,   1 chunk : “Fixed Uga                 “
pga heap        ds=0x9e5fa20  dsprt=(nil)
27248 bytes,   6 chunks: “free memory

——————————————————————

Here is second command.Self explanatory.

SQL> oradebug unit_test_nolg dump_top_pga
Statement processed.

*** 2009-06-19 15:54:10.612
******************************************************
HEAP DUMP heap name=”pga heap”  desc=0x9e5fa20
extent sz=0x20c0 alt=184 het=32767 rec=0 flg=2 opc=2
parent=(nil) owner=(nil) nex=(nil) xsz=0xfff0 heap=(nil)
fl2=0×60, nex=(nil)
EXTENT 0 addr=0x2aaaac090010
Chunk     2aaaac090020 sz=    34176    free      ”               “
Chunk     2aaaac0985a0 sz=    31328    freeable  “Fixed Uga      “
EXTENT 1 addr=0x2aaaac080010
Chunk     2aaaac080020 sz=    34768    perm      “perm           ”  alo=8624
Chunk     2aaaac0887f0 sz=    19256    free      ”               “
Chunk     2aaaac08d328 sz=     4224    freeable  “Alloc environm ”  ds=0x2aaaabc97620
Chunk     2aaaac08e3a8 sz=     4224    freeable  “Alloc environm ”  ds=0x2aaaabc97620
Chunk     2aaaac08f428 sz=      592    freeable  “kopolal void   “
Chunk     2aaaac08f678 sz=      432    freeable  “kopolal void   “
Chunk     2aaaac08f828 sz=      344    freeable  “kopolal void   “
Chunk     2aaaac08f980 sz=      592    freeable  “kopolal void   “
Chunk     2aaaac08fbd0 sz=     1072    freeable  “kopolal void   “
EXTENT 2 addr=0x2aaaabcd1e70
Chunk     2aaaabcd1e80 sz=     4184    freeable  “diag pga       ”  ds=0x2aaaabb547a0
Chunk     2aaaabcd2ed8 sz=     4184    freeable  “diag pga       ”  ds=0x2aaaabb547a0
EXTENT 3 addr=0x2aaaabccfd80
Chunk     2aaaabccfd90 sz=     4184    freeable  “diag pga       ”  ds=0x2aaaabb547a0
Chunk     2aaaabcd0de8 sz=     4184    freeable  “diag pga       ”  ds=0x2aaaabb547a0
EXTENT 4 addr=0x2aaaabccdc80
Chunk     2aaaabccdc90 sz=     8384    freeable  “diag pga       ”  ds=0x2aaaabb547a0
EXTENT 5 addr=0x2aaaabccbb80

We can accomplish this  by running these commands with certain level

oradebug dump HEAPDUMP

oradebug dump PGA_DETAIL_GET

oradebug dump PGA_DETAIL_DUMP

oradebug dump PGA_DETAIL_CANCEL

etc..

Here is useful addition.Using this command I can perform heap stress with different number of iterations and various number of subheaps and buffers.

Heap stress test:

SQL> @od
Statement processed.
Enter value for p: heap_stress_test 100
Statement processed.

*** 2009-06-19 15:43:50.205
iterations: 100
maximum subheaps: 2
maximum buffers: 14
maximum bytes allocated: 450348
cumulative bytes allocated: 1636817
******************************************************
HEAP DUMP heap name=”pga heap”  desc=0x9e5fa20
extent sz=0x20c0 alt=184 het=32767 rec=0 flg=2 opc=3
parent=(nil) owner=(nil) nex=(nil) xsz=0xfff0 heap=(nil)
fl2=0×60, nex=(nil)
EXTENT 0 addr=0x2aaaac9a5010
Chunk     2aaaac9a5020 sz=     1320    free      ”               “
Chunk     2aaaac9a5548 sz=      160    freeable  “ksmhs subheap d”
Chunk     2aaaac9a55e8 sz=    64024    free      ”               “
EXTENT 1 addr=0x2aaaac995010
Chunk     2aaaac995020 sz=    12416    free      ”               “
Chunk     2aaaac9980a0 sz=    19512    freeable  “ksmhs buffer   “
Chunk     2aaaac99ccd8 sz=    33576    freeable  “ksmhs buffer   “
EXTENT 2 addr=0x2aaaac985010
Chunk     2aaaac985020 sz=     1136    free      ”               “
Chunk     2aaaac985490 sz=     3120    freeable  “ksmhs buffer   “
Chunk     2aaaac9860c0 sz=     8560    free      ”               “
Chunk     2aaaac988230 sz=    52688    freeable  “ksmhs buffer   “
EXTENT 3 addr=0x2aaaac975010
Chunk     2aaaac975020 sz=     9080    free      ”               “
Chunk     2aaaac977398 sz=    16704    freeable  “ksmhs buffer   “
Chunk     2aaaac97b4d8 sz=    39720    freeable  “ksmhs subheap  ”  ds=0x2aaaac9a5560
EXTENT 4 addr=0x2aaaac0d0010
Chunk     2aaaac0d0020 sz=    21032    free      ”               “
Chunk     2aaaac0d5248 sz=    44472    freeable  “ksmhs subheap  ”  ds=0x2aaaac9a5560

SQL> @od
Statement processed.
Enter value for p: heap_stress_test 1000
Statement processed.

*** 2009-06-19 15:44:02.223
iterations: 1000
maximum subheaps: 8
maximum buffers: 18
maximum bytes allocated: 560147
cumulative bytes allocated: 15866914
******************************************************
HEAP DUMP heap name=”pga heap”  desc=0x9e5fa20
extent sz=0x20c0 alt=184 het=32767 rec=0 flg=2 opc=3
parent=(nil) owner=(nil) nex=(nil) xsz=0xfff0 heap=(nil)
fl2=0×60, nex=(nil)
EXTENT 0 addr=0x2aaaaca35010
Chunk     2aaaaca35020 sz=    21440    free      ”               “
Chunk     2aaaaca3a3e0 sz=    44064    freeable  “ksmhs buffer   “
EXTENT 1 addr=0x2aaaaca05010
Chunk     2aaaaca05020 sz=     5632    free      ”               “
Chunk     2aaaaca06620 sz=      160    freeable  “ksmhs subheap d”
Chunk     2aaaaca066c0 sz=      112    freeable  “ksmhs subheap i”
Chunk     2aaaaca06730 sz=   125136    free      ”               “
EXTENT 2 addr=0x2aaaac9a5010
Chunk     2aaaac9a5020 sz=       88    free      ”               “
Chunk     2aaaac9a5078 sz=      160    freeable  “ksmhs subheap d”
Chunk     2aaaac9a5118 sz=      112    freeable  “ksmhs subheap i”
Chunk     2aaaac9a5188 sz=      160    freeable  “ksmhs subheap d”
Chunk     2aaaac9a5228 sz=      112    freeable  “ksmhs subheap i”
Chunk     2aaaac9a5298 sz=    48016    free      ”               “
Chunk     2aaaac9b0e28 sz=    16856    recreate  “ksmhs subheap  ”  latch=(nil)
ds     2aaaac9a5090 sz=    16856 ct=        1

SQL> @od
Statement processed.
Enter value for p: heap_stress_test 10000
Statement processed.

*** 2009-06-19 15:44:13.028
iterations: 10000
maximum subheaps: 14
maximum buffers: 43
maximum bytes allocated: 1548196
cumulative bytes allocated: 168945876
******************************************************
HEAP DUMP heap name=”pga heap”  desc=0x9e5fa20
extent sz=0x20c0 alt=184 het=32767 rec=0 flg=2 opc=3
parent=(nil) owner=(nil) nex=(nil) xsz=0xfff0 heap=(nil)
fl2=0×60, nex=(nil)
EXTENT 0 addr=0x2aaaacae5010
Chunk     2aaaacae5020 sz=    46208    free      ”               “
Chunk     2aaaacaf04a0 sz=    19296    freeable  “ksmhs buffer   “
EXTENT 1 addr=0x2aaaac1b5010
Chunk     2aaaac1b5020 sz=    60872    free      ”               “
Chunk     2aaaac1c3de8 sz=  8000024    freeable  “ksmhs buffer pt”
EXTENT 2 addr=0x2aaaac0e5010
Chunk     2aaaac0e5020 sz=    51912    free      ”               “
Chunk     2aaaac0f1ae8 sz=   800024    freeable  “ksmhs subheap p”
EXTENT 3 addr=0x2aaaac090010
Chunk     2aaaac090020 sz=    34176    free      ”               “
Chunk     2aaaac0985a0 sz=    31328    freeable  “Fixed Uga      “
EXTENT 4 addr=0x2aaaac080010

Also I can use  Oracle events to collect additional data about PGA but I will write about it when I get a chance.