The Oracle7 Workgroup Server Installer automatically establishes default values for many different parameters. This section lists those values.

The Oracle7 Workgroup Server Installer automatically sets up oracle7 as the default user. This account is a member of the default database administration group, dba, thus, the group ID (GID) for the oracle7 user is set to dba.

The following table shows the directory locations of important files and directories on each of the supported Intel UNIX platforms.

On your system, the default values for the basic environment variables are shown in the following table:

On Intel UNIX systems, the system administrator can change parameters that control the allocation of shared memory. The Workgroup Server installation process can also be used to configure this process automatically. The parameters that interact with Oracle are described as follows:

Note: The file /etc/default/login typically contains a value for ULIMIT which is used by the Bourne shell to control the largest file size (in 512-bytes blocks) that a process can access. This value, which is typically too low for the Oracle7 Workgroup Server, supercedes the UNIX kernel parameters SFSZLIM and HFSZLIM and therefore needs to be removed from the Solaris x86 and UnixWare /etc/default/login file. Before installing the Oracle7 Workgroup Server on Solaris x86 and UnixWare, the system administrator should remove the line in /etc/default/login referring to ULIMIT.

On Solaris x86, the system administrator can modify the UNIX kernel parameter by editing the /etc/system file with the new parameter values and then rebooting the system. The UNIX kernel will pick up the new values dynamically. Here is an example of an /etc/system file:

	set max_nprocs = 600
	set maxusers = 35
	set semsys: seminfo_semmnni = 70
	set semsys: seminfo_semmns = 200
	set shmsys: shminfo_shmmax = 33554432
	set shmsys: shminfo_shmmni = 100

On SCO UNIX and UnixWare, the system administrator can modify the UNIX kernel parameter by editing the /etc/conf/cf.d/stune file with the new parameter values, run /etc/conf/bin/idbuild to relink the UNIX kernel, and then reboot the system. Here is an example of an stune file:

	SHMMAX	2097152
	SEMMNS	100
	SHMMNI	100

The System Global Area (SGA) is the Oracle structure that resides in shared memory and contains, among other things, static data structures, locks, and data buffers.

The address at which the SGA is attached affects the amount of virtual address space available, not only for database buffers in the SGA, but also for such things as cursors in the user's application data area.

Warning: Do not attempt to relocate the SGA without first consulting Oracle Worldwide Support. For support in the United States, call + 1 415 506 1500. For support outside the U.S., call + 44 344 860160.

After consultation, use the following steps to relocate the SGA.

1. Determine the valid virtual address range for attaching shared memory segments.

   This can be done by invoking the shared memory test program, tstshm, with no arguments.

   In the resulting tstshm display, two lines indicate the valid range ("Lowest shared memory address" and "Highest shared memory address"), as shown in the following example. (Different systems will show different values from the one in this example.)

   
   	Number of segments gotten by shmget() = 50
   
   	Number of segments attached by shmat() = 6
   
   	Segments attach at higher addresses
   
   	Default shared memory address = 0xbc00
   
   	Lowest shared memory address  = 0xbc00
   
   	Highest shared memory address = 0x156bc00
   
   	Total shared memory range     = 22544384 (0x7ff00000)
   
   	Total shared memory attached  =   786432 (0x60000)
   
   	Largest single segment size   =   131072 (0x20000)
   
   	Segment boundaries (SHMLBA)   =     1024 (0x400)
   
   

2. Determine the valid virtual address boundaries at which a shared memory segment can be attached.

   For example, some systems require that shared memory segments be on 512-byte boundaries. This information can be obtained from the "Segment boundaries" output of tstshm.

3. Determine your new SGA address, then invoke tstshm with the new SGA base address and size to determine whether it can be attached.

   The following command will verify whether it is possible to attach 175,628 bytes at address 0x300000:

   
   $ tstshm -b0x300000 -t175628
   
   

4. Move to the $ORACLE_HOME/rdbms/lib directory, and run genksms to generate the file ksms.s.

   	$ genksms > ksms.s
   
   

5. Raise or lower the value of the symbol sgabeg by editing the file ksms.s.

   For example, the ksms.s file looks like this:

   	.set    sgabeg,0xe0100000
   	.globl  _ksmsgf_
   	.set    _ksmsgf_,sgabeg+0
   	.globl  _ksmstv_
   	.set    _ksmstv_,sgabeg+4
   			.
   			.
   			.
   
   

   Here, the sgabeg symbol specifies the address at which the SGA will be attached. Change the line:

   
   	.set    sgabeg,0xe0100000
   
   

   to reflect the new address at which you want to attach the SGA:

   	.set    sgabeg, address
   
   

   The ksms.s file contains a warning message about editing. This message can be ignored when you are relocating the SGA.

6. Shutdown the existing Oracle database.

7. Rebuild the oracle executable by entering the following command:

   	$ make -f oracle.mk ioracle
   
   

Using ioracle will back up the old executable (oracle.old), assign the correct privileges to the new oracle executable, and move the new executable into the $ORACLE_HOME/bin directory. The result is a new Oracle kernel that loads the SGA at the address specified by sgabeg.

The shared memory feature of the UNIX IPC facility is required by the Oracle system. The SGA resides in shared memory; therefore, enough shared memory must be available to each Oracle process to address the entire SGA.

The following init.ora parameters control the size of the SGA:

• db_block_buffers
• db_block_size
• sort_area_size
• shared_pool_size

Be careful when setting values for these parameters. If you set the values too high, too much of your machine's physical memory will be devoted to shared memory, resulting in poor performance. As a guideline, the total of all instances' SGA sizes should be no more than one third of your total physical memory.

Using the values of the above parameters, you can calculate the approximate size of an instance's SGA with the following formula:

(db_block_buffers X db_block_size) + sort_area_size+ shared_pool_size + 1Mb = SGA size

Alternatively, you can enter the UNIX command ipcs -b. The SGA size, in bytes, is displayed under the heading "SEGS7" on SCO UNIX and "System Global Area" on Solaris x86 and UnixWare.

To find the exact size of the SGA for a running database, use the SQL*DBA command SHOW SGA. This command displays the size of the SGA, in bytes, under the heading "System Global Area."

Additional Information: For more information on UNIX shared memory operations, refer to ipcs, ipcrm, shmget, shmctl, and shmop in your operating system documentation. For more information on the SGA parameters and SQL*DBA, see the Oracle7 Server Administrator's Guide.

The Oracle7 Server uses shared memory segments for the SGA.

On SCO UNIX, the size of the SGA is limited by the address space of the CPU, available memory, and performance considerations.

Prior to installing the Oracle7 Server, the UNIX kernel parameters need to be reconfigured and the system rebooted.

For more information on reconfiguring the UNIX kernel, refer to the appropriate operating system documentation as listed in the following:

• On UnixWare refer to "Tunable Parameters" in your Univel System Performance Administration Guide.
• On SCO UNIX refer to "Tuning System Parameter Description" in your SCO UNIX System Administration Guide.
• On Solaris x86 refer to your Solaris Answerbook.

Careful tuning can dramatically increase the speed of the Oracle7 Server on your UNIX system. This section explains how to monitor and tune your system for optimal performance of the Oracle7 Server. For a general discussion of performance and tuning issues, see the Oracle7 Server Administrator's Guide.

case sensitive filenames
UNIX is case-sensitive. All values listed for parameters should be entered exactly as they appear in your Oracle7 for UNIX documentation.

creating and editing
The init.ora parameter file (default name initORCL.ora) is created upon initial installation, and can be edited as a text file on UNIX using a system editor.

default filename
Although the sid is listed in this file name, it is referred to as simply init.ora.

format
See the Oracle7 Server Administrator's Guide (SARG)for the default init.ora template file supplied with your distribution.

location
The init.ora file is located in $ORACLE_HOME/dbs by default.

operating system parameters
See the following"Initialization Parameters" section.

This section covers UNIX-specific initialization parameters (init.ora parameters) that are important for tuning the Oracle7 Server on your system. Some of these are described in the Oracle7 Server Administrator's Guide. The others are new and therefore have not been described before.

This parameter specifies the number of database writer (DBWR) processes to be brought up when the database is started. The default value is 1.

Oracle Corporation recommends you use asynchronous I/O to obtain maximum performance from the Oracle7 Server. With asynchronous I/O, the DBWR process sends multiple writes to multiple drives without waiting for the previous writes to finish. It can use all disks simultaneously. With 10 disks, for example, 10 asynchronous writes can take place in the same amount of time as a single synchronous write.

Note: This feature is currently available only on Solaris x86.

The number of database writers needed is application-specific. In general, however, this number should be at least equal to the number of disk drives used for tablespaces. Note that each additional DBWR process will consume one more semaphore on the system, so the processes parameter in init.ora, and the UNIX semaphore parameters, may need to be raised accordingly.

This parameter is used for multi-block read. It sets the number of blocks to read when doing I/O during a sequential scan. It is a useful parameter for performing a full table scan in which the WHERE clause does not refer to an indexed column. The range is 1 - 32; the default is 8.

Latches protect shared data structures in the SGA. A process acquires a latch when working with a structure, and drops the latch when finished with the structure. Each latch protects a different set of data, identified by the name of the latch.

Although data storage space is often measured in megabytes (one MB = 1024 * 1024 bytes), the UNIX operating system and the Oracle RDBMS each perform input and output in units of data storage called "blocks." Note that the size of the operating system blocks is not necessarily equal to Oracle blocks.

In the Oracle7 Workgroup Server for Intel UNIX, Oracle7 Server block size is not fixed. You can set its value when creating a database by changing the DB_BLOCK_SIZE parameter in the init.ora file.

Changing the Oracle block size may change Oracle performance, depending on your disk hardware, file system, and application. You may wish to experiment with different block sizes to see what is most efficient for your configuration. The size of 2K bytes should be adequate for all circumstances, but some benefits may be gained by optimization.

You can set the actual block size to any multiple of 2K between 2K and the maximum block size, inclusive.

The optimal block size on your system is typically 2K, but varies with your applications. To create a database with an Oracle block size that is different from the default, specify the new block size in the init.ora file by adding the line:

db_block_size=new_block_size

You may wish to experiment with block size before transferring your data to a new database since database creation is required for a block size change.

The only way to increase the limit is to change the database block size (which requires a full export/import). The following table shows the database block size relative to the greatest maxextents value.

Database Blocksize vs maxextents

Use the following figures for calculating the size required by an index using the formula in the Oracle7 Server Administrator's Guide:

I is the value of INITRANS for the index.

The following parameters set values relating to log archiving that affect performance.

This parameter sets the number of bytes allocated to the redo log buffers in the SGA. In general, larger values reduce redo log file I/O, particularly if transactions are long or numerous. The default is four times DB_BLOCK_SIZE.

Writes to the redo log buffers include the block of altered bytes, the rollback segments block, and the database header data. These are therefore generally larger than database buffers.

This init.ora parameter sets the number of redo log file blocks (operating-system blocks, not Oracle blocks) required to trigger a checkpoint. A checkpoint ensures that all blocks modified since the previous checkpoint are actually written to disk. A checkpoint occurs when either the redo log file is full or the LOG_CHECKPOINT_INTERVAL is reached. The default value is 320.

Refer to the Oracle7 Server Administrator's Guide for further discussion of performance and tuning issues related to setting checkpoints.

This parameter sets the maximum number of redo log files that can be opened at runtime for this database. This is in effect every time an instance opens a database, and lasts until the database is closed.

This parameter is related to the MAXLOGFILES argument for the CREATE DATABASE statement, which is the absolute maximum number of redo log files for all instances. If neither LOG_FILES nor MAXLOGFILES is used, the default limit to the number of redo log files is the Oracle limit of 255 files. Otherwise, the default is 16 files.

You can use certain tools, as well as the SQL*DBA MONITOR command, to observe the behavior of the database.

To evaluate performance you need to look at:

• I/O activity
• CPU usage
• paging

Additional Information: For information on monitoring I/O activity, CPU usage, and paging, see the "Monitoring and Tuning your Oracle System" chapter of the Oracle7 Server for UNIX Administrator's Reference Guide.

