8
Optimizing Oracle8i

This chapter describes basic tuning activities that optimize the performance of the Oracle8i Enterprise Edition on Alpha OpenVMS. This chapter contains the following major sections:

Tuning Memory Usage for the Oracle8i Enterprise Edition

There are various ways of tuning your Oracle8i system to improve system performance. This section describes the following four ways:

Adjusting INIT.ORA parameters
Modifying Alpha OpenVMS process quotas
Adjusting the SGA

Refer to the Oracle8i Server Administrator's Guide for more information about system tuning, space management, and database tuning.

Adjusting INIT.ORA Parameters

The most direct method of tuning the system is to adjust the startup parameters defined in the INIT.ORA file.

Modify startup parameters for any of the following reasons:

To specify the name of the database to be used
To improve system performance
To use database space more efficiently
To establish backup and recovery procedures

The ORACLEINS procedure creates a copy of the INIT.ORA file in the ORA_DB directory. Modify the copy of the distributed INIT.ORA file to make changes that will affect all instances.

Modify the instance-specific INIT.ORA file to override settings in the ORA_DB:INIT.ORA files and to tune specific instances (for example, you can tune them for different hardware capabilities or different types of usage).

If you edit the INIT.ORA file, it is a good idea to make a copy of the distributed file to preserve the original parameter values. You can also add comments to the file so the parameters you have changed are marked with their original values.

Changes to INIT.ORA take effect only after you restart the instance. Therefore, if you create a new parameter file, you must shut down the current instance, reassign the ORA_PARAMS logical name, and start the instance to reference the new startup parameters.

Modifying Alpha OpenVMS Process Quotas

At instance startup time, Alpha OpenVMS process quota limits are set for the Oracle8i Enterprise Edition detached (background) processes. Some of the background processes are ARCH, DBWR, LCK0-9,LGWR, PMON, RECO, SMON, the dispatchers (D<xxx>), and the multi-threaded server processes (S<xxx>).

How the Oracle8i Enterprise Edition Sets Process Quotas

The Oracle8i Enterprise Edition automatically sets and adjusts the Alpha OpenVMS process quota limits. Therefore, Oracle Corporation does not recommend that you define process quota logical names for the background processes (as you did with Oracle Server version 6) because they override the quotas set by the Oracle Server.

To see how the Server determines the values of these quotas, see Table A-1, "Oracle quota limits" in Appendix A of this guide.

Warnings about Modifying the Process Quotas

If you do need to increase the Alpha OpenVMS quota limits, you can use the Oracle8i process quota logical names to do so. If you plan to change a process quota other than ENQLM, first consult Oracle Support Services. If you have insufficient Alpha OpenVMS quota limits, you will receive the Oracle8i errors ORA-07623 and/or ORA-00445.

How to Change the Process Quotas

To change the process quotas for a particular instance of ORACLE, complete the following steps:

Log in to the Oracle8i account (or the account from which you will restart the instance).
Shut down the instance if it is currently running.

Define the Alpha OpenVMS logical name that sets a new quota for a background process associated with the instance.

You can either set the quota limit for a specific detached process or set the quota limit globally for all detached processes. The quota limit set for a specific detached process has precedence over quota limits set globally for all detached processes.

To set the quota limit for a specific detached process, use the following logical name:

ORA_<sid>_<process>_PQL$_<quota logical name>

For example, to increase the WSQUOTA to 4096 for the background process, PMON, where the SID for a particular database is PROD, use the following command:

$ DEFINE/SYSTEM ORA_PROD_PMON_PQL$_WSQUOTA 4096

To set the quota limit globally for all detached processes, use the following logical name:

ORA_<sid>_PQL$_<quota logical name>

For example, to increase the WSQUOTA to 4096 for all of the background processes where the SID for a particular database is PROD, use the following command:

$ DEFINE/SYSTEM ORA_PROD_PQL$_WSQUOTA 4096

If neither logical name is defined, Oracle8i will find a value based on the size of the SGA and other factors.

Start the instance to make the new quotas take effect.

Adjusting the SGA

The System Global Area (SGA) is an area of shared memory. It includes database block buffers, the redo log buffer, and the data dictionary caches. The SGA is accessed by user processes and background processes.

In Alpha OpenVMS, the SGA is implemented as a global section. This section is created at instance startup. It is mapped by background processes at startup. The SGA does not page out to a system paging file.

By default, the SGA is created as a non-file backed memory resident global section that is not pageable. This results in significantly faster startup of processes that map the SGA.

The INIT.ORA parameter VLM_BACKING_STORAGE_FILE is provided. When this parameter is set to TRUE, a backing file is used for the SGA. This is provided in case there is some reason to allow the SGA to page. This parameter also disables the use of OpenVMS Fast I/O. For the best performance, leave this parameter set to the default value of FALSE.

If the SGA is pageable, it is paged to its own backing file, ORA_INSTANCE:ORA_<sid>_SGA.ORA, which is the size of the SGA.

You can adjust the SGA size by modifying your INIT.ORA parameters. If the SGA size increases, you might need to reserve additional Alpha OpenVMS memory space for the SGA.

Additional Information:
See Chapter 1 "System Requirements", Section "Alpha OpenVMS SYSGEN Parameters" of Oracle8i Installation for more information about modifying SGA.

The size of the SGA and the SGA buffers is displayed whenever you start an Oracle8i instance as in the following example:

To show the size of the SGA at other times, use the following command, SHOW SGA:

SVRMGR> SHOW SGA

Installing Products in Shared Memory

If you have more than one concurrent Oracle product user, installing the product in shared memory can potentially save physical memory and increase performance.

Installing Oracle Products

You can also install some or all of the Oracle products in shared memory by running the ORA_INSUTL.COM file. If a product is installed into shared memory, each product needs additional global pages (these numbers are approximate) as indicated in Table 8-2:

Program	Alpha Pagelets
Export	10539
Import	10137
SQL*Loader	10502
SQL*Plus	14849
Server Manager	10252
Pro*C	21606
Table 8-2 Additional Global Pages Required by Oracle Products

Running ORA_INSUTL

The ORA_INSUTL.COM file can be run from the ORA_INSTALL directory any time after the products have been installed. Run ORA_INSUTL as often as you want to install different products.

To run the ORA_INSUTL file, enter the following command:
```
$ @ORA_INSTALL:ORA_INSUTL  
```

A list of all the Oracle products installed on your system is displayed, similar to the following:

Installable Utilities

---------------------

     1. NETCONFIG
     2. PROGINT
     3. RDBMS
     4. SQLPLUS
     5. SVRMGR

Enter the number of the product you want to install and press [RETURN]. Enter ALL to install all products. Enter E or EXIT to leave this menu with the products you selected. Enter Q for Quit to leave this menu without installing any product.

ORA_INSUTL creates ORA_UTIL:INSUTILITY.COM and ORA_UTIL:REMUTILITY.COM. It then runs INSUTILITY to install the selected products in shared memory and exits.

ORA_UTIL:INSUTILITY.COM and ORA_UTIL:REMUTILITY.COM are defined as follows:
- INSUTILITY.COM
  - Installs Oracle products other than the Server (RDBMS), such as Oracle Forms and SQL*Plus, in shared memory.
- REMUTILITY.COM
  - Removes the products from shared memory, such as Oracle Forms and SQL*Plus, installed by INSUTILITY.COM. It does not affect the shared global sections of the Oracle8i images.
Reducing Database Fragmentation

This section supplements the instructions for reducing database fragmentation in the Oracle8i Server Utilities User's Guide. Refer to that document for more information about the Export and Import utilities. Every time a structural change is made to the database, such as adding, moving, or dropping database files, back up the control files using the ALTER DATABASE command.
1. Shut down all instances associated with the database.
2. Start up the database so that it can be accessed only by DBAs as in the following example:
```
SVRMGR> STARTUP RESTRICT OPEN <dbname>  
```
1. Perform a full database export (FULL=Y) to backup the entire database.
2. Use the MONITOR command in Server Manager to check for active users and shut down Oracle8i when all users are logged off.
3. Edit the ORA_DB:CREATE_<dbname>.SQL file and modify any parameters if desired (for example, when increasing the size of the initial datafiles).
4. Perform a full backup of your database.
Warning:
Do not proceed to Step 7 until you have fully backed up your database.
1. Run the ORA_DB:CREATE_<dbname>.COM file to recreate your database.
2. CONNECT to Oracle8i as SYSTEM and run the CATDBSYN.SQL file from the ORA_rdbms_admin directory.
3. Next, create a rollback segment in the SYSTEM tablespace. Refer to the Oracle8i Server Administrator's Guide for instructions.
4. Add the name of the rollback segment to your INIT.ORA file and create any additional desired rollback segments.
  
  You create private rollback segments using the following SQL statement:
```
CREATE ROLLBACK SEGMENT <name> <additional_parameters>  
```
1. Import the export file using the Import utility.

8 Optimizing Oracle8i