Oracle 18C New Initialization Parameters

Dear Readers,

In this article we will focusing on new initialization parameters that have been introduced in Oracle 18c. Below is the list of such parameters along with its significance:

ADG_ACCOUNT_INFO_TRACKING

This parameter has been introduced to control failed login attempts by users on Active Data Guard environment (ADG).  Basically this allows you to set one of below two values:

• Local (default): Login attempts are tracked locally on database & login is denied when max threshold value is reached.
• Global: This is advanced mode wherein login attempts are tracked globally across all databases in ADG setup & logins to any database will be denied when the count is reached.

AWR_PDB_MAX_PARALLEL_SLAVES

By using this parameter DBA’s can now control maximum number of MMON slave processes that can be concurrently used to quick & timely Automatic Workload Repository flushes on multitenant container databases.

For CDB’s with higher number of PDB’s, DBA’s now can increase this value for quick AWR flush operations & other hand this value can be decreased for CDB’s with smaller number of PDB’s to reduce performance spikes.

This parameter is set only on root of CDB database to control degree of parallelism for creating AWR snapshots on associated PDB’s.

You can clearly see performance benefit in below example:

FORWARD_LISTENER

As a name suggests this parameter is used to forward connections to from existing remote listener to listener value configured. This parameter is useful when a database has been moved into the Oracle Cloud.

Once a FORWARD_LISTENER parameter is set, the LOCAL_LISTENER parameter needs to be cleared by setting its value to “-oracle-none-“so that all the incoming to an existing set of remote listeners are forwarded only to listeners configured through FORWARD_LISTENER.

The FORWARD_LISTENER parameter can also be specified in the LISTENER_NETWORKS parameter.

PFB syntax for parameter usage:

INMEMORY_AUTOMATIC_LEVEL

INMEMORY_AUTOMATIC_LEVEL is used to control the Automatic In-Memory feature. This helps us in managing the In-Memory Column Store. This helps to keep the working data set in the IM column store at all times by moving segments in and out of the IM column store based on access patterns. Basically this allows you to set one of below three values:

• OFF (default): When this value is set, Automatic In-Memory is disabled.
• LOW: When this value is set, the database removes cold segments from the IM column store when it is under memory pressure.
• MEDIUM: This level ensures that any hot segment that was not populated because of memory pressure is populated first.

INMEMORY_OPTIMIZED_ARITHMETIC

One of the new features in Oracle 18c is In-Memory Optimized Arithmetic. This enables, for tables compressed with QUERY LOW (which is the default), NUMBER columns can be encoded using this optimized format. With this feature one can expect a significant performance boost for arithmetic computations (Up to 9 times). But one should keep in mind that this comes at a cost of increased space overhead.

MEMOPTIMIZE_POOL_SIZE

One of the cool performance enhancement feature that has been introduced in 18c. This functions similar to KEEP pool but with additional features. This can be configured to store specific heap tables that you may wish to optimize, also 25% of the pool is configured to automatically create and store a hash index based on the Primary Key (PK) of the table. Also the memoptimize pool size is not controlled by automatic memory management & occupies fix space in SGA.

Below screenshot shows memoptimize pool occupies fix space in SGA.

You can alter existing table to use memoptimize pool as given below:

Here you can see execution plan clearely shows table is read optimized.

OPTIMIZER_IGNORE_HINTS

12c’s “_optimizer_ignore_hints” is now “optimizer_ignore_hints”. As name suggests, this controls behavior of embedded hints. This is feature is very useful so that embedded hints can be removed in legacy code. This can be set at system or session level.

Below example can be referred for feature use:

OPTIMIZER_IGNORE_PARALLEL_HINTS

As name suggests, this controls behavior of embedded parallel hints. This is feature is very useful so that embedded parallel hints can be removed in legacy code. This can be set at system or session level.

Below example can be referred for feature use:

MULTISHARD_QUERY_DATA_CONSISTENCY

You can use MULTISHARD_QUERY_DATA_CONSISTENCY to way to control data consistency when executing multi-shard queries across shards, which can be globally distributed. Basically this allows you to set one of below three values:

• STRONG (default):  With this setting, SCN synchronization is performed across all shards, and data is consistent across all shards. This setting provides global consistent read capability.
• SHARD_LOCAL: With this setting, SCN synchronization is not performed across all shards. Data is consistent within each shard. This setting provides the most current data.
• DELAYED_STANDBY_ALLOWED: With this setting, SCN synchronization is not performed across all shards. Data is consistent within each shard. This setting allows data to be fetched from Data Guard standby databases when possible (for example, depending on load balancing), and may return stale data from standby databases

PARALLEL_MIN_DEGREE

PARALLEL_MIN_DEGREE controls the minimum degree of parallelism computed by automatic degree of parallelism. Default value of this parameter is 1. The value of PARALLEL_MIN_DEGREE is either a number that corresponds to the lower bound on the degree of parallelism computed by automatic degree of parallelism, or the string value CPU, which is computed by the function CPU_COUNT * PARALLEL_THREADS_PER_CPU.

PRIVATE_TEMP_TABLE_PREFIX

The PRIVATE_TEMP_TABLE_PREFIX initialization parameter, which defaults to “ORA$PTT_”, specifies the prefix that must be used in the name when creating the private temporary table. If you want to specify a different prefix value, it should start with string ORA$ and must be unique across the database.

TDE_CONFIGURATION

Prior Oracle Database 18c, each PDB stored their own encryption keys in the CDB’s keystore (united mode). Starting with Oracle Database 18c, a PDB can optionally store its encryption keys in a separate keystore (isolation mode), thus allowing protection by a separate keystore password. This parameter can only be used in case WALLET_ROOT is set.

UNIFIED_AUDIT_SYSTEMLOG

UNIFIED_AUDIT_SYSTEMLOG enables you to choose whether a piece of unified audit records will be written to the SYSLOG utility (on UNIX platforms) or to the Windows Event Viewer (on Windows).

WALLET_ROOT

With 18c oracle has introduced the parameter wallet_root, using which we can specify the location of the KEYSTORE or WALLET. This acts like a root directory to a subdirectory for each pluggable database (PDB), under which a directory structure similar to the Oracle ASM wallet storage directory structure is used to store the various wallets associated with the PDB.

---

Stay tuned for More articles on Oracle 18c.