Re: How can I tell how much memory a database is using?
- From: Kenny Payton <k3nnyp@xxxxxxxxx>
- To: dmarc-noreply@xxxxxxxxxxxxx
- Date: Thu, 15 Jan 2015 20:01:36 -0500
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
> <mailto: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 <http://mgogala.freehostia.com/>
Other related posts:
