Re: How can I tell how much memory a database is using?
- From: Keith Moore <kmoore@xxxxxxxxxxxx>
- To: k3nnyp@xxxxxxxxx
- Date: Sun, 18 Jan 2015 22:55:52 -0600
Yes, it’s easy to forget about process memory and it may not be significant if
there aren’t many connections.
But, we support an application that that has connection pooling issues so
processes don’t get reused and we end up with 1000+ connections. Even at 10 MB
per process, that is 10 GB of process memory. For a database with a 4 GB SGA,
that’s significant.
Keith
On Jan 15, 2015, at 7:01 PM, Kenny Payton <k3nnyp@xxxxxxxxx> wrote:
>
> You might also want to look at process memory, on Linux 11gR2 I use 20MB per
> session as an example but this can vary.
>
> If you’re not using huge pages and have a large number of connections with
> large SGA I’d suggest considering PageTable (vmPTE) usage from /proc/meminfo.
> Page Tables grow per process as the session accesses pages in the shared
> memory segment. 8 bytes for every page accessed during the sessions life.
> Here is a quick calculation for a 30G SGA and 2,000 connections.
>
> These are maximums, you’re mileage might vary depending on session SGA usage.
>
> Normal 4k pages - 30G/4K * 2,000 = 60MB/session * 2,000 = 117GB
> Huge Pages ( 2MB ) - 30G/2M * 2,000 = 120KB * 2,000 = 234MB
>
>
>
> Kenny
>
>
>
>> On Jan 15, 2015, at 7:48 PM, Mladen Gogala (Redacted sender
>> "mgogala@xxxxxxxxx" for DMARC) <dmarc-noreply@xxxxxxxxxxxxx> wrote:
>>
>> On 01/15/2015 03:25 PM, David Fitzjarrell (Redacted sender oratune@xxxxxxxxx
>> for DMARC) wrote:
>>> 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
>>
>> V$SGAINFO has all the necessary info neatly summarized:
>> SQL> select * from v$sgainfo;
>>
>> NAME BYTES RES
>> -------------------------------- ---------- ---
>> Fixed SGA Size 2252824 No
>> Redo Buffers 8892416 No
>> Buffer Cache Size 838860800 Yes
>> Shared Pool Size 301989888 Yes
>> Large Pool Size 83886080 Yes
>> Java Pool Size 16777216 Yes
>> Streams Pool Size 0 Yes
>> Shared IO Pool Size 0 Yes
>> Granule Size 16777216 No
>> Maximum SGA Size 1252663296 No
>> Startup overhead in Shared Pool 87253648 No
>>
>> NAME BYTES RES
>> -------------------------------- ---------- ---
>> Free SGA Memory Available 0
>>
>> 12 rows selected.
>>
>> SQL> select round(sum(bytes)/1048576,2) MB from v$sgainfo;
>>
>> MB
>> ----------
>> 2488.47
>>
>> SQL>
>>
>>
>> That would give you the size of SGA. The size of the PGA is unimportant
>> because hashing and sorting are soooo XX century.
>>
>> --
>> Mladen Gogala
>> Oracle DBA
>> http://mgogala.freehostia.com
>
Other related posts:
