4 Optimizing PL/SQL Performance</ti tle> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <meta http-equiv="Content-Language" content="en-US"> <m eta http-equiv="Content-Style-Type" content="text/css"> <meta http-equiv="Content-Script-Type" content="text/javascript"> <meta name= "robots" content="all" scheme="http://www.robotstxt.org/"> <meta name="doctitle" content="Oracle® HTTP Server mod_plsql User's Gu ide 10g Release 1 (10.1)"> <meta name="partno" content="B12303-01"> <link rel="Start" href="../../index.htm" title="Home" type="text/ html"> <link rel="Copyright" href="../../dcommon/html/cpyr.htm" title="Copyright" type="text/html"> <link rel="Stylesheet" href="../. ./dcommon/css/doccd.css" title="Default" type="text/css"> <link rel="Contents" href="toc.htm" title="Contents" type="text/html"> <lin k rel="Index" href="index.htm" title="Index" type="text/html"> <link rel="Prev" href="secure.htm" title="Previous" type="text/html"> <link rel="Next" href="app_a.htm" title="Next" type="text/html"> <link rel="alternate" href="../b12303.pdf" title="PDF version" type= "application/pdf"> </head> <body> <div class="header"> <p><a name="top" id="top" href="#BEGIN">Skip Headers</a></p> <table summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"><font color="#330099" face="Helvetica, Arial, sans- serif"><strong>Oracle® HTTP Server mod_plsql User's Guide<br> 10<i>g</i> Release 1 (10.1)</strong><br> Part Number B12303-01</fo nt></td> <td valign="bottom" align="right"> <table summary="" cellspacing="0" cellpadding="0" width="270"> <tr> <td align="center" va lign="top"><a href="../../index.htm"><img src="../../dcommon/gifs/prodicon.gif" alt="Go to Documentation Home" border="0"><br> <font size="-2">Home</font></a></td> <td align="center" valign="top"><a href="../../nav/portal_3.htm"><img src="../../dcommon/gifs/bookicon .gif" alt="Go to Book List" border="0"><br> <font size="-2">Book List</font></a></td> <td align="center" valign="top"><a href="toc.ht m"><img src="../../dcommon/gifs/conticon.gif" alt="Go to Table of Contents" border="0"><br> <font size="-2">Contents</font></a></td> <td align="center" valign="top"><a href="index.htm"><img src="../../dcommon/gifs/indxicon.gif" alt="Go to Index" border="0"><br> <fon t size="-2">Index</font></a></td> <td align="center" valign="top"><a href="../../mix$101/b12039/toc.htm"><img src="../../dcommon/gifs /mix.gif" alt="Go to Master Index" border="0"><br> <font size="-2">Master Index</font></a></td> <td align="center" valign="top"> <a href="../../dcommon/html/feedback.htm"><img src="../../dcommon/gifs/feedback.gif" alt="Go to Feedback page" border="0"><br> <font size="-2">Feedback</font></a></td></tr></table></td></tr></table> <hr> <table summary="layout table" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"> <table summary="layout table" cellspacing="0" cellpadding="0" align="left" width="9 0"> <tr> <td align="center" valign="top"><a href="secure.htm"><img src="../../dcommon/gifs/larrow.gif" alt="Go to previous page" bord er="0"><br> <font size="-2">Previous</font></a></td> <td align="center" valign="top"><a href="app_a.htm"><img src="../../dcommon/gifs /rarrow.gif" alt="Go to next page" border="0"><br> <font size="-2">Next</font></a></td></tr></table></td> <td align="right" valign="t op"><a href="../b12303.pdf"><font size="-2">View PDF</font></a></td></tr></table> <a name="BEGIN"></a></div> <div class="IND"> <a name="1020614"></a>  <h1 class="Title"><font face="Arial, Helvetica, sans-serif" color="#33 0099">4 Optimizing PL/SQL Performance</font></h1>  <a name="1020618"></a> <p class="BP">This chapter discusses the techniques for improving PL/SQL performance in Oracle HTTP Server.</p> <a name="1020620"></a> <p class="BP">This chapter contains th e following sections:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1028606"></a><a href="optplsql.htm#1020653">PL/SQL Pe rformance in Oracle HTTP Server - Overview</a></li> <li class="LB1" type="disc"><a name="1028648"></a><a href="optplsql.htm#1020830"> Process-Based and Thread-Based Operation in Oracle HTTP Server</a></li> <li class="LB1" type="disc"><a name="1028651"></a><a href="op tplsql.htm#1020882">Performance Tuning Issues for mod_plsql</a></li> <li class="LB1" type="disc"><a name="1028654"></a><a href="optpl sql.htm#1021593">Performance Tuning Areas in mod_plsql</a></li> <li class="LB1" type="disc"><a name="1028657"></a><a href="optplsql.h tm#1023304">Using Caching with PL/SQL Based Web Applications</a></li> <li class="LB1" type="disc"><a name="1028660"></a><a href="optp lsql.htm#1024840">Tuning File System Cache to Improve Caching Performance</a></li> <li class="LB1" type="disc"><a name="1028663"></a> <a href="optplsql.htm#1026750">Oracle HTTP Server Directives</a></li></ul> <a name="1020653"></a>  <h2 class=" H1"><font face="Arial, Helvetica, sans-serif" color="#330099">4.1 PL/SQL Performance in Oracle HTTP Server - Overview</font></h2> <!- -/TOC=h1--> <a name="1020654"></a> <p class="BP">This chapter describes several techniques to improve the performance of PL/SQL base d Web applications in Oracle HTTP Server.</p> <a name="1020664"></a> <p class="BP"><a href="optplsql.htm#1026943">Table 4-1</a> lists recommendations for Database Access Descriptor (DAD) parameters and settings. By default, these DAD parameters are specified i n the file <code>dads.conf</code>. On UNIX systems, this is in the <em><code>ORACLE_HOME</code></em><code>/Apache/modplsql/conf</code > directory. On Windows systems, by default, this file is in the directory <em><code>ORACLE_HOME</code></em><code>\Apache\Apache\modp lsql\conf</code> directory. The file <code>dads.README</code> in this directory describes the DAD parameters in detail.</p> <a name=" 1026835"></a> <h5 class="TTW"><a name="1026835"></a><a name="1026943"></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Tab le 4-1 Database Access Descriptor (DAD) Parameters Recommended Setting Summary</em></font></strong></h5> <table summary="" class="For malWide" border="1" frame="HSIDES" rules="GROUPS" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class= "Formal"> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1026947"></a> <font face="Arial, Helvetica, sans-serif "><strong>Parameter</strong></font></th> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1026949"></a> <font fac e="Arial, Helvetica, sans-serif"><strong>Recommended Setting</strong></font></th></tr></thead> <tbody> <tr class="Formal" align="left " valign="top"> <td class="Formal"><a name="1026951"></a> <p class="TB"><code>PlsqlAlwaysDescribeProcedure</code></p></td> <td class ="Formal"><a name="1026953"></a> <p class="TB">Set this to <code>off</code> for best performance. <a name="1026954"></a></p> <p clas s="TB">Default Value: <code>off</code></p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="102695 6"></a> <p class="TB"><code>PlsqlDatabaseConnectString</code></p></td> <td class="Formal"><a name="1026958"></a> <p class="TB">For newer DADs, use the <code>ServiceNameFormat</code>. Use the <code>SIDFormat</code> only for backward compatibility. <a name="1026959" ></a></p> <p class="TB">Note: for HA configurations of the database, it is recommended that the connect string parameter gets resolve d through an LDAP lookup.</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1026961"></a> <p c lass="TB"><code>PlsqlFetchBufferSize</code></p></td> <td class="Formal"><a name="1026963"></a> <p class="TB">For multibyte character sets like Japanese or Chinese, setting this to <code>256</code> should provide better performance <a name="1026964"></a></p> <p clas s="TB">Default Value: <code>128</code></p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="102696 6"></a> <p class="TB"><code>PlsqlIdleSessionCleanupInterval</code></p></td> <td class="Formal"><a name="1026968"></a> <p class="TB" >Increasing this parameter allows pooled database connections to remain available, in the pool, for the specified time <a name="10269 69"></a></p> <p class="TB">Default Value: <code>15</code> (minutes) <a name="1026972"></a></p> <p class="TB">See Also: <a href="optpl sql.htm#1021528">Section 4.3.3, "Restarting the mod_plsql Connection Pool"</a></p></td></tr> <tr class="Formal" align="left" val ign="top"> <td class="Formal"><a name="1026978"></a> <p class="TB"><code>PlsqlLogEnable</code></p></td> <td class="Formal"><a name=" 1026980"></a> <p class="TB">This parameter should be set to <code>Off</code> unless recommended by Oracle support for debugging purp oses <a name="1026981"></a></p> <p class="TB">Default Value: <code>off</code></p></td></tr> <tr class="Formal" align="left" valign="t op"> <td class="Formal"><a name="1026983"></a> <p class="TB"><code>PlsqlMaxRequestsPerSession</code></p></td> <td class="Formal"><a name="1026985"></a> <p class="TB">If the PL/SQL based Web application does not leak resources or memory, this parameter can be set t o a higher value (for example, 5000). <a name="1026986"></a></p> <p class="TB">Default Value: <code>1000</code> <a name="1026995"></a ></p> <p class="TB">See Also: <a href="optplsql.htm#1021343">Section 4.3.2, "Closing Pooled Database Sessions"</a> and <a href=" optplsql.htm#1021857">Section 4.4.2, "Connection Pooling Tips and Oracle HTTP Server Configuration"</a>.</p></td></tr> <tr class ="Formal" align="left" valign="top"> <td class="Formal"><a name="1027001"></a> <p class="TB"><code>PlsqlNLSLanguage</code></p></td> <td class="Formal"><a name="1027003"></a> <p class="TB">Set this parameter to match the database Globalization Support parameters to eliminate overheads in character set conversions in Oracle Net Services</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1027005"></a> <p class="TB"><code>PlsqlSessionStateManagement</code></p></td> <td class="Formal"><a name ="1027007"></a> <p class="TB">Set this parameter to <code>StatelessWithFastResetPackageState</code> if the database is 8.1.7.2 or la ter.</p></td></tr></tbody></table> <a name="1027020"></a> <p class="BP"><a href="optplsql.htm#1027031">Table 4-2</a> lists mod_ plsql caching options and the sections that describe these caching options.</p> <h5 class="TT"><a name="1027025"></a><a name="1027031 "></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-2 Caching Options</em></font></strong></h5> <table summary="" cl ass="Formal" border="1" frame="HSIDES" rules="GROUPS" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr cl ass="Formal"> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1027051"></a> <font face="Arial, Helvetica, sans-s erif"><strong>Option</strong></font></th> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1027053"></a> <font fa ce="Arial, Helvetica, sans-serif"><strong>Description</strong></font></th></tr></thead> <tbody> <tr class="Formal" align="left" valig n="top"> <td class="Formal"><a name="1027055"></a> <p class="TB">Expires Technique</p></td> <td class="Formal"><a name="1027057"></a > <p class="TB">Best performance - for content that changes predictably. <a name="1027064"></a></p> <p class="TB">See Also: <a href= "optplsql.htm#1024011">Section 4.5.2, "Using the Expires Technique"</a></p></td></tr> <tr class="Formal" align="left" valign="to p"> <td class="Formal"><a name="1027066"></a> <p class="TB">Validation technique</p></td> <td class="Formal"><a name="1027068"></a> <p class="TB">Good performance - for content that changes unpredictably. <a name="1027075"></a></p> <p class="TB">See Also: <a href= "optplsql.htm#1023409">Section 4.5.1, "Using the Validation Technique"</a></p></td></tr> <tr class="Formal" align="left" valign= "top"> <td class="Formal"><a name="1027077"></a> <p class="TB">System-level caching</p></td> <td class="Formal"><a name="1027079"></ a> <p class="TB">Improves performance by caching one copy for everyone on system. <a name="1027085"></a></p> <p class="TB">See Also: <a href="optplsql.htm#1024388">Section 4.5.3, "System- and User-level Caching with PL/SQL Based Web Applications"</a></p></td>< /tr></tbody></table> <a name="1020812"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cell spacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="N oteAlso"> <td class="NoteAlso"><a name="1020791"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH"><strong class="Bold ">See Also</strong>:</strong></font> <a name="1020800"></a> <p class="NB">Chapter 6 "Oracle HTTP Server Modules" in the <em class="I talic"><a class="olinkSRC HSADM" href="../../server$101/b12255/toc.htm">Oracle HTTP Server Administrator's Guide</a></em> for details on the DAD Parameters shown in <a href="optplsql.htm#1026943">Table 4-1</a>.</p></td></tr></table></div> <a name="1020830"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">4.2 Process-Based and Thread-Based Operation in Oracle HTTP Server</font></h2>  <a name="1020834"></a> <p class="BP">This chapter describes PL/SQL perfor mance issues that apply on platforms where the Oracle HTTP Server is process-based and thread-based. On a process-based Oracle HTTP S erver, 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 wi th multiple threads within the process; individual threads can be used serve all types of HTTP requests.</p> <a name="1020873"></a> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout t able to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1020862"></ a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a name="1020866"></a> <p class="NB">In some ca ses in this chapter we make references to performance optimizations that apply for PL/SQL based Web applications where the distinctio n between platforms, either process-based or thread based is significant.</p> <hr></td></tr></table></div> <a name="1020882"></a> <!- -TOC=h1-"1020882"--> <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">4.3 Performance Tuning Issues for mod_p lsql</font></h2>  <a name="1020890"></a> <p class="BP">When tuning mod_plsql to improve the performance of PL/SQL base d Web applications, it is important to be familiar with some mod_plsql internals. This section presents a basic overview of some mod_ plsql functionality.</p> <a name="1020904"></a> <p class="BP">This section covers the following topics:</p> <ul class="LB1"> <li cla ss="LB1" type="disc"><a name="1020920"></a><a href="optplsql.htm#1020956">Connection Pooling with mod_plsql</a></li> <li class="LB1" type="disc"><a name="1020933"></a><a href="optplsql.htm#1021343">Closing Pooled Database Sessions</a></li> <li class="LB1" type="disc "><a name="1020947"></a><a href="optplsql.htm#1021528">Restarting the mod_plsql Connection Pool</a></li></ul> <a name="1020956"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.3.1 Connection Pooling with mod_pl sql</font></h3>  <a name="1020964"></a> <p class="BP">The Database Server connection pooling logic supplied with mod_p lsql can be best explained with an example.</p> <a name="1020970"></a> <p class="BP">For example, consider the following typical sce nario:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a name="1020983"></a>The Oracle HTTP Server listener is star ted. There are no database connections in the connection pool maintained by mod_plsql.</li> <li class="LN1" type="1" value="2"><a nam e="1021000"></a>A browser makes a mod_plsql request (R1) for Database Access Descriptor (DAD) D1.</li> <li class="LN1" type="1" value ="3"><a name="1021018"></a>One of the Oracle HTTP Server processes (<code>httpd</code> process P1) starts servicing the request R1.</ li> <li class="LN1" type="1" value="4"><a name="1021031"></a>mod_plsql in process P1 checks its connection pool and finds that there are no database connections in its pool for that user request.</li> <li class="LN1" type="1" value="5"><a name="1021044"></a>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 databa se connection to its pool.</li> <li class="LN1" type="1" value="6"><a name="1021053"></a>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.</li> <li class="LN1" type="1" value="7"><a name="1021066"></a>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.</li> <li class="LN1" type="1" value="8"><a name="1021079"></a>From this point on, all subsequent requests to process P2 for DAD D1 can now make use of the database connection p ooled by mod_plsql.</li> <li class="LN1" type="1" value="9"><a name="1021092"></a>Now, assume that a request R2 is made for DAD D2 an d this request gets routed to process P1.</li> <li class="LN1" type="1" value="10"><a name="1021105"></a>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. <p><a name="1021111"></a></p> < p class="BP1">The important details in the example shown in steps 1-10 are:</p> <ol class="LA2" type="a"> <li class="LA2" type="a" va lue="1"><a name="1021124"></a>Each Oracle HTTP Server process serves all types of requests, such as static files requests, servlet re quests, and mod_plsql requests. There is no control on which Oracle HTTP Server process services the next request.</li> <li class="LA 2" type="a"><a name="1021143"></a>One Oracle HTTP Server process cannot use or share the connection pool created by another process. <p><a name="1021173"></a></p> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="l tr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="N ote"> <hr> <a name="1021159"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a name="1021166" ></a> <p class="NB">On Windows systems, the Oracle HTTP Server is just one process. Therefore, the connection pool is shared and can be used across threads.</p> <hr></td></tr></table></div></li> <li class="LA2" type="a"><a name="1021183"></a>Each Oracle HTTP Server process pools at most one database connection for each DAD.</li> <li class="LA2" type="a"><a name="1021192"></a>User sessions are sw itched within a pooled database connection for a DAD. For DADs based on Oracle Application Server Single Sign-On (SSO), proxy authent ication is used to switch the user session. For non-SSO users, using HTTP basic authentication with the username and password not in the DAD, users are re-authenticated on the same connection.</li> <li class="LA2" type="a"><a name="1021204"></a>Multiple DADs may poi nt to the same database instance, but database connections are not shared across DADs even within the same process.</li> <li class="L A2" type="a"><a name="1021218"></a>Unused DADs do not result in any database connections.</li></ol></li></ol> <a name="1021227"></a> <p class="BP">In the worst-case scenario, the total number of database connections pooled by mod_plsql is a factor of the total numb er of active DADs multiplied by the number of Oracle HTTP Server (<code>httpd</code>) processes running at any given time for a singl e Oracle HTTP Server instance. If you have configured the Oracle HTTP Server processes to a high number, you need to configure the ba ck-end database to handle a corresponding number of database sessions, and remember that this configuration value needs to be multipl ied times the number of Oracle HTTP Server instances that use the back-end database.</p> <a name="1021255"></a> <p class="BP">For ex ample, if there are three Oracle HTTP Server instances configured to spawn a maximum of 50 <code>httpd</code> processes each, plus tw o active DADs, you need to set up the database to allow 300 (<code>3*50*2</code>) sessions. This number does not include any sessions that are needed to allow other Web applications to connect.</p> <a name="1021272"></a> <p class="BP">On UNIX systems, database conn ections cannot be shared across <code>httpd</code> processes, and process-based platforms have more of a <em class="Italic">Connectio n Reuse</em> feature than <em class="Italic">Connection Pooling</em>. Note that this is an artifact of the process-model in Oracle HT TP Server.</p> <a name="1021334"></a> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0 " dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1021299"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a name=" 1021311"></a> <p class="NB">Refer to <a href="optplsql.htm#1022562">Section 4.4.4, "Two-Listener Strategy"</a> if the number of database sessions is a concern for details on how to address this problem. <a name="1021319"></a></p> <p class="NB">On Windows syste ms, the Oracle HTTP Server runs as a single process, and the connection pool is shared. Therefore, <a href="optplsql.htm#1022562">Sec tion 4.4.4, "Two-Listener Strategy"</a> does not apply to Windows systems.</p> <hr></td></tr></table></div> <a name="1021343"></ a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.3.2 Closing Pooled Database Ses sions</font></h3>  <a name="1021348"></a> <p class="BP">Pooled database sessions are closed under the following circum stances:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a name="1021358"></a>When a pooled connection has been use d to serve a configured number of requests. <p><a name="1021367"></a></p> <p class="BP1">By default each connection pooled by mod_pl sql is used to service a maximum of 1000 requests and then the database connection is shut down and reestablished. This is done to ma ke sure that any resource leaks in the PL/SQL based Web application, or in the Oracle client server side, do not adversely affect the system. To change the default value of <code>1000</code> by tuning the DAD configuration parameter <code>PlsqlMaxRequestsPerSession< /code>.</p></li> <li class="LN1" type="1" value="2"><a name="1021383"></a>When a pooled connection has been idle for an extended peri od of time. <p><a name="1021387"></a></p> <p class="BP1">By default, each pooled connection gets automatically cleaned up after 15 m inutes of idle time. This operation is performed by the cleanup thread in mod_plsql. For heavily loaded sites, each connection could be 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 con nection would be cleaned up based on the configuration value of <code>PlsqlMaxRequestsPerSession</code>. Change the default value of 15 minutes by tuning the mod_plsql configuration parameter <code>PlsqlIdleSessionCleanupInterval</code>. Consider increasing the defa ult for better performance in cases where the site is not heavily loaded.</p></li> <li class="LN1" type="1" value="3"><a name="102142 0"></a>On UNIX systems, when the Oracle HTTP Server process goes down. <p><a name="1021427"></a></p> <p class="BP1">On UNIX systems, the Oracle HTTP Server configuration parameter <code>MaxRequestsPerChild</code> governs when an Oracle HTTP Server process will be s hut down. For example, if this parameter is set to <code>5000</code>, each Oracle HTTP Server process would serve exactly 5000 reques ts before it is shut down. Oracle HTTP Server processes could also start up and shut down as part of Oracle HTTP Server maintenance b ased on the configuration parameters <code>MinSpareServers</code>, <code>MaxSpareServers</code>, and <code>MaxClients</code>. For mod _plsql connection pooling to be effective, it is extremely important that Oracle HTTP Server is configured so that each Oracle HTTP S erver 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 Serve r process replenish the connection pool before subsequent requests gain any benefit of pooling.</p> <a name="1021519"></a> <div alig n="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1021498"></a>< font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1021508"></a> <p class="NB">Chapter 6 "Oracle HTTP Server Modules" in the <em class="Italic"><a class="olinkSRC HSADM" href="../../server$101/b12255/toc.htm">Oracle HTTP Server Administrator's Guide</a>.</em></p></td></tr></table></div></li></ol> <a name="1021528"></a>  <h3 class ="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.3.3 Restarting the mod_plsql Connection Pool</font></h3>  <a name="1021532"></a> <p class="BP">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 Oracle HT TP Server listener. This is because the cleanup thread in mod_plsql cleans up database sessions that are unused for more than 15 minu tes. The time specified for cleaning up idle sessions is tunable using the <code>PlsqlIdleSessionCleanupInterval</code>, configuratio n parameter (the default value is 15 minutes).</p> <a name="1021548"></a> <p class="BP">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 ex perience failure is equal to the number of connections that were pooled by mod_plsql.</p> <a name="1021584"></a> <div align="center" > <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1021568"></a><font face= "Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1021575"></a> <p class="NB"><a href="optplsql.h tm#1026943">Table 4-1, "Database Access Descriptor (DAD) Parameters Recommended Setting Summary"</a></p></td></tr></table></div> <a name="1021593"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">4.4 Performa nce Tuning Areas in mod_plsql</font></h2>  <a name="1021601"></a> <p class="BP">While using mod_plsql, there are three areas that affect performance and scalability:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1021615"></a><a href="optpl sql.htm#1021654">PL/SQL Based Web Application Development Considerations</a></li> <li class="LB1" type="disc"><a name="1021631"></a>< a href="optplsql.htm#1021857">Connection Pooling Tips and Oracle HTTP Server Configuration</a></li> <li class="LB1" type="disc"><a na me="1021645"></a><a href="optplsql.htm#1022427">Tuning the Number of Database Sessions</a></li></ul> <a name="1021654"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.4.1 PL/SQL Based Web Application Developmen t Considerations</font></h3>  <a name="1021658"></a> <p class="BP">PL/SQL Gateway users should consider the following topics when developing PL/SQL based Web applications:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a name="10216 74"></a><strong class="Bold">Manage the use of Database Access Descriptors (DADs)</strong> <p><a name="1021681"></a></p> <p class="B P1">Try to restrict the number of DADs that each Oracle HTTP Server node uses.</p> <a name="1021708"></a> <div align="center"> <tabl e class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" tit le="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1021697"></a><font face="Arial, Helve tica, sans-serif"><strong class="NH">Note:</strong></font> <a name="1021701"></a> <p class="NB">Performance is not affected if there are DADs that are not being used.</p> <hr></td></tr></table></div></li> <li class="LN1" type="1" value="2"><a name="1021720"></a><st rong class="Bold">Use of Nested Tables</strong> <p><a name="1021724"></a></p> <p class="BP1">PL/SQL provides the ability to create t ables. 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 o f the table is the binary integer ranging from -2147483647 to +2147483647. This table index option is known as <em class="Italic">spa rsity</em>, and allows meaningful index numbers such as customer numbers, employee number, or other useful index keys. Use PL/SQL tab les to process large amounts of data.</p> <a name="1021741"></a> <p class="BP1">PL/SQL provides <code>TABLE</code> and <code>VARRAY< /code> (variable size array) collection types. The <code>TABLE</code> collection type is called a nested table. Nested tables are unl imited in size and can be sparse, which means that elements within the nested table can be deleted using the <code>DELETE</code> proc edure. 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 st orage table, where each element maps to a row in the storage table.</p></li> <li class="LN1" type="1" value="3"><a name="1021763"></a ><strong class="Bold">Use procedure naming overloading with caution</strong> <p><a name="1021767"></a></p> <p class="BP1">PL/SQL bas ed Web applications should use the procedure name overloading feature with caution. It is best if procedure name overloading is avoid ed by having multiple procedures with different names.</p></li> <li class="LN1" type="1" value="4"><a name="1021779"></a><strong clas s="Bold">Consider rewriting applications where there is significant overhead in determining the type parameters</strong> <p><a name= "1021785"></a></p> <p class="BP1">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 a ttempt to execute a procedure fails and the procedure signature needs to be described before it can be executed by mod_plsql.</p></li > <li class="LN1" type="1" value="5"><a name="1021803"></a><strong class="Bold">Use procedures with 2-parameter style flexible parame ter passing</strong> <p><a name="1021807"></a></p> <p class="BP1">Procedures should make use of the more performant 2-parameter styl e flexible parameter passing rather than the 4-parameter style parameter passing</p> <a name="1021845"></a> <div align="center"> <ta ble class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a not e" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1021825"></a><font face="Aria l, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1032770"></a> <p class="NB"><a class="olinkSRC ADFNS " href="../../appdev$101/b10795/toc.htm"><em class="Italic">Oracle Database Application Developer's Guide - Fundamentals</em></a></p> </td></tr></table></div></li></ol> <a name="1021857"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-s erif" color="#330099">4.4.2 Connection Pooling Tips and Oracle HTTP Server Configuration</font></h3>  <a name="1021863" ></a> <p class="BP">Consider the following topics when configuring connection pooling with Oracle HTTP Server:</p> <ol class="LN1" t ype="1"> <li class="LN1" type="1" value="1"><a name="1021875"></a>Using the default connections pooling and setting values for <code> PlsqlMaxRequestsPerSession</code> <p><a name="1021884"></a></p> <p class="BP1">Creating new database connections is an expensive ope ration 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 administra tor 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 <code>PlsqlMaxRequestsPerSession</code> t o the value 1.</p> <a name="1021919"></a> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspacin g="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="1021904"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a na me="1021912"></a> <p class="NB">Setting <code>PlsqlMaxRequestsPerSession</code> to the value 1 reduces the number of available datab ase sessions and may impact performance.</p> <hr></td></tr></table></div></li> <li class="LN1" type="1" value="2"><a name="1021929">< /a>On UNIX systems, Oracle HTTP Server configuration should be properly tuned so that once processes are started up, the processes re main up for a while. Otherwise, the connection pooling in mod_plsql is rendered useless. The Oracle HTTP Server listener should not h ave to continually start up and shut down processes. A proper load analysis should be performed of the site to determine what the ave rage load on the Web site. The Oracle HTTP Server configuration should be tuned such that the number of <code>httpd</code> processes can handle the average load on the system. In addition, the configuration parameter <code>MaxClients</code> in the <code>httpd.conf</ code> file should be able to handle random load spikes as well.</li> <li class="LN1" type="1" value="3"><a name="1021963"></a>On UNIX systems, Oracle HTTP Server processes should be configured so that processes are eventually killed and restarted. This is required t o manage any possible memory leaks in various components accessed through the Oracle HTTP Server. This is specifically required in mo d_plsql to ensure that any database session resource leaks do not cause a problem. Make sure that <code>MaxRequestsPerChild</code> co nfiguration parameter is set to a high number. For PL/SQL based Web applications, this should not be set to <code>0</code>. <p><a na me="1022008"></a></p> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summa ry="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr > <a name="1021994"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a name="1021998"></a> <p class="NB">On Windows systems, to assure that database session resource leaks do not cause problems, the Oracle HTTP Server needs to be periodically restarted.</p> <hr></td></tr></table></div></li> <li class="LN1" type="1" value="4"><a name="1022018"></a>For heavil y loaded sites, the Oracle HTTP Server configuration parameter <code>KeepAlive</code> should be disabled. This ensures that each proc ess is available to service requests from other clients as soon as a process is done with servicing the current request. For sites wh ich are not heavily loaded, and where it is guaranteed that the number of Oracle HTTP Server processes are always greater than the nu mber of simultaneous requests to the Oracle HTTP Server listener, enabling the <code>KeepAlive</code> parameter results in performanc e improvements. In such cases, make sure to tune the <code>KeepAliveTimeout</code> parameter appropriately.</li> <li class="LN1" type ="1" value="5"><a name="1022052"></a>You may want to lower the value of <code>Timeout</code> 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 thi s value too low, otherwise slower responding clients could time out.</li> <li class="LN1" type="1" value="6"><a name="1022064"></a>Mo st Web sites have many static image files, which are displayed in each screen for a consistent user interface. Such files rarely chan ge and you can reduce a considerable load on the system by tagging each image served by the Oracle HTTP Server listener with <code>mo d_expires</code>. You should also consider front-ending your Web site with Oracle Application Server Web Cache. <ul class="LB2"> <li class="LB2" type="disc"><a name="1022090"></a>How do I know if the Web site can benefit from the use of <code>mod_expires</code>? < ul class="LD3"> <li class="LD3" type="CIRCLE"><a name="1022100"></a>Use Netscape, or any browser that enables you to view page cachin g information, and visit several heavily accessed Web pages on the site. On each page, right click the mouse and select <code>View In fo</code> from the pop up menu (or the equivalent command for your browser). If the top panel in the page information window lists ma ny different images and static content, then the site could benefit from the use of <code>mod_expires</code>.</li> <li class="LD3" ty pe="CIRCLE"><a name="1022120"></a>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 <code>grep</code> utility to search for <code>304</code> in the <code>access_log</code> and d ivide this resulting number of lines by the total number of lines in the <code>access_log</code>. If this percentage is high, then th e site could benefit from the use of <code>mod_expires</code>.</li></ul></li> <li class="LB2" type="disc"><a name="1022152"></a>How d o I tag static files with the Expires header? <ul class="LD3"> <li class="LD3" type="CIRCLE"><a name="1022166"></a>Locate the <code> Location</code> directive used to serve your static image files. Add the <code>ExpiresActive</code> and <code>ExpiresDefault</code> d irectives to it. <pre class="CE3"><a name="1022183"></a>Alias /images/ "/u01/app/oracle/myimages/" <a name="1022188"></a><Direct ory "/u01/app/oracle/myimages/"> <a name="1022193"></a> AllowOverride None <a name="1022198"></a> Order allow, deny <a name="1022203"></a> Allow from all <a name="1022208"></a> ExpiresActive On <a name="1022213"></a> ExpiresDefault A25920 00 <a name="1022218"></a></Directory> <a name="1022224"></a> </pre> <a name="1022232"></a> <p class="BP3">The browser caches a ll static files served off the <code>/images</code> path for 30 days from now. Refer to the <a class="olinkSRC HSADM" href="../../ser ver$101/b12255/toc.htm"><em class="Italic">Oracle HTTP Server Administrator's Guide</em></a> for more details.</p></li></ul></li> <li class="LB2" type="disc"><a name="1022352"></a>How do I know if the static files are being tagged with the <code>Expires</code> heade r? <ul class="LD3"> <li class="LD3" type="CIRCLE"><a name="1022363"></a>Using Netscape, or the browser of your choice, clean up all the cached files in the browser.</li> <li class="LD3" type="CIRCLE"><a name="1022376"></a>Visit a Web page that should have images ta gged with the <code>Expires</code> header. Right click the mouse on the page and select <strong class="Bold">View Info</strong>, from the pop up menu. or use the equivalent command for your browser.</li> <li class="LD3" type="CIRCLE"><a name="1022385"></a>In the top panel of the page information, select an image that should be tagged with the <code>Expires</code> header.</li> <li class="LD3" type ="CIRCLE"><a name="1022402"></a>Review the information displayed in the bottom panel. The <code>Expires</code> header should be set t o a valid date. If this entry is <code>No date given</code>, then the file is not being tagged with the <code>Expires</code> header.< /li></ul></li></ul></li></ol> <a name="1022427"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.4.3 Tuning the Number of Database Sessions</font></h3>  <a name="1022432"></a> <p class="BP">Consid er the following topics when tuning the number of database sessions:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1" ><a name="1022454"></a>The <code>processes</code> and <code>sessions</code> parameters in the Oracle <code>init$SID.ora</code> config uration file should be set so that Oracle is able to handle the maximum number of database sessions. This number should be proportion al to the number of DADs times the maximum number of Oracle HTTP Server processes, times the number of Oracle HTTP Server instances.< /li> <li class="LN1" type="1" value="2"><a name="1022469"></a>Using a two-listener strategy or using a shared server reduces the numb er of database sessions. See <a href="optplsql.htm#1022562">Section 4.4.4, "Two-Listener Strategy"</a>.</li> <li class="LN1" typ e="1" value="3"><a name="1022488"></a>On UNIX platforms, the connection pool is not shared across Oracle HTTP Server processes. For t his reason, it is recommended that the application use as few DADs as possible.</li></ol> <a name="1022562"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.4.4 Two-Listener Strategy</font></h3>  < a name="1022569"></a> <p class="BP">On platforms where the Oracle HTTP Server is process-based, such as all UNIX-based platforms, ea ch process serves all types of HTTP requests, including servlets, PLSQL, static files, and CGI. In a single Oracle HTTP Server listen er setup, each <code>httpd</code> process maintains its own connection pool to the database. The maximum number of database sessions is governed by the setting in <code>httpd.conf</code> configuration file for <code>StartServers</code>, <code>MinSpareServers</code>, and <code>MaxSpareServers</code>, plus the load on the system. This architecture does not allow for tuning the number of database se ssions 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.</p> <a name="1022622"></a> <p class="BP">For example, assume a main Oracle HTTP Server listener is running on port 7777 of <code>mylsnr1.mycompany.com</code>. First, you can install another Oracle HTTP Server listener on port 888 8 on <code>mylsnr2.mycompany.com</code>. Next, redirect all mod_plsql requests made to <code>mylsnr1.mycompany.com:7777</code> to the second listener on <code>mylsnr2.mycompany.com:8888</code>. Review the following steps:</p> <ol class="LN1" type="1"> <li class="LN1 " type="1" value="1"><a name="1022644"></a>To redirect all PL/SQL requests for <code>mylsnr1.mycompany.com:7777</code> to <code>mylsn r2.mycompany.com:8888</code>, make the following configuration changes: <ol class="LA2" type="a"> <li class="LA2" type="a" value="1" ><a name="1022662"></a>For the Oracle HTTP Server listener running on Port 7777, edit <em><code>ORACLE_HOME</code></em><code>/Apache/ modplsql/conf/plsql.conf</code> file. Comment out the following line by putting a <code>#</code> in front of the line: <pre class="C E2"><a name="1022679"></a>#LoadModule plsql_module... <a name="1022686"></a> </pre> </li> <li class="LA2" type="a"><a name="1022693"> </a>Copy the DAD location used to service PL/SQL requests in <code>mylsnr1.mycompany.com</code> to the configuration file <em><code>O RACLE_HOME</code></em><code>/Apache/modplsql/conf/dads.conf</code> in <code>mylsnr2.mycompany.com</code>. <p><a name="1022710"></a>< /p> <p class="BP2">Comment out the DAD location configuration parameters on <code>mylsnr1.mycompany.com</code> by prefixing the line with a "#" character.</p> <pre class="CE2"><a name="1022723"></a>#<Location /pls/dad> <a name="1022728"></a>#... <a name="102 2733"></a>#</Location> <a name="1022740"></a> </pre> </li> <li class="LA2" type="a"><a name="1022751"></a>Configure this listen er to forward all mod_plsql requests for this DAD location to the second listener by adding the following line in <code>dads.conf</co de>: <pre class="CE2"><a name="1022764"></a>ProxyPass /pls/dad http://mylsnr2.mycompany.com:8888/pls/dad <a name="1022772"></a> </pr e> </li></ol> <a name="1022778"></a> <p class="BP1">Repeat the configuration procedures for all DAD Locations.</p></li> <li class="LN 1" type="1" value="2"><a name="1022785"></a>Because the PL/SQL procedures generate URLs that are displayed in the browser, it is impo rtant that all URLs are constructed without any references to the internal mod_plsql listener on <code>mylsnr2.mycompany.com:8888</co de>. Depending on how the URLs are being generated in the PL/SQL based Web application, there are three options: <ul class="LB2"> <l i class="LB2" type="disc"><a name="1022800"></a> If the URLs are hard-coded into the application, make sure that they are always gene rated using the hard-coded values as HOST=mylsnr1.mycompany.com and PORT=7777. No change would be required for this scenario.</li> <l i class="LB2" type="disc"><a name="1022809"></a>If the PL/SQL based Web applications always use the CGI environment variables <code>S ERVER_NAME</code> and <code>SERVER_PORT</code>, then it is easy to change the configuration of the listener on <code>mylsnr2.mycompan y.com</code>. Edit the file and change the lines <code>ServerName</code> and <code>Port</code> in the <em><code>ORACLE_HOME</code></e m><code>/Apache/Apache/conf/httpd.conf</code> file for the second listener as follows: <pre class="CE2"><a name="1022838"></a>Server Name mylsnr1.mycompany.com (was mylsnr2.mycompany.com) <a name="1022843"></a>Port 7777 (was 8888) <a name="102 2850"></a> </pre> </li> <li class="LB2" type="disc"><a name="1022857"></a>If the URLs are being generated using the CGI environment v ariable <code>HTTP_HOST</code>, you need to override the CGI environment variables for the Oracle HTTP Server listener running on Por t 8888. Add the following lines to the <em><code>ORACLE_HOME</code></em><code>/Apache/modplsql/conf/dads.conf</code> file for each DA D to override the default CGI environment variables <code>HOST</code>, <code>SERVER_NAME</code>, and <code>SERVER_PORT</code>: <pre class="CE2"><a name="1022889"></a>PlsqlCGIEnvironmentList SERVER_NAME mylsnr1.mycompany.com <a name="1022894"></a>PlsqlCGIEnvironm entList SERVER_PORT 7777 <a name="1022899"></a>PlsqlCGIEnvironmentList HOST mylsnr1.us.oracle.com:7777 <a name="1022907"></a> </p re> </li></ul> <a name="1022911"></a> <p class="BP1">In all cases, the intent is to fool the application to generate URLs as if there never was a second listener.</p></li> <li class="LN1" type="1" value="3"><a name="1022920"></a>Test the setup and make sure that you can access all the DADs without any problems.</li> <li class="LN1" type="1" value="4"><a name="1022929"></a>In this setup, the main listener <code>mylsnr1.mycompany.com</code> can be configured based on the total load on the Oracle HTTP Server listener. The second listener on <code>mylsnr2.mycompany.com</code> can be fine-tuned based on just the mod_plsql requests being made.</li></ol> <a name=" 1022948"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.4.5 Overhead Problem s</font></h3>  <a name="1022956"></a> <p class="BP">While executing some of the stored procedures, mod_plsql may incur a <code>Describe</code> overhead, which would result in two extra round trips to the database for a successful execution. This has p erformance implications.</p> <a name="1022969"></a>  <h4 class="H3"><font face="Arial, Helvetica, sans-serif" color="#330099">4.4.5.1 The Describe Overhead</font></h4>  <a name="1022977"></a> <p class="BP">In order to execute st ored procedures, mod_plsql needs to know about the datatype of the parameters being passed in. Based on this information, mod_plsql b inds each parameter either as an array or as a scalar. One way to know the procedure signature is to describe the procedure before ex ecuting it. However, this approach is not efficient because every procedure has to be described before execution. To avoid the descri be overhead, mod_plsql looks at the number of parameters passed for each parameter name. It uses this information to assume the datat ype 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 multipl e values for a scalar. In such cases, the first attempt to execute the PL/SQL procedure fails. mod_plsql issues a <code>Describe</cod e> call to get the signature of the PL/SQL procedure and binds each parameter based on the information retrieved from the <code>Descr ibe</code> operation. The procedure is re-executed and results are sent back.</p> <a name="1023006"></a> <p class="BP">This <code>De scribe</code> 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.</p> <a name="1023030"></a>  <h4 class="H3"><font fac e="Arial, Helvetica, sans-serif" color="#330099">4.4.5.2 Avoiding the Describe Overhead</font></h4>  <a name="1023035"> </a> <p class="BP">You can avoid performance problems with the following:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name=" 1023047"></a>Use flexible parameter passing.</li> <li class="LB1" type="disc"><a name="1023054"></a>Always ensure that you pass multi ple values for arrays. For single values, you can pass dummy values that are ignored by the procedure.</li> <li class="LB1" type="dis c"><a name="1023063"></a>Use the following workaround, which defines a two-parameter style procedure which defaults the unused variab les. <ol class="LN2" type="1"> <li class="LN2" type="1" value="1"><a name="1023074"></a>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: <pre c lass="CE2"><a name="1023083"></a>CREATE OR REPLACE PACKAGE testpkg AS <a name="1023088"></a> TYPE myArrayType is TABLE of VARCHAR2(32 767) INDEX BY binary_ integer; <a name="1023093"></a> PROCEDURE arrayproc (arr myArrayType); <a name="1023098"></a>END testpkg; <a na me="1023103"></a>/ <a name="1023110"></a> </pre> </li> <li class="LN2" type="1" value="2"><a name="1023120"></a>If you are making URL calls like <code>/pls/.../testpkg.arrayproc? arr= 1</code>, change the specification to be similar to the following: <pre class="CE 2"><a name="1023130"></a>CREATE OR REPLACE PACKAGE testpkg AS <a name="1023135"></a> TYPE myArrayType is TABLE of VARCHAR2( 32767) IN DEX BY binary_integer; <a name="1023140"></a> PROCEDURE arrayproc (arr varchar2); <a name="1023145"></a> PROCEDURE arrayproc (arr m yArrayType); <a name="1023150"></a>END testpkg; <a name="1023155"></a>/ <a name="1023162"></a> </pre> </li> <li class="LN2" type="1" value="3"><a name="1023174"></a>The procedure <code>arrayproc</code> should be similar to: <pre class="CE2"><a name="1023182"></a>CR EATE OR REPLACE PACKAGE BODY testpkg AS <a name="1023187"></a>PROCEDURE arrayproc (arr varchar2) IS <a name="1023192"></a> localArr myArrayType; <a name="1023197"></a>BEGIN <a name="1023202"></a> localArr( 1) := arr; <a name="1023207"></a> arrayproc (localArr); < a name="1023212"></a>END arrayproc; <a name="1023217"></a> <a name="1023229"></a> </pre> </li></ol></li></ul> <a name="1023237"></a>< !--TOC=h2-"1023237"--> <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.4.6 The Flexible Parameter Passing (four-parameter) Overhead</font></h3>  <a name="1023241"></a> <p class="BP">Round-trip overhead exists if a PL/SQL pro cedure is using the older style four-parameter interface. The PL/SQL Gateway first tries to execute the procedure by using the two-pa rameter interface. If this fails, the PL/SQL Gateway tries the four-parameter interface. This implies that all four-parameter interfa ce procedures experience one extra round-trip for execution.</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1023253"></a>A voiding the flexible parameter passing overhead <p><a name="1023257"></a></p> <p class="BP1">To avoid this overhead, it is recommend ed that you write corresponding wrappers that use the two-parameter interface and internally call the four-parameter interface proced ures. 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 th e future.</p></li> <li class="LB1" type="disc"><a name="1023267"></a>Using flexible parameters and the exclamation mark <p><a name=" 1023274"></a></p> <p class="BP1">The flexible parameter passing mode in Oracle HTTP Server expects the PL/SQL procedure to have the e xclamation 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 descr ibed completely before being executed. The procedure <code>Describe</code> call determines the signature of the procedure and require s around-trip to the database. The PL/SQL Gateway in Oracle HTTP Server avoids this round trip by having end-users explicitly indicat e the flexible parameter passing convention by adding the exclamation mark before the procedure.</p></li></ul> <a name="1023304"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">4.5 Using Caching with PL/SQL Based Web Applications</font></h2>  <a name="1023308"></a> <p class="BP">Caching can improve the performance of PL/SQL base d Web applications. To improve performance, you can cache Web content generated by PL/SQL procedures in the middle-tier and decrease the database workload.</p> <a name="1023314"></a> <p class="BP">This section covers the techniques used in caching, including the fo llowing:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1023328"></a><a href="optplsql.htm#1023409">Using the Validation T echnique</a> - An application asks the server if the page has been modified since it was last presented.</li> <li class="LB1" type="d isc"><a name="1023341"></a><a href="optplsql.htm#1024011">Using the Expires Technique</a> - Based upon a specific time period, the PL /SQL based Web application determines the page will be cached, or should be generated again.</li> <li class="LB1" type="disc"><a name ="1023354"></a><a href="optplsql.htm#1024388">System- and User-level Caching with PL/SQL Based Web Applications</a> - This is valid w hether you are using the Validation Technique or the Expires Technique. The level of caching is determined by whether a page is cache d for a particular user or for every user in the system.</li></ul> <a name="1023365"></a> <p class="BP">These techniques and levels are implemented using <code>owa_cache</code> packages located inside the PL/SQL Web Toolkit.</p> <a name="1023400"></a> <div align=" center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="1023381"></a><fon t face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1023391"></a> <p class="NB"><em class="I talic"><a class="olinkSRC ADFNS" href="../../appdev$101/b10795/toc.htm">Oracle Database Application Developer's Guide - Fundamentals< /a></em></p></td></tr></table></div> <a name="1023409"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans -serif" color="#330099">4.5.1 Using the Validation Technique</font></h3>  <a name="1023415"></a> <p class="BP">In gene ral, 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 t he user and then cached.</p> <a name="1023420"></a> <p class="BP">There are two methods which use the Validation Technique: Last-Mod ified 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 principles are used.</p> <a name="1023429"></a>  <a name= "1023436"></a> <p class="BP">When a Web page is generated using the HTTP protocol, it contains a <strong class="Bold">Last-Modified< /strong> Response Header. This header indicates the date, relative to the server, of the content that was requested. Browsers save th is date information along with the content. When subsequent requests are made for the URL of the Web page, the browser then:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a name="1023450"></a>Determines if it has a cached version.</li> <li class= "LN1" type="1" value="2"><a name="1023459"></a>Extracts the date information.</li> <li class="LN1" type="1" value="3"><a name="102347 2"></a>Generates the Request Header <strong class="Bold">If-Modified-Since</strong>.</li> <li class="LN1" type="1" value="4"><a name= "1023482"></a>Sends the request the server.</li></ol> <a name="1023490"></a> <p class="BP">Cache-enabled servers look for the <stron g class="Bold">If-Modified-Since</strong> header and compare it to the date of their content. If the two match, an HTTP Response stat us 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.</p> <a name="1023495"></a> <p class="BP">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 <strong class="Bold">Last-Mo dified Response h</strong>eader. Upon receipt of this status code, the browser must replace its cache entry with the new content and new date information.</p> <a name="1023509"></a>  <h4 class="H3"><font face="Arial, Helvetica, sans-serif" col or="#330099">4.5.1.2 Entity Tag Method</font></h4>  <a name="1023519"></a> <p class="BP">Another validation method pro vided by the HTTP protocol is the <strong class="Bold">ETag</strong> (Entity Tag) Response and Request header. The value of this head er 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 <strong class="Bold">If-Modified-Since</strong> header, which can only contain a date value.</p> <a name= "1023532"></a> <p class="BP">The <strong class="Bold">ETag</strong> method works very similar to the Last Modified method. Servers g enerate the ETag as part of the Response Header. The browser stores this opaque header value along with the content that is steamed b ack. When the next request for this content arrives, the browser passes the <strong class="Bold">If-Match</strong> header with the op aque 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 <strong class="Bold">Last-Modified</strong> validation method as described earlier.</p> <a name="1023550"></a>  <h4 class="H3"><font face="Arial, Helvetica, sans-serif" color="#330099">4.5.1.3 Using t he Validation Technique for mod_plsql</font></h4>  <a name="1023554"></a> <p class="BP">Using HTTP validation caching as a framework, the following is the Validation Model for mod_plsql.</p> <a name="1023565"></a> <p class="BP">PL/SQL based Web appli cations that want to control the content being served should use this type of caching. This technique offers some moderate performanc e gains. One example of this would be a Web application that serves dynamic content that could change at any given time. In this case , the Web application needs full control over what is being served. Validation caching always asks the Web application whether the ca ched content is stale or not before serving it back to the browser.</p> <a name="1023579"></a> <p class="BP"><a href="optplsql.htm#1 023806">Figure 4-1</a> shows the use of the validation technique for mod_plsql.</p> <ol class="LN1" type="1"> <li class="LN1" ty pe="1" value="1"><a name="1023592"></a>The Oracle HTTP Server receives a PL/SQL procedure request from a client server. The Oracle HT TP Server routes the request to mod_plsql.</li> <li class="LN1" type="1" value="2"><a name="1023614"></a>mod_plsql prepares the reque st.</li> <li class="LN1" type="1" value="3"><a name="1023625"></a>mod_plsql invokes the PL/SQL procedure in the Web application. mod_ plsql passes the usual Common Gateway Interface (CGI) environment variables to the Web application.</li> <li class="LN1" type="1" val ue="4"><a name="1023638"></a>The PL/SQL procedure generates content to pass back. If the PL/SQL procedure decides that the generated content is cacheable, it calls the <code>owa_cache</code> procedure from the PL/SQL Web Toolkit to set the tag and cache level: <pre class="CE1"><a name="1023652"></a>owa_cache.set_cache(p_etag, p_level); </pre> <h5 class="TT"><a name="1023758"></a><a name="102366 2"></a><strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-3 Validation Model Parameters</em></font></strong></h5> <table s ummary="" class="Formal" border="1" frame="HSIDES" rules="GROUPS" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <t head> <tr class="Formal"> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1023672"></a> <font face="Arial, Helve tica, sans-serif"><strong>Parameter</strong></font></th> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1023679 "></a> <font face="Arial, Helvetica, sans-serif"><strong>Description</strong></font></th></tr></thead> <tbody> <tr class="Formal" ali gn="left" valign="top"> <td class="Formal"><a name="1023691"></a> <p class="TB"><code>set_cache</code> procedure</p></td> <td class= "Formal"><a name="1023701"></a> <p class="TB">Sets up the headers to notify mod_plsql that the content being streamed back can be ca ched. Then, the mod_plsql caches the content on the local file system along with the tag and caching level information as it is strea med back to the browser.</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1023718"></a> <p cl ass="TB"><em><code>p_etag</code></em></p></td> <td class="Formal"><a name="1023725"></a> <p class="TB">The string that the procedure generates to tag the content.</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1023737"></a> <p class="TB"><em><code>p_level</code></em></p></td> <td class="Formal"><a name="1023752"></a> <p class="TB">The caching level: <co de>SYSTEM</code> for system level or <code>USER</code> for user level.</p></td></tr></tbody></table></li> <li class="LN1" type="1" va lue="5"><a name="1023771"></a>The HTML is returned to mod_plsql.</li> <li class="LN1" type="1" value="6"><a name="1023784"></a>mod_pl sql stores the cacheable content in its file system for the next request.</li> <li class="LN1" type="1" value="7"><a name="1023797">< /a>The Oracle HTTP Server sends the response to the client browser.</li></ol> <a name="1023806"></a> <h4 class="FT"><font face="Aria l, Helvetica, sans-serif"><em>Figure 4-1 Validation Technique</em></font></h4> <a name="1023817"><img src="images/asper016.gif" alt=" Text description of asper016.gif follows." border="0" hspace="0" vspace="0"></a> <p><a href="img_text/asper016.htm">Text description of the illustration asper016.gif</a></p> <a name="1023825"></a>  <h4 class="H3"><font face="Arial, Helvetica, sans-serif" color="#330099">4.5.1.4 Second Request Using the Validation Technique</font></h4>  <a name="1023833"></a> <p class="BP">Using the Validation Technique for mod_plsql, a second request is made by the client browser for the same PL/SQL proce dure.</p> <a name="1023843"></a> <p class="BP"><a href="optplsql.htm#1023975">Figure 4-2</a> shows the second request using the Validation Technique.</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a name="1023859"></a>mod_plsql detects that it has a cached content for the request.</li> <li class="LN1" type="1" value="2"><a name="1023870"></a>mod_plsql forwards the same ta g and caching level information (from the first request) to the PL/SQL procedure as part of the CGI environment variables.</li> <li c lass="LN1" type="1" value="3"><a name="1023879"></a>The PL/SQL procedure uses these caching CGI environment variables to check if the content has changed. It does so by calling the following <code>owa_cache</code> functions from the PL/SQL Web Toolkit: <pre class=" CE1"><a name="1023892"></a>owa_cache.get_etag; <a name="1023897"></a>owa_cache.get_level; <a name="1023903"></a> </pre> <a name="102 3909"></a> <p class="BP1">These owa functions get the tag and caching level.</p></li> <li class="LN1" type="1" value="4"><a name="102 3922"></a>The Web application sends the caching information to mod_plsql.</li> <li class="LN1" type="1" value="5"><a name="1023929">< /a>Based on that information determines whether the content needs to be regenerated or can be served from the cache. <ol class="LA2" type="a"> <li class="LA2" type="a" value="1"><a name="1023940"></a>If the content is still the same, the procedure calls the <code>o wa_cache.set_not_modified</code> procedure and generates no content. This causes mod_plsql to use its cached content. The cached cont ent is directly streamed back to the browser.</li> <li class="LA2" type="a"><a name="1023957"></a>If the content has changed, it gene rates 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.</li></ol></li></ol> <a name="1023975 "></a> <h4 class="FT"><font face="Arial, Helvetica, sans-serif"><em>Figure 4-2 Validation Technique-Second Request</em></font></h4> <a name="1023985"><img src="images/asper017.gif" alt="Text description of asper017.gif follows." border="0" hspace="0" vspace="0"></a > <p><a href="img_text/asper017.htm">Text description of the illustration asper017.gif</a></p> <a name="1024011"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.5.2 Using the Expires Technique</font></h3>  <a name="1024019"></a> <p class="BP">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 pre-establishes 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 dat abase is required.</p> <a name="1024032"></a> <p class="BP">This caching technique offers the best performance. Use if your PL/SQL b ased Web 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.</p> <a n ame="1024041"></a> <p class="BP">Assume the same scenario described for the Validation model, except the procedure uses the Expires model for caching.</p> <a name="1024055"></a> <p class="BP"><a href="optplsql.htm#1028674">Figure 4-3</a> shows the use of the expires technique for mod_plsql.</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a name="1024068"></a>The Oracle HT TP Server receives a PL/SQL Server Page request from a client server. The Oracle HTTP Server routes the request to mod_plsql.</li> <l i class="LN1" type="1" value="2"><a name="1024090"></a>The request is forwarded by mod_plsql to the Oracle Database.</li> <li class=" LN1" type="1" value="3"><a name="1024101"></a>mod_plsql invokes the PL/SQL procedure in the application and passes the usual Common G ateway Interface (CGI) environment variables to the application.</li> <li class="LN1" type="1" value="4"><a name="1024110"></a>The PL /SQL procedure generates content to pass back. If the PL/SQL procedure decides that the generated content is cacheable, it calls the <code>owa_cache</code> procedure from the PL/SQL Web Toolkit to set the validity period and cache level: <pre class="CE1"><a name="1 024124"></a>owa_cache.set_expires(p_expires, p_level); </pre> <h5 class="TT"><a name="1024222"></a><a name="1024134"></a><strong><fo nt face="Arial, Helvetica, sans-serif"><em>Table 4-4 Expires Model Parameters</em></font></strong></h5> <table summary="" class="Form al" border="1" frame="HSIDES" rules="GROUPS" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Form al"> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1024144"></a> <font face="Arial, Helvetica, sans-serif"><st rong>Parameter</strong></font></th> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1024151"></a> <font face="Ar ial, Helvetica, sans-serif"><strong>Description</strong></font></th></tr></thead> <tbody> <tr class="Formal" align="left" valign="top "> <td class="Formal"><a name="1024163"></a> <p class="TB"><code>set_expires</code> procedure</p></td> <td class="Formal"><a name="1 024173"></a> <p class="TB">Sets up the headers to notify mod_plsql that Expires caching is being used. mod_plsql then caches the con tent to the file system along with the validity period and caching level information.</p></td></tr> <tr class="Formal" align="left" v align="top"> <td class="Formal"><a name="1024190"></a> <p class="TB"><em><code>p_expires</code></em></p></td> <td class="Formal"><a name="1024197"></a> <p class="TB">Number of minutes that the content is valid.</p></td></tr> <tr class="Formal" align="left" valign= "top"> <td class="Formal"><a name="1024209"></a> <p class="TB"><em><code>p_level</code></em></p></td> <td class="Formal"><a name="10 24216"></a> <p class="TB">Caching level.</p></td></tr></tbody></table></li> <li class="LN1" type="1" value="5"><a name="1024235"></a >The HTML is returned to mod_plsql.</li> <li class="LN1" type="1" value="6"><a name="1024248"></a>mod_plsql stores the cacheable cont ent in its file system for the next request.</li> <li class="LN1" type="1" value="7"><a name="1024261"></a>The Oracle HTTP Server sen ds the response to the client browser.</li></ol> <a name="1028674"></a> <h4 class="FT"><font face="Arial, Helvetica, sans-serif"><em >Figure 4-3 The Expires Technique</em></font></h4> <a name="1028675"><img src="images/asper018.gif" alt="Text description of asper018 .gif follows." border="0" hspace="0" vspace="0"></a> <p><a href="img_text/asper018.htm">Text description of the illustration asper01 8.gif</a></p> <a name="1028676"></a> <h4 class="SH1"><font face="Arial, Helvetica, sans-serif">Second Request Using the Expires Tech nique</font></h4> <a name="1024292"></a> <p class="BP">Using the same expires model explained earlier, a second request is made by t he client browser for the same PL/SQL procedure.</p> <a name="1024302"></a> <p class="BP"><a href="optplsql.htm#1024367">Figure ;4-4</a> shows the second request using the Expires Technique.</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a na me="1024316"></a>mod_plsql detects that it has a cached copy of the content that is expires-based.</li> <li class="LN1" type="1" valu e="2"><a name="1024329"></a>mod_plsql checks the content's validity by taking the difference between the current time and the time th is cache file was created. <ol class="LA2" type="a"> <li class="LA2" type="a" value="1"><a name="1024340"></a>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 d irectly streamed back to the browser.</li> <li class="LA2" type="a"><a name="1024349"></a>If the difference is not within the validit y period, the cached copy is stale. mod_plsql invokes the PL/SQL procedure and generates new content. The procedure then decides whet her to use expires-based caching again. If so, it also determines the validating period for this new content. The newly generated con tent is streamed back to the browser.</li></ol></li></ol> <a name="1024367"></a> <h4 class="FT"><font face="Arial, Helvetica, sans-s erif"><em>Figure 4-4 The Expires Technique-Second Request</em></font></h4> <a name="1024379"><img src="images/asper019.gif" alt="Text description of asper019.gif follows." border="0" hspace="0" vspace="0"></a> <p><a href="img_text/asper019.htm">Text description of the illustration asper019.gif</a></p> <a name="1024388"></a>  <h3 class="H2"><font face="Arial, Helvetica, san s-serif" color="#330099">4.5.3 System- and User-level Caching with PL/SQL Based Web Applications</font></h3>  <a name=" 1024394"></a> <p class="BP">A PL/SQL procedure determines whether generated content is system-level content or user-level. This help s 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:</p > <ul class="LB1"> <li class="LB1" type="disc"><a name="1024413"></a>For <strong class="Bold">system-level</strong> content, the proc edure passes the string <code>SYSTEM</code> as the caching level parameter to the <code>owa_cache</code> functions (<code>set_cache</ code> for validation model or <code>set_expires</code> for expires model). This is for every user that shares the cache. <p><a name= "1024432"></a></p> <p class="BP1">By using system-level caching, you can save both space in your file system and time for all users i n the system. One example of this would be a Web application that generates content that is intended for everybody using the Web appl ication. 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.</p></li> <li class="LB1" type="disc"><a na me="1024451"></a>For <strong class="Bold">user-level</strong> content, it passes the string <code>USER</code> as the parameter for th e 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 following table for the different types of users. <h5 class="TT"><a name="1024531"></a><a name="1024464"></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-5 Type of User Determined by Authentication Mode</em></font></strong></h5> <table summary="" class="Formal" border="1" frame="HSIDES" rules="GROUPS " width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Formal"> <th class="Formal" align="left" valign ="bottom" scope="col"><a name="1024474"></a> <font face="Arial, Helvetica, sans-serif"><strong>Authentication Mode</strong></font></t h> <th class="Formal" align="left" valign="bottom" scope="col"><a name="1024481"></a> <font face="Arial, Helvetica, sans-serif"><stro ng>Type of User</strong></font></th></tr></thead> <tbody> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1 024489"></a> <p class="TB">Single Sign On (SSO)</p></td> <td class="Formal"><a name="1024496"></a> <p class="TB">Lightweight user</ p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1024504"></a> <p class="TB">Basic</p></td> <t d class="Formal"><a name="1024511"></a> <p class="TB">Database user</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1024519"></a> <p class="TB">Custom</p></td> <td class="Formal"><a name="1024526"></a> <p class="TB">Remote user</p></td></tr></tbody></table></li></ul> <a name="1024535"></a> <p class="BP">For example, if no user customizes a PL/SQL based 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 syste m. User information is not used since the cache can be used by multiple users.</p> <a name="1024540"></a> <p class="BP">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.</p> <a nam e="1024570"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summa ry="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAl so"><a name="1024556"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1024564"></ a> <p class="NB"><a href="secure.htm#1006255">Section 3.1, "Authenticating Users Through mod_plsql"</a> for more information on authentication modes.</p></td></tr></table></div> <a name="1024584"></a> <h4 class="SH1"><font face="Arial, Helvetica, sans-serif"> PL/SQL Web Toolkit Functions (owa_cache package)</font></h4> <a name="1024588"></a> <p class="BP">Your decision whether to use the V alidation technique or the Expires technique determines which <code>owa_cache</code> functions to call.</p> <a name="1024601"></a> < p class="BP">The <code>owa_cache</code> 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.</p> < a name="1024611"></a> <p class="BP"><a href="optplsql.htm#1024621">Table 4-6</a> lists the primary functions to call.</p> <h5 c lass="TT"><a name="1024822"></a><a name="1024621"></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-6 Primary owa_ca che Functions</em></font></strong></h5> <table summary="" class="Formal" border="1" frame="HSIDES" rules="GROUPS" width="100%" cellpa dding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Formal"> <th class="Formal" align="left" valign="bottom" scope="col" ><a name="1024631"></a> <font face="Arial, Helvetica, sans-serif"><strong>owa Functions</strong></font></th> <th class="Formal" align ="left" valign="bottom" scope="col"><a name="1024638"></a> <font face="Arial, Helvetica, sans-serif"><strong>Purpose</strong></font>< /th></tr></thead> <tbody> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1024649"></a> <p class="TB"><cod e>owa_cache.set_cache</code> <code>(p_etag IN varchar2,</code> <code>p_level IN varchar2)</code></p></td> <td class="Formal"><a name= "1024665"></a> <p class="TB">Validation Model - Sets up the headers.</p> <ul class="TLB"> <li class="TLB" type="disc"><a name="10246 81"></a><code>p_etag</code> parameter tags the generated content.</li> <li class="TLB" type="disc"><a name="1024695"></a><code>p_leve l</code> parameter is the caching level to use.</li></ul></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"> <a name="1024707"></a> <p class="TB"><code>owa_cache.set_not_modified</code></p></td> <td class="Formal"><a name="1024713"></a> <p class="TB">Validation Model - Sets up the headers to notify mod_plsql to use the cached content. Only used when a validation -based c ache hit occurs.</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1024730"></a> <p class="TB" ><code>owa_cache.get_level</code></p></td> <td class="Formal"><a name="1024740"></a> <p class="TB">Validation Model - Gets the cachi ng level, <code>USER</code> or <code>SYSTEM</code>. Returns null if the cache is not hit.</p></td></tr> <tr class="Formal" align="lef t" valign="top"> <td class="Formal"><a name="1024757"></a> <p class="TB"><code>owa_cache.get_etag</code></p></td> <td class="Formal" ><a name="1024763"></a> <p class="TB">Validation Model - Gets the tag associated with the cached content. Returns null if the cache is not hit.</p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1024775"></a> <p class="TB"><cod e>owa_cache.set_expires(</code> <code>p_expires IN number,</code> p_level IN varchar2)</p></td> <td class="Formal"><a name="1024787"> </a> <p class="TB">Expires Model - Sets up the headers.</p> <ul class="TLB"> <li class="TLB" type="disc"><a name="1024801"></a><code >p_expires</code> parameter is the number of minutes the content is valid.</li> <li class="TLB" type="disc"><a name="1024817"></a><co de>p_level</code> parameter is the caching level to use.</li></ul></td></tr></tbody></table> <a name="1024840"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">4.6 Tuning File System Cache to Improve Caching Perfo rmance</font></h2>  <a name="1024844"></a> <p class="BP">You can configure and use a File System Cache to improve the performance of PL/SQL based Web applications.</p> <a name="1024853"></a> <p class="BP">This section covers the following topics:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1024869"></a><a href="optplsql.htm#1027364">Introducing File System Cache Tuni ng</a></li> <li class="LB1" type="disc"><a name="1024882"></a><a href="optplsql.htm#1025369">Enabling File System Cache</a></li> <li class="LB1" type="disc"><a name="1024895"></a><a href="optplsql.htm#1025436">Configuring File System Cache to Reside on a Faster File System</a></li> <li class="LB1" type="disc"><a name="1024921"></a><a href="optplsql.htm#1026273">Resizing File System Cache</a></li> <li class="LB1" type="disc"><a name="1027362"></a><a href="optplsql.htm#1026623">Configuring Cache Cleanup</a></li></ul> <a name="10 27364"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.6.1 Introducing File S ystem Cache Tuning</font></h3>  <a name="1024956"></a> <p class="BP">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 store d 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 fas t storage).</p> <a name="1024969"></a> <p class="BP">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.</p> <a name="1024992"></a> <p class="BP"><a href="optplsql. htm#1025045">Table 4-7</a> lists the cache related parameters that you can set for mod_plsql. Set these parameters in the <code> cache.conf</code> file that is available on UNIX in the directory, <em><code>ORACLE_HOME</code></em><code>/Apache/modplsql/conf</code >, and on Windows, this is found in the directory, <em><code>ORACLE_HOME</code></em><code>\Apache\modplsql\conf</code>.</p> <a name=" 1025038"></a> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="Thi s is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a na me="1025020"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a name="1025032"></a> <p class= "NB">The file <code>cache.README</code> in the <code>conf</code> directory includes a full description of each parameter, and provide s examples showing how to set parameter values.</p> <hr></td></tr></table></div> <a name="1025350"></a> <h5 class="TTW"><a name="102 5350"></a><a name="1025045"></a> <strong><font face="Arial, Helvetica, sans-serif"><em>Table 4-7 mod_plsql cache.conf Configuration P arameter Summary</em></font></strong></h5> <table summary="" class="FormalWide" border="1" frame="HSIDES" rules="GROUPS" width="100%" cellpadding="3" cellspacing="0" dir="ltr" title=""> <thead> <tr class="Formal"> <th class="Formal" align="left" valign="bottom" scop e="col"><a name="1025055"></a> <font face="Arial, Helvetica, sans-serif"><strong>Parameter</strong></font></th> <th class="Formal" al ign="left" valign="bottom" scope="col"><a name="1025062"></a> <font face="Arial, Helvetica, sans-serif"><strong>Description</strong>< /font></th></tr></thead> <tbody> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1025074"></a> <p class="T B"><code>PlsqlCacheCleanupTime</code></p></td> <td class="Formal"><a name="1025081"></a> <p class="TB">Sets the interval for running cache cleanup routines. <a name="1025092"></a></p> <p class="TB">Default: <code>Everyday 23:00</code> (run cleanup routine daily at 11PM local time) <a name="1025106"></a></p> <p class="TB">See Also: <a href="optplsql.htm#1026623">Section 4.6.5, "Configuring C ache Cleanup"</a></p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="1025118"></a> <p class="TB "><code>PlsqlCacheDirectory</code></p></td> <td class="Formal"><a name="1032931"></a> <p class="TB">Defines the directory that holds the mod_plsql cache. <a name="1032935"></a></p> <p class="TB">Default: <a name="1032936"></a></p> <p class="TB">On UNIX systems, the default directory for the error log is: <em><code>ORACLE_HOME</code></em><code>/Apache/modplsql/cache</code> <a name="1025155"></a>< /p> <p class="TB">On Windows systems, the default directory is: <em><code>ORACLE_HOME</code></em><code>\Apache\modplsql\cache</code> <a name="1025171"></a></p> <p class="TB">See Also: <a href="optplsql.htm#1025436">Section 4.6.3, "Configuring File System Cache to Reside on a Faster File System"</a></p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="Formal"><a name="102518 5"></a> <p class="TB"><code>PlsqlCacheEnable</code></p></td> <td class="Formal"><a name="1025192"></a> <p class="TB">Enables the fi le system cache. <a name="1025204"></a></p> <p class="TB">Default: <code>On</code> <a name="1025217"></a></p> <p class="TB">See Also: <a href="optplsql.htm#1025369">Section 4.6.2, "Enabling File System Cache"</a></p></td></tr> <tr class="Formal" align="left" va lign="top"> <td class="Formal"><a name="1025229"></a> <p class="TB"><code>PlsqlCacheMaxAge</code></p></td> <td class="Formal"><a nam e="1025236"></a> <p class="TB">Controls the aging, in days for the cache contents. <a name="1025248"></a></p> <p class="TB">Default: <code>30</code> (days) <a name="1025259"></a></p> <p class="TB">See Also: <a href="optplsql.htm#1026554">Section 4.6.4.2, "Sett ing the Days of Aging for Cache with PlsqlCacheMaxAge"</a></p></td></tr> <tr class="Formal" align="left" valign="top"> <td class="For mal"><a name="1025273"></a> <p class="TB"><code>PlsqlCacheMaxSize</code></p></td> <td class="Formal"><a name="1025280"></a> <p clas s="TB">Sets the maximum size, in bytes, for an individual file stored in the cache. <a name="1025288"></a></p> <p class="TB">Default: <code>1048576</code> (1 Megabyte) <a name="1025295"></a></p> <p class="TB">See Also: <a href="optplsql.htm#1026596">Section 4.6 .4.3, "Setting the Maximum File Size for a Cache File with PlsqlCacheMaxSize"</a></p></td></tr> <tr class="Formal" align="left" valig n="top"> <td class="Formal"><a name="1025313"></a> <p class="TB"><code>PlsqlCacheTotalSize</code></p></td> <td class="Formal"><a nam e="1025320"></a> <p class="TB">Limits the total size of the cache. The value is specified in bytes. <a name="1025331"></a></p> <p cl ass="TB">Default: <code>20971520</code> (20 Megabytes) <a name="1025345"></a></p> <p class="TB">See Also: <a href="optplsql.htm#10262 73">Section 4.6.4, "Resizing File System Cache"</a></p></td></tr></tbody></table> <a name="1025369"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.6.2 Enabling File System Cache</font></h3>  <a name="1025385"></a> <p class="BP">The <code>cache.conf</code> parameter <code>PlsqlCacheEnable</code> enables mod_plsql caching. For maximum performance, enable <code>PlsqlCacheEnable</code> by setting the value of this parameter to <code>On</code>.</p> <a name ="1025428"></a><a name="1025436"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099 ">4.6.3 Configuring File System Cache to Reside on a Faster File System</font></h3>  <a name="1025440"></a> <p class=" BP">This section describes how to configure a File System Cache to reside on a separate disk. When you use File System Cache and stor e the cache on a faster separate disk, performance should improve for all types of Web applications using File System Cache, includin g OracleAS Portal and generic PL/SQL based Web applications.</p> <a name="1025448"></a> <p class="BP">When you configure File System Cache, the cache can reside either on a separate physical disk or in a memory disk.</p> <a name="1025454"></a> <p class="BP">To set up a File System Cache on a separate disk:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1"><a name="1025465"></a>As sume that the file system for the cache resides at the location: <p><a name="1025474"></a></p> <p class="BP1">On UNIX: <code>/u01/ca che</code></p> <a name="1025483"></a> <p class="BP1">On Windows: <code>E:\cache</code></p></li> <li class="LN1" type="1" value="2">< a name="1025488"></a>Update the file: <p><a name="1025497"></a></p> <p class="BP1">On UNIX: <em><code>ORACLE_HOME</code></em><code>/ Apache/modplsql/conf/cache.conf</code></p> <a name="1025506"></a> <p class="BP1">On Windows: <em><code>ORACLE_HOME</code></em><code> \Apache\modplsql\conf\cache.conf</code></p></li> <li class="LN1" type="1" value="3"><a name="1025515"></a>Change the cache parameter <code>PlsqlCacheDirectory</code>: <p><a name="1025524"></a></p> <p class="BP1">On UNIX: <code>PlsqlCacheDirectory /u01/cache</code>< /p> <a name="1025533"></a> <p class="BP1">On Windows: <code>PlsqlCacheDirectory E:\cache</code></p></li></ol> <a name="1026273"></a>  <h3 class="H2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.6.4 Resizing File System Cache</f ont></h3>  <a name="1026278"></a> <p class="BP">This section covers the following topics:</p> <ul class="LB1"> <li cla ss="LB1" type="disc"><a name="1026294"></a><a href="optplsql.htm#1026329">Setting the Total Cache Size with PlsqlCacheTotalSize</a></ li> <li class="LB1" type="disc"><a name="1026307"></a><a href="optplsql.htm#1026554">Setting the Days of Aging for Cache with PlsqlCa cheMaxAge</a></li> <li class="LB1" type="disc"><a name="1026321"></a><a href="optplsql.htm#1026596">Setting the Maximum File Size for a Cache File with PlsqlCacheMaxSize</a></li></ul> <a name="1026329"></a>  <h4 class="H3"><font face="Arial, H elvetica, sans-serif" color="#330099">4.6.4.1 Setting the Total Cache Size with PlsqlCacheTotalSize</font></h4>  <a nam e="1032955"></a> <p class="BP">The default installation sets the mod_plsql file system cache size to 2097152 bytes (20 Megabytes). I f your PL/SQL application does not make use of the OWA_CACHE packages, or uses them to cache small amounts of content, then the defau lt setting should be sufficient. If your PL/SQL application caches a lot of content in the mod_plsql file system cache, you should co nsider specifying a higher value.</p> <a name="1026351"></a> <p class="BP">To control the cache size, set the <code>PlsqlCacheTotalS ize</code> parameter in the file <code>cache.conf</code>. On UNIX systems, this file is located under <em><code>ORACLE_HOME</code></e m><code>/Apache/modplsql/conf</code> directory. On Windows systems, this file is located under <em><code>ORACLE_HOME</code></em><code >\Apache\modplsql\conf</code>.</p> <a name="1026368"></a> <p class="BP">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 li mit 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 ineffici ent to search.</p> <a name="1026377"></a> <p class="BP">The value for <code>PlsqlCacheTotalSize</code> is specified as a number of b ytes. 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.</p> <a name="1026383"></a> <p class="BP">To determine a reasonable cache size, do the following:</p> <ol class="LN1" type="1"> <li class="LN1" type="1" value="1 "><a name="1026401"></a>Turn on mod_plsql performance logging by setting the <code>LogLevel</code> in <code>httpd.conf</code> to the <code>info</code> level to enable mod_plsql logging.</li> <li class="LN1" type="1" value="2"><a name="1027313"></a>Monitor the <code> error_log</code> on a daily basis. On UNIX systems, the default directory for the error log is: <em><code>ORACLE_HOME</code></em><cod e>/Apache/Apache/log</code>. On Windows systems, the default directory is: <em><code>ORACLE_HOME</code></em><code>\Apache\Apache\log< /code>.</li></ol> <a name="1026450"></a> <p class="BP">The mod_plsql <code>error_log</code> entries have the form:</p> <pre class="C E"><a name="1026457"></a>[info] mod_plsql: cachecleanup deleted=2571 max_age=96,2178852b kept=1042,25585368b time=128s limit=256000 00b <a name="1026464"></a> </pre> <a name="1026469"></a> <p class="BP">where:</p> <a name="1026478"></a> <p class="BP"><code>delete d</code> is the number of cache files that got deleted during the cleanup process.</p> <a name="1026486"></a> <p class="BP"><code>ma x_age</code> is the number of cache files and total size that got deleted because they haven't been used for some time.</p> <a name=" 1026495"></a> <p class="BP"><code>kept</code> is the number of cache files and total size that was kept after the cleanup process.</ p> <a name="1026505"></a> <p class="BP"><code>time</code> is the amount of time to perform the cleanup.</p> <a name="1026517"></a> <p class="BP"><code>limit</code> is the total cache size. This is the value of the <code>PlsqlCacheTotalSize</code> setting.</p> <a n ame="1026523"></a> <p class="BP">Interpret the entries in the error log as follows:</p> <ul class="LB1"> <li class="LB1" type="disc" ><a name="1026533"></a>If a high number of files are being deleted when compared to the number of files that were kept, this is a cle ar indication that your cache size is too small. You probably need to increase the size of the cache.</li> <li class="LB1" type="disc "><a name="1026542"></a>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.</li></ul> <a name="1026554"></a>  <h4 class="H3"><f ont face="Arial, Helvetica, sans-serif" color="#330099">4.6.4.2 Setting the Days of Aging for Cache with PlsqlCacheMaxAge</font></h4>  <a name="1026562"></a> <p class="BP">Using the <code>PlsqlCacheMaxAge</code> parameter, you can control the "stalene ss" 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.</p> <a name="1026582"></a> <p class="BP">The <code>max_age</code> information in mod_plsql <code>error_log</co de> shows cache file aging information. If your site is a highly dynamic site, it would make sense to configure this setting to a low er 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 <code>PlsqlCacheMaxAge</code> so that the cleanup process does not deliberately delete the cache content.</p> <a name="1026596"></a>  <h4 class="H3"><f ont face="Arial, Helvetica, sans-serif" color="#330099">4.6.4.3 Setting the Maximum File Size for a Cache File with PlsqlCacheMaxSize </font></h4>  <a name="1026604"></a> <p class="BP">Using the <code>PlsqlCacheMaxSize</code> 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 enti re cache.</p> <a name="1026612"></a> <p class="BP">The default value for this parameter is 1048576 (bytes). In general, set this par ameter to a value that represents about 1-3% of the total cache size.</p> <a name="1026623"></a>  <h3 class="H 2"><font face="Arial, Helvetica, sans-serif" color="#330099">4.6.5 Configuring Cache Cleanup</font></h3>  <a name="1026 627"></a> <p class="BP">The cache cleanup parameter determines the frequency in which the File System Cache is examined and, if nece ssary, cleaned up. The cache cleanup parameter, <code>PlsqlCacheCleanupTime</code> is specified in the <code>cache.conf</code> 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.</p> <a name="1026643"></a> <p class="BP">The default mod_plsql setting of <code>PlsqlCacheCleanupTime</code > is daily at 11PM local time. Therefore, by default, every night at 11PM, the cleanup routine runs. When you select the monthly freq uency, the cleanup occurs on the first Saturday of each month.</p> <a name="1026652"></a> <p class="BP">Configuring this parameter c orrectly 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.</p> <a name="1026665"></a> <p class="BP">Monitor the cleanup activities using the entries in the mod_plsql <code>error_log</code>; then tune the cleanup parameter, <code>PlsqlCacheCleanupTime</code> by analyzing the entrie s.</p> <pre class="CE"><a name="1026677"></a>[info] mod_plsql: cachecleanup deleted=2571 max_age=96,2178852b kept=1042,25585368b ti me=128s limit=25600000b <a name="1026684"></a> </pre> <a name="1026689"></a> <p class="BP">Note the following:</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="1026699"></a>Seeing a large number for the cleanup time can be an indication that the cleanup fr equency 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.</li> <li class="LB1" type="disc"><a name="1026708"></a >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 freque ntly.</li></ul> <a name="1026750"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#33009 9">4.7 Oracle HTTP Server Directives</font></h2>  <a name="1026757"></a> <p class="BP">To improve PL/SQL performance i n Oracle HTTP Server, you need to tune the Oracle HTTP Server directives appropriately for your configuration.</p> <a name="1026825"> </a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name= "1026776"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="1026781"></a> <p class ="NB"><a class="olinkSRC HSADM" href="../../server$101/b12255/toc.htm"><em class="Italic">Oracle HTTP Server Administrator's Guide</e m></a></p></td></tr></table></div></div> <div class="footer"> <hr> <table summary="" cellspacing="0" cellpadding="0" width="100%"> <t r> <td style="width: 33%;" align="left"> <table summary="layout table" cellspacing="0" cellpadding="0" align="left" width="90"> <tr> <td align="center" valign="top"><a href="secure.htm"><img src="../../dcommon/gifs/larrow.gif" alt="Go to previous page" border="0"><b r> <font size="-2">Previous</font></a></td> <td align="center" valign="top"><a href="app_a.htm"><img src="../../dcommon/gifs/rarrow.g if" alt="Go to next page" border="0"><br> <font size="-2">Next</font></a></td></tr></table></td> <td style="width: 34%;" align="cente r"><img src="../../dcommon/gifs/oracle.gif" alt="Oracle"><br> <a href="../../dcommon/html/cpyr.htm"><font size="-2">Copyright © 1996, 2003 Oracle Corporation</font></a><br> <font size="-2">All Rights Reserved.</font></td> <td style="width: 33%;" align="right"> <table summary="" cellspacing="0" cellpadding="0" width="270"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img sr c="../../dcommon/gifs/prodicon.gif" alt="Go to Documentation Home" border="0"><br> <font size="-2">Home</font></a></td> <td align="ce nter" valign="top"><a href="../../nav/portal_3.htm"><img src="../../dcommon/gifs/bookicon.gif" alt="Go to Book List" border="0"><br> <font size="-2">Book List</font></a></td> <td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/gifs/conticon.gif " alt="Go to Table of Contents" border="0"><br> <font size="-2">Contents</font></a></td> <td align="center" valign="top"><a href="ind ex.htm"><img src="../../dcommon/gifs/indxicon.gif" alt="Go to Index" border="0"><br> <font size="-2">Index</font></a></td> <td align= "center" valign="top"><a href="../../mix$101/b12039/toc.htm"><img src="../../dcommon/gifs/mix.gif" alt="Go to Master Index" border="0 "><br> <font size="-2">Master Index</font></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><i mg src="../../dcommon/gifs/feedback.gif" alt="Go to Feedback page" border="0"><br> <font size="-2">Feedback</font></a></td></tr></tab le></td></tr></table></div> </body> </html>