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=0×9e5fa20 dsprt=(nil)
53120 bytes, 2 chunks: “free memory “
session heap ds=0×2aaaabd66998 dsprt=0×9e65120
44608 bytes, 1 chunk : “permanent memory “
session heap ds=0×2aaaabd66998 dsprt=0×9e65120
32792 bytes, 1 chunk : “permanent memory “
———————————————————-
===============================================
PGA memory detail for pid 3, OS pid 13743
===============================================
141976 bytes, 18 chunks: “permanent memory “
pga heap ds=0×9e5fa20 dsprt=(nil)
53120 bytes, 2 chunks: “free memory “
session heap ds=0×2aaaabd66998 dsprt=0×9e65120
44608 bytes, 1 chunk : “permanent memory “
session heap ds=0×2aaaabd66998 dsprt=0×9e65120
32792 bytes, 1 chunk : “permanent memory “
top call heap ds=0×9e64f00 dsprt=(nil)
31608 bytes, 1 chunk : “free memory “
top call heap ds=0×9e64f00 dsprt=(nil)
31328 bytes, 1 chunk : “Fixed Uga “
pga heap ds=0×9e5fa20 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=0×9e5fa20
extent sz=0×20c0 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=0×2aaaac090010
Chunk 2aaaac090020 sz= 34176 free “ “
Chunk 2aaaac0985a0 sz= 31328 freeable “Fixed Uga “
EXTENT 1 addr=0×2aaaac080010
Chunk 2aaaac080020 sz= 34768 perm “perm “ alo=8624
Chunk 2aaaac0887f0 sz= 19256 free “ “
Chunk 2aaaac08d328 sz= 4224 freeable “Alloc environm “ ds=0×2aaaabc97620
Chunk 2aaaac08e3a8 sz= 4224 freeable “Alloc environm “ ds=0×2aaaabc97620
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=0×2aaaabcd1e70
Chunk 2aaaabcd1e80 sz= 4184 freeable “diag pga “ ds=0×2aaaabb547a0
Chunk 2aaaabcd2ed8 sz= 4184 freeable “diag pga “ ds=0×2aaaabb547a0
EXTENT 3 addr=0×2aaaabccfd80
Chunk 2aaaabccfd90 sz= 4184 freeable “diag pga “ ds=0×2aaaabb547a0
Chunk 2aaaabcd0de8 sz= 4184 freeable “diag pga “ ds=0×2aaaabb547a0
EXTENT 4 addr=0×2aaaabccdc80
Chunk 2aaaabccdc90 sz= 8384 freeable “diag pga “ ds=0×2aaaabb547a0
EXTENT 5 addr=0×2aaaabccbb80
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=0×9e5fa20
extent sz=0×20c0 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=0×2aaaac9a5010
Chunk 2aaaac9a5020 sz= 1320 free “ “
Chunk 2aaaac9a5548 sz= 160 freeable “ksmhs subheap d”
Chunk 2aaaac9a55e8 sz= 64024 free “ “
EXTENT 1 addr=0×2aaaac995010
Chunk 2aaaac995020 sz= 12416 free “ “
Chunk 2aaaac9980a0 sz= 19512 freeable “ksmhs buffer “
Chunk 2aaaac99ccd8 sz= 33576 freeable “ksmhs buffer “
EXTENT 2 addr=0×2aaaac985010
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=0×2aaaac975010
Chunk 2aaaac975020 sz= 9080 free “ “
Chunk 2aaaac977398 sz= 16704 freeable “ksmhs buffer “
Chunk 2aaaac97b4d8 sz= 39720 freeable “ksmhs subheap “ ds=0×2aaaac9a5560
EXTENT 4 addr=0×2aaaac0d0010
Chunk 2aaaac0d0020 sz= 21032 free “ “
Chunk 2aaaac0d5248 sz= 44472 freeable “ksmhs subheap “ ds=0×2aaaac9a5560
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=0×9e5fa20
extent sz=0×20c0 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=0×2aaaaca35010
Chunk 2aaaaca35020 sz= 21440 free “ “
Chunk 2aaaaca3a3e0 sz= 44064 freeable “ksmhs buffer “
EXTENT 1 addr=0×2aaaaca05010
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=0×2aaaac9a5010
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=0×9e5fa20
extent sz=0×20c0 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=0×2aaaacae5010
Chunk 2aaaacae5020 sz= 46208 free “ “
Chunk 2aaaacaf04a0 sz= 19296 freeable “ksmhs buffer “
EXTENT 1 addr=0×2aaaac1b5010
Chunk 2aaaac1b5020 sz= 60872 free “ “
Chunk 2aaaac1c3de8 sz= 8000024 freeable “ksmhs buffer pt”
EXTENT 2 addr=0×2aaaac0e5010
Chunk 2aaaac0e5020 sz= 51912 free “ “
Chunk 2aaaac0f1ae8 sz= 800024 freeable “ksmhs subheap p”
EXTENT 3 addr=0×2aaaac090010
Chunk 2aaaac090020 sz= 34176 free “ “
Chunk 2aaaac0985a0 sz= 31328 freeable “Fixed Uga “
EXTENT 4 addr=0×2aaaac080010
Also I can use Oracle events to collect additional data about PGA but I will write about it when I get a chance.
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?
Comment by Dion Cho — June 22, 2009 @ 1:31 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.
Comment by Bernard Polarski — June 23, 2009 @ 8:20 am
[...] Miladin Madrakovic - Memory Diagnostics – PGA Part 1 [...]
Pingback by Blogroll Report 12/06/09 – 19/06/09 « Coskan’s Approach to Oracle — June 23, 2009 @ 11:24 am
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
Comment by oraclue — June 23, 2009 @ 10:03 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
Comment by oraclue — June 23, 2009 @ 10:14 pm
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
Comment by Hans-Peter — August 21, 2009 @ 11:49 am
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
Comment by oraclue — August 21, 2009 @ 4:42 pm
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
Comment by Hans-Peter — August 24, 2009 @ 8:47 am