1. Oracle Database Parameters and Their Significance ================

Table of Contents- 1. Common Oracle Parameters and Their Meanings - 1.1. Viewing and Modifying Parameters - 1.2. Understanding pfile and spfile Differences

• Core Initialization Parameters
  • 1.2.1. Core Database Parameters
  • 1.2.2. Memory and Buffer Configuration
  • 1.2.3. Performance and Logging Parameters
  • 1.2.4. Diagnostic and Trace Locations
  • 1.2.5. Advanced Memory Management
• Understanding Alert Log
• Managing DB_FILES Parameter
• Oracle 11gR2 Hidden Parameters Reference

1.1. Viewing and Modifying Parameters

Oracle provides several views and commands to examine parameter types, default values, and valid ranges:

-- Display all current parameter settings
SQL> SHOW PARAMETER;

-- Retrieve parameter details including type and current value
SELECT name, type, value FROM v$parameter;

-- View acceptable values for specific parameters
SELECT * FROM v$parameter_valid_values;
SELECT * FROM v$parameter_valid_values WHERE name LIKE '%recyclebin%';

-- VALUE column shows the allowed settings
-- ISDEFAULT column indicates whether the parameter uses its default value

Oracle Parameters

1.2. Understanding pfile and spfile Differences

The Oracle database uses two types of initialization parameter files:

pfile (Initialization Parameter File)

• Default naming convention: init<instance_name>.ora
• Location: $ORACLE_HOME/dbs/
• Text-based format that can be edited with any text editor
• Static configuration file requiring database restart for changes to take effect

spfile (Server Parameter File)

• Default naming convention: spfile<instance_name>.ora
• Location: $ORACLE_HOME/dbs/
• Binary format that cannot be directly edited with text editors
• Dynamic configuration file supporting runtime modifications

Key Differences:

1. Startup Priority: spfile takes precedence over pfile during database startup
2. Modification Behavior:
   • pfile is static; changes require database restart to become effective
   • spfile is dynamic; changes made via ALTER SYSTEM take effect immediately or at next startup
3. Editing Method: pfile can be modified directly with text editors; spfile requires ALTER SYSTEM commands

Conversion Commands:

CREATE PFILE FROM SPFILE;
CREATE SPFILE FROM PFILE;

Modifying Parameters:

SQL> ALTER SYSTEM SET parameter_name=value SCOPE=scope_option;

-- Scope options:
-- SPFILE: Changes recorded in server parameter file, effective next startup
-- MEMORY: Changes applied to current instance immediately, lost after restart
-- BOTH: Changes applied to both spfile and memory (default for dynamic parameters)

If using server parameter file, SCOPE=BOTH is the default behavior. Specifying SCOPE=SPFILE or SCOPE=BOTH when not using spfile will result in errors.

1.2.1. Core Database Parameters

Database Identification Parameters

DB_NAME

db_name = "productiondb"

A unique database identifier that must match the name specified in the CREATE DATABASE statement. This parameter is fundamental to database identification and is used in various database operations and connections.

INSTANCE_NAME

instance_name = productiondb1

Used to uniquely identify a database instance when multiple instances share the same service name. The INSTANCE_NAME differs from SID—it serves as a unique identifier for instances sharing memory on a single host.

CONTROL_FILES

control_files = ("/u01/oradata/prod/control01.ctl", "/u01/oradata/prod/control02.ctl")

Specifies the locations and names of control files. Oracle strongly recommends multiplexinging control files across different storage devices for redundancy.

Cursor Management Parameters

OPEN_CURSORS

open_cursors = 300

Defines the maximum number of cursors (context areas) a single session can have open simultaneously. This parameter also limits the size of the PL/SQL cursor cache to prevent applications from exhausting available cursors.

-- Set open cursors for specific instances
ALTER SYSTEM SET open_cursors=1000 SID='1' SCOPE=SPFILE;
ALTER SYSTEM SET open_cursors=1000 SID='2' SCOPE=SPFILE;

-- Configure session cursor cache
ALTER SYSTEM SET session_cached_cursors=200 SID='1' SCOPE=SPFILE;
ALTER SYSTEM SET session_cached_cursors=200 SID='2' SCOPE=SPFILE;

Understanding OPEN_CURSORS and SESSION_CACHED_CURSORS

OPEN_CURSORS controls the maximum number of cursors a session can have open simultaneously. SESSION_CACHED_CURSORS determines how many closed cursors can be cached per session for reuse.

SQL> SHOW PARAMETER open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

SQL> SHOW PARAMETER session_cached_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     20

SQL> SELECT COUNT(*) FROM v$open_cursor;

  COUNT(*)
----------
       108

These parameters serve complementary purposes:

• Both reduce SQL parsing overhead by caching cursor information
• They operate independently without affecting each other
• Proper configuration improves application performance by avoiding soft parses

Guidelines for Setting SESSION_CACHED_CURSORS:

1. SESSION_CACHED_CURSORS should be less than OPEN_CURSORS
2. Consider shared pool size when adjusting these values
3. Monitor usage to determine optimal settings

SELECT 'session_cached_cursors' AS parameter,
       LPAD(value, 5) AS value,
       DECODE(value, 0, '  n/a', TO_CHAR(100 * used / value, '990') || '%') AS usage
