Discover what process/query is using the oracle SGA.

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.

From stackoverflow

• 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.

  Simon Gibbs : This answer covers topics specifically excluded from the scope of the question.

  Simon Gibbs : Actually, remembering its shared space proved to be a little useful, thanks.