Discover what process/query is using the oracle SGA.

Question

I have a query to monitor SGA (non-)utilisation:

select sum(bytes)/1024/1024 " SGA Free " from v$sgastat where name='free memory';

That's great, but I need more detail about what exactly is using the space. Essentially, I'm looking for clues to tell me more precisely what might be wrong with this (rather large application).

select * from v$sgastat does not give me anything that I recognise as a developer - at least not without some pointers.

Answer 1

Here's a query that I use to see pool size in the SGA

SELECT POOL, NAME, Round(BYTES/1024/1024,2) Mb
FROM V$SGASTAT WHERE POOL LIKE '%pool%'
order by 1,3

Looks great in SQL Developer as a BAR_HORIZ_STACK chart.

All the information you need ought to be in the V$SGASTAT view. Remember that it's shared space, so it's difficult to tie usage to particular processes.