In this second article regarding the installation and configuration of Oracle 12c, I’ll discuss the server’s memory configuration and the settings chosen for the default installation. Again, I’m working with Oracle 12c Enterprise Edition Release 18.104.22.168.0 – 64-bit on Windows 7.
Like many, I accepted all of the default configuration options when installing Oracle 12c Enterprise Edition, but I was subsequently surprised to see how much memory the server process was consuming. A quick check confirmed what was going on:
Right – the server’s SGA (System Global Area) size was set by the installation program to 4832MB, and combined with other (lesser) server memory requirements, the server’s memory footprint was over 5GB, a bit excessive for my Thinkpad. The default configuration options aren’t specified in the Oracle 12c Complete Reference, but a bit of sleuthing found the online “managing memory” portion of the online Oracle 12c Database Administrator’s Guide, which reads as follows:
If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled when system memory is less than or equal to 4 gigabytes. When system memory is greater than 4 gigabytes, automatic memory management is disabled, and automatic shared memory management is enabled. If you choose advanced installation, then DBCA enables you to select automatic memory management or automatic shared memory management.
So with Automatic Memory Management (AMM) turned off, Oracle 12c is using the (simpler) Automatic Shared Memory Management (ASSM) to control memory allocation on the server – but the crux of the issue is the default setting for the server’s cache size of 3.8GB, far too large for my needs. The server’s SGA encompasses the bulk of the memory used by the Oracle server, including the buffer pool. In prior releases (8i) of Oracle, the SGA could not change its size dynamically; in 12c that is no longer true, but with AMM turned off the effect was the same. The diagram below is from online Oracle 12c Database Administrator’s Guide:
Oracle has two different configuration file options which are termed PFILE and SPFILE. A PFILE – Parameter File – is a simply a text-mode configuration file. In prior releases this text-mode configuration file was universally named init.ora; in my installation it is called init.ora.4272015182352 in directory “c:\oracle12c\admin\ora12c\pfile”, as in my installation I named the Oracle service “ora12c”. Changes to a PFILE are made manually with a text editor and only take effect on server restart. It is important to note that a text-mode PFILE is only used if an encoded SPFILE cannot be found. An SPFILE – Server Parameter File – is an encoded parameters file that can be updated directly by the server when it executes ALTER SERVER statements. Oracle also supports CREATE PFILE and CREATE SPFILE statements to create either version from an existing file. Ordinarily an ALTER SERVER statement to change the memory configuration only affects the running server; if one specifies the optional SCOPE clause, the SPFILE configuration file will also be modified so that the change will be persistent across server restarts.
My first change was to reduce the amount of memory required for buffer pools and for aggregate processing:
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 100m SCOPE=BOTH; ALTER SYSTEM SET DB_CACHE_SIZE = 450M SCOPE=BOTH;
Once these were set, I was able to set a much lower MEMORY_TARGET and MEMORY_MAX_TARGET for the server as a whole. After a server restart, my memory consumption for the Oracle 12c server is now considerably reduced:
I am going to leave these settings for the moment since I plan on installing the SonarQube service on my machine so I can investigate that tool’s utility in doing static analysis of Java projects; it may well be that I can reduce Oracle’s memory footprint even more. Certainly one can also re-configure the Oracle server service so that the database server is started manually, rather than when Windows 7 boots, though it can be inconvenient.