| NAME | VALUE |
|---|---|
| _b_tree_bitmap_plans | FALSE |
| _fast_full_scan_enabled | FALSE |
| _like_with_bind_as_equality | TRUE |
| _optimizer_autostats_job | FALSE |
| _sort_elimination_cost_ratio | 5 |
| _sqlexec_progression_cost | 2147483647 |
| _system_trig_enabled | TRUE |
| _use_single_log_writer | TRUE |
| aq_tm_processes | 4 |
| audit_sys_operations | TRUE |
| audit_trail | DB (default is NONE) |
| compatible | 12.1.0 |
| control_file_record_keep_time | 30 |
| control_files | /path/to1/ctrl01.dbf, /path/to2/ctrl02.dbf, /path/to3/ctrl03.dbf |
| cursor_sharing | EXACT |
| db_block_checking | FALSE |
| db_block_checksum | TRUE |
| db_block_size | 8192 |
| db_cache_size | 5368709120 (or, 5G) |
| db_files | 1000 |
| db_name | EBSPROD |
| diagnostic_dest | /path/to/db_ebsprod/admin/EBSPROD_ebsdb1p |
| dml_locks | 10000 |
| filesystemio_options | setall |
| java_jit_enabled | FALSE |
| job_queue_processes | 20 |
| local_listener | (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EBSPROD_ipc))) |
| log_archive_dest | /path/to/EBSPROD/arch/arch.log |
| log_buffer | 10485760 (or, 10M) |
| log_checkpoint_interval | 100000 |
| log_checkpoint_timeout | 1200 |
| log_checkpoints_to_alert | TRUE |
| max_dump_file_size | 10000 |
| memory_max_target | 25769803776 (or, 24G) |
| memory_target | 25769803776 |
| nls_comp | binary |
| nls_date_format | DD-MON-RR |
| nls_language | AMERICAN |
| nls_length_semantics | BYTE |
| nls_numeric_characters | ., |
| nls_sort | binary |
| nls_territory | AMERICA |
| olap_page_pool_size | 4194304 (or, 4M) |
| open_cursors | 4096 |
| optimizer_adaptive_features | FALSE |
| optimizer_secure_view_merging | FALSE |
| os_authent_prefix | ext$ |
| parallel_max_servers | 64 |
| parallel_min_servers | 0 |
| parallel_servers_target | 64 |
| parallel_threads_per_cpu | 2 |
| pga_aggregate_limit | 0 |
| pga_aggregate_target | 0 (this is the default. Could be set to 1G) |
| plsql_code_type | INTERPRETED |
| plsql_optimize_level | 0 |
| processes | 3000 |
| query_rewrite_enabled | true |
| recovery_parallelism | 1 |
| recyclebin | ON |
| remote_login_passwordfile | NONE |
| remote_os_authent | FALSE |
| sec_case_sensitive_logon | FALSE |
| sec_protocol_error_further_action | drop,3 |
| session_cached_cursors | 500 |
| session_max_open_files | 100 |
| sessions | 4536 |
| sga_max_size | 25769803776 (or, 24G) |
| shared_pool_reserved_size | 214748364 |
| shared_pool_size | 4294967296 (or, 4G) |
| sql92_security | TRUE |
| temp_undo_enabled | TRUE |
| timed_statistics | TRUE |
| undo_management | AUTO |
| undo_retention | 43200 |
| undo_tablespace | APPS_UNDO_TBS |
| utl_file_dir | /path/to/EBSPROD/utl_dir, /usr/tmp |
| workarea_size_policy | AUTO |
The database server is an IBM LPAR configuration. 4 Oracle databases on it share its resources of 6 cores CPUs and 92 GB memory, and it is all virtual.
parallel_max_servers = Maximum value should be 2 X #of CPUs.
This parameter depends on the number of cores. It uses logical CPUs and 5 cores (4-way SMP). The SMP threads don’t really replicate a full core.
optimizer_adaptive_features = FALSE
If it is set to true, queries may run OK on a first run, but poorly on subsequent runs. Oracle will try to adapt the plans using cardinality feedback and other features that can be good, but can also cause issues.
parallel_threads_per_cpu = 2
Because the core CPU are 4-way SMP, it shows 4x more CPUs than it really has. If it set to a higher value (say, 16), Oracle will keep overallocating the CPU resources for parallel queries and spinning up too many parallel threads for the memory and CPU that it has.
