Top 25 initialization parameters

TOP 25 INITIALIZATION PARAMETERS

The following list is my list of the top 25 most important initialization parameters, in order of importance.

1.   MEMORY_TARGET This is the initialization parameter setting for all of the memory allocated to both the PGA and SGA combined (new in 11g). Setting MEMORY_TARGET enables Automatic Memory Management, so Oracle allocates memory for you based on system needs, but you can also set minimum values for key parameters. MEMORY_TARGET is used for everything that SGA_TARGET was used for but now additionally includes the PGA (especially important as MEMORY_TARGET now includes the important area PGA_AGGREGATE_TARGET). Important parameters such as DB_CACHE_SIZE, SHARED_POOL_SIZE, PGA_AGGREGATE_TARGET, LARGE_POOL_SIZE, and JAVA_POOL_SIZE are all set automatically when you set MEMORY_TARGET. Setting minimum values for important initialization parameters in your system is also a very good idea.

2.   MEMORY_MAX_TARGET This is the maximum memory allocated for Oracle and the maximum value to which MEMORY_TARGET can be set.

3.   DB_CACHE_SIZE Initial memory allocated to data cache or memory used for data itself. This parameter doesn’t need to be set if you set MEMORY_TARGET or SGA_TARGET, but setting a value for this as a minimum setting is a good idea. Your goal should always be toward a memory-resident database or at least toward getting all data that will be queried in memory.

4.   SHARED_POOL_SIZE Memory allocated for the data dictionary and for SQL and PL/SQL statements. The query itself is put in memory here. This parameter doesn’t need to be set if you set MEMORY_TARGET, but setting a value for this as a minimum is a good idea. Note that SAP recommends setting this to 400M. Also note that the Result Cache gets its memory from the shared pool and is set with the RESULT_CACHE_SIZE and RESULT_CACHE_MODE (FORCE/AUTO/MANUAL) initialization parameters. Lastly, an important note since 11g is that this parameter now includes some SGA overhead (12M worth) that it previously did not in version 10g. Set this 12M higher than you did in 10g!

5.   INMEMORY_SIZE The In-Memory column store resides in this area, which is separate from the buffer cache used to store data in memory. Tables, tablespaces, partitions, and other objects can have single columns stored in this memory area in a compressed fashion. This allows for much faster analytics (like summing an individual column). Oracle builds indexes to make this even faster based on ranges of values. This is new in 12c.

6.   SGA_TARGET If you use Oracle’s Automatic Shared Memory Management, this parameter is used to determine the size of your data cache, shared pool, large pool, and Java pool automatically (see Chapter 1 for more information). Setting this to 0 disables it. This parameter doesn’t need to be set if you set MEMORY_TARGET, but you may want to set a value for this as a minimum setting for the SGA if you’ve calibrated it in previous versions. The SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and DB_CACHE_SIZE are all set automatically based on this parameter (or MEMORY_TARGET if used). INMEMORY_SIZE is also included in this number.

7.   PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT The _TARGET is a soft memory cap for the total of all users’ PGAs. This parameter doesn’t need to be set if you set MEMORY_TARGET, but setting a value as a minimum setting is a good idea. Note that SAP specifies to set this to 20 percent of available memory for OLTP and 40 percent for OLAP. The _LIMIT sets the upper limit that is allowed (the hard memory cap).

8.   SGA_MAX_SIZE Maximum memory that SGA_TARGET can be set to. This parameter doesn’t need to be set if you set MEMORY_TARGET, but you may want to set a value if you use SGA_TARGET.

9.   OPTIMIZER_MODE FIRST_ROWS, FIRST_ROWS_n, or ALL_ROWS. Although RULE/CHOOSE are definitely desupported and obsolete and people are often scolded for even talking about using rule-based optimization, I was able to set the mode to RULE. Consider the following error I received when I set OPTIMIZER_MODE to a mode that doesn’t exist (SUPER_FAST):

10.   SEC_MAX_FAILED_LOGIN_ATTEMPTS If the user fails to enter the correct password after this many tries (new as of 11g) the server process drops the connection and the server process is terminated. The default is 3 (consider increasing this value for less secure systems). A similar parameter in my top 25 list in the prior edition included SEC_CASE_SENSITIVE_LOGON, which was new as of 11g, but is deprecated as of 12.1. Be careful if you’re still using this parameter in 11g (fix case issues with passwords by ensuring passwords can be lower, upper, or mixed case before you upgrade to 12c!).

