CODEWITHSUNDEEP
performanceoracle-databasedebuggingstatisticsoracleinternals
Simon Gibbs
Simon Gibbs

Reputation: 4818

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.

Upvotes: 1

Views: 6829

Answers (1)

David Aldridge
David Aldridge

Reputation: 52376

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.

Upvotes: 1

Related Questions