Oracle9i Application Server Performance Guide Release 2 (9.0.2) Part Number A95102-02 |
|
This chapter discusses the techniques for optimizing PL/SQL performance in Oracle9i Application Server.
This chapter contains:
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.
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:
|
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:
The connection pooling logic in mod_plsql
can be best explained with an example. Consider the following typical scenario:
mod_plsql
.
mod_plsql
request (R1) for Database Access Descriptor (DAD) D1.
httpd
process P1) starts servicing the request R1.
mod_plsql
in process P1 checks its connection pool and finds that there are no database connections in its pool for that user request.
mod_plsql
in process P1 opens a new database connection, services the PL/SQL request, and adds the database connection to its pool.
mod_plsql
.
mod_plsql
in process P2 opens its own database connection, services the request, and adds the database connection to its pool.
mod_plsql
.
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:
mod_plsql
requests. There is no control on which Oracle HTTP Server process services the next request.
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.
Pooled database sessions are closed under the following circumstances:
By default each connection pooled by mod_plsql
is used to service a maximum of 1000 requests and then the database connection is shut down and re-established. This is done to make sure that any resource leaks in the PL/SQL application, or in the Oracle client/server side, do not adversely affect the system. This default of 1000 can be changed by tuning the DAD configuration parameter PlsqlMaxRequestsPerSession
.
By default, each pooled connection gets automatically cleaned up after 15 minutes of idle time. This operation is performed by the cleanup thread in mod_plsql
. For heavily loaded sites, each connection could get used at least once every 15 minutes and the connection cleanup might not happen for a long period of time. In such a case, the connection would get cleaned up based on the configuration of PlsqlMaxRequestsPerSession
. This default of 15 minutes can be changed by tuning the mod_plsql
configuration parameter PlsqlIdleSessionCleanupInterval
. Consider increasing the default for better performance in cases where the site is not heavily loaded.
The Oracle HTTP Server configuration parameter MaxRequestsPerChild
governs when an Oracle HTTP Server process will be shut down. For example, if this parameter is set to 5000, each Oracle HTTP Server process would serve exactly 5000 requests before it is shut down. Oracle HTTP Server processes could also start up and shut down as part of Oracle HTTP Server maintenance based on the configuration parameters MinSpareServers
, MaxSpareServers
, and MaxClients
. For mod_plsql
connection pooling to be effective, it is extremely important that Oracle HTTP Server in Oracle9iAS be configured such that each Oracle HTTP Server process remains active for some period of time. An incorrect configuration of Oracle HTTP Server could result in a setup where Oracle HTTP Server processes are heavily started up and shut down. Such a configuration would require that each new Oracle HTTP Server process replenish the connection pool before subsequent requests gain any benefit of pooling.
See Also:
Chapter 6 "Oracle HTTP Server Modules" in the Oracle HTTP Server Administration Guide |
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
.
While using mod_plsql
, there are three areas that affect performance and scalability:
PL/SQL Gateway users should consider the following topics when developing PL/SQL applications:
You should restrict the number of DADs that are used on each Oracle9iAS node. Note that performance is not affected if you have additional DADs that are not being used. See Oracle9i Application Server PL/SQL Web Toolkit Reference for details.
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.
mod_plsql
.
mod_plsql
is rendered useless. The Oracle9iAS 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.
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 mod_plsql
applications, this should not be set to 0
.
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 Oracle9iAS listener, enabling the KeepAlive
parameter results in performance improvements. In such cases, make sure to tune the KeepAliveTimeout
parameter appropriately.
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 may start getting timed out.
mod_expires
. Refer to Oracle9i Application Server mod_plsql User's Guide for details on how to use mod_expires
. You should also consider front-ending your Web site with Oracle9iAS Web Cache.
mod_expires
?
View Info
from the pop up menu. 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
.
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
.
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 Oracle HTTP Server Administration Guide for more details.
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=259200' </Files> </Directory>
Refer to the Oracle9iAS Web Cache Administration and Deployment Guide for more details on the Surrogate-Control
header.
Expires
header?
Expires
header. Right click the mouse on the page and select View Info
from the pop up menu.
Expires
header.
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.
processes
parameter 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, maximum number of Oracle HTTP Server processes, and the number of Oracle9iAS instances.
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:
ORACLE9IAS_HOME/Apache/modplsql/conf/plsql.conf
file. Comment out the following line by putting a #
in front of the line:
#LoadModule plsql_module...
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>
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.
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:
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)
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.
mod_plsql
requests being made.
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.
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.
You can avoid performance problems with the following:
CREATE OR REPLACE PACKAGE testpkg AS TYPE myArrayType is TABLE of VARCHAR2(32767) INDEX BY binary_ integer; PROCEDURE arrayproc (arr myArrayType); END testpkg; /
/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; /
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;
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.
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.
The flexible parameter passing mode in Oracle9i Application 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 Oracle9iAS, the exclamation mark is now required for flexible parameter passing in Oracle9i Application Server. In Oracle9iAS, 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 Oracle9i Application Server avoids this round trip by having end-users explicitly indicate the flexible parameter passing convention by adding the exclamation mark before the procedure.
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.
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.
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:
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.
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 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
.
mod_plsql
.
mod_plsql
prepares the request.
mod_plsql
invokes the PL/SQL procedure in the application. mod_plsql
passes the usual Common Gateway Interface (CGI) environment variables to the application.
owa_cache
procedure from the PL/SQL Web Toolkit to set the tag and cache level:
owa_cache.set_cache(
p_etag
,
p_level
);
mod_plsql
.
mod_plsql
stores the cacheable content in its file system for the next request.
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.
mod_plsql
detects that it has a cached content for the request.
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.
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.
mod_plsql
.
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.
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.
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
.
mod_plsql
.
mod_plsql
to the Oracle Database.
mod_plsql
invokes the PL/SQL procedure in the application and passes the usual Common Gateway Interface (CGI) environment variables to the application.
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
);
mod_plsql
.
mod_plsql
stores the cacheable content in its file system for the next request.
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.
mod_plsql
detects that it has a cached copy of the content that is expires-based.
mod_plsql
checks the content's validity by taking the difference between the current time and the time this cache file was created.
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.
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:
SYSTEM
as the caching level parameter to the owa_cache functions (set_cache for validation model or set_expires for expires model). This is for every user that shares the cache.
By using system-level caching, you can save both space in your file system and time for all users in the system. One example of this would be an application that generates content that is intended for everybody using the application. By caching the content with the system-level setting, only one copy of the content is cached in the file system. Furthermore, every user on that system benefits since the content is served directory from the cache.
USER
as the parameter for the caching level. This is for a specific user that is logged in. The stored cache is unique for that user. Only that user can use the cache.The type of user is determined by the authentication mode. Refer to the table below for the different types of users.
Table 8-5 Type of User Determined by Authentication Mode
Authentication Mode | Type of User |
---|---|
Single Sign On (SSO) |
Lightweight user |
Basic |
Database user |
Custom |
Remote user |
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.
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-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.
Directive | Default Value |
---|---|
|
On |
|
15 seconds |
|
150 |
|
100 |
|
10 |
|
10 |
|
5 |
|
5 |
See Also:
|
|
Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|