Re: Trying to understand PGA usage (20GB) for ONE process/session
Date: Mon, 13 Nov 2017 18:44:26 +0100 (CET)
Message-ID: <172906849.142656.1510595066280_at_ox.hosteurope.de>
Hey Chris,
and here we go ... category "Other" is not under control of pga_aggregate_target (and its sub-limits like _pga_max_size, etc.).
Depending on your Oracle version you have to dump the PGA/UGA heap or you can use view V$PROCESS_MEMORY_DETAIL to drill down further more. I had a talk about this and you may find it useful for troubleshooting:
- http://www.soocs.de/public/talk/161115_DOAG2016_Hacking_Oracles_Memory_About_Internals_Troubleshooting_PPT.pdf
- http://www.soocs.de/public/talk/161115_DOAG2016_Hacking_Oracles_Memory_About_Internals_Troubleshooting_PDF.pdf
You also can drill-down into bug / memory leaks and find the corresponding code part very easily ...
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: _at_OracleSK
Chris Taylor <christopherdtaylor1994_at_gmail.com> hat am 13. November 2017 um 18:33 geschrieben:-- http://www.freelists.org/webpage/oracle-l Received on Mon Nov 13 2017 - 18:44:26 CETThat query confirms the usage:
SID
SPID
TOTAL_ALLOCATED
PID
SERIAL#
CATEGORY
ALLOCATED
USED
MAX_ALLOCATED
CON_ID
100
161947
27389800644
805
172
SQL
69598064
24
87929160
0
100
161947
27389800644
805
172
Other
27320114452
27320114452
0
100
161947
27389800644
805
172
PL/SQL
88128
9216
95288
0
Now I need to understand how one session can be allocated that much (outside of a bug/memory leak).Chris