Maths on Windows
I have four databases on a server. The SHOW SGA command for each displays the following:
Total System Global Area 4680843264 bytes
Total System Global Area 3489660928 bytes
Total System Global Area 8841592832 bytes
Total System Global Area 3942645760 bytes
These figures are all completely expected, since that's about what I set shared pool + buffer cache to. (We're using 10gR2, but not automatic memory management, except we use PGA_AGGREGATE_TARGET). Anyway:
that's a total of about 20.5GB. This server has 30GB of RAM, so that leaves lots of space for PGA. The PGAs for these four databases are configured as follows:
*.pga_aggregate_target=1024M
*.pga_aggregate_target=768M
*.pga_aggregate_target=768M
*.pga_aggregate_target=768M
And that's about 3.25GB in total for the entire set of PGAs. Which means my server ought to be consuming about 25GB, with 5GB going spare for backup software and the like, plus lots of expansion room.
When I do task manager on this server, though, I see:
Physical Memory (K)
Total: 31454892
Available: 8605868
System Cache: 323064
That's correctly reporting 30GB (actually, 29.99 but near enough). And it's saying (I think!) that about 8MB of that is still free and available.
However, the PF Usage bar chart thing reads '31.3GB' -which is interesting as that means 1.3GB more memory than physically exists is in use, and presumably that means no memory at all is actually available. The machine is indeed behaving sluggishly, as though a lot of paging were happening. Therefore, I explore further.
When I use Process Explorer, I see one of my databases reads:
Private Bytes: 10,292,164K
Working Set: 3,360,180 K
And that's for the instance which is showing a total SGA of 3489660928 bytes.
It seems to me that the 'working set' for that database corresponds pretty well to the total SGA figure. I am **guessing** that 'private bytes' refers to, at least in part, the PGA for this instance -which ought to be no more than 768MB.. and yet it's reporting about 3,280MB.
Various questions arise in my head about all of this. Some are more Windows than Oracle, but maybe someone can explain regardless.
First, what is the relationship between the Task Manager 'Total/Available/System Cache' and the PF Usage bar? Why would one say I have 8MB free and the other one report I'm using 1.3GB more memory than physically exists? Why the discrepancy.
Second, why when my SGA+PGA comes in at abut 25GB and nothing else is running on my system does the OS report, in one way or another, that I'm using in the region of 30GB? I believe PGA is set as a 'TARGET', and I have read that target can be exceeded if necessary -but how do I know if that's what's caused this excessive memory consumption?
Third, even if my PGAs do go up over their target, aren't they supposed to go down again in quiet times? My measurements above are taken when the databases are quiet, so that doesn't seem to be happening.
Fourth, am I right in thinking that the 'private bytes' shown in Process Explorer is indeed a reflection, at least substantially, of PGA usage?
And fifth, when I use SQLplus to shutdown the database with the 10GB of private bytes and then restart it, absolutely nothing significant happens to memory usage. 31.3GB before, 31.2GB afterward. However, if I stop and re-start the service behind this instance, memory drops to about 21GB and comes back to about 25GB. To my mind, that suggests some sort of memory leak. This is 64 bit Windows 2003 and 10.2.0.3: is anyone aware of such a problem with that combination?
Sorry for the long post. Any help appreciated.