0

My question deals specifically with a way to tell how much memory is being used by an Oracle instance on a UNIX (Solaris) server.

For example (I'm gonna really oversimplify this...), if I see that there's 8GB of RAM in use, is there a "simple" method (either through TOAD or some UNIX tool) to identify what portion of that memory is being used by a particular Oracle instance?

Through contact with an Oracle instructor and Sun support site searches, I was able to find "pmap" command usage to obtain the "anon" memory for Oracle processes, but hopefully someone will have an alternate solution.

I do have the Sun Management Center at my disposal (if that helps), and I'm open to any solution, even bribing the server with burgers and beer. Thanks everyone!

Dizzle
  • 175
  • 1
  • 2
  • 8

2 Answers2

1

There's two types of memory for an Oracle instance. The SGA is shared memory, and PGA is memory only accessible to the individual processes. If you connect to an instance with appropriate privileges (eg DBA or with SELECT_CATALOG_ROLE) you can

SELECT SUM(VALUE) FROM V$SGA;

SELECT SUM(PGA_ALLOC_MEM) FROM V$PROCESS;

to see the totals for each of those.

Gary
  • 1,839
  • 10
  • 14
  • Thanks for the answer Gary; I'll give this a shot tomorrow. – Dizzle Jul 30 '09 at 23:10
  • Thanks Gary, I'm not sure yet if this is exactly what I needed, but it appears to be leading me down the right track. I see where there are a couple of other PGA parameters available; PGA_USED_MEM and PGA_MAX_MEM. So right now I'm trying to wrap my head around the data they provide versus what's provided by other tools (such as prstat and pmap). I should be able to report back by Wednesday of next week. Thanks again! – Dizzle Jul 31 '09 at 21:28
  • This hasn't given me the exact answer yet, but it has led me to other great information, thanks again Gary. I'll put this as answered because I'll be tinkering for a while, with the usual requisite of interruptions I suspect. Thanks! – Dizzle Aug 05 '09 at 15:38
  • By Googling "PGA_ALLOC_MEM" I came across a site that subsequently led me to others, each with great information. Here's the initial site: http://dbaforums.org/oracle/index.php?showtopic=8968 – Dizzle Aug 05 '09 at 15:42
1

If Oracle runs on a separate user, you can use prstat to see how much memory it uses overall:

prstat -a
MoshiBin
  • 390
  • 2
  • 7
  • Thanks Spidey, Oracle does run on a separate user, and running prstat appeared to give me what I'm looking for, I just need to confirm with our DBA group that a summation of the Oracle user's memory is a stat we can report on and be confident with. I'll reply back in a day or two, but this helps, thank you. – Dizzle Jul 31 '09 at 19:28
  • So what I found out from our DBA crew is that there is an Oracle-specific user, but there are multiple databases, so the trick now is to discern between processes for specific instances. And each process run with an argument appended with the DB name, so I'm getting closer. The prstat info was helpful though, thanks again. – Dizzle Aug 05 '09 at 15:33
  • The problem is that Solaris shows all that shared SGA area as beloging to each process. So `prstat -a` will show you user 'oracle' is using a lot more memory than it really is (because it's summing all the VSZ of those processes). – gtirloni Jun 15 '11 at 19:44