Skip Headers

Oracle® Application Server mod_plsql User's Guide 10g Release 3 (10.1.3) B25599-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# 4 Optimizing PL/SQL Performance

This chapter discusses the techniques for improving PL/SQL performance in Oracle HTTP Server.

This chapter contains the following sections:

- PL/SQL Performance in Oracle HTTP Server - Overview

- Process-Based and Thread-Based Operation in Oracle HTTP Server

- Performance Tuning Issues in mod_plsql

- Tuning File System Cache to Improve Caching Performance

- Oracle HTTP Server Directives

## 4.1 PL/SQL Performance in Oracle HTTP Server - Overview

This section describes several techniques to improve the performance of PL/SQL based Web applications in Oracle HTTP Server.

Table 4-1 lists recommendations for Database Access Descriptor (DAD) parameters and settings. By default, these DAD parameters are specified in the file `dads.conf`. On UNIX systems, this is in the `ORACLE_HOME``/Apache/modplsql/conf` directory. On Windows systems, by default, this file is in the `ORACLE_HOME``\Apache\modplsql\conf` directory. The file `dads.README` in this directory describes the DAD parameters in detail.

Table 4-1 Database Access Descriptor (DAD) Parameters Recommended Setting Summary

Parameter | Recommended Setting
--- | ---
`PlsqlAlwaysDescribeProcedure` | Set this to `off` for best performance. Default Value: `off`
`PlsqlDatabaseConnectString` | For newer DADs, use the `ServiceNameFormat`. Use the `SIDFormat` only for backward compatibility. Note: For HA configurations of the database, it is recommended that the connect string parameter gets resolved through an LDAP lookup.
`PlsqlFetchBufferSize` | For multibyte character sets like Japanese or Chinese, setting this to `256` should provide better performance Default Value: `200`
`PlsqlIdleSessionCleanupInterval` | Increasing this parameter allows pooled database connections to remain available, in the pool, for the specified time Default Value: `15` (minutes) See Also: Section 3.11.3, "Detecting Dead Database Connections in a Connection Pool"
`PlsqlLogEnable` | This parameter should be set to `Off` unless recommended by Oracle support for debugging purposes Default Value: `off`
`PlsqlConnectionValidation` | This parameter specifies the option mod_plsql should use to detect dead connections in its connection pool. The different options are: `Automatic` - mod_plsql tests all pooled database connections created before the detection of a failure that could indicate an instance failure. `ThrowAwayOnFailure` - mod_plsql discards all pooled database connections created before the detection of a failure that could indicate an instance failure. `AlwaysValidate` - mod_plsql always tests all pooled database connections before issuing a request. Important: Since this option has an associated performance overhead for each request, this should be used with caution. `NeverValidate` - mod_plsql will never ping any pooled database connection. Default Value: `Automatic` See Also: Section 3.11.3.1, "Specifying the Option to Detect Dead Database Connections".
`PlsqlConnectionTimeout` | This parameter specifies the timeout (in milliseconds) for testing a connection pooled in mod_plsql. Default Value: `10000` See Also: Section 3.11.3.2, "Specifying the Timeout Period".
`PlsqlMaxRequestsPerSession` | If the PL/SQL based Web application does not leak resources or memory, this parameter can be set to a higher value (for example, 5000). Default Value: `1000` See Also: Section 3.11.2, "Closing Pooled Database Sessions" and Section 4.3.2, "Connection Pooling Tips and Oracle HTTP Server Configuration".
`PlsqlNLSLanguage` | Set this parameter to match the database Globalization Support parameters to eliminate overheads in character set conversions in Oracle Net Services
`PlsqlSessionStateManagement` | Set this parameter to `StatelessWithFastResetPackageState` if the database is 8.1.7.2 or later.

Table 4-2 lists mod_plsql caching options and the sections that describe these caching options.

Table 4-2 Caching Options

Option | Description
--- | ---
Expires Technique | Best performance - for content that changes predictably. See Also: Section 3.10.2, "Using the Expires Technique"
Validation technique | Good performance - for content that changes unpredictably. See Also: Section 3.10.1, "Using the Validation Technique"
System-level caching | Improves performance by caching one copy for everyone on system. See Also: Section 3.10.3, "System- and User-level Caching with PL/SQL Based Web Applications"

See Also: The appendix on performance metrics in the Oracle Application Server Performance Guide for information on mod_plsql metrics The section "mod_plsql" in the Oracle HTTP Server Administrator's Guide for details on the DAD Parameters shown in Table 4-1. Oracle Application Server PL/SQL Web Toolkit Reference

## 4.2 Process-Based and Thread-Based Operation in Oracle HTTP Server

This section describes PL/SQL performance issues that apply on platforms where the Oracle HTTP Server is process-based and thread-based. On a process-based Oracle HTTP Server, such as those running on UNIX-based platforms, each process servers all types of HTTP requests, including servlets and PL/SQL, static files. On a thread-based Oracle HTTP Server, such as Windows-based platforms, there is just one Oracle HTTP Server process with multiple threads within the process; individual threads can be used serve all types of HTTP requests.

Note: In some cases in this chapter we make references to performance optimizations that apply for PL/SQL based Web applications where the distinction between platforms, either process-based or thread based is significant.

## 4.3 Performance Tuning Issues in mod_plsql

While using mod_plsql, the areas that affect performance and scalability are:

- PL/SQL Based Web Application Development Considerations and Programming Tips

- Connection Pooling Tips and Oracle HTTP Server Configuration

- Tuning the Number of Database Sessions

- Two-Listener Strategy

- Overhead Problems

- The Flexible Parameter Passing (four-parameter) Overhead

See Also: Section 3.11, "Performance Tuning Areas for mod_plsql"

### 4.3.1 PL/SQL Based Web Application Development Considerations and Programming Tips

PL/SQL Gateway users should consider the following topics when developing PL/SQL based Web applications:

1. Manage the use of Database Access Descriptors (DADs)

   Try to restrict the number of DADs that each Oracle HTTP Server node uses.

   Note: Performance is not affected if there are DADs that are not being used.

2. Use of Nested Tables

   PL/SQL provides the ability to create tables. To build PL/SQL tables, you build a table that gives the datatype of the table, as well as the index of the table. The index of the table is the binary integer ranging from -2147483647 to +2147483647. This table index option is known as sparsity, and allows meaningful index numbers such as customer numbers, employee number, or other useful index keys. Use PL/SQL tables to process large amounts of data.

   PL/SQL provides `TABLE` and `VARRAY` (variable size array) collection types. The `TABLE` collection type is called a nested table. Nested tables are unlimited in size and can be sparse, which means that elements within the nested table can be deleted using the `DELETE` procedure. Variable size arrays have a maximum size and maintain their order and subscript when stored in the database. Nested table data is stored in a system table that is associated with the nested table. Variable size arrays are suited for batch operations in which the application processes the data in batch array style. Nested tables make for efficient queries by storing the nested table in a storage table, where each element maps to a row in the storage table.

3. Use procedure naming overloading with caution

   PL/SQL based Web applications should use the procedure name overloading feature with caution. It is best if procedure name overloading is avoided by having multiple procedures with different names.

4. Consider rewriting applications where there is significant overhead in determining the type parameters

   PL/SQL based Web applications should be aware of the overhead in trying to execute procedures where the URL does not provide enough details to know about the type of the parameter, such as scalar or array. In such cases, the first attempt to execute a procedure fails and the procedure signature needs to be described before it can be executed by mod_plsql.

   See Also: Section 4.3.5, "Overhead Problems"

5. Use procedures with 2-parameter style flexible parameter passing

   Procedures should make use of the more performant 2-parameter style flexible parameter passing rather than the 4-parameter style parameter passing.

   See Also: Oracle Application Server PL/SQL Web Toolkit Reference Section 4.3.6, "The Flexible Parameter Passing (four-parameter) Overhead"

### 4.3.2 Connection Pooling Tips and Oracle HTTP Server Configuration

Consider the following topics when configuring connection pooling with Oracle HTTP Server:

1. Using the default connections pooling and setting values for `PlsqlMaxRequestsPerSession`

   Creating new database connections is an expensive operation and it is best if every request does not have to open and close it own database connections. The optimal technique is to make sure that database connections opened in one request are reused in subsequent requests. In some rare situations, where a database is accessed very infrequently and performance is not a major concern, connection pooling can be disabled. For example, if the administrator accesses a site infrequently to perform some administration tasks, then the DAD used to access the administrator applications can choose to disable connection pooling. To disable connection pooling, set the DAD parameter `PlsqlMaxRequestsPerSession` to the value 1.

   Note: Setting `PlsqlMaxRequestsPerSession` to the value 1 reduces the number of available database sessions and may impact performance.

2. On UNIX systems, Oracle HTTP Server configuration should be properly tuned so that once processes are started up, the processes remain up for a while. Otherwise, the connection pooling in mod_plsql is rendered useless. The Oracle HTTP Server listener should not have to continually start up and shut down processes. A proper load analysis should be performed of the site to determine what the average load on the Web site. The Oracle HTTP Server configuration should be tuned such that the number of `httpd` processes can handle the average load on the system. In addition, the configuration parameter `MaxClients` in the `httpd.conf` file should be able to handle random load spikes as well.

3. On UNIX systems, Oracle HTTP Server processes should be configured so that processes are eventually killed and restarted. This is required to manage any possible memory leaks in various components accessed through the Oracle HTTP Server. This is specifically required in mod_plsql to ensure that any database session resource leaks do not cause a problem. Make sure that `MaxRequestsPerChild` configuration parameter is set to a high number. For PL/SQL based Web applications, this should not be set to `0`.

4. For heavily loaded sites, the Oracle HTTP Server configuration parameter `KeepAlive` should be disabled. This ensures that each process is available to service requests from other clients as soon as a process is done with servicing the current request. For sites which are not heavily loaded, and where it is guaranteed that the number of Oracle HTTP Server processes are always greater than the number of simultaneous requests to the Oracle HTTP Server listener, enabling the `KeepAlive` parameter results in performance improvements. In such cases, make sure to tune the `KeepAliveTimeout` parameter appropriately.

5. You may want to lower the value of `Timeout` in the Oracle HTTP Server configuration. This ensures that Oracle HTTP Server processes are freed up earlier if a client is not responding in a timely manner. Do not set this value too low, otherwise slower responding clients could time out.

6. Most Web sites have many static image files, which are displayed in each screen for a consistent user interface. Such files rarely change and you can reduce a considerable load on the system by tagging each image served by the Oracle HTTP Server listener with `mod_expires`. You should also consider front-ending your Web site with Oracle Application Server Web Cache.

   - How do I know if the Web site can benefit from the use of `mod_expires`?

     - Use Netscape, or any browser that enables you to view page caching information, and visit several heavily accessed Web pages on the site. On each page, right click the mouse and select `View Info` from the pop up menu (or the equivalent command for your browser). If the top panel in the page information window lists many different images and static content, then the site could benefit from the use of `mod_expires`.

     - You can also check the Oracle HTTP Server access logs to see what percentage of requests result in HTTP 304 (Not Modified) status. Use the `grep` utility to search for `304` in the `access_log` and divide this resulting number of lines by the total number of lines in the `access_log`. If this percentage is high, then the site could benefit from the use of `mod_expires`.

   - How do I tag static files with the Expires header?

     - Locate the `Location` directive used to serve your static image files. Add the `ExpiresActive` and `ExpiresDefault` directives to it.

           Alias /images/ "/u01/app/oracle/myimages/"
           <Directory "/u01/app/oracle/myimages/">
                AllowOverride None
                Order allow, deny
                Allow from all
                ExpiresActive On
                ExpiresDefault A2592000
           </Directory>

     - The browser caches all static files served off the `/images` path for 30 days from now. Refer to the Oracle HTTP Server Administrator's Guide for more details.

       If you are using Oracle Application Server Web Cache, these files can be cached in memory with the use of the `Surrogate-Control` header. For example:

           Alias /images/ "/u01/app/oracle/myimages/"
           <Directory "/u01/app/oracle/myimages/">
                AllowOverride None
                Order allow, deny
                Allow from all
                ExpiresActive On
                ExpiresDefault A2592000
                <Files *>
                   Header set Surrogate-Control 'max-age=2592000'
                </Files>
           </Directory>

       Refer to the Oracle Application Server Web Cache Administrator's Guide for more details on the `Surrogate-Control` header.

   - How do I know if the static files are being tagged with the `Expires` header?

     - Using Netscape, or the browser of your choice, clean up all the cached files in the browser.

     - Visit a Web page that should have images tagged with the `Expires` header. Right click the mouse on the page and select View Info, from the pop up menu. or use the equivalent command for your browser.

     - In the top panel of the page information, select an image that should be tagged with the `Expires` header.

     - Review the information displayed in the bottom panel. The `Expires` header should be set to a valid date. If this entry is `No date given`, then the file is not being tagged with the `Expires` header.

### 4.3.3 Tuning the Number of Database Sessions

Consider the following topics when tuning the number of database sessions:

1. The `processes` and `sessions` parameters in the Oracle `init$SID.ora` configuration file should be set so that Oracle is able to handle the maximum number of database sessions. This number should be proportional to the number of DADs times the maximum number of Oracle HTTP Server processes, times the number of Oracle HTTP Server instances.

2. Using a two-listener strategy or using a shared server reduces the number of database sessions. See Section 4.3.4, "Two-Listener Strategy".

3. On UNIX platforms, the connection pool is not shared across Oracle HTTP Server processes. For this reason, it is recommended that the application use as few DADs as possible.

4. Front ending your Oracle HTTP Server with Oracle Application Server Web Cache reduces the requirement to have a high number of processes for your HTTP configuration, resulting in lesser number of database sessions.

   Note: This is only beneficial when Oracle HTTP Server is front-ended with OracleAS Web Cache and OracleAS Web Cache caches static content. To test that OracleAS Web Cache is caching static content, see item 6 in Section 4.3.2, "Connection Pooling Tips and Oracle HTTP Server Configuration".

### 4.3.4 Two-Listener Strategy

On platforms where the Oracle HTTP Server is process-based, such as all UNIX-based platforms, each process serves all types of HTTP requests, including servlets, PLSQL, static files, and CGI. In a single Oracle HTTP Server listener setup, each `httpd` process maintains its own connection pool to the database. The maximum number of database sessions is governed by the setting in `httpd.conf` configuration file for `StartServers`, `MinSpareServers`, and `MaxSpareServers`, plus the load on the system. This architecture does not allow for tuning the number of database sessions based on the number of mod_plsql requests. To tune the number of database sessions based on the number of mod_plsql requests, install a separate HTTP listener for mod_plsql requests only. This approach greatly reduces the number of database sessions that are needed to serve mod_plsql requests.

For example, assume a main Oracle HTTP Server listener is running on port 7777 of `mylsnr1.mycompany.com`. First, you can install another Oracle HTTP Server listener on port 8888 on `mylsnr2.mycompany.com`. Next, redirect all mod_plsql requests made to `mylsnr1.mycompany.com:7777` to the second listener on `mylsnr2.mycompany.com:8888`. Review the following steps:

1. To redirect all PL/SQL requests for `mylsnr1.mycompany.com:7777` to `mylsnr2.mycompany.com:8888`, make the following configuration changes:

   1. For the Oracle HTTP Server listener running on Port 7777, edit `ORACLE_HOME``/Apache/modplsql/conf/plsql.conf` file. Comment out the following line by putting a `#` in front of the line:

          #LoadModule plsql_module...

   2. Copy the DAD location used to service PL/SQL requests in `mylsnr1.mycompany.com` to the configuration file `ORACLE_HOME``/Apache/modplsql/conf/dads.conf` in `mylsnr2.mycompany.com`.

      Comment out the DAD location configuration parameters on `mylsnr1.mycompany.com` by prefixing the line with a "#" character.

          #<Location /pls/portal>
          #...
          #</Location>

   3. Configure this listener to forward all mod_plsql requests for this DAD location to the second listener by adding the following line in `dads.conf`:

          ProxyPass /pls/portal http://mylsnr2.mycompany.com:8888/pls/portal

   Repeat the configuration procedures for all DAD Locations.

2. Because the PL/SQL procedures generate URLs that are displayed in the browser, it is important that all URLs are constructed without any references to the internal mod_plsql listener on `mylsnr2.mycompany.com:8888`. Depending on how the URLs are being generated in the PL/SQL based Web application, there are three options:

   - If the URLs are hard-coded into the application, make sure that they are always generated using the hard-coded values as HOST=mylsnr1.mycompany.com and PORT=7777. No change would be required for this scenario.

   - If the PL/SQL based Web applications always use the CGI environment variables `SERVER_NAME` and `SERVER_PORT`, then it is easy to change the configuration of the listener on `mylsnr2.mycompany.com`. Edit the file and change the lines `ServerName` and `Port` in the `ORACLE_HOME``/Apache/conf/httpd.conf` file for the second listener as follows:

         ServerName mylsnr1.mycompany.com (was mylsnr2.mycompany.com)
         Port 7777                        (was 8888)

   - If the URLs are being generated using the CGI environment variable `HTTP_HOST`, you need to override the CGI environment variables for the Oracle HTTP Server listener running on Port 8888. Add the following lines to the `ORACLE_HOME``/Apache/modplsql/conf/dads.conf` file for each DAD to override the default CGI environment variables `HOST`, `SERVER_NAME`, and `SERVER_PORT`:

         PlsqlCGIEnvironmentList  SERVER_NAME  mylsnr1.mycompany.com
         PlsqlCGIEnvironmentList  SERVER_PORT  7777
         PlsqlCGIEnvironmentList  HOST mylsnr1.us.oracle.com:7777

   In all cases, the intent is to fool the application to generate URLs as if there never was a second listener.

3. Test the setup and make sure that you can access all the DADs without any problems.

4. In this setup, the main listener `mylsnr1.mycompany.com` can be configured based on the total load on the Oracle HTTP Server listener. The second listener on `mylsnr2.mycompany.com` can be fine-tuned based on just the mod_plsql requests being made.

### 4.3.5 Overhead Problems

While executing some of the stored procedures, mod_plsql may incur a `Describe` overhead, which would result in two extra round trips to the database for a successful execution. This has performance implications.

#### 4.3.5.1 The Describe Overhead

In order to execute PL/SQL procedures, mod_plsql needs to know about the datatype of the parameters being passed in. Based on this information, mod_plsql binds each parameter either as an array or as a scalar. One way to know the procedure signature is to describe the procedure before executing it. However, this approach is not efficient because every procedure has to be described before execution. To avoid the describe overhead, mod_plsql looks at the number of parameters passed for each parameter name. It uses this information to assume the datatype of each variable. The logic is simply that if there is a single value being passed, then the parameter is a scalar, otherwise it is an array. This works for most cases but fails if there is an attempt to pass a single value for an array parameter or pass multiple values for a scalar. In such cases, the first attempt to execute the PL/SQL procedure fails. mod_plsql issues a `Describe` call to get the signature of the PL/SQL procedure and binds each parameter based on the information retrieved from the `Describe` operation. The procedure is re-executed and results are sent back.

This `Describe` call occurs transparently to the procedure, but internally mod_plsql has encountered two extra round trips, one for the failed execute call and the other for the describe call.

#### 4.3.5.2 Avoiding the Describe Overhead

You can avoid performance problems with the following:

- Use flexible parameter passing.

- Always ensure that you pass multiple values for arrays. For single values, you can pass dummy values that are ignored by the procedure.

- Use the following workaround, which defines a two-parameter style procedure which defaults the unused variables.

  1. Define a scalar equivalent of your procedure, which internally calls the original procedure. For example, the original package could be similar to the following example:

         CREATE OR REPLACE PACKAGE testpkg AS
          TYPE myArrayType is TABLE of VARCHAR2(32767) INDEX BY binary_ integer;
          PROCEDURE arrayproc (arr myArrayType);
         END testpkg;
         /

  2. If you are making URL calls like `/pls/.../testpkg.arrayproc? arr= 1`, change the specification to be similar to the following:

         CREATE OR REPLACE PACKAGE testpkg AS
          TYPE myArrayType is TABLE of VARCHAR2( 32767) INDEX BY binary_integer;
           PROCEDURE arrayproc (arr varchar2);
           PROCEDURE arrayproc (arr myArrayType);
         END testpkg;
         /

  3. The procedure `arrayproc` should be similar to:

         CREATE OR REPLACE PACKAGE BODY testpkg AS
         PROCEDURE arrayproc (arr varchar2) IS
           localArr myArrayType;
         BEGIN
           localArr( 1) := arr;
           arrayproc (localArr);
         END arrayproc;

### 4.3.6 The Flexible Parameter Passing (four-parameter) Overhead

Round-trip overhead exists if a PL/SQL procedure is using the older style four-parameter interface. The PL/SQL Gateway first tries to execute the procedure by using the two-parameter interface. If this fails, the PL/SQL Gateway tries the four-parameter interface. This implies that all four-parameter interface procedures experience one extra round-trip for execution.

- Avoiding the flexible parameter passing overhead

  To avoid this overhead, it is recommended that you write corresponding wrappers that use the two-parameter interface and internally call the four-parameter interface procedures. Another option is to change the specification of the original procedure to default to the parameters that are not passed in the two-parameter interface. The four-parameter interface has been provided only for backward compatibility and will be deprecated in the future.

- Using flexible parameters and the exclamation mark

  The flexible parameter passing mode in Oracle HTTP Server expects the PL/SQL procedure to have the exclamation mark before the procedure name. Due to performance implications of the auto-detect method used in Oracle HTTP Server, the exclamation mark is now required for flexible parameter passing in Oracle HTTP Server. In Oracle HTTP Server, each procedure is described completely before being executed. The procedure `Describe` call determines the signature of the procedure and requires around-trip to the database. The PL/SQL Gateway in Oracle HTTP Server avoids this round trip by having end-users explicitly indicate the flexible parameter passing convention by adding the exclamation mark before the procedure.

## 4.4 Tuning File System Cache to Improve Caching Performance

You can configure and use a File System Cache to improve the performance of OracleAS Portal applications and generic PL/SQL based Web applications.

This section covers the following topics:

- Introducing File System Cache Tuning

- Enabling File System Cache

- Configuring File System Cache to Reside on a Faster File System

- Resizing File System Cache

- Configuring Cache Cleanup

### 4.4.1 Introducing File System Cache Tuning

This section covers mod_plsql related File System Cache tuning options. Cache contents are cached using Operating System supplied file system calls; the cached contents are not stored in the mod_plsql memory space. Using the mod_plsql File System Cache, the contents of the cache may be in memory when the Operating System supports, and the system is configured to use features such as memory disk (some UNIX platforms support memory disk based fast storage).

The information in this section can improve the performance of PL/SQL based Web applications when mod_plsql is configured to use the File System Cache. For example, OracleAS Portal uses the File System Cache, and therefore, OracleAS Portal performance should improve when the File System Cache is properly tuned.

Table 4-3 lists the cache related parameters that you can set for mod_plsql. Set these parameters in the `cache.conf` file that is available on UNIX in the directory, `ORACLE_HOME``/Apache/modplsql/conf`, and on Windows, this is found in the directory, `ORACLE_HOME``\Apache\modplsql\conf`.

Note: The file `cache.README` in the `conf` directory includes a full description of each parameter, and provides examples showing how to set parameter values.

Table 4-3 mod_plsql cache.conf Configuration Parameter Summary

Parameter | Description
--- | ---
`PlsqlCacheCleanupTime` | Sets the interval for running cache cleanup routines. Default: `Everyday 23:00` (run cleanup routine daily at 11PM local time) See Also: Section 4.4.5, "Configuring Cache Cleanup"
`PlsqlCacheDirectory` | Defines the directory that holds the mod_plsql cache. Default: On UNIX systems, the default directory for the error log is: `ORACLE_HOME``/Apache/modplsql/cache` On Windows systems, the default directory is: `ORACLE_HOME``\Apache\modplsql\cache` See Also: Section 4.4.3, "Configuring File System Cache to Reside on a Faster File System"
`PlsqlCacheEnable` | Enables the file system cache. Default: `On` See Also: Section 4.4.2, "Enabling File System Cache"
`PlsqlCacheMaxAge` | Controls the aging, in days for the cache contents. Default: `30` (days) See Also: Section 4.4.4.2, "Setting the Days of Aging for Cache with PlsqlCacheMaxAge"
`PlsqlCacheMaxSize` | Sets the maximum size, in bytes, for an individual file stored in the cache. Default: `1048576` (1 Megabyte) See Also: Section 4.4.4.3, "Setting the Maximum File Size for a Cache File with PlsqlCacheMaxSize"
`PlsqlCacheTotalSize` | Limits the total size of the cache. The value is specified in bytes. Default: `20971520` (20 Megabytes) See Also: Section 4.4.4.1, "Setting the Total Cache Size with PlsqlCacheTotalSize"

### 4.4.2 Enabling File System Cache

The `cache.conf` parameter `PlsqlCacheEnable` enables mod_plsql caching. For maximum performance, enable `PlsqlCacheEnable` by setting the value of this parameter to `On`.

Note: Only applications that support PLSQL caching, such as Oracle Portal, will benefit by setting `PlsqlCacheEnable` to `On`.

### 4.4.3 Configuring File System Cache to Reside on a Faster File System

This section describes how to configure a File System Cache to reside on a separate disk. When you use File System Cache and store the cache on a faster separate disk, performance should improve for all types of Web applications using File System Cache, including OracleAS Portal and generic PL/SQL based Web applications.

When you configure File System Cache, the cache can reside either on a separate physical disk or in a memory disk.

To set up a File System Cache on a separate disk:

1. Assume that the file system for the cache resides at the location:

   On UNIX: `/u01/cache`

   On Windows: `E:\cache`

2. Update the file:

   On UNIX: `ORACLE_HOME``/Apache/modplsql/conf/cache.conf`

   On Windows: `ORACLE_HOME``\Apache\modplsql\conf\cache.conf`

3. Change the cache parameter `PlsqlCacheDirectory`:

   On UNIX: `PlsqlCacheDirectory /u01/cache`

   On Windows: `PlsqlCacheDirectory E:\cache`

4. Restart Oracle HTTP Server for the configuration changes to take effect.

### 4.4.4 Resizing File System Cache

This section covers the following topics:

- Setting the Total Cache Size with PlsqlCacheTotalSize

- Setting the Days of Aging for Cache with PlsqlCacheMaxAge

- Setting the Maximum File Size for a Cache File with PlsqlCacheMaxSize

#### 4.4.4.1 Setting the Total Cache Size with PlsqlCacheTotalSize

The default installation sets the mod_plsql file system cache size to 2097152 bytes (20 Megabytes). If your PL/SQL application does not make use of the OWA_CACHE packages, or uses them to cache small amounts of content, then the default setting should be sufficient. If your PL/SQL application caches a lot of content in the mod_plsql file system cache, you should consider specifying a higher value.

To control the cache size, set the `PlsqlCacheTotalSize` parameter in the file `cache.conf`. On UNIX systems, this file is located under `ORACLE_HOME``/Apache/modplsql/conf` directory. On Windows systems, this file is located under `ORACLE_HOME``\Apache\modplsql\conf`.

You need to set the cache size high enough to achieve a high cache hit ratio. Try to set the cache size large enough so that frequently accessed content stays cached. It is also important to limit the amount of disk space, so that the cache size does not grow too large. Correct tuning for the cache size provides enough cache to hold all frequently accessed content while preventing the cache size from growing too large, since a very large cache is inefficient to search.

The value for `PlsqlCacheTotalSize` is specified as a number of bytes. 1MB equals 1048576 bytes. This setting is a soft limit on the amount of cache allocated. In some cases, the cache size may grow beyond this limit until the next cleanup operation. Therefore, the hard limit on the cache size is the underlying physical hard disk size. When this limit is reached, no cache content can be written out to disk until space is available.

Note: The maximum value allowed for `PlsqlCacheTotalSize` is 4294967296 bytes (4 Gigabytes).

To determine a reasonable cache size, do the following:

1. Turn on mod_plsql performance logging by setting the `LogLevel` in `httpd.conf` to the `info` level to enable mod_plsql logging.

2. Monitor the `error_log` on a daily basis. On UNIX systems, the default directory for the error log is: `ORACLE_HOME``/Apache/Apache/log`. On Windows systems, the default directory is: `ORACLE_HOME``\Apache\Apache\log`.

The mod_plsql `error_log` entries have the form:

    [info] mod_plsql: cachecleanup deleted=2571 max_age=96,2178852b  kept=1042,25585368b time=128s limit=25600000b

where:

`deleted` is the number of cache files that got deleted during the cleanup process.

`max_age` is the number of cache files and total size that got deleted because they haven't been used for some time.

`kept` is the number of cache files and total size that was kept after the cleanup process.

`time` is the amount of time to perform the cleanup.

`limit` is the total cache size. This is the value of the `PlsqlCacheTotalSize` setting.

Interpret the entries in the error log as follows:

- If a high number of files are being deleted when compared to the number of files that were kept, this is a clear indication that your cache size is too small. You probably need to increase the size of the cache.

- If a low number of files being deleted when compared to the number of files that were kept is observed, this is an indication that your cache size is probably too big. If you have enough disk space, you can chose to leave it as it or you can decrease the size of the cache to reclaim some disk space.

#### 4.4.4.2 Setting the Days of Aging for Cache with PlsqlCacheMaxAge

Using the `PlsqlCacheMaxAge` parameter, you can control the "staleness" of cache content. The value for parameter is specified in units of days. The default value for this parameter is 30 (days). This means cache content is kept in the cache if it is less than 30 days old. After 30 days, the content is considered for deletion during the cleanup process.

The `max_age` information in mod_plsql `error_log` shows cache file aging information. If your site is a highly dynamic site, it would make sense to configure this setting to a lower value, since the older cache content will usually not be used again and, therefore, the lower value does not affect the cache hit ratio. If the site contains many static pages, it would make sense to increase the value of `PlsqlCacheMaxAge` so that the cleanup process does not deliberately delete the cache content.

#### 4.4.4.3 Setting the Maximum File Size for a Cache File with PlsqlCacheMaxSize

Using the `PlsqlCacheMaxSize` parameter, you can specify the maximum size for individual files in the cache. Using this parameter prevents the case in which one cache file fills up the entire cache.

The default value for this parameter is 1048576 (bytes). In general, set this parameter to a value that represents about 1-3% of the total cache size.

Note: The maximum value allowed for `PlsqlCacheMaxSize` is 4294967296 bytes (4 Gigabytes).

### 4.4.5 Configuring Cache Cleanup

The cache cleanup parameter determines the frequency in which the File System Cache is examined and, if necessary, cleaned up. The cache cleanup parameter, `PlsqlCacheCleanupTime` is specified in the `cache.conf` file. The frequency can be set to daily, weekly, or monthly. When specifying weekly cleanup, it is possible to specify the day of the week and the time of the day.

The default mod_plsql setting of `PlsqlCacheCleanupTime` is daily at 11PM local time. Therefore, by default, every night at 11PM, the cleanup routine runs. When you select the monthly frequency, the cleanup occurs on the first Saturday of each month.

Configuring this parameter correctly is important since cleaning up too often can result in a lower cache hit ratio and when cleaning does not occur often enough, the cache's disk usage may be excessive.

Monitor the cleanup activities using the entries in the mod_plsql `error_log`; then tune the cleanup parameter, `PlsqlCacheCleanupTime` by analyzing the entries.

    [info] mod_plsql: cachecleanup deleted=2571 max_age=96,2178852b  kept=1042,25585368b time=128s limit=25600000b

Note the following:

- Seeing a large number for the cleanup time can be an indication that the cleanup frequency is set too low. When the log indicates that the cleanup operation is busy examining or deleting many cache files, increasing the cleanup frequency should decrease the time spent in the cleanup operation.

- If a high number files are being deleted during the cleanup operation because of "staleness", this is an indication that the cleanup frequency is too low. In this case, increase the frequency so that the cleanup can actively delete "stale" cache content more frequently.

## 4.5 Oracle HTTP Server Directives

To improve PL/SQL performance in Oracle HTTP Server, you need to tune the Oracle HTTP Server directives appropriately for your configuration.

See Also: The section "Configuring Oracle HTTP Server Directives" in the Oracle Application Server Performance Guide. Oracle HTTP Server Administrator's Guide.Chapter 3, "Managing Server Processes" and Chapter 4, "Managing the Network Connection".