B
Troubleshooting

B.1 OAS Compatibility

B.1.1 Using the mod_plsql without the /pls in the URL

You can use the mod_plsql without the /pls in the URL (similar to the Oracle Application Server [OAS] 4.0.8).

[...] 
<Location /pls> 
  SetHandler pls_handler 
  ... 
</Location> 
[...] 

There are several methods, outlined below.

• Rewrite directive (without an external redirect)
  Since using a redirect or a rewrite engine both cause an external redirect, which adds additional workload, consider using this version of the rewrite directive:

  [...] 
  RewriteEngine on 
  RewriteRule ^/myapp/(.*)$ /pls/[DAD]/$1 [PT] 
  [...] 
  

The passthru (PT) flag is a way to post-process the output of RewriteRule directives, but in this case prevents a redirect. Another advantage is that you can hide your URL behind the alias, meaning that the URL does not get rewritten in the browser location window. Therefore this method is the preferred way to set alias names for your application URL.

For more information on rewrites, refer to the documentation at http://www.apache.org/docs/mod/mod_rewrite.html.

• Redirect the request
  The PL/SQL Gateway does not map to a filesystem but as a module handler it cannot use an alias or similar mod_alias directives. You can, however, redirect the request. The syntax in the plsql.conf is similar to:

  Redirect /myapp http://[hostname]:[port]/pls/[DAD] 
  

  This redirects all requests http://[hostname]:[port]/myapp/... to http://[hostname]:[port]/pls/[DAD]/...

  For more information on rewrites, refer to the documentation at http://www.apache.org for more information on the redirect directive.

• Use the Rewrite Engine
  Refer to http://www.apache.org/docs/mod/mod_rewrite.html. The commands offered by this complex module allow rule-based URL manipulations dynamically. The sample rewrites any URL /myapp/... to /pls/[DAD]/ and forces a redirect. The syntax for using the rewrite engine is similar to:

  [...] 
  RewriteEngine on 
  RewriteRule ^/myapp/(.*)$ /pls/[DAD]/$1 [R,L] 
  [...] 
  

B.1.2 Using Flexible Parameters and the Exclamation Mark

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 OAS, the exclamation mark is now required for flexible parameter passing in Oracle9i Application Server.

In OAS, each procedure is described completely before being executed. The Procedure Describe call figures out the signature of the procedure and requires a round-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. Refer to "Flexible Parameter Passing" for more information.

B.2 Logging

To turn on logging for mod_plsql, do the following:

1. Stop the Apache Listener.

   Note: When sending log file to Oracle support, start with a clean set of log files to expedite the process. After Step 1., either archive or delete the older logs.

2. Edit the file <ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app.

3. Change the line containing debugModules to debugModules=all.

4. Edit the Apache configuration file httpd.conf to set the log level to Info.

5. Restart your listener.

6. When you get an error, stop the Apache listener. The mod_plsql debug log files can be found in <ORACLE_HOME>/Apache/modplsql/log directory. Additionally, look at the following log files for errors:

        <ORACLE_HOME>/Apache/Apache/logs/httpd_access_log 
        <ORACLE_HOME>/Apache/Apache/logs/httpd_error_log 
        <ORACLE_HOME>/Apache/Jserv/logs/jserv.log 
        <ORACLE_HOME>/network/admin/sqlnet.log 
        <ORACLE_HOME>/admin/<database>/bdump/alert 
   
   

7. When you are finished debugging, revert the changes since enabling logging degrades your web server's performance. Revert the change in the wdbsvr.app file by typing a semi-colon before the line to comment it out.

8. Reset the log level to Warn in the httpd.conf file.

B.3 Controlling Database Processes for Each mod_plsql Request

The database connection pool in mod_plsql is not shared across Apache processes (each process maintains its own pool). The total number of database connections pooled in Apache mod_plsql is directly related to the number of Apache processes that are spawned and the number of DAD's used to access different PL/SQL applications. The mod_plsql pools one database session per DAD per Apache process. So, the maximum number of database sessions that will be pooled by mod_plsql will be (NumberOfApacheProcesses*NumberOfDADs).

On Windows NT, where Apache is multi-threaded, all threads share the same database connection pool. The maximum number of database sessions that will be pooled by mod_plsql is (MaximumNumberOfApacheThreadsConcurrentlyActive
ForEachDAD). This is the ideal case scenario where every thread can take advantage of a database session created by another thread.

On platforms where Apache is not multi-threaded, it is important that the following parameters be tuned.

• Tune the Apache process configuration so that processes are not started or shutdown heavily (each process takes down its connection pool with it, and a new process will need to replenish its pool). This tuning is governed by the load on the Web Server. Set the following: Set MaxClients = MaxSpareServers, and MaxRequestsPerchild=HighNumber and MinSpareServers=0. This configuration ensures that Apache processes are very rarely shutdown and the overhead of creating an Apache process or new database connection is greatly reduced.

• Set up the maximum number of database sessions according to the maximum number of Apache processes expected.

• For heavily loaded sites, turn KeepAlive off (Apache's recommendation). Otherwise, one Apache process gets blocked for the duration of "KeepAliveTimeOut" setup in httpd.conf.

If your database is unable to handle the load, do one of the following:

• Set up a new Listener for handling PL/SQL requests. The main Apache Listener can redirect all PL/SQL requests to the new Listener. For the new Listener, you can set up the Apache processes parameter to a low number (since it will not be doing any other request except PL/SQL). This will keep the database session number to a minimum.

• Consider using Oracle Multi-Threaded Server (MTS). This should reduce the load on the database.

• Consider using the Calypso/iCache/mod_plsql OWA based-caching approaches.

The mod_plsql in Oracle9i Application Server v1.0.2 has a cleanup thread which periodically cleans up database sessions which are inactive for more than 15 minutes. So, you need not be concerned about database related resources since mod_plsql does the cleanup automatically at idle time.

B.4 Connection Pooling in mod_plsql

The connection pooling logic in mod_plsql is best explained with an example. Here are the details for a typical scenario:

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

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

3. One of the Apache processes (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.

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 (say 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 main things to note in this model is that

• Each Apache process serves all kinds of requests (static files requests/Servlet requests/mod_plsql requests) and there is no control on which Apache process will service the next request.

• One Apache process cannot use/share the connection pool created by another process.

• Each Apache process pools at most one database connection for each DAD

• Windows NT has a threaded model for Apache, and the connection pool is shared between all the threads. In this model, the maximum number of database connections created by mod_plsql for each DAD will never exceed the maximum number of simultaneous requests for the DAD.

B.4.1 Install a Separate Apache Listener for mod_plsql Requests

On platforms where Apache is process based (e.g. Solaris), each process serves all kinds of HTTP requests (servlets/PLSQL/static file etc). In a single Oracle9i Application Server listener setup, each Apache process maintains its own connection pool to the database. The maximum number of database sessions is governed by the setting in httpd.conf for StartServers/MinSpareServers/MaxSpareServers and the load on the system. This architecture does not allow you to tune 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 Apache listener for just mod_plsql requests.

For example: Assume your main Oracle9i Application Server Listener is running on port 7777 of mylsnr1.us.oracle.com. You can install another Oracle9i Application Server Listener on port 8888 on mylsnr2.us.oracle.com. Then, redirect all mod_plsql requests made to mylsnr1.us.oracle.com:777 to the second listener on mylsnr2.us.oracle.com:8888. This is achieved by doing the following:

1. For the Oracle9i Application Server Listener running on Port 7777, edit $IAS_HOME/Apache/modplsql/cfg/plsql.conf in the following manner:
   • Disable servicing of PL/SQL requests from this listener by commenting out the lines between the two Location parameters.

         <Location /pls> 
         ... 
         </Location> 
     
     
     
     
     
     
     Note:
     
     
     
     Comment out lines by putting a # in front of the line.  
     
     
     
     
     
     
     
     
     
     

   • Comment out the line:

      LoadModule plsql_module... 
     
     

   • Configure this listener to forward all mod_plsql requests to the second listener by adding the following line:

     ProxyPass /pls/ http://my.lsnr2.us.oracle.com:8888/pls/ 
     
     

2. For the Oracle9i Application Server Listener running on Port 8888, configure each DAD to override the default CGI environment variables HOST, SERVER_NAME, SERVER_PORT so that PL/SQL procedures which construct URLs can do so without any problems. To do this, edit $IAS_HOME/Apache/modplsql/cfg/wdbsvr.app and add the following line for each DAD:

   cgi_env_list=SERVER_NAME=mylsnr1.us.oracle.com,SERVER_
   PORT=7777,HOST=mylsnr1.us 
   oracle.com:7777 
   
   

In this setup, the main listener lsnr1.us.oracle.com can be configured based on the load to the Oracle9i Application Server Listener. Then the second listener can be fine-tuned based on the mod_plsql requests being made.

B.5 Debugging

The following steps can help you isolate your problem. Verify you can do each step before proceeding to the next one.

1. If you are getting Login failures, confirm that your DAD configuration in <ORACLE_HOME>\Apache\modplsql\cfg\wdbsvr.app has the proper user name, password and connect string. Use SQL*Plus to verify that you have connectivity to the database.

2. If you have multiple Oracle homes, verify that the New8 alias is present in the correct tnsnames.ora file. Synchronize all versions of tnsnames.ora to have the same content. Merge the files instead of directly copying one over the other.

3. If you can logon, try accessing a simple procedure, for example:

   http://host:port/pls/DAD/htp.p?cbuf=Hello
   
   

4. If you still have problems, contact Oracle Support and provide the log files. Refer to the "Logging" section for instructions on obtaining a log.

B.5.1 Error Code 503 (Service Temporarily Unavailable)

If you are getting error code "503 Service Temporarily Unavailable" when your web server is under some load, determine if the mod_plsql cannot connect to the database because the maximum number of database sessions has been reached.

• Check the maximum number of sessions parameter in your database configuration file. This parameter is called processes and is in your database configuration file init$SID.ora. This number must be at least equal to the maximum number of Apache processes configured in httpd.conf (StartServers+MaxSpareServers).

• Verify this is the issue by doing the following.
  1. As soon as you get this error, connect as SYS through SQL*Plus.

  2. Issue "select username from v$session".

  3. If the count of the number of rows is almost the same as what you have in init$SID.ora, then you are having this problem.

B.6 Symbols Displaying Incorrectly with Netscape 6

There is an issue with symbols outside the range of the US-ASCII charset displaying correctly with Netscape 6. If your symbols are being displayed as question marks, implement the following fix:

Enter the following to emulate the various symbols:

Table 6-3 Code to emulate restricted symbols

<SUP><SMALL>TM</SMALL></SUP>

&copy

&reg

B.7 Overhead Problems

While executing some of the Portal stored procedures, mod_plsql is incurring a Describe overhead which results in two extra round trips to the database for a successful execution. This has performance implications.

B.7.1 The Describe Overhead

In order to execute PL/SQL procedures, mod_plsql needs to know about the data type 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 (unless the procedure descriptions are cached).

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, "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 someone tries to pass a single value for an array parameter. Then the PL/SQL procedure execution fails because it binds an array as a scalar. In such circumstances, mod_plsql issues the Describe call for the procedure and binds each parameter based on the information retrieved from the describe call, reexecutes the procedure and sends back the results. This happens transparently to the procedure, but internally mod_plsql has encountered two extra round trips (one for the failed execute and the other for the "Describe" call).

B.7.2 Avoiding the Describe Overhead

You can avoid performance problems by doing the following:

• Use Flexible Parameter Passsing.

• Always ensure that you pass multiple values for arrays (for single values, you can pass dummy values which will be ignored by your procedure).

• Using the following workaround. The workaround 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, if the original package is similar to 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; 
          /
     
     

  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. Create or replace package body testpkg as:

            procedure arrayproc (arr varchar2) is 
              localArr myArrayType; 
            begin 
              localArr(1) := arr; 
              arrayproc (localArr); 
            end arrayproc; 
     

B.7.3 The Flexible Parameter Passing (four parameter) Overhead

A similar 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 will experience one extra round-trip for execution.

B.7.4 Avoiding the Flexible Parameter Passing Overhead

To avoid this overhead, it is recommended that you write corresponding wrappers which 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 the parameters which are not passed in the two parameter interface.

B.8 Setting up PL/SQL to Work with WebDB

WebDB users running WebDB version 2.x (2.0, 2.1, 2.2) through the PL/SQL Gateway must perform the following steps.

1. Drop any older OWA packages in OWA_PUBLIC or OAS_PUBLIC.

2. Install the latest OWA packages shipped with the PL/SQL Gateway. To do so, connect to the database as the SYS user and at the command prompt run

   @owaload.sql log.txt
   
   

   This may invalidate some of your existing PL/SQL procedures. You may need to recompile them. See "Installing Required Packages" for more information

3. Set the following in the DAD configuration for the WebDB 2.x schema in wdbsvr.app configuration file.

   Authentication Mode = Basic

   Document Table = schema.wwv_document

   Extensions to be Uploaded as Long Raw = *

   If you set up your DAD using the Add for WebDB 2.x configuration page (http://<hostname>:<port>/pls/admin_/dadentries.htm), these settings are automatically set.

4. Connect to the database as the owner of the site and run wwvdocs.sql and wwvdocb.plb to enable WebDB 2.x sites. These files are located in the same directory as the owaload.sql file. See "Installing Required Packages" for more information.