11.   CURSOR_SHARING Converts literal SQL to SQL with bind variables, reducing parse overhead. The default is EXACT. Consider setting it to FORCE after research.

12.   OPTIMIZER_USE_INVISIBLE_INDEXES The default is FALSE to ensure invisible indexes are not used by default (new in 11g). Set this parameter to TRUE to use all of the indexes and to check which ones might have been set incorrectly to be invisible; this could be a helpful tuning exercise, or it could also bring the system to halt, so only use it in development.

13.   OPTIMIZER_USE_PENDING_STATISTICS The default is FALSE to ensure pending statistics are not used, whereas setting this to TRUE enables all pending statistics to be used (new in 11g).

14.   OPTIMIZER_INDEX_COST_ADJ Coarse adjustment between the cost of an index scan and the cost of a full table scan. Set between 1 and 10 to force index use more frequently. Setting this parameter to a value between 1 and 10 pretty much guarantees index use, however, even when not appropriate, so be careful because it is highly dependent on the index design and implementation being correct. Please note that if you are using Applications 11i, setting OPTIMIZER_INDEX_COST_ADJ to any value other than the default (100) is not supported (see My Oracle Support Note 169935.1). I’ve seen a benchmark where this was set to 200. Also, see bug 4483286. SAP suggests that you not set it for OLAP, but set it to 20 for OLTP.

15.   DB_FILE_MULTIBLOCK_READ_COUNT For full table scans to perform I/O more efficiently, this parameter reads the given number of blocks in a single I/O. The default value is 12812cR2, but it is usually noted not to change this from the default.

16.   LOG_BUFFER Server processes making changes to data blocks in the buffer cache generate redo data into the log buffer. SAP says to use the default, whereas Oracle Applications sets it to 10M. I’ve seen benchmarks with it set over 100M.

17.   DB_KEEP_CACHE_SIZE Memory allocated to the keep pool or an additional data cache that you can set up outside the buffer cache for very important data that you don’t want pushed out of the cache.

18.   DB_RECYCLE_CACHE_SIZE Memory allocated to a recycle pool or an additional data cache that you can set up outside the buffer cache and in addition to the keep pool described in item 17. Usually, DBAs set this up for ad hoc user query data with poorly written queries.

19.   OPTIMIZER_USE_SQL_PLAN_BASELINES The default is TRUE, which means Oracle uses these baselines if they exist (new in 11g). Note that Stored Outlines are deprecated (discouraged but they still work) in 11g, as they are replaced with SQL Plan Baselines.

20.   OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES The default is FALSE, which means that Oracle does not capture them by default, but if you create some, it will use them as stated in the previous parameter (new in 11g).

21.   LARGE_POOL_SIZE Total bytes in the large pool allocation for large PL/SQL and a few other Oracle options less frequently used.

22.   STATISTICS_LEVEL Used to enable advisory information and optionally keep additional OS statistics to refine optimizer decisions. TYPICAL is the default.

23.   JAVA_POOL_SIZE Memory allocated to the JVM for Java stored procedures.

24.   JAVA_MAX_SESSIONSPACE_SIZE Upper limit on memory that is used to keep track of the user session state of Java classes.

25.   OPEN_CURSORS Specifies the size of the private area used to hold (open) user statements. If you get an “ORA-01000: maximum open cursors exceeded,” you may need to increase this parameter, but make sure you are closing cursors that you no longer need. Prior to 9.2.0.5, these open cursors were also cached and, at times, caused issues (ORA-4031) if OPEN_CURSORS was set too high. As of 9.2.0.5, SESSION_CACHED_CURSORS now controls the setting of the PL/SQL cursor cache. Do notset the parameter SESSION_CACHED_CURSORS as high as you set OPEN_CURSORS, or you may experience ORA-4031 or ORA-7445 errors. SAP recommends setting this to 2000; Oracle Applications has OPEN_CURSORS at 600 and SESSION_CACHED_CURSORS at 500.