Skip Headers

Oracle9i Application Server Performance Guide
Release 2 (9.0.2) for UNIX

Part Number A97380-01
Go To Documentation Library
Home
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

8
Optimizing PL/SQL Performance

This chapter discusses the techniques for optimizing PL/SQL performance in Oracle9i Application Server.

This chapter contains:

PL/SQL Performance in Oracle9iAS - Overview

This chapter describes several techniques to improve the performance of your PL/SQL application in Oracle9i Application Server (Oracle9iAS).

Table 8-1 lists recommendations for Database Access Descriptor (DAD) parameters and settings. By default, these DAD parameters are specified in the file plsql.conf in the directory $ORACLE_HOME/Apache/modplsql/conf.

Table 8-2 lists caching options.

Table 8-1  Database Access Descriptor (DAD) Parameters
Parameter Setting

PlsqlAlwaysDescribeProcedure

Set this to "Off" for best performance.

PlsqlDatabaseConnectString

Use the host:port:sid format instead of a TNS entry

PlsqlFetchBufferSize

Default=128

For multi-byte character sets like Japanese, Chinese, setting this to 256 will give better performance

PlsqlIdleSessionCleanupInterval

Default=15 (minutes)

Increasing this parameter allows a pooled database connection to stay around for longer times

PlsqlLogEnable

Default=off

This parameter should be set to "Off" unless recommended by Oracle support for debugging purposes

PlsqlMaxRequestsPerSession

Default=1000

If the PL/SQL application does not leak resources/memory, this parameter can be tuned higher (for example, 5000)

PlsqlNLSLanguage

Setting this parameter to match the database NLS language will disable overheads in character set conversions occurring in Oracle Net Services.

PlsqlSessionStateManagement

Set this parameter to "StatelessWithFastResetPackageState" if the database is 8.1.7.2 or above.

Oracle9iAS Portal is not yet certified with the mode StatelessWithFastResetPackageState. For Oracle9iAS Portal, set this parameter to the value StatelessWithResetPackageState.

Table 8-2  Caching Options

Expires Technique

Best performance - for content that changes predictably

See Also: "Using the Expires Technique"

Validation technique

Good performance - for content that changes unpredictably

See Also: "Using the Validation Technique"

System-level caching

Improves performance by caching one copy for everyone on system

See Also: "System- and User-level Caching with PL/SQL Web Applications"

See Also:

  • Appendix A, "Oracle9iAS Performance Metrics" for information on mod_plsql metrics

  • Chapter 6" Oracle HTTP Server Modules" in the Oracle HTTP Server Administration Guide for information on DAD Parameters

  • Oracle9i Application Server mod_plsql User's Guide

  • Oracle9i Application Server PL/SQL Web Toolkit Reference

Performance Tuning Issues for mod_plsql

When tuning mod_plsql to improve the performance of PL/SQL in Web applications, it is important to be familiar with some mod_plsql internals. This section presents a basic overview of some mod_plsql functionality.

This section covers the following topics:

Connection Pooling with mod_plsql

The connection pooling logic in mod_plsql can be best explained with an example. Consider the following typical scenario:

  1. The Oracle9i Application Server listener is started. There are no database connections in the connection pool maintained by mod_plsql.

  2. A browser makes a mod_plsql request (R1) for Database Access Descriptor (DAD) D1.

  3. One of the Oracle HTTP Server processes (httpd process P1) starts servicing the request R1.

  4. mod_plsql in process P1 checks its connection pool and finds that there are no database connections in its pool for that user request.

  5. Based on the information in DAD D1, mod_plsql in process P1 opens a new database connection, services the PL/SQL request, and adds the database connection to its pool.

  6. From this point on, all subsequent requests to process P1 for DAD D1 can now make use of the database connection pooled by mod_plsql.

  7. If a request for DAD D1 gets picked up by another process (process P2), then mod_plsql in process P2 opens its own database connection, services the request, and adds the database connection to its pool.

  8. From this point on, all subsequent requests to process P2 for DAD D1 can now make use of the database connection pooled by mod_plsql.

  9. Now, assume that a request R2 is made for DAD D2 and this request gets routed to process P1.

  10. mod_plsql in process P1 does not have any database connections pooled for DAD D2, and a new database session is created for DAD D2 and pooled after servicing the request. Process P1 now has two database connections pooled, one for DAD D1 and another for DAD D2.

The important details in the previous example are:

In the worst case scenario, the total number of database connections that can be pooled by mod_plsql is a factor of the total number of active DADs multiplied by the number of Oracle HTTP Server (httpd) processes running at any given time for a single Oracle9i Application Server instance. If you have configured the Oracle HTTP Server processes to a high number, you need to configure the backend database to handle a corresponding amount of database sessions.

For example, if there are three Oracle9iAS instances configured to spawn a maximum of 50 httpd processes each, plus two active DADs, you need to set up the database to allow 300 (3*50*2) sessions. This number does not include any sessions that are needed to allow other applications to connect.

Because database connections cannot be shared across httpd processes, process-based platforms have more of a Connection Reuse feature than Connection Pooling. Note that this is an artifact of the process-model in Oracle HTTP Server. Whenever Oracle HTTP Server becomes threaded in the future, mod_plsql will allow for true connection pooling. If the number of database sessions is a concern, then refer to the "Two-Listener Strategy" for details on how to address this problem.

Closing Pooled Database Sessions

Pooled database sessions are closed under the following circumstances:

What Happens to the mod_plsql Connection Pool when the Database Restarts?

This depends primarily on the amount of time the database is shut down. If the database is restarted after more than 15 minutes from being shut down, the users do not experience any problems when trying to use the Oracle9iAS listener. This is because the cleanup thread in mod_plsql cleans up database sessions that are unused for more than 15 minutes. The time specified for cleaning up idle sessions is tunable using the, PlsqlIdleSessionCleanupInterval, configuration parameter (the default value is 15 minutes).

If the database is restarted in less than 15 minutes, then a few initial requests return with errors, but the system quickly becomes usable again. The number of requests that experience failure is equal to the number of connections that were pooled by mod_plsql.

See Also:

Table 8-1, " Database Access Descriptor (DAD) Parameters"

Performance Tuning Areas in mod_plsql

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

PL/SQL Application

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

Connection Pooling and Oracle HTTP Server Configuration

Tuning the Number of Database Sessions

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 Oracle9i Application 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 Oracle9iAS listener is running on port 7777 of mylsnr1.mycompany.com. First, you can install another Oracle9iAS 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 Oracle9iAS listener running on Port 7777, edit ORACLE9IAS_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 prepending 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 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 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 ORACLE9IAS_HOME/Apache/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 Oracle9iAS listener running on Port 8888. Add the following lines to the ORACLE9IAS_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 Oracle9iAS listener. The second listener on mylsnr2.mycompany.com can be fine-tuned based on just the mod_plsql requests being made.

Overhead Problems

While executing some of the Portal 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.

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.

Avoiding the Describe Overhead

You can avoid performance problems with the following:

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.

Using Caching with PL/SQL Web Applications

Caching can improve the performance of PL/SQL Web applications. You can cache Web content generated by PL/SQL procedures in the middle-tier and decrease the database workload.

This section covers the techniques used in caching, including the following:

These techniques and levels are implemented using ows_cache packages located inside the PL/SQL Web Toolkit.

See Also:

Oracle9i Application Server PL/SQL Web Toolkit Reference

Using the Validation Technique

In general, the validation technique basically asks the server if the page has been modified since it was last presented. If it has not been modified, the cached page will be presented to the user. If the page has been modified, a new copy will be retrieved, presented to the user and then cached.

There are two methods which use the Validation Technique, Last-Modified method and the Entity Tag method. The next two sections show how these techniques are used in the HTTP protocol. Although the PL/SQL Gateway does not use the HTTP protocol, many of the same are principles are used.

Last-Modified

When a Web page is generated using the HTTP protocol, it contains a Last-Modified Response Header. This header indicates the date, relative to the server, of the content that was requested. Browsers save this date information along with the content. When subsequent requests are made for the URL of the Web page, the browser then:

  1. Determines if it has a cached version.

  2. Extracts the date information.

  3. Generates the Request Header If-Modified-Since.

  4. Sends the request the server.

Cache-enabled servers look for the If-Modified-Since header and compare it to the date of their content. If the two match, an HTTP Response status header such as "HTTP/1.1 304 Not Modified" is generated, and no content is streamed. After receiving this status code, the browser can reuse its cache entry because it has been validated.

If the two do not match, an HTTP Response header such as "HTTP/1.1 200 OK" is generated and the new content is streamed, along with a new Last-Modified Response header. Upon receipt of this status code, the browser must replace its cache entry with the new content and new date information.

Entity Tag Method

Another validation method provided by the HTTP protocol is the ETag (Entity Tag) Response and Request header. The value of this header is a string that is opaque to the browser. Servers generate this string based on their type of application. This is a more generic validation method than the If-Modified-Since header, which can only contain a date value.