FROM (SELECT MAX(s.value) AS used
      FROM v$statname n, v$sesstat s
      WHERE n.name = 'session cursor cache count'
        AND s.statistic# = n.statistic#),
     (SELECT value FROM v$parameter WHERE name = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
       LPAD(value, 5),
       TO_CHAR(100 * used / value, '990') || '%'
FROM (SELECT MAX(SUM(s.value)) AS used
      FROM v$statname n, v$sesstat s
      WHERE n.name IN ('opened cursors current', 'session cursor cache count')
        AND s.statistic# = n.statistic#
      GROUP BY s.sid),
     (SELECT value FROM v$parameter WHERE name = 'open_cursors');

Resolving ORA-01000: Maximum Open Cursors Exceeded

SELECT MAX(a.value) AS highest_open_cur, c.value AS max_open_cur
FROM v$sesstat a, v$statname b, v$parameter c
WHERE a.statistic# = b.statistic#
  AND b.name = 'opened cursors current'
  AND c.name = 'open_cursors'
GROUP BY c.value;

If the highest open cursor count approaches the parameter limit, increase OPEN_CURSORS. Before blindly increasing the parameter, verify application code properly closes cursors after use.

1.2.2. Memory and Buffer Configuration

DB_BLOCK_SIZE

db_block_size = 8192

Sets the standard Oracle database block size in bytes. This parameter is established at database creation and cannot be modified thereafter. Valid range is typically 1024 to 65536 bytes, depending on the operating system.

DB_BLOCK_BUFFERS (Deprecated in 9i)

db_block_buffers = 200

Historical parameter specifying the number of Oracle blocks in the buffer cache. Replaced by DB_CACHE_SIZE in later versions. Significantly impacts the total SGA size of an instance.

LARGE_POOL_SIZE

large_pool_size = 1048576

Specifies the allocation heap size for the large pool, used by multi-threaded server (MTS) for session memory, parallel execution message buffers, and RMAN backup/recovery disk I/O buffers.

JAVA_POOL_SIZE

java_pool_size = 67108864

Defines the Java pool size in bytes for storing Java method and class definitions in shared memory, along with Java objects transferred to Java session space during call completion.

SHARED_POOL_SIZE

shared_pool_size = 134217728

Controls the shared pool, which includes the library cache and data dictionary cache. The library cache stores recently parsed SQL statements, PL/SQL blocks, and Java classes. The dictionary cache stores recently referenced data dictionary information.

SQL> SHOW PARAMETERS SHARED_POOL_SIZE;

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='50M' SCOPE=BOTH;

Cache misses in the library cache or dictionary cache are more expensive than buffer cache misses, making proper shared pool sizing critical for performance.

Default Values:

• With SGA_TARGET set: Defaults to 0 (Oracle-determined) unless explicitly specified
• Without SGA_TARGET (32-bit): 64 MB, rounded to nearest granule
• Without SGA_TARGET (64-bit): 128 MB, rounded to nearest granule

1.2.3. Performance and Logging Parameters

LOG_BUFFER

log_buffer = 8388608

Specifies the memory size in bytes for buffering redo log entries before LGWR writes them to redo log files. For large transactions or high-throughput systems, values greater than 65536 can reduce redo log file I/O. Maximum value is the greater of 500K or 128K multiplied by CPU_COUNT.

LOG_CHECKPOINT_INTERVAL

log_checkpoint_interval = 10000

Determines the number of OS blocks (not database blocks) that must be written to redo log files before a checkpoint occurs. Checkpoints always occur during log switches regardless of this value. Lower values reduce instance recovery time but may increase disk I/O.

PROCESSES

processes = 220

Sets the maximum number of operating system user processes that can simultaneously connect to the database. This parameter should be set higher than the expected concurrent user count, accounting for background processes.

COMPATIBLE

compatible = "11.2.0.4.0"

Allows the use of new database release features while maintaining backward compatibility with earlier versions. This parameter should match the database version.

SORT_AREA_SIZE

sort_area_size = 524288

Specifies the maximum memory allocation for sort operations in bytes. Rows are returned and memory released after sort completion. Increasing this value improves large sort efficiency. When exceeded, temporary disk segments are used.

DB_FILE_MULTIBLOCK_READ_COUNT

db_file_multiblock_read_count = 128

Determines the number of blocks read in a single I/O operation during full table scans. The default typically reads 128 blocks (approximately 1MB). Data warehouse environments may benefit from higher values. Optimal settings require empirical testing based on workload characteristics.

1.2.4. Diagnostic and Trace Locations

BACKGROUND_DUMP_DEST

background_dump_dest = $ORACLE_BASE/admin/productiondb/bdump

Specifies the directory path for trace files written by background processes (LGWR, DBWR, and others). Also defines the location of the database alert log that records significant events and messages.

USER_DUMP_DEST

user_dump_dest = $ORACLE_BASE/admin/productiondb/udump

Sets the directory path where server processes write debug trace files when acting as user processes. Examples include NT: C:/ORACLE/UTRC, UNIX: /oracle/utrc, VMS: DISK$UR3:[ORACLE.UTRC].

CORE_DUMP_DEST

core_dump_dest = /opt/apps/oracle/admin/productiondb/cdump

Specifies the directory for core dump files on UNIX-based systems.

1.2.5. Advanced Memory Management

PRE_PAGE_SGA

pre_page_sga = TRUE

Controls whether Oracle loads the minimum required SGA components at instance startup, with additional SGA memory allocated virtually and paged into physical memory as processes access it. When set to TRUE, all SGA memory is allocated to physical memory at startup, providing maximum performance at the cost of longer startup times.

Each Oracle process touches the pages it needs to access, so setting this parameter to TRUE increases process startup time in dedicated server configurations.

LOCK_SGA

lock_sga = TRUE

Ensures the entire SGA remains in physical memory, preventing paging to virtual memory. Works in conjunction with PRE_PAGE_SGA for comprehensive memory locking.

Memory Parameter Relationships

memory_max_target >= memory_target > sga_max_size >= sga_target

• MEMORY_MAX_TARGET: Total memory Oracle can use, including SGA and PGA
• SGA_MAX_SIZE: Maximum SGA memory allocation
• MEMORY_TARGET: Enables Automatic Memory Management (AMM)
• SGA_TARGET: Enables Automatic Shared Memory Management (ASMM)

When SGA_TARGET is set to a non-zero value, ASMM activates automatically, allowing Oracle to dynamically adjust memory distribution among components like shared pool, buffer cache, and other SGA regions.

DB_WRITER_PROCESSES

db_writer_processes = 4

Configures the number of database writer processes, labeled as DBW0, DBW1, and so on. The recommended formula is MAX(1, TRUNC(CPU_count/8)). For systems with CPU counts less than 8, a single writer process (DBW0) is typically sufficient. This parameter should not exceed the CPU count, with a maximum value of 20.

1.2.6. Additional Configuration Parameters

REMOTE_LOGIN_PASSWORDFILE

remote_login_passwordfile = exclusive

Controls whether the operating system or a password file checks authorized user passwords:

• NONE: Password file ignored
• EXCLUSIVE: Database password file used for authentication
• SHARED: Multiple databases share SYS and INTERNAL password file

ALTER SYSTEM SET remote_login_passwordfile=exclusive SCOPE=SPFILE;

When set to NONE, remote users cannot connect using SYSDBA or SYSOPER privileges.

JOB_QUEUE_PROCESSES

job_queue_processes = 4

Specifies the number of SNP job queue processes per instance (SNP0 through SNP9, SNPA through SNPZ) for replication environments. Set to 1 or higher to enable automatic table snapshot refresh and DBMS_JOB requests. Range: 0 to 36.

JOB_QUEUE_INTERVAL

job_queue_interval = 10

Defines the wake-up frequency in seconds for SNPn background processes in replication environments. Range: 1 to 3600.

DISTRIBUTED_TRANSACTIONS

distributed_transactions = 5

Sets the maximum number of distributed transactions a database can participate in simultaneously. Reducing this value when network issues cause frequent failures prevents pending transaction buildup.

OPEN_LINKS

open_links = 4

Specifies the maximum number of concurrent connections to remote databases in a single session. Should equal or exceed the number of databases referenced in SQL statements accessing multiple databases.

TIMED_STATISTICS

timed_statistics = TRUE

Collects operating system timing information used for optimizing database and SQL statements. Settting to FALSE eliminates timing collection overhead. TRUE is useful for monitoring progress of long-running operations.

ORACLE_TRACE_ENABLE

oracle_trace_enable = TRUE

Enables default Oracle Trace collection until set to NULL.

1.2.7. UNDO_RETENTION and Automatic Tuning

UNDO_RETENTIONControls the minimum duration (in seconds) that undo data is retained after transaction commit. When _UNDO_AUTOTUNE is enabled (default TRUE), Oracle automatically adjusts this value based on undo tablespace size and historical undo usage patterns.

-- Query undo segment information
SELECT segment_name, owner, tablespace_name FROM dba_rollback_segs;

-- Assign transaction to specific rollback segment
SET TRANSACTION USE ROLLBACK SEGMENT "_SYSSMU8_517538920$";

-- Check transaction rollback segment
SELECT XIDUSN FROM v$transaction;

Understanding Alert Log

The alert log (alert_<instance>.log) records:

• Database startup and shutdown events
• Error conditions and critical events
• Non-default initialization parameters
• ALTER SYSTEM and ALTER DATABASE commands
• Tablespace and datafile operations
• Space allocation failures and corrupted files

The alert log can grow significantly over time and may be renamed or deleted when necessary. However, it contains valuable database security, maintenance, and recovery information.

Managing DB_FILES Parameter

The DB_FILES parameter specifies the maximum number of database files that can be opened. While Oracle 11g automatically extends the MAXDATAFILES control file parameter, DB_FILES requires explicit modification.

Problem Analysis:

[oracle@server trace]$ oerr ora 59
00059, 00000, "maximum number of DB_FILES exceeded"
// Cause: The value of the DB_FILES initialization parameter was exceeded.
// Action: Increase the value of the DB_FILES parameter and warm start.

Checking Current Usage:

SELECT COUNT(1) FROM dba_data_files;

  COUNT(1)
----------
        200

SHOW PARAMETER db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

Resolution:

ALTER SYSTEM SET db_files=500 SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP;

SHOW PARAMETER db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     500

Important Considerations:

1. DB_FILES is a "soft limit" controlling physical OS file mappings
2. Increasing DB_FILES requires instance restart to take effect
3. Primary and standby databases must have matching DB_FILES values
4. Operating system limits (ulimit -n) may also need adjustment

DB_FILES vs MAXDATAFILES:

• DB_FILES: Soft limit parameter controlling maximum open database files
• MAXDATAFILES: Hard limit stored in control files during database creation
• Oracle 11g automatically expands MAXDATAFILES as needed

Oracle 11gR2 Hidden Parameters Reference

The following hidden parameters are commonly adjusted in Oracle 11gR2 environments:

ALTER SYSTEM SET "_px_use_large_pool" = TRUE SCOPE=SPFILE;
ALTER SYSTEM SET "_clusterwide_global_transactions" = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET "_gc_defer_time" = 3 SCOPE=SPFILE;
ALTER SYSTEM SET "_resource_manager_always_off" = TRUE SCOPE=SPFILE;
ALTER SYSTEM SET "_resource_manager_always_on" = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET "_serial_direct_read" = never SCOPE=SPFILE;
ALTER SYSTEM SET "_cleanup_rollback_entries" = 400 SCOPE=SPFILE;
ALTER SYSTEM SET "_optimizer_use_feedback" = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET "_dbms_sql_security_level" = 0 SCOPE=SPFILE;
ALTER SYSTEM SET "_bloom_pruning_enabled" = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET "_gc_policy_time" = 0 SCOPE=SPFILE SID='*';
ALTER SYSTEM SET "_bloom_filter_enabled" = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET "_gc_read_mostly_locking" = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET "_gc_undo_affinity" = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET "_undo_autotune" = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET deferred_segment_creation = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET audit_trail = none SCOPE=SPFILE;
ALTER SYSTEM SET event='28401 trace name context forever,level 1' SCOPE=SPFILE;
ALTER SYSTEM SET "_optimizer_extended_cursor_sharing_rel" = NONE;
ALTER SYSTEM SET "_optimizer_extended_cursor_sharing" = NONE;
ALTER SYSTEM SET "_optimizer_adaptive_cursor_sharing" = FALSE;
ALTER SYSTEM SET "_memory_imm_mode_without_autosga" = FALSE SID='*';
ALTER SYSTEM SET "_b_tree_bitmap_plans" = FALSE SID='*';
ALTER SYSTEM SET "_partition_large_extents" = 'FALSE' SID='*';
ALTER SYSTEM SET "parallel_force_local" = TRUE SCOPE=SPFILE SID='*';
ALTER SYSTEM SET "parallel_max_servers" = 64 SCOPE=SPFILE SID='*';
ALTER SYSTEM SET "_use_adaptive_log_file_sync" = 'FALSE' SID='*';

Parallel Execution Parameters

_PX_USE_LARGE_POOLControls parallel execution slave memory allocation from large pool rather than shared pool. In Oracle 10g and later, PX message buffers are allocated from large pool when parallel automatic tuning is enabled, _PX_USE_LARGE_POOL is true, or SGA memory is auto-tuned via SGA_TARGET or MEMORY_TARGET.

ALTER SYSTEM SET "_px_use_large_pool" = TRUE SID='1' SCOPE=SPFILE;
ALTER SYSTEM SET "_px_use_large_pool" = TRUE SID='2' SCOPE=SPFILE;

Global Cache Parameters

_GC_DEFER_TIMEDetermines how long (in milliseconds) to defer pings for hot buffers before writing to disk, reducing contention on frequently accessed blocks. Default is 0; recommended value is 3.

ALTER SYSTEM SET "_gc_defer_time" = 3 SCOPE=SPFILE;

_GC_POLICY_TIMEDefault value is 10. Setting to 0 disables Dynamic Resource Management (DRM), which is unstable in 11g with numerous known bugs.

_GC_READ_MOSTLY_LOCKINGDefault is TRUE, enabling read-mostly locking to reduce messaging and CPU consumption for read operations. Setting to FALSE disables this feature, which is appropriate for read-intensive, write-light workloads.

_GC_UNDO_AFFINITYDefault is TRUE. Setting to FALSE disables DRM functionality.

Direct Path Read Parameters

_SERIAL_DIRECT_READControls whether serial full table scans use direct path read (bypassing buffer cache). Default is AUTO. Setting to NEVER significantly reduces direct path read operations.

ALTER SYSTEM SET "_serial_direct_read" = never SCOPE=SPFILE;

Resource Manager Parameters

_RESOURCE_MANAGER_ALWAYS_OFF and _RESOURCE_MANAGER_ALWAYS_ONSetting _RESOURCE_MANAKER_ALWAYS_OFF=TRUE and _RESOURCE_MANAGER_ALWAYS_ON=FALSE disables the default resource scheduler, preventing resmgr:cpu quantum wait events. Recommended for 11g due to scheduler bugs documented in Oracle support.

Transaction Management Parameters

_CLEANUP_ROLLBACK_ENTRIESSpecifies the number of rollback entries processed per batch during recovery. Default is 100; increasing to 400 accelerates transaction rollback operations.

_SMU_DEBUG_MODEDefault is 0. Setting to 134217728 addresses various SMON-related issues including undo segment contention and MMON blocking problems.

Optimizer Parameters

_OPTIMIZER_USE_FEEDBACKEnables Cardinality Feedback (introduced in 11.2), an optimizer feature that automatically corrects execution plens for repeated queries. Default is TRUE; recommend FALSE due to numerous bugs in 11gR2.

_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL, _OPTIMIZER_EXTENDED_CURSOR_SHARING, _OPTIMIZER_ADAPTIVE_CURSOR_SHARINGCollectively control Adaptive Cursor Sharing. Setting all to disabled (none/false) avoids associated bugs including excessive mutex waits and parsing issues.

Bloom Filter Parameters

_BLOOM_PRUNING_ENABLED and _BLOOM_FILTER_ENABLEDBloom filters provide efficient set membership testing. These parameters should be set to FALSE in 11gR2 to avoid known bugs 9124206 and 8361126 that cause ORA-00060 and ORA-10387 errors.

Security Parameters

_DBMS_SQL_SECURITY_LEVELControls DBMS_SQL package security checking. Values: 0 (disabled), 1 (cursor level), 2 (strict). Default is 1. Setting to 0 disables security checks for DBMS_SQL usage.

AUDIT_TRAILControls database auditing. Set to NONE to disable auditing completely.

ALTER SYSTEM SET audit_trail = none SCOPE=SPFILE;

Segment Creation Parameters

DEFERRED_SEGMENT_CREATIONDefault is TRUE, enabling segment creation deferred until first data insertion. Setting to FALSE ensures segments are created immediately, which is required for consistent EXP/EXPDP behavior.

Performance-Related Parameters

_B_TREE_BITMAP_PLANSControls whether the optimizer generates bitmap plans for B-tree indexes. Setting to FALSE addresses optimizer issues in certain scenarios.

_PARTITION_LARGE_EXTENTSControls large extent allocation for partitioned tables. Setting to FALSE changes allocation to standard extent sizes.

PARALLEL_FORCE_LOCALSetting to TRUE restricts parallel execution to the local instance in RAC environments, reducing interconnect traffic.

PARALLEL_MAX_ServersSets the maximum number of parallel query servers available.

_USE_ADAPTIVE_LOG_FILE_SYNCSetting to FALSE addresses log file sync wait event issues in certain Oracle 11g versions.

Cluster-Wide Parameters

_CLUSTERWIDE_GLOBAL_TRANSACTIONSEnables clusterwide global transactions, an 11g feature allowing XA distributed transactions to operate transparently across RAC. Setting to FALSE resolves several documented bugs including ORA-600 errors related to rollback segment corruption.

Distributed transactions follow the XA specification, which includes:

• Application (AP)
• Transaction Manager (TM)
• Resource Manager (RM)
• Communication Resource Manager (CRM)

Two-phase commit protocol ensures atomicity across all participating databases.

ALTER SYSTEM SET "_clusterwide_global_transactions" = FALSE SCOPE=SPFILE;

These hidden parameters should be adjusted carefully, with thorough testing in non-production environments before implementation in production systems. Oracle Support should be consulted for guidance on specific parameter requirements based on encountered issues and workload characteristics.