Home > Internals > Memory Diagnostics – PGA Part 1

Memory Diagnostics – PGA Part 1

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 these structures.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=0x60, 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=0x60, 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=0x60, 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=0x60, 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.

About these ads
Categories: Internals
  1. Dion Cho
    June 22, 2009 at 1:31 am

    Miladin, this is very interesting 11g new feature.

    One question, do you think that unit_test_nolog has the ability to dump subheap, not just top pga?

  2. June 23, 2009 at 8:20 am

    I am following this quite close, but honestly I have not yet found any practical usage to these PGA dumps even for study.

    One practical usage would be to associate such PGA dumps to existing objects in x$kgllk and then using an yet-to-be-found procedure to unpin orphaned handlers. This could spare the shutdown of production database when the permanent memory is so fragmented and your DB is hits with 4031. Don already published a practical method to dump PGA tracefiles and process these dumps as Oracle tables, which is first step to cross their informations with other x$ or even other tracefile.

  3. June 23, 2009 at 10:03 pm

    Dion,

    Option unit_test_nolog has many different arguments.I am in process of discovering them ( hard way ).I am planning to write about it in comming posts.Back to your question , I have to check.Did not find any option to dump subheap so far.

    Thanks,

    Miladin

  4. June 23, 2009 at 10:14 pm

    Polarski,

    Thanks for following my posts.

    There is nothing new about dumping and analyzing heaps and subheaps.Plenty of good resources online and they date back many years.

    This is just another way to do some of the dumps using undocumented and never before published option of oradebug command.
    Also there is use for any of these commands.Sometimes these commands look useless but that is not the case.You just have to find use for it.Oracle uses it itself.That’s why is undocumented.

    Regards,

    Miladin

  5. Hans-Peter
    August 21, 2009 at 11:49 am

    Hi Miladin,

    I am trying to diagnose a ORA-4030 that occurs during impdp (trying to allocate 64544 (sort subheap,sort key).
    The sort subheap is full with 1182 pieces marked permanent most of the of size 64544 (as the one requested).
    Do you have any advice about how to go on? Not much help or Oracle Support.

    regards Hans-peter

  6. August 21, 2009 at 4:42 pm

    Hi Hans,

    Are you getting this error only on impdp? on full impdp or on any impdp ( table level etc )?

    Without knowing much details about your environment I will give you general advice for troubleshooting ORA-4030:

    ORA-4030 means that PGA allocation fails due to the following limits.

    1. Physical memory, Swap space ,ulimit etc
    2. Oracle PGA limits
    3. Bugs,memory leak , application code

    So first check your OS resources.Are you running out of them?

    Then run few generla queries to see PGA usage:

    select * from v$pgastat; for total

    and

    select spid, program, trunc(pga_used_mem/1024/1024) USED,
    trunc(pga_alloc_mem/1024/1024) ALLOC , trunc(pga_max_mem/1024/1024) MAX
    from v$process
    order by pga_alloc_mem desc;

    Next would be to do dumps:

    SQL> connect sys as sysdba
    SQL> oradebug setospid SPID oradebug unlimit
    SQL> oradebug dump heapdump 536870917
    SQL> oradebug tracefile_name

    Also you can use heapdump analyzer.Tanel got one

    http://www.tanelpoder.com/files/scripts/heapdump_analyzer

    It is relatively easy to read it.He also got few good articles on memory troubleshooting explained into detail.

    I am asssuming that you are running version 10g.There are some unpublished bugs related to expdp/impd.I do not have call stack from failing impdp and rest of information and cannot make any conclusions.

    If you cannot quickly resolve issue can you use exp/imp instead or try to use impdp with few objects.That’s why I have asked you are you using full or partial impdp.
    And
    if you have access to metalink support then pick the phone and talk to manager and ask for ADVANCED TEAM.They should be able to help you.

    Please let me know how it goes.If you need further assistance you can send me info to my email

    miladin.modrakovic@gmail.com

    I will see what I can do.

    Thanks,

    Miladin

    • Hans-Peter
      August 24, 2009 at 8:47 am

      Hi Miladin

      Unfortunately I have no direct access myself to the database.
      I instructed people on how to get a heap dump for a worker process.
      We have a least 4 different ORA-04030’s:
      (PLS non-lib hp,pdz2M87_Allocate_Permanent), (sort subheap, sortkey),
      (kxs-heap-c,kghsseg: qcstxsInit),
      (pga heap,buf_kgcstate).
      The physical memory and the ulimits are OK (btw it is AIX 64 bit).
      It is a schema import of a relatively small schema.

      I used the available heap dump analyzers.

      Regards Hans-Peter

  1. June 23, 2009 at 11:24 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 47 other followers

%d bloggers like this: