Re: How can I tell how much memory a database is using?
- From: "David Fitzjarrell" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "oratune@xxxxxxxxx" for DMARC)
- To: "sbecker6925@xxxxxxxxx" <sbecker6925@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Jan 2015 20:25:16 +0000 (UTC)
It depends on the O/S. If it's a Linux/UNIX system then you can get the shared
memory footprint using ipcs -m as the 'oracle' user. If you're using NUMA
you'll see one entry with a non-zero address and one or more additional entries
with a 0 address value. As an example here is the output from that command
from one of our Linux servers:
$ ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 3637251 oracle 640 8388608 58
0x00000000 3670020 oracle 640 1308622848 58
0x27126a4c 3702789 oracle 640 2097152 58
0x00000000 9404422 oracle 640 268435456 558
0x00000000 9437191 oracle 640 23353884672 558
0x64d65604 9469960 oracle 640 2097152 558
0x00000000 3899401 oracle 640 167772160 103
0x00000000 3932170 oracle 640 3053453312 103
0x5d2c1050 3964939 oracle 640 2097152 103
0x00000000 4030476 oracle 640 150994944 53
0x00000000 4063245 oracle 640 1996488704 53
0x2bd68bd0 4096014 oracle 640 2097152 53
0x00000000 4161551 oracle 640 184549376 61
0x00000000 4194320 oracle 640 6257901568 61
0x5546f8dc 4227089 oracle 640 2097152 61
0x00000000 4292626 oracle 640 184549376 62
0x00000000 4325395 oracle 640 6257901568 62
0x75ff1d14 4358164 oracle 640 2097152 62
0x00000000 4423701 oracle 640 167772160 61
0x00000000 4456470 oracle 640 3053453312 61
0x2e3eafac 4489239 oracle 640 2097152 61
0x00000000 4554776 oracle 640 150994944 61
0x00000000 4587545 oracle 640 1996488704 61
0x42121e5c 4620314 oracle 640 2097152 61
0x00000000 8486939 oracle 640 167772160 214
0x00000000 8519708 oracle 640 4127195136 214
0xa7a3279c 8552477 oracle 640 2097152 214
I would say that the nattch values should match for all related segments but
that's not the case with this output. The 0 address lines do associate with
the non-zero address at the 'bottom' of each group (notice in this case there
are three segments in each group). Find the non-zero address value and sum the
bytes for that entry with the 0-address lines above it to get the total shared
memory for that group. Of course you could also connect to each database and
issue 'show sga' to get what Oracle says it's using. The PGA is a different
story as that is governed by the number of user connections to the database.
The advisors will also show you what is currently configured in the database;
unlike the SGA hard limit the PGA 'limit' is a request to allocate memory up to
the specified aggregate. It's a 'suggestion' and it can be exceeded without
error. To return what is currently being used by the database you can use the
following query:
select sum(PGA_USED_MEM)/1024/1024 , sum(PGA_ALLOC_MEM)/1024/1024,
sum(PGA_FREEABLE_MEM)/1024/1024, sum(PGA_MAX_MEM)/1024/1024
from v$process
/
Know that PGA_USED_MEM can exceed the pga_aggregate_target setting. David
Fitzjarrell
Principal author, "Oracle Exadata Survival Guide"
On Thursday, January 15, 2015 11:35 AM, Sandra Becker
<sbecker6925@xxxxxxxxx> wrote:
We're moving all of our databases to new servers. SEs are wanting to know how
much memory each of the new servers will need to host the databases. We're
moving from a set of 5 down to a set of 3. Mix of releases from 9.2.0.4 to
11.2.0.4.
--
Sandy
GHX
Other related posts:
