Re: How can I tell how much memory a database is using?

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 15 Jan 2015 20:25:16 +0000
Message-ID: <335814984.1083957.1421353516256.JavaMail.yahoo_at_jws100174.mail.ne1.yahoo.com>

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_at_gmail.com> 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


   
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 15 2015 - 21:25:16 CET