The ETag method works very similar to the Last Modified method. Servers generate the ETag as part of the Response Header. The browser stores this opaque header value along with the content that is steamed back. When the next request for this content arrives, the browser passes the If-Match header with the opaque value that it stored to the server. Because the server generated this opaque value, it is able to determine what to send back to the browser. The rest is exactly like the Last-Modified validation method as described above.

Using the Validation Technique for mod_plsql

Using HTTP validation caching as a framework, the following is the Validation Model for mod_plsql.

PL/SQL applications that want to control the content being served should use this type of caching. This technique offers some moderate performance gains. One example of this would be an application that serves dynamic content that can change at any given time. In this case, the application needs full control over what is being served. Validation caching always asks the application whether the cached content is stale or not before serving it back to the browser.

Figure 8-1 shows the use of the validation technique for mod_plsql.

  1. The Oracle HTTP Server receives a PL/SQL procedure request from a client server. The Oracle HTTP Server routes the request to mod_plsql.

  2. mod_plsql prepares the request.

  3. mod_plsql invokes the PL/SQL procedure in the application. mod_plsql passes the usual Common Gateway Interface (CGI) environment variables to the application.

  4. The PL/SQL procedure generates content to pass back. If the PL/SQL procedure decides that the generated content is cacheable, it calls the owa_cache procedure from the PL/SQL Web Toolkit to set the tag and cache level:

    owa_cache.set_cache(p_etag, p_level);
    
    Table 8-3  Validation Model Parameters
    Parameter Description

    set_cache procedure

    Sets up the headers to notify mod_plsql that the content being streamed back can be cached. Then, the mod_plsql caches the content on the local file system along with the tag and caching level information as it is streamed back to the browser.

    p_etag

    The string that the procedure generates to tag the content.

    p_level

    The caching level: SYSTEM for system level or USER for user level.

  5. The HTML is returned to mod_plsql.

  6. mod_plsql stores the cacheable content in its file system for the next request.

  7. The Oracle HTTP Server sends the response to the client browser.

Figure 8-1 Validation Technique

Text description of asper016.gif follows

Text description of the illustration asper016.gif

Second Request Using the Validation Technique

Using the Validation Technique for mod_plsql, a second request is made by the client browser for the same PL/SQL procedure.

Figure 8-2 shows the second request using the Validation Technique.

  1. mod_plsql detects that it has a cached content for the request.

  2. mod_plsql forwards the same tag and caching level information (from the first request) to the PL/SQL procedure as part of the CGI environment variables.

  3. The PL/SQL procedure uses these caching CGI environment variables to check if the content has changed. It does so by calling the following owa_cache functions from the PL/SQL Web Toolkit:

    owa_cache.get_etag;
    owa_cache.get_level;
    
    

    These owa functions get the tag and caching level.

  4. The application sends the caching information to mod_plsql.

  5. Based on that information determines whether the content needs to be regenerated or can be served from the cache.

    1. If the content is still the same, the procedure calls the owa_cache.set_not_modified procedure and generates no content. This causes mod_plsql to use its cached content. The cached content is directly streamed back to the browser.

    2. If the content has changed, it generates the new content along with a new tag and caching level. mod_plsql replaces its stale cached copy with a new one and updates the tag and caching level information. The newly generated content is streamed back to the browser.

Figure 8-2 Validation Technique-Second Request

Text description of asper017.gif follows

Text description of the illustration asper017.gif

Using the Expires Technique

In the validation model, mod_plsql always asks the PL/SQL procedure if it can serve the content from the cache. In the expires model, the procedure preestablishes the content validity period. Therefore, mod_plsql can serve the content from its cache without asking the procedure. This further improves performance because no interaction with the database is required.

This caching technique offers the best performance. Use if your PL/SQL application is not sensitive to serving stale content. One example of this is an application that generates news daily. The news can be set to be valid for 24 hours. Within the 24 hours, the cached content is served back without contacting the application. This is essentially the same as serving a file. After 24 hours, mod_plsql will again fetch new content from the application.

Assume the same scenario described for the Validation model, except the procedure uses the Expires model for caching.

Figure 8-3 shows the use of the expires technique for mod_plsql.

  1. The Oracle HTTP Server receives a PL/SQL Server Page request from a client server. The Oracle HTTP Server routes the request to mod_plsql.

  2. The request is forwarded by mod_plsql to the Oracle Database.

  3. mod_plsql invokes the PL/SQL procedure in the application and passes the usual Common Gateway Interface (CGI) environment variables to the application.

  4. The PL/SQL procedure generates content to pass back. If the PL/SQL procedure decides that the generated content is cacheable, it calls the owa_cache procedure from the PL/SQL Web Toolkit to set the validity period and cache level:

    owa_cache.set_expires(p_expires, p_level);
    

Table 8-4  Expires Model Parameters
Parameter Description

set_expires procedure

Sets up the headers to notify mod_plsql that Expires caching is being used. mod_plsql then caches the content to the file system along with the validity period and caching level information.

p_expires

Number of minutes that the content is valid.

p_level

Caching level.

  • The HTML is returned to mod_plsql.

  • mod_plsql stores the cacheable content in its file system for the next request.

  • The Oracle HTTP Server sends the response to the client browser.

    Figure 8-3 The Expires Technique

    Text description of asper018.gif follows

    Text description of the illustration asper018.gif

    Second Request Using the Expires Technique

    Using the same expires model explained above, a second request is made by the client browser for the same PL/SQL procedure.

    Figure 8-4 shows the second request using the Expires Technique.

    1. mod_plsql detects that it has a cached copy of the content that is expires-based.

    2. mod_plsql checks the content's validity by taking the difference between the current time and the time this cache file was created.

      1. If this difference is within the validity period, the cached copy is still fresh and will be used without any database interaction. The cached content is directly streamed back to the browser.

      2. If the difference is not within the validity period, the cached copy is stale. mod_plsql invokes the PL/SQL procedure and generates new content. The procedure then decides whether to use expires-based caching again. If so, it also determines the validating period for this new content. The newly generated content is streamed back to the browser.

    Figure 8-4 The Expires Technique-Second Request

    Text description of asper019.gif follows

    Text description of the illustration asper019.gif

    System- and User-level Caching with PL/SQL Web Applications

    A PL/SQL procedure determines whether generated content is system-level content or user-level. This helps the PL/SQL Gateway cache to store less redundant files if more than one user is looking at the same content. It decides this by:

    For example, if no user customizes a PL/SQL Web application, then the output can be stored in a system-level cache. There will be only one cache copy for every user on the system. User information is not used since the cache can be used by multiple users.

    However, if a user customizes the application, a user-level cache is stored for that user only. All other users still use the system level cache. For a user-level cache hit, the user information is a criteria. A user-level cache always overrides a system-level cache.

    PL/SQL Web Toolkit functions (owa_cache package)

    Your decision whether to use the Validation technique or the Expires technique determines which owa_cache functions to call.

    The owa_cache package contains procedures to set and get special caching headers and environment variables. These allow developers to use the PL/SQL Gateway cache more easily. This package should already be installed in your database.

    These are the primary functions to call:

    Table 8-6  Primary owa_cache Functions
    owa Functions Purpose

    owa_cache.set_cache
    (p_etag IN varchar2,
    p_level IN varchar2)

    Validation Model - Sets up the headers.

    • p_etag parameter tags the generated content.

    • p_level parameter is the caching level to use.

    owa_cache.set_not_modified

    Validation Model - Sets up the headers to notify mod_plsql to use the cached content. Only used when a validation -based cache hit occurs.

    owa_cache.get_level

    Validation Model - Gets the caching level, USER or SYSTEM. Returns null if the cache is not hit.

    owa_cache.get_etag

    Validation Model - Gets the tag associated with the cached content. Returns null if the cache is not hit.

    owa_cache.set_expires(
    p_expires IN number,
    p_level IN varchar2)

    Expires Model - Sets up the headers.

    • p_expires parameter is the number of minutes the content is valid.

    • p_level parameter is the caching level to use.

    Other Oracle HTTP Server Directives

    Table 8-7 lists some of the Oracle HTTP Server directives that need to be tuned appropriately for your configuration. Adjust these settings for the directives listed in Table 8-7 to values appropriate for your system.

    Table 8-7  Default Settings
    Directive Default Value

    KeepAlive

    On

    KeepAliveTimeout

    15 seconds

    MaxClients

    150

    MaxKeepAliveRequests

    100

    MaxRequestsPerChild

    10

    MaxSpareServers

    10

    MinSpareServers

    5

    StartServers

    5

    See Also:


  • Go to previous page Go to next page
    Oracle
    Copyright © 2002 Oracle Corporation.

    All Rights Reserved.
    Go To Documentation Library
    Home
    Go To Table Of Contents
    Contents
    Go To Index
    Index