Geek Answers Handbook

SQLDeveloper uses over 100 MB PGA

This may be fine, but in my Oracle 11g database, I see that programmers using Oracle SQL Developer regularly consume more than 100 MB of combined UGA and PGA memory. I would like to know if this is normal and what can be done about it. Our database is located on a 32-bit version of Windows 2008, so memory limitations are becoming increasingly relevant. I use the following query to show memory usage:

SELECT e.SID, e.username, e.status, b.PGA_MEMORY
FROM v$session e
LEFT JOIN 
   (select y.SID, y.value pga, 
      TO_CHAR(ROUND(y.value/1024/1024),99999999) || ' MB' PGA_MEMORY 
   from v$sesstat y, v$statname z 
   where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b
ON e.sid=b.sid
WHERE (PGA)/1024/1024 > 20
ORDER BY 4 DESC;

Resource usage seems to increase at any time when a table is opened in SQLDeveloper, but even when it is closed, memory does not disappear. The problem is worse if the table is sorted while it is open, as it seems to be using even more memory. I understand how this will use memory during sorting, and maybe even while it is still open, but it seems wrong to use memory after closing it. Can anyone confirm this?

Update: I found that my numbers were disconnected due to the fact that they did not understand that UGA was stored in PGA in dedicated server mode . This makes the numbers lower than they were, but the problem still remains that SQL Developer seems to be using excessive PGA.

+5
3

, SQL Developer , . , , , SQL Developer 20 , , .

, PGA, ( ), EOF ( ).

:

select sql_id,operation_type,actual_mem_used,max_mem_used,tempseg_size
from v$sql_workarea_active
where sid = &SID_OF_INTEREST

, ...

+3

Oracle, AMM, , . , . : 20 , . Oracle , .

I believe a simple test should ease your problems. If it is 32-bit and each SQL Developer session uses 100 MB + RAM, you will need only a few hundred sessions to cause a low memory problem ... if that is true.

0
source

More articles:


All Articles