Several tools are available to monitor the performance of Oracle on Intel UNIX:

• ps
• sar

These tools give statistics for CPU usage, interrupts, swapping, paging, and context switching, for the entire system, not just for the Oracle processes. For more information about these commands, see your system administrator's manual.

The Oracle Post-Wait Kernel Extension implements an optimized mechanism of inter-process communications without the overhead of signal handlers or semaphores. For better performance on your UnixWare system, you should set use_post_wait_driver=TRUE in your initORCL.ora file, before starting your Oracle database. (By default use_post_wait_driver=TRUE).

If you are running Oracle software under SCO UNIX Version 3.2.4.2 or later, including MSV4.2 and MPX 3.0, you can take advantage of the new Post-Wait Driver latching mechanism. The latching mechanism uses the "Sleeper" Driver, a new kernel driver distributed by SCO as part of these operating system releases. Using the Sleeper Driver will increase performance in your database configuration.

Perform the following tasks to use the Post-Wait Driver:

1. Make sure your operating system revision level is 3.2.4.2 or later.
2. Ask your System Administrator to turn on the Sleeper Driver. Turning on the Sleeper Driver requires the following steps:
   • Change N to Y in the /etc/conf/sdevice.d/sleeper file.
   • Rebuild the UNIX kernel and reboot the system.

Additional Information: Refer to your SCO System Administration documentation for more information on rebooting the system.

1. Initiate the Post-Wait Driver in your database. Add the following line to your initORCL.ora file:

   
   	use_post_wait_driver=TRUE
   

2. Restart your database.

To verify that the Post-Wait Driver is enabled, startup your database and run the UNIX ipcs command. If your database is using the Post-Wait Driver, semaphores will not be listed as belonging to the database owner when you run ipcs. Databases using the Post-Wait Driver do not utilize semaphores.

Note: Oracle Corporation recommends you use the Post-Wait Driver to increase database performance.

Since Oracle7 Workgroup Server supports UNIX AIO, on File system multiple database writers usually are not necessary. When UNIX AIO is used, Oracle Server can write to many disks simultaneously using only one database writer process. UNIX AIO works on raw devices as well as UFS file systems.

Oracle Server uses UNIX AIO by default (the parameter async_write= TRUE by default). Note that the db_writers parameter must be set to 1 when UNIX AIO is used (it is set to 1 by default). To turn off AIO on UNIX, set the async_write parameter to FALSE in the init sid.ora file.

To use multiple database writers, set the db_writers parameter in the init.sid.ora file. Make sure the async_write parameter is set to FALSE when multiple database writers are used.

When creating database objects, you can use certain optional command parameters to improve performance. Database objects include tables, views, indexes, sequences, and clusters. For more information on these objects, refer to the Oracle7 Server Administrator's Guide. Some of these methods are listed below.

Oracle recommends using the Object Manager tool for basic object creation procedures. See the Oracle Database Tools User's Guide for your platform for instructions. The Object Manager tool cannot perform advanced object creation procedures. Use SQL for procedures such as using parameters associated with CREATE TABLE (discussed below).

Tables should be designed and created so that they:

• do not repeat entries of other tables
• create indexes only when necessary
• use clusters when possible
• use the TABLESPACE parameter of the CREATE TABLE command to store the table in a specific tablespace
• use the PCTFREE, PCTUSED, and other STORAGE parameters of the CREATE TABLE command to optimize the storage of table information
• use the INITRANS and MAXTRANS parameters of the CREATE TABLE command to improve the efficiency of how concurrent transactions manipulate a table's data

The STORAGE parameters greatly affect database performance. A discussion of how the parameters of the CREATE TABLE command can affect your database performance appears in the Oracle7 Server Administrator's Guide. Also refer to the discussion of the CREATE TABLE command in the Oracle7 Server SQL Language Reference Manual for more information about these parameters.

Note: When you create a tablespace, you can set default STORAGE parameters as well. These default parameters affect all database objects created in the tablespace that are not created with their own STORAGE parameters. For more information about creating a tablespace with default STORAGE parameters, refer to the Oracle7 Server Administrator's Guide.