1 Database Initialization Parameters
Oracle standard database initialization parameters have been derived after performing the required benchmark tests (Performance Load tests). We recommend installing Oracle Banking Treasury Management in the Pluggable database and a few of these parameters need to be set at the PDB level.
Note:
Since some of the initialization parameters values are specific to customer volume, parameters should be derived using the Oracle Banking Treasury Management-Disk-Layouts-initparams-19c (the excel is in macros) excel sheet baselined along with this document.Following are the Parameters with the details and their relevance to Oracle Banking Treasury Management:
ALLOW_LEVEL_WITHOUT_CONNECT_BY
Recommended Value: TRUE
The ALLOW_LEVEL_WITHOUT_CONNECT_BY parameter is set to avoid following error:
After Upgrading To Oracle 10g, Getting ORA-01788 When Running A Query That Includes The LEVEL Pseudo Column [ID 455953.1]CURSOR_SHARING
The cursor sharing parameter determines the kind of SQL statements can the same cursors share.
Table 1-1 Cursor Sharing
| Property | Description |
|---|---|
| Parameter Type | String |
| Default Value | EXACT |
| Recommended Value | Force |
Oracle Banking Treasury Management relevance
Some of the Oracle Banking Treasury Management SQL statements are generated dynamically. So they contain literal values in the where clause conditions. It results in many identical statements with separate parse trees in Oracle's library cache, which can slow performance and cause latch problems.
By setting, the cursor_sharing to the FORCE database, it converts literals to bind variables before parsing the statement.
DB_CACHE_ADVICE
The DB_CACHE_ADVICE parameter enables or disables the statistics gathered for predicting behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view.
Table 1-2 DB_CACHE_ADVICE
| Property | Description |
|---|---|
| Parameter Type | String |
| Syntax | DB_CACHE_ADVICE = { ON | READY | OFF } |
| Default Value | If STATISTICS_LEVEL is set to TYPICAL / ALL, then ON. If STATISTICS_LEVEL is set to BASIC, then OFF. |
| Recommended Value | OFF (Should be ON while Performance Monitoring) |
Oracle Banking Treasury Management Relevance
Turning ON advisory will have an extra overhead.
Note:
It should be ON, only during performance monitoring.FAST_START_MTTR_TARGET
The FAST_START_MTTR_TARGET parameter enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified, FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL.
Table 1-3 FAST_START_MTTR_TARGET
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 0 |
| Range of Values | 0 to 3600 seconds |
| Recommended Value | 300 |
Oracle Banking Treasury Management Relevance
If FAST_START_MTTR_TARGET is not set, to 300 then run time performance for write/redo generation intensive workloads will not be optimized. It will reduce checkpoint writes from DBWR processes, making more room for LGWR IO. To optimize run time performance for write/redo generation intensive workloads, increase the FAST_START_MTTR_TARGET initialization parameter to 300.
JOB_QUEUE_PROCESSES
The JOB_QUEUE_PROCESSES parameter specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, J999).
Table 1-4 JOB_QUEUE_PROCESSES
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 4000 |
| Range of Values | 0 to 1000 |
| Recommended Value | Refer Oracle Banking Treasury Management-Disk-Layouts-initparams-19c.xlsx(reference to be given) |
Oracle Banking Treasury Management Relevance
The JOB_QUEUE_PROCESSES parameter has to be set with respect to the maximum number of scheduler jobs. To arrive at the right value, refer to Oracle Banking Treasury Management-Disk-Layouts-initparams-19c.xlsx excel (reference to be given).
LOG_BUFFER
Recommended Value: Refer Oracle Banking Treasury Management-Disk-Layouts-initparams-19c.xlsx (reference to be given)
Oracle Banking Treasury Management Relevance
The default log buffer size is too small as Oracle Banking Treasury Management performs heavy DML during batch processing.
MEMORY TARGET/MEMORY_MAX_TARGET
Recommended Value: Refer Oracle Banking Treasury Management-Disk-Layouts-initparams-19c.xlsx (reference to be given)
For Linux systems, make sure that the value of operating system /dev/shm mount is set to the appropriate value to accommodate memory_Target.
NLS_DATE_FORMAT
The NLS_DATE_FORMAT parameter specifies the default date format to use with the TO_CHAR and TO_DATE functions.
Table 1-5 NLS_DATE_FORMAT
| Property | Description |
|---|---|
| Parameter Type | String |
| Syntax | NLS_DATE_FORMAT = "format" |
| Default Value | Derived from NLS_TERRITORY |
| Recommended Value | DD-MON-RRRR |
Oracle Banking Treasury Management Relevance
Oracle Banking Treasury Management standard date format.
OPEN_CURSORS
The OPEN_CURSORS parameter specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.
Table 1-6 JOB_QUEUE_PROCESSES
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 50 |
| Modifiable | ALTER SYSTEM |
| Range of Values | 1 to 4294967295 (4 GB -1) |
| Recommended Value | 5000 |
Oracle Banking Treasury Management Relevance
The value of OPEN_CURSORS is required to prevent the Oracle Banking Treasury Management application from running out of open cursors. ORA-01000: maximum open cursors exceeded
OPTIMIZER_DYNAMIC_SAMPLING
The OPTIMIZER_DYNAMIC_SAMPLING parameter controls the level of dynamic sampling performed by the optimizer.
Table 1-7 OPTIMIZER_DYNAMIC_SAMPLING
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value |
|
| Recommended Values | 1 |
| Range of Values | 0 to 10 |
Oracle Banking Treasury Management Relevance
Dynamic Sampling is a method of gathering additional statistics during optimization by recursively sampling statements. When the dynamic sampling is enabled, queries are recursively generated by Oracle to test various selectivity based upon the real values to improve their accuracy. This can result in the production of better explain plans.
Value 1 Sample all tables that have not been analyzed that meet certain criteria.
OPTIMIZER_INDEX_CACHING
The OPTIMIZER_INDEX_CACHING parameter allows to adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.
Table 1-8 OPTIMIZER_DYNAMIC_SAMPLING
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 0 |
| Recommended Values | 90 |
| Range of Values | 0 to 100 |
Oracle Banking Treasury Management Relevance
The cost of executing an index using IN-list iterators or of executing nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. Oracle Banking Treasury Management favors nested loop joins by setting optimizer_index_caching to 90.
OPTIMIZER_INDEX_COST_ADJ
The OPTIMIZER_INDEX_COST_ADJ parameter allows to tune optimizer behavior for access path selection to be more or less index friendly - that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
Table 1-9 OPTIMIZER_DYNAMIC_SAMPLING
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 100 |
| Recommended Values | 50 |
| Range of Values | 1 to 10000 |
Oracle Banking Treasury Management Relevance
Oracle Banking Treasury Management favors index read over full table scan as it is very useful when optimizer favors to give a lower cost to index scans over full-table scans.
PARALLEL_MAX_SERVERS
The PARALLEL_MAX_SERVERS parameter specifies the maximum number of parallel execution processes and parallel recovery processes in an instance. As demand increases, Oracle Database increases the number of processes from the number created at instance startup up to this value.
Table 1-10 PARALLEL_MAX_SERVERS
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | Derived from the values of CPU_COUNT, PARALLEL_THREADS_PER_CPU, and PGA_AGGREGATE_TARGET |
| Recommended Values | Refer Oracle Banking Treasury Management-Disk-Layouts-initparams-19c.xlsx (need to give the reference) |
| Range of Values | 0 to 3600 |
| Real Application Clusters | Multiple instances can have different values |
Oracle Banking Treasury Management Relevance
To arrive right value, refer to Oracle Banking Treasury Management-Disk-Layouts-initparams-19c.xlsx excel. (need to give the reference)
PGA_AGGREGATE_LIMIT
Recommended Value: 0
Oracle Banking Treasury Management Relevance
Setting this parameter limits the pga consumed by the instance, hence might cause failure to a few of the running processes.
PLSQL_CODE_TYPE
The PLSQL_CODE_TYPE parameter specifies the compilation mode of the PL/SQL units.
Table 1-11 PLSQL_CODE_TYPE
| Property | Description |
|---|---|
| Parameter Type | String |
| Default Value | INTERPRETED |
| Recommended Values | NATIVE |
| Range of Values | INTERPRETED, NATIVE |
Oracle Banking Treasury Management Relevance
The PL/SQL interpreter overhead will be minimal when set to NATIVE.
PROCESSES
The PROCESSES parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.
Table 1-12 PROCESSES
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 100 |
| Range of Values | 6 to operating system dependent |
| Recommended Values | Refer to Oracle Banking Treasury Management-Disk-Layouts-initparams-19c.xlsx |
Oracle Banking Treasury Management Relevance
This parameter can set be set with respect to maximum no of sessions connected to DB.
REMOTE_DEPENDENCIES_MODE
The REMOTE_DEPENDENCIES_MODE parameter specifies how Oracle should handle dependencies upon remote PL/SQL stored procedures.
Table 1-13 REMOTE_DEPENDENCIES_MODE
| Property | Description |
|---|---|
| Parameter Type | String |
| Syntax | REMOTE_DEPENDENCIES_MODE = { TIMESTAMP | SIGNATURE } |
| Default Value | TIMESTAMP |
| Recommended Values | SIGNATURE |
Oracle Banking Treasury Management Relevance
Oracle allows the procedure to execute as long as the signatures are considered safe. This setting allows client PL/SQL applications to run without recompilation.
SESSION_CACHED_CURSORS
The SESSION_CACHED_CURSORS parameter specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.
Table 1-14 SESSION_CACHED_CURSORS
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 100 |
| Recommended Values | 400 |
| Range of Values | 0 to operating system-dependent |
Oracle Banking Treasury Management Relevance
The SESSION_CACHED_CURSORS parameter helps to cache the cursor thus avoid parsing of the cursor which is heavy CPU intensive, particularly in batch.
SKIP_UNUSABLE_INDEXES
The SKIP_UNUSABLE_INDEXES parameter enables or disables the use and reporting of tables with unusable indexes or index partitions.
Table 1-15 SKIP_UNUSABLE_INDEXES
| Property | Description |
|---|---|
| Parameter Type | Boolean |
| Default Value | True |
| Recommended Values | FALSE |
| Range of Values | true / false |
Oracle Banking Treasury Management Relevance
TRUE enables error reporting of indexes marked UNUSABLE. This setting does not allow inserts, deletes, and updates on tables with unusable indexes or index partitions. It is set to false because the Oracle Banking Treasury Management application should throw an error if any of the indexes become UNUSABLE.
UNDO_RETENTION
This UNDO_RETENTION parameter specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains for at least the time specified in this parameter and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed-size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.
The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires to undo space, and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.
Table 1-16 UNDO_RETENTION
| Property | Description |
|---|---|
| Parameter Type | Integer |
| Default Value | 900 |
| Range of Values | 0 to 231 – 1 |
| Recommended Values | 1800 |
Oracle Banking Treasury Management Relevance
Increased value along with automatic undo management helps to avoid snapshot too old error.