Skip to Main Content
  • Questions
  • Unjustified memory consumption of windows server equal to SGA_MAX_SIZE in 12.2.0.1 (with manual MM)

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jess.

Asked: September 04, 2017 - 2:55 pm UTC

Last updated: September 14, 2017 - 3:28 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Hi,

I just installed the latest Oracle 12.2.0.1 Enterprise Edition and I noticed something different with 12.1.0.2 regarding the memory consumption of the Windows Oracle RDBMS Kernel Executable.

The database is installed on a Windows server 2012R2 with 16GB of server memory.
The database is configured with manual memory management, so we have the following settings : SGA_TARGET=0 AND MEMORY_TARGET=0

Other system parameters are : SGA_MAX_SIZE=16G
DB_CACHE_SIZE=3G
PGA_AGGREGATE_TARGET=400M

Now we see that the oracle process on the windows server has consumed the whole 16 Gigabytes instead of the about expected total of 6 Gigabytes ...

show SGA (looks correct) --->

Total System Global Area 17179869184 bytes
Fixed Size 8767392 bytes
Variable Size 13925091424 bytes
Database Buffers 3221225472 bytes
Redo Buffers 24784896 bytes

-- Pool consumption (looks correct)
select pool, name, round(bytes/1024/1024/1024) GB from v$sgastat where round(bytes/1024/1024/1024) > 0 order by bytes desc, pool, name;

POOL NAME GB
-------------- -------------------------- ----------
buffer_cache 3
shared pool free memory 2

We could lower the SGA_MAX_SIZE to avoid this memory consumption but this does not explain why there is this different behavior.
With manual memory management the taken windows server memory should be the sum of the different oracle sga components instead of immediately the full sga_max_size.

Is this intended new behaviour for this release or perhaps a bug?

Regards.

and Connor said...

My interpretation is this (assuming memory_max_target=0 and memory_target=0 in all cases)

1) sga_target=0 only specified

we obey any settings, and use the defaults if not present

2) sga_target=n only specified

we obey any settings, and we'll consume up to 'n'

3) sga_target=n, sga_max_size=NN

We will start with 'n' *if the platform supports dynamically growing up to sga_max_size*. But if we can't, we have to pre-reserve sga_max_size *anyway* to the oracle instance, so it makes no sense to not the use it.

eg

init.ora v1
===========
db_name="basic"
compatible=12.2.0
diagnostic_dest=c:\oracle
memory_max_target=0
memory_target=0
audit_file_dest="c:\oracle\admin\basic\adump"
audit_trail=db
--
pga_aggregate_target=400m
db_cache_size=3G
sga_target=0

SQL> startup nomount pfile=c:\temp\init.ora
ORACLE instance started.

Total System Global Area 3472883712 bytes
Fixed Size                  8752664 bytes
Variable Size             234881512 bytes
Database Buffers         3221225472 bytes
Redo Buffers                8024064 bytes


init.ora v2
===========

db_name="basic"
compatible=12.2.0
diagnostic_dest=c:\oracle
memory_max_target=0
memory_target=0
audit_file_dest="c:\oracle\admin\basic\adump"
audit_trail=db
--
pga_aggregate_target=400m
db_cache_size=3G
sga_target=0
sga_max_size=8G

SQL> startup nomount pfile=c:\temp\init.ora
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  8767392 bytes
Variable Size            5351934048 bytes
Database Buffers         3221225472 bytes
Redo Buffers                8007680 bytes


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Not an answer on my intended question about SGA_MAX_SIZE in 12.2.0.1 (Windows)

Jess Storme, September 05, 2017 - 2:19 pm UTC

Thank you for your answer, your second case v2 is my situation indeed and there is nothing different with that regarding earlier releases. But my question has to do with what you see in Windows. I observe different behavior by the Windows OS compared to earlier releases such as version 12.1.0.2 and before.

Indeed the bounce free possibility to increase pools (as noted in the manual) is the reason why we would put SGA_MAX_SIZE higher and in this case on 16GB even if we currently only use a small fraction of the 16G. My question has to do with the memory you see used in the TASK Manager in Windows by the Oracle RDBMS kernel Executable (in my demo case Windows 12 R2 is the OS used). It will show in earlier releases of Oracle with my settings only what is used by my parameters (eg. a total here of about 5G-6G) and not more, from 12.2.0.1 on it will show the 16G set by SGA_MAX_SIZE, this is something that has changed. It really takes it all from the OS now.

Why is this, is this a new bug or a bug solved from the past?

You can test it also easily by trying the case below :

A consequence by this noticed change of behavior is that you can't set anymore SGA_MAX_SIZE to more memory than present in the system. So for example if you have 16G of server memory and you do 'alter system set sga_max_size=256G', then restart the DB you will get in 12.2.0.1 -> ORA-27102 etc. This was not the case in earlier releases, there you could set this parameter to a higher number than there was memory available in the OS because it would not take it all immediately, only what really was used. (so it excluded the variable size of the SGA)

Connor McDonald
September 14, 2017 - 3:28 am UTC

I don't see that on my machine, eg

SQL> show sga

Total System Global Area 6442450944 bytes
Fixed Size                  8761232 bytes
Variable Size            1107296368 bytes
Database Buffers         5318377472 bytes
Redo Buffers                8015872 bytes
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 6G
sga_min_size                         big integer 0
sga_target                           big integer 6G
unified_audit_sga_queue_size         integer     1048576


yet on my task manager I see:

ORA_MEM

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database