Archive for the ‘Initialization parameters’ Category

Oracle Parameter Files

Parameter Files

Oracle must read either an initialization parameter file or a server
parameter file(SPFILE) to start an instance. These files contain list of initialization parameters and a value for each parameter.

In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the following order:

  • spfile$ORACLE_SID.ora
  • spfile.ora
  • init$ORACLE_SID.ora

Initialization Parameters

 

Initialization parameters are divided into two groups: basic and advanced. In the majority of cases, it is necessary to set and tune only the basic parameters to get reasonable performance. In rare situations, modification to the advanced parameters may be needed for optimal performance.

Complete List of Initialization Parameters (10g)

Basic Parameters

There are about 29 basic parameters. Only a few of these parameters must be explicitly set because the default values are adequate in the majority of cases.

DB_DOMAIN NLS_LANGUAGE
DB_NAME , DB_UNIQUE_NAME NLS_TERRITORY
DB_RECOVERY_FILE_DEST OPEN_CURSORS
DB_RECOVERY_FILE_DEST_SIZE PGA_AGGREGATE_TARGET
DB_CREATE_ONLINE_LOG_DEST_n PROCESSES
COMPATIBLE REMOTE_LISTENER
CONTROL_FILES REMOTE_LOGIN_PASSWORDFILE
CLUSTER_DATABASE ROLLBACK_SEGMENTS
DB_BLOCK_SIZE SESSIONS
DB_CREATE_FILE_DEST SGA_TARGET
INSTANCE_NUMBER SHARED_SERVERS
JOB_QUEUE_PROCESSES STAR_TRANSFORMATION_ENABLED
LOG_ARCHIVE_DEST_n UNDO_MANAGEMENT
LOG_ARCHIVE_DEST_STATE_n UNDO_TABLESPACE

  

Advanced Parameters

Other than basic parameters, several parameters are used for database tuning and other advance services. Few examples of parameters

SESSION_CACHED_CURSORS
CURSOR_SHARING
DB_KEEP_CACHE_SIZE
OPEN_LINKS
AUDIT_FILE_DEST
JAVA_POOL_SIZE
LARGE_POOL_SIZE
STREAMS_POOL_SIZE
QUERY_REWRITE_ENABLED
 

Hidden/Undocumented Parameters

Hidden parameters should only be used as the last resort and preferably when advised by Oracle Support.

_abort_recovery_on_join

_alert_message_purge

_backup_io_pool_size

_cursor_plan_enabled

_log_checkpoint_recovery_check

Complete List of Hidden Parameters(10g)

 

Viewing and Changing Parameters Settings

  • SHOW PARAMETERS command from SQL*PLUS can be used to to view parameter values
    • SQL> SHOW PARAMTERS CONTROL_FILES

  • V$PARAMETER, V$PARAMETER2 views is used to display current parameter values
  • V$SPPARAMETER view is used to display contents of server parameter file.

CHANGING PARAMETER VALUES 

  PFILE can be edited with any text editor but the SPFILE is a binary file and its parameter values can be changed using “ALTER SYSTEM SET” and “ALTER SYSTEM RESET”commands.

Removing a Parameter from SPFILE: 

SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;

 

Parameter Setting to SPFILE / Memory/ Both

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET timed_statistics=TRUE SCOPE=BOTH SID='*';

 

The scope parameter can be set to SPFILE, MEMORY or BOTH. Memory is default if PFILE was used at STARTUP and BOTH is default if SPFILE was used at STARTUP.

  • MEMORY:  The parameter value is set for the current instance only.
  • SPFILE: The parameter value is updated in SPFILE and takes effect at next STARTUP.
  • BOTH: The parameter value is set for current instance and persists to the SPFILE


Recommended init parameters for Websphere Portal

Recommended init parameters for IBM Websphere Portal

wspprd.__db_cache_size=587202560
wspprd.__java_pool_size=16777216
wspprd.__large_pool_size=16777216
wspprd.__shared_pool_size=939524096
wspprd.__streams_pool_size=33554432
*.compatible=’10.2.0.3.0′
*.db_block_size=8192
*.db_cache_size=524288000
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_files=1024
*.db_recovery_file_dest=’/wspprd/oracle/product/10.2.0/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.open_cursors=1600
*.pga_aggregate_target=314572800
*.pre_page_sga=TRUE
*.processes=600
*.sessions=665
*.sga_max_size=1610612736
*.sga_target=1610612736
*.shared_pool_size=536870912
*.db_writer_processes=20
*.sort_area_size=65536
*.cursor_space_for_time=TRUE
*.session_cached_cursors=500
*.log_checkpoints_to_alert=TRUE
*.log_checkpoint_interval=0
*.log_checkpoint_timeout=0
*.fast_start_mttr_target=3600
*.log_buffer=419430400

Enhanced by Zemanta

OPEN CURSORS

Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

Monitoring open cursors

v$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.

To monitor open cursors, query v$sesstat where name=’opened cursors current’. This will give the number of currently opened cursors, by session:

   --Total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current';
Tuning OPEN_CURSORS

To see if you’ve set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS

select max(a.value) as highest_open_cur, p.value as max_open_cur

   from v$sesstat a, v$statname b, v$parameter p

   where a.statistic# = b.statistic#

   and b.name = ‘opened cursors current’

   and p.name= ‘open_cursors’

   group by p.value;

Monitoring the session cursor cache

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.

--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' ;

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

select c.user_name, c.sid, sql.sql_textfrom v$open_cursor c, v$sql sqlwhere c.sql_id=sql.sql_id — for 9i and earlier use: c.address=sql.addressand c.sid=&sid

Check Session Cursor Cache tuning for details.

 

CONTROL FILE

The control file is a small binary file necessary for the database to start and operate
successfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use. A control file is updated continuously by the Oracle server during database use, so it must be available for writing whenever the database is open.

The control file is a binary file that defines the current state of the physical database..
• Loss of the control file requires recovery
• Is read at MOUNT stage
• Is required to operate
• Is linked to a single database
• Should be multiplexed
• Maintains integrity of database
• Sized initially by CREATE DATABASE

Control File Contents

The information in the control file includes:
• Database name is taken from either the name specified by the initialization parameter
DB_NAME or the name used in the CREATE DATABASE statement.
• Database identifier is recorded when the database is created.
• Time stamp of database creation is also recorded at database creation.
• Names and locations of associated data files and online redo log files are updated when
a data file or redo log is added to, renamed in, or dropped from the database.
• Tablespace information is updated as tablespaces are added or dropped.
• Redo log history is recorded during log switches.
• Location and status of archived logs are recorded when archiving occurs.
• Location and status of backups are recorded by the Recovery Manager utility.
• Current log sequence number is recorded when log switches occur.
• Checkpoint information is recorded as checkpoints are made.

 

Obtaining Control File Information

The SHOW PARAMETERS command can also be used to find the location of the control files.

show parameters control_files;

SELECT name FROM V$CONTROLFILE;

Information in several of the other dynamic performance views is obtained from the control
file:
• V$BACKUP
• V$DATAFILE
• V$TEMPFILE
• V$TABLESPACE
• V$ARCHIVE
• V$LOG
• V$LOGFILE
• V$LOGHIST
• V$ARCHIVED_LOG
• V$DATEBASE

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.

These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.

PGA

Program Global Area (PGA)

The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated.  The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.

Contents of PGA:

The contents of the PGA memory varies, depending whether the instance is running in a Dedicated Server or Shared Server configuration. Generally the PGA memory includes these components:

Private SQL area:

Contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area. The private SQL area of a cursor is divided into two areas:

Persistent area: Contains bind information, and is freed only when the cursor is closed

Run-time area: Created as the first step of an execute request. For INSERT, UPDATE, and DELETE commands, this area is freed after the statement has been executed. For queries, this area is freed only after all rows are fetched or the query is canceled.

The location of private SQL area depends on the type of connection established for the session. In a Dedicated Server environment, the private SQL areas are located in the PGA of their server process. In a Shared Server environment, the private SQL areas are located in the SGA.

The management of private SQL areas is the responsibility of the User Process. The number of Private SQL areas that a User Process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50.

Session Memory:

Consists of memory allocated to hold a session’s variables and other information related to the session. For a Shared Server environment, the session memory is shared and not private.

SQL Work Areas:

Used for memory-intensive operations such as: Sort, Hash-join, Bitmap merge, Bitmap create. The size of a work area can be controlled and tuned.

Beginning with Oracle9i, the size of the work area can be automatically and globally managed. This is enabled by setting the WORKAREA_SIZE_POLICY parameter to AUTO, which is the default, and the PGA_AGGREGATE_TARGET initialization parameter. The PGA_AGGREGATE_TARGET parameter is set by the DBA to specify the target aggregate amount of PGA memory available to the instance. This parameter is only a target and can be dynamically modified at the instance level by the DBA. It will accept a number of bytes, kilobytes, megabytes or gigabytes. When these parameters are set, sizing of work areas becomes automatic and all *_AREA_SIZE parameters are ignored for these sessions.

Prior to Oracle9i, the DBA controlled the maximum size of SQL work areas by setting the following parameters: SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE. Setting these parameters can be difficult because the maximum work area size is ideally selected on the basis of the data input size and the total number of work areas active in the system. These two factors vary a lot from one work area to another and from one time to another. Thus, these parameters are hard to tune under the best circumstances.

 

 

PGA Memory Management for Dedicated Mode

You can automatically and globally manage the size of SQL work areas. The database administrator simply needs to specify the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target.

 

There are fixed views and columns that provide PGA memory use statistics. Most of these statistics are enabled when PGA_AGGREGATE_TARGET is set.

  • Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:

V$SYSSTAT

V$SESSTAT

V$PGASTAT

V$SQL_WORKAREA

V$SQL_WORKAREA_ACTIVE

  • The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle process:

PGA_USED_MEM

PGA_ALLOCATED_MEM

PGA_MAX_MEM

Note:

The automatic PGA memory management mode applies to work areas allocated by both dedicated and shared Oracle database servers.