1

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.

Yaroslav Shabalin
  • 1,645
  • 3
  • 17
  • 29
Simon Gibbs
  • 4,737
  • 6
  • 50
  • 80

1 Answers1

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.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96