Performance Monitoring with modPLSQL in Oracle9iAS Portal

Portal Performance Monitoring in Oracle Application Server Portal

An OracleAS Portal Technical Note
Version: Sept 3rd, 2003, for Oracle Portal middle tier versions 3.0.9.8.3 and higher, and 9.0.2.3.0 and higher.

Overview

The ability to judge the performance of a web site is an important feature of any Internet server. With the 3.0.9.8.3 patch release, Oracle introduced a performance logging service for Portal. One line of output is emitted per web request, in a summary format.

The statistics collected indicate how long the overall request took, how much of that time was spent in the user's procedure, what user made the request, whether a database connection was obtained from the connection pool, what type of caching was used, etc.

Contents

Back to Top

Requirements

  • One of the following releases of the Oracle Portal middle tier:

     
    • 3.0.9 (must be at patch level 3.0.9.8.3 or higher; 3.0.9.8.2 and previous versions are NOT supported)

       
    • 9.0.2.3 or higher (9.0.2 versions prior to 9.0.2.3 are NOT supported)

      You must apply the following patches in the following order to obtain full functionality:


       

    • An Oracle9i (or greater) database in which to store the performance data and run the reports (9i is the minimum version required because the reports use PERCENTILE_CONT, which was not available in prior releases). A separate database is strongly recommended, as running these reports can significantly degrade the performance of your Portal installation. Database version 9.2.0.3 is recommended if you wish to use direct-path loading of the performance data (again, strongly recommended), and allow maximum possible fast refresh of the materialized views. There are known issues with direct-path loading with DB versions prior to 9.2.0.3; if you experience SQL*Loader issues with the scripts, then either turn off the direct-path load option, or upgrade the database.

       
    • A UNIX, Linux, or Win32 system with a SQL*Loader of the same version as the database used to store the performance data, and Perl, on which to run the loader script. This may or may not be the same system used for the database. If you are getting errors when you load the data, check the version of SQL*Loader in $ORACLE_HOME.

       
    • rcp, scp, or ftp automatic login access to the physical hosts on which the log files are located. See your OS documentation for more information.

       

    Back to Top

    Preparation

    Turning on performance logging

    The settings different somewhat from version to version, so make sure you are looking at the correct section.

    After the following settings are changed, you must restart Oracle Application Server 10g in order for performance logging to start.

    3.0.9.8.X

    mod_plsql
    • In $ORACLE_HOME/Apache/Apache/conf/httpd.conf, set LogLevel to info:
      LogLevel info
      
    • In $ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app, set debugModules to info:
      debugModules=info
      

      Note: info is the recommended setting for performance logging. The infoDebug setting will log all the same information as the info setting, with the addition of extra debugging information which is not necessary for performance reporting (the text of the browser cookie, its length, and the mod_plsql trace cookie). This information is quite lengthy, so this setting should not be used unless this data is necessary for debugging a specific problem.

       

    Jserv

    Jserv can be configured in one of two ways. Either all properties are contained in a single jserv.properties file and a single zone.properties file, or these files can be broken up into per-zone files. The locations of these files are defined in $ORACLE_HOME/Apache/Jserv/etc/jserv.conf (note that the location of this file can also change, and is defined in your top-level httpd.conf file).

     

    • In each Jserv properties file, the following settings are required:
      log=true
      log.timestamp=true
      log.dateFormat=[dd/MM/yyyy HH:mm:ss:SSS zz]
      log.channel.servletLog=true
      

      Warning: The log.dateFormat setting must be exactly as specified, or the loader will not be able to parse the log files.

       

    • In the appropriate zone properties file for the PPE servlet, usually located under $ORACLE_HOME/Apache/Jserv/etc, set servlet.page.initArgs to logmode=perf:
      servlet.page.initArgs=logmode=perf
      
    • In the appropriate zone properties file for each JPDK servlet, usually located under $ORACLE_HOME/Apache/Jserv/etc, add debuglevel=0 to the initArgs property for the servlet:
      servlet.<your servlet name here>.initArgs=<your servlet arg 1>, <your servlet arg 2>..., debuglevel=0
      

    9.0.2.X/9.0.3.X

    mod_plsql

    Note: if you are running Portal 9.0.2.3.0, and you have installed a separate infrastructure mid-tier, then mod_plsql performance logging should be enabled on the infrastructure mid-tier as well as the Portal mid-tier.

     

    • In $ORACLE_HOME/Apache/Apache/conf/httpd.conf, set LogLevel to info:
      LogLevel info
      
    • Note that Oracle Diagnostic Logging (ODL) logging format is not currently supported. If you have a line like
      OraLogMode oracle
      

      in your httpd.conf file, it must be changed back to

      OraLogMode apache
      

      for these scripts to work.

    OC4J
    • In the web.xml file for Portal, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/applications/portal/portal/WEB-INF, uncomment the lines containing the initialization parameter logmode. Set logmode for the page servlet to perf:
        <servlet>
          <servlet-name>page</servlet-name>
          <servlet-class>oracle.webdb.page.ParallelServlet</servlet-class>
      
          ...
      
          <init-param>
            <param-name>logmode</param-name>
            <param-value>perf</param-value>
          </init-param>
      
          ...
      
        </servlet>
      
      
    • For each JPDK provider which uses JNDI configuration parameters:

       
      • In the web.xml file for each JPDK provider, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/applications/<dir1>/<dir2>/WEB-INF (where the <dir1> and <dir2> directory names are specific to your provider), a definition should appear for the parameter oracle/portal/log/logLevel. If the definition exists, proceed to the next bullet. If the provider web.xml file does not include this parameter, you will have to add it (note that if the provider is redeployed for some reason, this file will be overwritten, and you will have to add the parameter again. The provider author should be notified that they should include this parameter definition in future releases). Example:
        <web-app>
        
          <display-name>Your Display Name</display-name>
          <description>Your Description</description>
        
          <env-entry>
            <env-entry-name>oracle/portal/log/logLevel</env-entry-name>
            <env-entry-value>4</env-entry-value>
            <env-entry-type>java.lang.Integer</env-entry-type>
          </env-entry>
        
        </web-app>
        

        4 is a good value to use, as this will be overridden anyway by the orion-web.xml setting in the next step.

         

      • In the orion-web.xml file for each JPDK provider, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/<dir1>/<dir2> (where the <dir1> and <dir2> directory names are specific to your provider), set the parameter oracle/portal/log/logLevel to 5. Example:
        <orion-web-app deployment-version="9.0.2.6.0"
                       jsp-cache-directory="./persistence"
                       temporary-directory="./temp"
                       servlet-webdir="/servlet/">
        
            ...
        
            <env-entry-mapping name="oracle/portal/log/logLevel">5</env-entry-mapping>
        
        </orion-web-app>
        
    • For each JPDK provider which uses context initialization parameters:

       
      • In the web.xml file for each JPDK provider, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/applications/<dir1>/<dir2>/WEB-INF (where the <dir1> and <dir2> directory names are specific to your provider), a definition should appear for the parameter oracle.portal.log.LogLevel. If the definition exists, proceed to the next bullet. If the provider web.xml file does not include this parameter, you will have to add it (note that if the provider is redeployed for some reason, this file will be overwritten, and you will have to add the parameter again. The provider author should be notified that they should include this parameter definition in future releases). Example:
        <web-app>
        
          <display-name>Your Display Name</display-name>
          <description>Your Description</description>
        
          <context-param>
            <param-name>oracle.portal.log.LogLevel</param-name>
            <param-value>4</param-value>
          </context-param>
        
        </web-app>
        

        4 is a good value to use, as this will be overridden anyway by the orion-web.xml setting in the next step.

         

      • In the orion-web.xml file for each JPDK provider, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/<dir1>/<dir2> (where the <dir1> and <dir2> directory names are specific to your provider), set the parameter oracle.portal.log.LogLevel to 5. Example:
        <orion-web-app deployment-version="9.0.2.6.0"
                       jsp-cache-directory="./persistence"
                       temporary-directory="./temp"
                       servlet-webdir="/servlet/">
        
            ...
        
            <context-param-mapping name="oracle.portal.log.LogLevel">5</context-param-mapping>
        
        </orion-web-app>
        
    • Note that Oracle Diagnostic Logging (ODL) logging format is not currently supported. If you have lines like
      <log>
          ...
          <odl ...>
          ...
      </log>
      

      in any of your OC4J configuration files, they must be changed back to

      <log>
          ...
          <file ...>
          ...
      </log>
      

      for these scripts to work.

    Web Cache
    1. Start the Web Cache Manager.

       
    2. In the navigator pane, select "Cache-Specific Configuration" > "Access Log".

      The Access Log page appears in the right pane.

       
    3. In the Access Log page, select the Cache Name to configure, and choose "Edit".

      The Change Options for Access Logs dialog box appears.

       
    4. Select "Yes" for Logging Enabled.

       
    5. In the "User-Specified Fields" field, cut and paste the following:
      c-ip c-dns c-auth-id date time cs-method cs-uri sc-status bytes cs(Cookie) cs(Referer) cs(User-Agent) time-taken r-time-taken x-ecid
      

      You must use only these fields, and exactly in this order.

       

    6. Choose "Submit".

       
    7. In the Web Cache Manager main window, choose "Apply Changes".

       
    8. Restart Web Cache.

       

    10g (9.0.4.X)

    mod_plsql

    Same as for 9.0.2.

    OC4J
    • In the web.xml file for Portal, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/applications/portal/portal/WEB-INF, uncomment the lines containing the initialization parameter logmode. Set logmode for the page servlet to perf:
        <servlet>
          <servlet-name>page</servlet-name>
          <servlet-class>oracle.webdb.page.ParallelServlet</servlet-class>
      
          ...
      
          <init-param>
            <param-name>logmode</param-name>
            <param-value>perf</param-value>
          </init-param>
      
          ...
      
        </servlet>
      
      
    • For each JPDK provider which uses JNDI configuration parameters:

       
      • In the web.xml file for each JPDK provider, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/applications/<dir1>/<dir2>/WEB-INF (where the <dir1> and <dir2> directory names are specific to your provider), a definition should appear for the parameter oracle/portal/provider/global/log/logLevel. If the provider web.xml file does not include this parameter, you will have to add it (note that if the provider is redeployed for some reason, this file will be overwritten, and you will have to add the parameter again. The provider author should be notified that they should include this parameter definition in future releases). Example:
        <web-app>
        
          <display-name>Your Display Name</display-name>
          <description>Your Description</description>
        
          <env-entry>
            <env-entry-name>oracle/portal/provider/global/log/logLevel</env-entry-name>
            <env-entry-value>4</env-entry-value>
            <env-entry-type>java.lang.Integer</env-entry-type>
          </env-entry>
        
        </web-app>
        

        4 is a good value to use, as this will be overridden anyway by the orion-web.xml setting in the next step.

         

      • In the orion-web.xml file for each JPDK provider, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/<dir1>/<dir2> (where the <dir1> and <dir2> directory names are specific to your provider), set the parameter oracle/portal/provider/global/log/logLevel to 5. Example:
        <orion-web-app deployment-version="9.0.4.0.0"
                       jsp-cache-directory="./persistence"
                       temporary-directory="./temp"
                       servlet-webdir="/servlet/">
        
            ...
        
            <env-entry-mapping name="oracle/portal/provider/global/log/logLevel">5</env-entry-mapping>
        
        </orion-web-app>
        
    • For each JPDK provider which uses context initialization parameters:

       
      • In the web.xml file for each JPDK provider, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/applications/<dir1>/<dir2>/WEB-INF (where the <dir1> and <dir2> directory names are specific to your provider), a definition should appear for the parameter oracle.portal.log.LogLevel. If the provider web.xml file does not include this parameter, you will have to add it (note that if the provider is redeployed for some reason, this file will be overwritten, and you will have to add the parameter again. The provider author should be notified that they should include this parameter definition in future releases). Example:
        <web-app>
        
          <display-name>Your Display Name</display-name>
          <description>Your Description</description>
        
          <context-param>
            <param-name>oracle.portal.log.LogLevel</param-name>
            <param-value>4</param-value>
          </context-param>
        
        </web-app>
        

        4 is a good value to use, as this will be overridden anyway by the orion-web.xml setting in the next step.

         

      • In the orion-web.xml file for each JPDK provider, usually located under $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/<dir1>/<dir2> (where the <dir1> and <dir2> directory names are specific to your provider), set the parameter oracle.portal.log.LogLevel to 5. Example:
        <orion-web-app deployment-version="9.0.4.0.0"
                       jsp-cache-directory="./persistence"
                       temporary-directory="./temp"
                       servlet-webdir="/servlet/">
        
            ...
        
            <context-param-mapping name="oracle.portal.log.LogLevel">5</context-param-mapping>
        
        </orion-web-app>
        
    • Note that Oracle Diagnostic Logging (ODL) logging format is not currently supported. If you have lines like
      <log>
          ...
          <odl ...>
          ...
      </log>
      

      in any of your OC4J configuration files, they must be changed back to

      <log>
          ...
          <file ...>
          ...
      </log>
      

      for these scripts to work.

    Web Cache
    1. Start Web Cache Manager.

       
    2. In the navigator pane, select "Logging and Diagnostics" > "Access Logs".

      The Access Log page appears in the right pane.

       
    3. In the Access Log page, under "Site-Specific Access Log Configuration", verify that the access_log is using the WCALF format style.

       
    4. If not, select it and hit the "Edit Selected" button.

       
    5. Select "Yes" for "Enabled".

       
    6. Select the WCALF format style.

       
    7. Choose "Submit".

       
    8. In the Web Cache Manager main window, choose "Apply Changes".

       
    9. Restart Web Cache.

       

    Setting up the reporting database

    The collected performance data must be loaded into an Oracle database for report generation. To prepare for this, execute the following steps:

     

    1. Create a database schema of your choice. Example, using SQL*Plus:
      CONNECT SYS/<passwd> AS SYSDBA;
      CREATE USER owa_perf IDENTIFIED BY owa_perf;
      GRANT RESOURCE, CREATE SESSION, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO owa_perf;
      
    2. During the installation process, you will be asked for three tablespace destinations for:

       
      • the log tables
      • the log indices
      • the log materialized views

      It is recommended, though not mandatory, that you create and specify individual tablespaces for the storage of these elements. You cannot use DEFAULT as a valid tablespace for these responses.

       

    3. Load the script install/install.sql (for a non-partitioned table) or install/install_part.sql (for a partitioned table) into it. This will create the table OWA_LOGGER in your schema. You must specify an argument of either parallel or noparallel, to indicate whether or not parallel access should be allowed to the tables, indexes, etc.

        sqlplus owa_perf/owa_perf@mydb @install/install.sql parallel
        

    Using Partitioning

    If you are using an Oracle Enterprise Edition database, and you have a large amount of data (more than a million rows), you may wish to partition the OWA_LOGGER table and its associated views and indexes. The following scripts are supplied to assist with this:

    • install/install_part.sql parallel

      Use this script to install the performance logging tables if you have not previously installed them.

       

    • install/convert_part.sql parallel

      Use this script to convert your non-partitioned performance logging tables to partitioned ones.

       

    You must have run the install/update.sql script before running install/install_part.sql.

    You must specify an argument of either parallel or noparallel, to indicate whether or not parallel access should be allowed to the tables, indexes, etc. Parallel access is recommended unless your system is I/O-bound.

    Note that you must execute loader/loadlogs.pl with the -pre_load option prior to loading all logs in a session, and with the -post_load option afterwards. This takes care of adding new partitions as necessary, and swapping partitions in and out of the main OWA_LOGGER table to increase its availability. You can refer to the sample csh script loader/sample_load_script.csh to see an example of how this would be done.

    Back to Top

    Updates

    From time to time, changes may be made to this software which require changes to the schema. If you have downloaded a new version, you may need to load install/update.sql to accomplish this. Example:

    • sqlplus owa_perf/owa_perf@mydb @install/update.sql
      

    You may see errors indicating that the changes have already been made, and these may be disregarded. Please also check the section on schema privileges BEFORE executing install/update.sql, as new privileges may need to be granted first.

    Back to Top

    Data Loading

    Once you have turned on performance logging and collected some data, it needs to be loaded into the database so that reports can be generated from it.

    Locating the data

    The data will be written into the Oracle HTTP Server error log (for mod_plsql), either the Jserv log or the OC4J application log (for PPE and JPDK), and the Web Cache access log for Web Cache. These are normally located in $ORACLE_HOME/Apache/Apache/error_log, $ORACLE_HOME/Apache/Jserv/jserv.log, $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1/application.log, and $ORACLE_HOME/webcache/logs/access_log, respectively, on the physical hosts where the Oracle HTTP Server is installed. However, your system may have been configured differently.

    Loading data directly from these files is not recommended. If you should wish to load more data later, all the data that you loaded the first time will still be in the log files, and will be duplicated in the database, unless you delete all your old data first. It is better to set up a rotation system, and load the archived log files instead.

    Loading data into the database

    A Perl script (loader/loadlogs.pl) is provided that will use either rcp, scp, or ftp (see your OS documentation for more information) to collect the log files from the physical hosts, and load them (using SQL*Loader) into the database schema specified by the connection. Running the script with no arguments will print out usage information:

    Usage: perl loadlogs.pl
    
      -logical_host     host          The logical host name, e.g. www.oracle.com.
                                      Required.
    
      -physical_hosts   hosts         The physical host names of the machines
                                      where the logs are located.  To specify a
                                      list of hosts, double-quote and separate by
                                      spaces, e.g. "host1 host2".  Optional.
                                      Default is local host.
    
      -remote_user      userid        The userid on the physical hosts.  Optional.
                                      Defaults to the current userid on the local
                                      host.
    
      -http_logfile     filename      Filename of the Oracle HTTP Server error log
                                      file to be loaded.  Optional.
    
      -jserv_logfile    filename      Filename of the Jserv log file to be loaded.
                                      Optional.
    
      -oc4j_logfile     filename      Filename of the OC4J log file to be loaded.
                                      Optional.
    
      -webcache_logfile filename      Filename of the Web Cache log file to be
                                      loaded.  Optional.
    
      -connection       cxn           Connection information for the database where
                                      the data is to be loaded.
    
      -[no]copy_logs                  Copy new log files from physical hosts.
                                      Specifying -nocopy_logs can be useful with
                                      -delete_old_logs or -purge_old_logs.
                                      Optional.  Defaults to -copy_logs.
    
      -[no]load_logs                  Load new log information from files into
                                      database.  Specifying -noload_logs can be
                                      useful with -delete_old_logs or
                                      -purge_old_logs.  Optional.  Defaults to
                                      -load_logs.
    
      -temp_dir         directory     Directory in which to store log files while
                                      loading.  Optional.  Defaults to value
                                      returned by File::Spec->tmpdir().
    
      -[no]delete_old_logs            Delete log information for specified hosts
                                      before loading new data.  If logical_host is
                                      not specified, then delete logs for all
                                      hosts.  Optional.
    
      -purge_old_logs   N             Purge log information for specified hosts
                                      older than N days.  If logical_host is not
                                      specified, then purge logs for all hosts.
                                      Optional.
    
      -transport_type   <rcp/scp/ftp> The method for retrieving the log files.
                                      Defaults to rcp on UNIX/Linux, ftp on Win32.
                                      Optional.
    
      -[no]direct                     Use direct-path loading of the log files.
                                      Defaults to -direct.
    
      -[no]parallel                   Enable parallel loading of the log files.
                                      Defaults to -noparallel.
    
      -version          V1.V2.V3      The version of the mid-tier to be loaded
                                      from, for example, 3.0.9.  Defaults to 9.0.4.
    
      -[no]pre_load                   Execute pre-load tasks for a staged load.
                                      Optional.
    
      -[no]post_load                  Execute post-load tasks for a staged load.
                                      Optional.
    
      -[no]pre_load                   Execute pre-load tasks for a staged load.
                                      Optional.
    
      -[no]post_load                  Execute post-load tasks for a staged load.
                                      Optional.
    
      -[no]maintain_indexes           Enable/disable index maintenance by
                                      SQL*Loader.  Optional.  On by default.
                                      Recommended to turn this off if loading
                                      multiple files.
    

    Example 1: Mid-Tier running on Single Host

    You have a Portal middle tier (say, www.yourcompany.com) installed on a single physical host (host1.yourcompany.com). Your performance database is installed on dbhost.yourcompany.com, and this is where you are running loadlogs.pl.

    Example: (\ = line continuation)

    • perl loadlogs.pl \
          -logical_host www.oracle.com \
          -physical_hosts "host1 host2 host3" \
          -connection owa_perf/owa_perf@mydb \
          -http_logfile /u01/app/oracle/product/iAS/Apache/Apache/logs/error_log.20020808:23:59 \
          -webcache_logfile /u01/app/oracle/product/iAS/webcache/logs/access_log.20020808:23:59 \
          -oc4j_logfile /u01/app/oracle/product/iAS/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1/application.log.20020808:23:59
      

      This command will load the following files:

      • host1:/u01/app/oracle/product/iAS/Apache/Apache/logs/error_log.20020808:23:59
      • host2:/u01/app/oracle/product/iAS/Apache/Apache/logs/error_log.20020808:23:59
      • host3:/u01/app/oracle/product/iAS/Apache/Apache/logs/error_log.20020808:23:59
      • host1:/u01/app/oracle/product/iAS/webcache/logs/access_log.20020808:23:59
      • host2:/u01/app/oracle/product/iAS/webcache/logs/access_log.20020808:23:59
      • host3:/u01/app/oracle/product/iAS/webcache/logs/access_log.20020808:23:59
      • host1:/u01/app/oracle/product/iAS/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1/application.log.20020808:23:59
      • host2:/u01/app/oracle/product/iAS/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1/application.log.20020808:23:59
      • host3:/u01/app/oracle/product/iAS/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1/application.log.20020808:23:59

    If the logging data files are located on another system, then loader/loadlogs.pl will copy them to the directory specified by -temp_dir on the local machine. You should make sure there is enough disk space available to hold them before running loader/loadlogs.pl.

    Win32 FTP tips:

    When using the -transport_type ftp option to copy files between Win32 and either Unix or Linux, use Unix syntax to specify the filename. When copying files between two Win32 systems, Windows syntax can be used.

    Compatibility notes:

    If you are upgrading from a previous version of these scripts, note that loader/loadlogs.csh has been replaced by loader/loadlogs.pl. loader/loadlogs.csh will no longer be supported. The following changes have been made to the options:

    • The -root_logpath option is no longer supported. Absolute file paths should be specified to -http_logfile, -oc4j_logfile, -jserv_logfile, and -webcache_logfile.

       
    • The -direct, -delete_old_logs, -copy_logs, and -load_logs options no longer accept the values true and false. Instead, for example, supply -copy_logs instead of -copy_logs true, and -nocopy_logs instead of -copy_logs false.

       

    Troubleshooting

    loader/loadlogs.pl will create log files in the directory specified by the -temp_dir option.

    SQL*Loader-951: Error calling once/load initialization

    Check the version of SQL*Loader on the machine where you are running loadlogs.pl. It should exactly match the database version, and be at least 9.2.0.3.

    Rejected records

    Records in the logfiles which do not match the correct format will be rejected and copied to the .dis file. See the Appendix for the required format.

    There should be no records in the discard (extension .dis) or bad (extension .bad) files which contain the string "[perf]". If there are, check the .log files for error messages.

    Known issues

    • loader/loadlogs.pl: There are known problems with direct-path loading of large files with SQL*Loader, and with direct-path loading files of any size on DB versions prior to 9.2.0.3. If you are having problems, either upgrade your database, or use the -nodirect option to loadlogs.pl. The -nodirect option will severely degrade the performance of the loader scripts.

       
    • PPE: request time will occasionally be logged as a large-magnitude negative number. These records will be rejected by the loader, and copied into a file with the extension .bad in the temporary directory specified to loader/loadlogs.pl.
    • PPE: HTTP user will sometimes erroneously be logged as (null).

    Automation

    If you wish to automate this step, you can install the previous command as a cron job. See the UNIX man page for cron for more information.

    Back to Top

    Report Generation

    SQL scripts (reports.sql, reports_weekly.sql) are supplied which will run all of the currently available reports. Those in reports.sql would typically be required on a daily basis, whereas those in reports_weekly.sql should not change much, and can be run less often. Example:

    • sqlplus owa_perf/owa_perf@mydb @reports
      sqlplus owa_perf/owa_perf@mydb @reports_weekly
      

    Automation

    You can install the previous command as a cron job. See the UNIX man page for cron for more information.

    Back to Top

    Report Publishing

    A sample web page (reports.html) is included which provides links to the generated reports.

    Back to Top

    Maintenance

    Rotating logs

    The log files will quickly grow quite large because of the performance logging data. You may wish to set up a rotation system, so that periodically the logs are archived, preferably to another filesystem. Note that on UNIX, the logs are kept open by the running process, so simple doing a 'mv' will not be effective. Examples:

     

    • csh
      set date = `date +%Y.%m.%d_%H:%M`
      
      setenv LOG_ARCHIVE /otherdisk/logging/archive
      
      cd $ORACLE_HOME/Apache/Apache/logs
      cp error_log $LOG_ARCHIVE/apache/error_log.$date && cat /dev/null > error_log
      
      cd $ORACLE_HOME/Apache/Jserv/logs
      cp jserv.log $LOG_ARCHIVE/jserv/jserv.log.$date && cat /dev/null > jserv.log
      
      cd $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1
      cp application.log $LOG_ARCHIVE/oc4j/application.log.$date && cat /dev/null > application.log
      
      cd $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/jpdk/OC4J_Portal_default_island_1
      cp application.log $LOG_ARCHIVE/jpdk/application.log.$date && cat /dev/null > application.log
      
      cd $ORACLE_HOME/webcache/logs
      cp access_log $LOG_ARCHIVE/webcache/access_log.$date && cat /dev/null > access_log
      
    • sh
      date=`date +%Y.%m.%d_%H:%M`
      LOG_ARCHIVE=/otherdisk/logging/archive
      
      cd $ORACLE_HOME/Apache/Apache/logs
      cp error_log $LOG_ARCHIVE/apache/error_log.$date && cat /dev/null > error_log
      
      cd $ORACLE_HOME/Apache/Jserv/logs
      cp jserv.log $LOG_ARCHIVE/jserv/jserv.log.$date && cat /dev/null > jserv.log
      
      cd $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1
      cp application.log $LOG_ARCHIVE/oc4j/application.log.$date && cat /dev/null > application.log
      
      cd $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/jpdk/OC4J_Portal_default_island_1
      cp application.log $LOG_ARCHIVE/jpdk/application.log.$date && cat /dev/null > application.log
      
      cd $ORACLE_HOME/webcache/logs
      cp access_log $LOG_ARCHIVE/webcache/access_log.$date && cat /dev/null > access_log
      

    It is suggested that you load the archived log files rather than the current ones, to eliminate the need to delete old data first. If you collect and load the current log files, then be sure to specify -delete_old_logs, otherwise, reloading these files will cause duplicate data to be loaded.

    Automation

    You can install the previous script as a cron job. See the UNIX man page for cron for more information.

    Purging/deleting old data

    The amount of data in the OWA_LOGGER table can grow quite large. You should purge or delete old data periodically. If you have only a few physical hosts, this can be combined with loading new data:

     

    • perl loadlogs.pl -logical_host <host> -physical_hosts "<host1 host2 host3...>" -delete_old_logs

    or

    • perl loadlogs.pl -logical_host <host> -physical_hosts "<host1 host2 host3...>" -purge_old_logs <n>

    However, it is much more efficient to purge data for all hosts at once before loading new data:

     

    • perl loadlogs.pl -purge_old_logs <n>
      perl loadlogs.pl -logical_host <host> -physical_hosts "<host1 host2 host3...>"
      

    Automation

    You can install the previous commands as a cron job. See the UNIX man page for cron for more information.

    Appendix

    Log File Format

    Note: the format of these log files is subject to change at any time without notice.

    Oracle HTTP Server Log

    This file is normally located in $ORACLE_HOME/Apache/Apache/logs/error_log, and contains, among other things, the logging output of mod_plsql.

    Sample output line:

    [Mon Apr  1 12:03:15 2002] [info] mod_plsql: [perf] 148.87.20.172 /pls/moc/!MOC.wwpob_page.show status=200 user=PUBLIC reqTime=161ms connSU=HIT,0ms connRO=HIT,0ms connNSSO=(null),0ms procTime=149ms sessionTidyTime=6ms cache=HIT,PING cookie=HIT,1ms pageCalls=0,0ms bytes=18779 describe=No,0ms streamTime=0ms pid=370d sessFile=774/8460 userFile=332/9310 sysFile=092/5279 cacheLevel=user cacheTime=47ms dbProcTime=40ms id=1014235401666Thread-14729 spid=20550 qs=(null) requestTrace=ReqId:1cd::PID:8bd cookieLen=1631 cookieValue=ptl30982perf_sso=3.0,en,us,AMERICA,C9D19D8D1232076A76EA58E6A90E719D3EDFF6BEEFDD2FAF8748BCE991D0A3D2A37D245CDC129B8E974F8ABD8E1E7AFC596AB4B06C6A6F455A2E81771578E0DB309A72F47570051BFB8863C950D69F6AA491BA8351C123AA
    
    

     

    Format and Description of mod_plsql log output
    Position Length Terminated By Enclosed By Datatype Column in OWA_LOGGER Table Description
    6:25 20     DATE event_date, event_day, event_hour date and time of the request. Must be exactly in the following Oracle date format: "Mon DD HH24:MI:SS YYYY".
    NEXT   " mod_plsql: "   CHARACTER unused unused
    NEXT   WHITESPACE   CHARACTER unused if equal to "[perf]", indicates that this line contains performance statistics.
    NEXT   WHITESPACE   CHARACTER ip_address IP address of the client requesting the operation. In most cases this is the IP address of the client browser, or HTTP proxy, being used. The Portal page assembly process will use loopback calls, where the IP address represents the Midtier itself, in order to assemble a page.
    NEXT <=4000 " status="   CHARACTER url URL of the operation being requested.
    NEXT   WHITESPACE   CHARACTER http_status HTTP status code. Typically, status codes less than 400 denote a successful response.
    NEXT     "user=" and " reqTime=" CHARACTER http_user user who requested the action.
    NEXT   "ms "   INTEGER request_time overall time spent processing the request, as recorded inside mod_plsql. Note that this does not include the costs of the Oracle HTTP Server, and of reaching it via HTTP.
    NEXT     "connSU=" and "," CHARACTER su_conn_pool_status status of getting a connection from the database connection pool. The connection status is either "NEW" or "HIT". If the connection is new then the time represents how long was spent connecting to the database. For Portal, this represents the connection to the Portal main schema (e.g., PORTAL30).
    NEXT   "ms "   INTEGER su_conn_pool_time time needed for above.
    NEXT     "connRO=" and "," CHARACTER pub_conn_pool_status see the description of su_conn_pool_status. For Portal, this represents the connection to the Portal public schema (e.g., PORTAL30_PUBLIC).
    NEXT   "ms "   INTEGER pub_conn_pool_time time needed for above.
    NEXT     "connNSSO=" and "," CHARACTER conn_pool_status see su_conn_pool_status. This type of database connection is used only for non-SSO based DADs and is not used by Portal.
    NEXT   "ms "   INTEGER conn_pool_time time needed for above.
    NEXT     "procTime=" and "ms " INTEGER proc_time time spent in executing the target PL/SQL procedure as recorded from mod_plsql.
    NEXT     "sessionTidyTime=" and "ms " INTEGER tidy_time time that mod_plsql spent cleaning up the session, after the requested operation was performed, in order to reset/reinitialize the state of the database connection for the next consumer.
    NEXT     "cache=" and " " CHARACTER content_cache_status status/type of content caching that this operation requested. A value of "(null)" indicates no caching. Other values are "HIT,PING", "HIT,EXPIRES", "MISS,STALE", "MISS,NEW".
    NEXT     "cookie=" and "," CHARACTER cookie_cache_status status of session caching. mod_plsql uses the session cache to avoid hitting the database to decode the cookie value. If the cache is configured correctly, most values here will be "HIT,0ms". This option only applies to SSO-style DADs.
    NEXT   "ms "   INTEGER cookie_cache_time time needed for above.
    NEXT     "pageCalls=" and "," INTEGER page_call_count number of times that mod_plsql was required to call the database to retrieve the content that the operation produced, and time spent doing so. The first chunk of output is bundled along with the initial response, so this value is typically 0 for small-sized content. As the content grows in size, this value will increase as the number of round trips needed to get the content from the database increases.
    NEXT   "ms "   INTEGER page_call_time time needed for above.
    NEXT     "bytes=" and " " INTEGER bytes number of bytes of output that the operation produced.
    NEXT     "describe=" and "," CHARACTER describe_status certain operations require that mod_plsql use a database describe call before they can be run. This property indicates if a describe was performed, and how long was spent performing it.
    NEXT   "ms "   INTEGER describe_time time needed for above.
    NEXT     "streamTime=" and "ms" INTEGER stream_time not currently used. This is a placeholder for future usage.
    NEXT     "pid=" and " sessFile=" CHARACTER pid process id.
    NEXT   " userFile="   CHARACTER session_file session cache file name.
    NEXT   " sysFile="   CHARACTER user_file user cache file name.
    NEXT   " cacheLevel="   CHARACTER sys_file system cache file name.
    NEXT   WHITESPACE   CHARACTER cache_level possible values are "user", "system", "unknown", or NULL.
    NEXT     "cacheTime=" and "ms" INTEGER content_cache_time time spent in the mod_plsql File System Caching subsystem.
    NEXT     "dbProcTime=" and "ms" INTEGER db_proc_time time spent in executing the complete anonymous PL/SQL block, as seen from the database. Note that procTime will be higher than this time, and will include the network time and any database time spent due to latch contentions.
    NEXT     "id=" and "spid=" CHARACTER unused request id, followed by an optional comma and a sequence number. This is parsed into the request_id and seq columns (see below).
            EXPRESSION request_id string used for correlating requests between the PPE, JPDK, mod_plsql, and Web Cache components. It is parsed from the previous field, minus any sequence number.
            EXPRESSION seq sequence number, as parsed from the field before the previous one. The sequence number is incremented each time the request is passed to another component.
    NEXT   WHITESPACE   CHARACTER s_pid shadow process id for the current session. This can be used to correlate to the trace file created on the server side (set only if mod_plsql log level is "infoDebug").
    NEXT     "qs=" and " requestTrace=" CHARACTER qs reserved for future use.
    NEXT   " cookieLen="   CHARACTER request_trace unique identifier logged for all requests coming from the same browser. This information can be used to trace all mod_plsql operations done by a single user (set only if mod_plsql log level is "infoDebug").
    NEXT   " cookieValue="   INTEGER cookie_length total size of the cookie submitted by the user (set only if mod_plsql log level is "infoDebug").
    NEXT   WHITESPACE   CHARACTER cookie "Portal Cookie" used for processing the request (set only if mod_plsql log level is "infoDebug").

     

    Jserv Log

    This file is normally located in $ORACLE_HOME/Apache/Jserv/logs/jserv.log, and contains, among other things, the logging output of the PPE and JPDK.

    PPE

    Sample output line:

    [20/02/2002 12:03:21:881 GMT-08:00] page/[perf] 130.35.79.22 www.oracle.com type=pageMeta name=53,11852 status=200 user=PUBLIC subscriberID=1 reqTime=213ms waitTime=0ms cache=HIT,PING timeout=No redirects=0 bytes=4099 authLevel=10
    

     

    Format and Description of PPE log output
    Position Length Terminated By Enclosed By Datatype Column in OWA_LOGGER Table Description
    2:20 19     DATE event_date, event_day, event_hour date and time of the request. Must be exactly in the following Oracle date format: "DD/MM/YYYY HH24:MI:SS".
    NEXT   " page/"   CHARACTER unused unused
    NEXT   WHITESPACE   CHARACTER unused if equal to "[perf]", indicates that this line contains performance statistics.
    NEXT   WHITESPACE   CHARACTER ip_address IP address of the client requesting the operation. In most cases this is the IP address of the client browser, or HTTP proxy, being used. The Portal page assembly process will use loopback calls, where the IP address represents the Midtier itself, in order to assemble a page.
    NEXT <=4000 " id="   CHARACTER url URL of the operation being requested.
    NEXT   " type="   CHARACTER unused request id, followed by an optional comma and a sequence number. This is parsed into the request_id and seq columns (see below).
            EXPRESSION request_id string used for correlating requests between the PPE, JPDK, mod_plsql, and Web Cache components. It is parsed from the previous field, minus any sequence number.
            EXPRESSION seq sequence number, as parsed from the field before the previous one. The sequence number is incremented each time the request is passed to another component.
    NEXT   " name="   CHARACTER request_type request type, e.g., page, pageMeta, loginMeta, portlet.
    NEXT   " status="   CHARACTER name [provider-name,]portlet-name.
    NEXT   WHITESPACE   CHARACTER http_status HTTP status code. Typically, status codes less than 400 denote a successful response.
    NEXT     "user=" and " subscriberID=" CHARACTER http_user user who requested the action.
    NEXT   " reqTime="   CHARACTER unused subscriber ID.
    NEXT   "ms waitTime="   INTEGER request_time overall time spent processing the request, as recorded inside the PPE. Note that this does not include the costs of the Oracle HTTP Server, and of reaching it via HTTP.
    NEXT   "ms "   INTEGER wait_time time spent waiting in the request queue.
    NEXT     "cache=" and " timeout=" CHARACTER content_cache_status status/type of content caching that this operation requested. A value of "(null)" indicates no caching. Other values are "HIT,PING", "HIT,EXPIRES", "MISS,STALE", "MISS,NEW".
    NEXT   WHITESPACE   CHARACTER timeout whether the request timed out.
    NEXT+10   " bytes="   INTEGER redirects number of redirects made.
    NEXT   WHITESPACE   INTEGER bytes number of bytes of output that the operation produced. This field may not be present in older versions of the PPE.

     

    JPDK

    Sample output line:

    [05/06/2002 11:08:15:656 GMT-08:00] <servletLog> syndication_oracle/[perf-provider] id=(null) providerId=9474633 portletId=106 portletName=ORACLE_NEWS portletInstance=558_ORACLE_NEWS_9474633 user=BISUSER operation=showPortlet requestTime=3ms processingTime=2ms frameworkTime=1ms headerRenderTime=1ms footerRenderTime=0ms readCustTime=(null) writeCustTime=(null) cache=MISS,EXPIRES cacheLevel=user expiryTime=30min cacheRequestKey=(null) cacheResponseKey=(null)
    

     

    Format and Description of JPDK log output
    Position Length Terminated By Enclosed By Datatype Column in OWA_LOGGER Table Description
    2:20 19     DATE event_date, event_day, event_hour date and time of the request. Must be exactly in the following Oracle date format: "DD/MM/YYYY HH24:MI:SS".
    NEXT     "<" and ">" CHARACTER unused optional log channel name, controlled by log.channel property in Jserv properties file; unused
    NEXT <=1000 "/"   CHARACTER provider provider name.
    NEXT   WHITESPACE   CHARACTER unused if equal to "[perf-provider]", indicates that this line contains performance statistics.
    NEXT     "id=" and " providerId=" CHARACTER unused request id, followed by an optional comma and a sequence number. This is parsed into the request_id and seq columns (see below).
            EXPRESSION request_id string used for correlating requests between the PPE, JPDK, mod_plsql, and Web Cache components. It is parsed from the previous field, minus any sequence number.
            EXPRESSION seq sequence number, as parsed from the field before the previous one. The sequence number is incremented each time the request is passed to another component.
    NEXT   " portletId="   CHARACTER provider_id provider id.
    NEXT   " portletName="   CHARACTER portlet_id portlet id.
    NEXT <=1000 " portletInstance="   CHARACTER name portlet name.
    NEXT   " user="   CHARACTER portlet_instance portlet instance name.
    NEXT <=1000 " operation="   CHARACTER http_user user who requested the action.
    NEXT <=1000 " requestTime="   CHARACTER operation operation requested.
    NEXT   WHITESPACE   INTEGER request_time overall time spent processing the request, as recorded inside the JPDK.
    NEXT   "processingTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER proc_time time spent in provider code servicing the request.
    NEXT   "frameworkTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER framework_time time spent in provider framework, calculated by requestTime - processTime.
    NEXT   "headerRenderTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER header_render_time time to render portlet header.
    NEXT   "footerRenderTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER footer_render_time time to render portlet footer.
    NEXT   "readCustTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER read_cust_time time to read customization objects.
    NEXT   "writeCustTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER write_cust_time time to write to customization objects.
    NEXT   "cache="   CHARACTER unused unused
    NEXT   " cacheLevel="   CHARACTER content_cache_status status/type of content caching that this operation requested. A value of "(null)" indicates no caching. Other values are "HIT,PING", "HIT,EXPIRES", "MISS,STALE", "MISS,NEW".
    NEXT   " expiryTime="   CHARACTER cache_level possible values are "user", "system", "unknown", or NULL.
    NEXT   WHITESPACE   INTEGER expiry_time length of time that content is cached for, in minutes.
    NEXT   "cacheRequestKey="   CHARACTER unused unused
    NEXT   "cacheResponseKey="   CHARACTER cache_request_key the cache version header value in request.
    NEXT   WHITESPACE   CHARACTER cache_response_key the cache version header value set by the portlet.

     

    OC4J Log

    PPE

    This file is normally located in $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1, and contains, among other things, the logging output of the PPE.

    Sample output line:

    7/17/02 10:34 PM portal: [perf] 130.35.93.217 http://webdbsvr1.us.oracle.com:5000/pls/ptl_9_0_3_0_97/!PTL_9_0_3_0_97.wwpob_page.show?_pageid=53,35483,53_35487 id=1026970458614ApplicationServerThread-15 type=pageMeta name=53,35483,53_35487 status=200 user=PUBLIC subscriberID=1 reqTime=4617ms waitTime=0ms cache=MISS,NEW timeout=No redirects=0 bytes=0 authLevel=0 webCacheStatus=MISS,NON-CACHEABLE [N] webCacheExpires=(null) webCacheAge=(null) csConv=Yes
    

     

    Format and Description of PPE log output
    Position Length Terminated By Enclosed By Datatype Column in OWA_LOGGER Table Description
    1   "portal:"   DATE event_date, event_day, event_hour date and time of the request. Must be exactly in the following Oracle date format: "MM/DD/YY HH:MI PM".
    NEXT   WHITESPACE   CHARACTER unused if equal to "[perf]", indicates that this line contains performance statistics.
    NEXT   WHITESPACE   CHARACTER ip_address IP address of the client requesting the operation. In most cases this is the IP address of the client browser, or HTTP proxy, being used. The Portal page assembly process will use loopback calls, where the IP address represents the Midtier itself, in order to assemble a page.
    NEXT <=4000 " id="   CHARACTER url URL of the operation being requested.
    NEXT   " type="   CHARACTER unused request id, followed by an optional comma and a sequence number. This is parsed into the request_id and seq columns (see below).
            EXPRESSION request_id string used for correlating requests between the PPE, JPDK, mod_plsql, and Web Cache components. It is parsed from the previous field, minus any sequence number.
            EXPRESSION seq sequence number, as parsed from the field before the previous one. The sequence number is incremented each time the request is passed to another component.
    NEXT   " name="   CHARACTER request_type request type, e.g., page, pageMeta, loginMeta, portlet.
    NEXT   " status="   CHARACTER name [provider-name,]portlet-name.
    NEXT   WHITESPACE   CHARACTER http_status HTTP status code. Typically, status codes less than 400 denote a successful response.
    NEXT     "user=" and " subscriberID=" CHARACTER http_user user who requested the action.
    NEXT   " reqTime="   CHARACTER unused subscriber ID.
    NEXT   "ms waitTime="   INTEGER request_time overall time spent processing the request, as recorded inside the PPE. Note that this does not include the costs of the Oracle HTTP Server, and of reaching it via HTTP.
    NEXT   "ms "   INTEGER wait_time time spent waiting in the request queue.
    NEXT     "cache=" and " timeout=" CHARACTER content_cache_status status/type of content caching that this operation requested. A value of "(null)" indicates no caching. Other values are "HIT,PING", "HIT,EXPIRES", "MISS,STALE", "MISS,NEW".
    NEXT   WHITESPACE   CHARACTER timeout whether the request timed out.
    NEXT+10   " bytes="   INTEGER redirects number of redirects made.
    NEXT   " authLevel="   INTEGER bytes number of bytes of output that the operation produced. This field may not be present in older versions of the PPE.
    NEXT   " webCacheStatus="   INTEGER auth_level authorization level of the request. This field may not be present in older versions of the PPE.
    NEXT   " webCacheExpires="   CHARACTER wc_status Web Cache status. This field may not be present in older versions of the PPE.
    NEXT   " webCacheAge="   INTEGER wc_expires number of seconds after which the cache will expire. This field may not be present in older versions of the PPE.
    NEXT   WHITESPACE   INTEGER wc_age age in seconds of the cache. This field may not be present in older versions of the PPE.
    NEXT   WHITESPACE   CHARACTER cs_conv whether a character set conversion was performed. This field may not be present in older versions of the PPE.

     

    JPDK

    This file is normally located in $ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/jpdk/OC4J_Portal_default_island_1, and contains, among other things, the logging output of the JPDK.

    Sample output line:

    8/28/02 2:54 PM jpdk: [id=1030571630855ApplicationServerThread-11, instance=1980_EXPIRESSAMPLE_14419686] [perf-provider] id=1030571630855ApplicationServerThread-11 providerId=14419686 portletId=5 portletName=ExpiresSample portletInstance=1980_EXPIRESSAMPLE_14419686 user=PTL_9_0_3_1_39 operation=showPortlet requestTime=1484ms processingTime=(null) frameworkTime=(null) headerRenderTime=151ms footerRenderTime=(null) readCustTime=0ms writeCustTime=(null) cache=MISS,EXPIRES cacheLevel=USER expiryTime=1min cacheRequestKey=(null) cacheResponseKey=(null) status=INCOMPLETE
    

     

    Format and Description of JPDK log output
    Position Length Terminated By Enclosed By Datatype Column in OWA_LOGGER Table Description
    1   "jpdk:"   DATE event_date, event_day, event_hour date and time of the request. Must be exactly in the following Oracle date format: "MM/DD/YY HH:MI PM".
    NEXT <=4000 "]"   CHARACTER unused unused.
    NEXT   WHITESPACE   CHARACTER unused if equal to "[perf-provider]", indicates that this line contains performance statistics.
    NEXT     "id=" and " providerId=" CHARACTER unused request id, followed by an optional comma and a sequence number. This is parsed into the request_id and seq columns (see below).
            EXPRESSION request_id string used for correlating requests between the PPE, JPDK, mod_plsql, and Web Cache components. It is parsed from the previous field, minus any sequence number.
            EXPRESSION seq sequence number, as parsed from the field before the previous one. The sequence number is incremented each time the request is passed to another component.
    NEXT   " portletId="   CHARACTER provider_id provider id.
    NEXT   " portletName="   CHARACTER portlet_id portlet id.
    NEXT <=1000 " portletInstance="   CHARACTER name portlet name.
    NEXT   " user="   CHARACTER portlet_instance portlet instance name.
    NEXT <=1000 " operation="   CHARACTER http_user user who requested the action.
    NEXT <=1000 " requestTime="   CHARACTER operation operation requested.
    NEXT   WHITESPACE   INTEGER request_time overall time spent processing the request, as recorded inside the JPDK.
    NEXT   "processingTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER proc_time time spent in provider code servicing the request.
    NEXT   "frameworkTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER framework_time time spent in provider framework, calculated by requestTime - processTime.
    NEXT   "headerRenderTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER header_render_time time to render portlet header.
    NEXT   "footerRenderTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER footer_render_time time to render portlet footer.
    NEXT   "readCustTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER read_cust_time time to read customization objects.
    NEXT   "writeCustTime="   CHARACTER unused unused
    NEXT   WHITESPACE   INTEGER write_cust_time time to write to customization objects.
    NEXT   "cache="   CHARACTER unused unused
    NEXT   " cacheLevel="   CHARACTER content_cache_status status/type of content caching that this operation requested. A value of "(null)" indicates no caching. Other values are "HIT,PING", "HIT,EXPIRES", "MISS,STALE", "MISS,NEW".
    NEXT   " expiryTime="   CHARACTER cache_level possible values are "user", "system", "unknown", or NULL.
    NEXT   WHITESPACE   INTEGER expiry_time length of time that content is cached for, in minutes.
    NEXT   "cacheRequestKey="   CHARACTER unused unused
    NEXT   "cacheResponseKey="   CHARACTER cache_request_key the cache version header value in request.
    NEXT   WHITESPACE   CHARACTER cache_response_key the cache version header value set by the portlet.
    NEXT   "status="   CHARACTER unused unused
    NEXT   WHITESPACE   CHARACTER status status of the JPDK request. Values are "COMPLETE" or "INCOMPLETE".

     

    Web Cache Log

    This file is normally located in $ORACLE_HOME/webcache/logs/access_log, and contains the logging output of Web Cache.

    Sample output line:

    138.1.184.119	-	-	2002-12-18	23:56:37	GET	/pls/wocstage/!wocstage.wwpob_page.show/OTN/	200	90064	-	-	"RPT-HTTPClient/0.3-3"	0.171201	0.168424	"17249916026, 1"
    

     

    Format and Description of Web Cache log output
    Position Length Terminated By Enclosed By Datatype Column in OWA_LOGGER Table Description
    NEXT   WHITESPACE   CHARACTER ip_address IP address of the client requesting the operation.
    NEXT   WHITESPACE   CHARACTER unused client's DNS name.
    NEXT   WHITESPACE   CHARACTER unused user name if the request contained an attempt to authenticate.
    NEXT       DATE event_date, event_day, event_hour date and time of the request. Must be exactly in the following Oracle date format: "YYYY-MM-DD HH24:MI:SS".
    NEXT   WHITESPACE   CHARACTER unused HTTP request method.
    NEXT <=4000 WHITESPACE   CHARACTER url URL of the operation being requested.
    NEXT   WHITESPACE   CHARACTER http_status HTTP status code. Typically, status codes less than 400 denote a successful response.
    NEXT   WHITESPACE   INTEGER bytes number of bytes of output that the operation produced.
    NEXT   WHITESPACE   CHARACTER unused cookie.
    NEXT   WHITESPACE   CHARACTER unused address (URI) of the resource from which the request-URI was obtained.
    NEXT   WHITESPACE   CHARACTER unused specifies the Web browser type, browser version, or operating system that initiated the request.
    NEXT   WHITESPACE   INTEGER request_time overall time spent processing the request, as recorded inside Web Cache. This is in seconds in the log file, but is converted to milliseconds by the loader, for consistency.
    NEXT       INTEGER proc_time time spent in executing the transaction.
    NEXT   WHITESPACE optionally enclosed by " CHARACTER unused request id, followed by an optional comma and a sequence number. This is parsed into the request_id and seq columns (see below).
            EXPRESSION request_id string used for correlating requests between the PPE, JPDK, mod_plsql, and Web Cache components. It is parsed from the previous field, minus any sequence number.
            EXPRESSION seq sequence number, as parsed from the field before the previous one. The sequence number is incremented each time the request is passed to another component.

     

    Description of Reports

    High-Level Summary

    What's broken? Your goal should be to reduce errors to as few as possible. Look first at the Summary reports, to see whether there is a problem. Then look at the reports with increased detail to locate the source of the problem.

       

    • stat_errors.sql

      Generates the following reports:

      • HTTP Error Summary, by Day
      • HTTP Error Summary, by Day, by Physical Host
      • HTTP Error Summary, by Hour
      • HTTP Error Summary, by Hour, by Physical Host

      An HTTP status code of 400 or greater is considered an error. Naturally, there should be as few errors as possible.

    • stat_timeouts.sql

      Generates the following reports:

      • HTTP Timeout Summary, by Day
      • HTTP Timeout Summary, by Hour

      Naturally, there should be as few timeouts as possible.

    • summ_concsess.sql

      Generates the following reports:

      • Concurrent Sessions Summary, by Day
      • Concurrent Sessions Summary, by Hour

      The number of concurrent users can be estimated by the number of concurrent session_file entries. The http_user column is not sufficient, as many sites run as the PUBLIC user. The number of concurrent users for any 1-minute period is defined as the number of unique session_file entries encountered.

    • summ_uniqsess.sql

      Generates the following reports:

      • Unique Sessions Summary, by Day
      • Unique Sessions Summary, by Hour

      The number of unique users can be estimated by the number of unique session_file entries. The http_user column is not sufficient, as many sites run as the PUBLIC user.

    • summ_login.sql

      Generates the following reports:

      • Successful Login Time Summary, by Day
      • Successful Login Time Summary, by Hour

      A successful login is composed of one or more successful calls to wwptl_login.login_url and wwsso_app_admin.ls_login, terminated by a successful call to wwsec_app_priv.process_signon. Success is defined as returning an HTTP status code of less than 400. Since the number of calls to the first two functions can vary, the time reported is a weighted sum based on the frequency of the 3 calls over the time period specified. If your system is properly configured, the average value should be 1.6-1.8 seconds.

    • summ_page.sql

      Generates the following reports:

      • Page Requests Summary, by Day
      • Page Requests Summary, by Hour

    • summ_response.sql

      Generates the following reports:

      • Response Time, by Day
      • Response Time, by Day, by IP Address
      • Response Time, by Day, by Physical Host
      • Response Time, by Hour
      • Response Time, by Hour, by IP Address
      • Response Time, by Hour, by Physical Host

    Details, by Functional Area

    Use these reports to track down performance problems. Those items with the highest average time will be those which are usually expensive, but not necessarily frequently used. Those with the highest sum will be those which are either very expensive, very frequently used, or moderately expensive and moderately used.

    If there is a very large spread between the values for 50th percentile, 90th percentile, and average, then that item has a very inconsistent execution time, and you must work to bring these values closer together.

       

    • top_errors.sql

      Generates the following reports:

      • Top <n> Page HTTP Errors, by Day
      • Top <n> Page HTTP Errors, by Hour
      • Top <n> Portlet HTTP Errors, by Day
      • Top <n> Portlet HTTP Errors, by Hour
      • Top <n> Document HTTP Errors, by Day
      • Top <n> Document HTTP Errors, by Hour
      • Top <n> Path Alias HTTP Errors, by Day
      • Top <n> Path Alias HTTP Errors, by Hour
      • Top <n> Style HTTP Errors, by Day
      • Top <n> Style HTTP Errors, by Hour
      • Top <n> URL HTTP Errors, by Day
      • Top <n> URL HTTP Errors, by Hour

      An HTTP status code of 400 or greater is considered an error.

      In this report, "Request Count" is the number of requests that generated an error, and "% Total Reqs" represents the percentage of errors over all requests for that object for the specified time period.

    • top_timeouts.sql

      Generates the following reports:

      • Top <n> Page HTTP Timeouts, by Day
      • Top <n> Page HTTP Timeouts, by Hour
      • Top <n> Portlet HTTP Timeouts, by Day
      • Top <n> Portlet HTTP Timeouts, by Hour
      • Top <n> Document HTTP Timeouts, by Day
      • Top <n> Document HTTP Timeouts, by Hour
      • Top <n> Path Alias HTTP Timeouts, by Day
      • Top <n> Path Alias HTTP Timeouts, by Hour
      • Top <n> Style HTTP Timeouts, by Day
      • Top <n> Style HTTP Timeouts, by Hour
      • Top <n> URL HTTP Timeouts, by Day
      • Top <n> URL HTTP Timeouts, by Hour

      In this report, "Request Count" is the number of requests that timed out, and "% Total Reqs" represents the percentage of timeouts over all requests for that object for the specified time period.

    • all_pages.sql

      Generates the following reports:

      • Pages Requested, by Day
      • Pages Requested, by Hour

    • top_page.sql

      Generates the following reports:

      • Top <n> Page Requests (by average time, ascending), by Day
      • Top <n> Page Requests (by average time, descending), by Day
      • Top <n> Page Requests (by average time, ascending), by Hour
      • Top <n> Page Requests (by average time, descending), by Hour
      • Top <n> Page Requests (by total time, ascending), by Day
      • Top <n> Page Requests (by total time, descending), by Day
      • Top <n> Page Requests (by total time, ascending), by Hour
      • Top <n> Page Requests (by total time, descending), by Hour
      • Top <n> Page Requests (by count, ascending), by Day
      • Top <n> Page Requests (by count, descending), by Day
      • Top <n> Page Requests (by count, ascending), by Hour
      • Top <n> Page Requests (by count, descending), by Hour

      Pages with a high ping rate (~95%) should have a very low average access time (<200ms).

      Once you have determined that a particular page has a performance problem, you will then want to look at the portlets on that page, to see which of them is causing the problem.

    • stat_page.sql

      Generates the following reports:

      • Page Requests, by Day
      • Page Requests, by Day, by User
      • Page Requests, by Hour
      • Page Requests, by Hour, by User

    • all_portlets.sql

      Generates the following reports:

      • Portlets Requested, by Day
      • Portlets Requested, by Hour

    • top_portlet.sql

      Generates the following reports:

      • Top <n> Portlet Requests (by average time, ascending), by Day
      • Top <n> Portlet Requests (by average time, descending), by Day
      • Top <n> Portlet Requests (by average time, ascending), by Hour
      • Top <n> Portlet Requests (by average time, descending), by Hour
      • Top <n> Portlet Requests (by total time, ascending), by Day
      • Top <n> Portlet Requests (by total time, descending), by Day
      • Top <n> Portlet Requests (by total time, ascending), by Hour
      • Top <n> Portlet Requests (by total time, descending), by Hour
      • Top <n> Portlet Requests (by count, ascending), by Day
      • Top <n> Portlet Requests (by count, descending), by Day
      • Top <n> Portlet Requests (by count, ascending), by Hour
      • Top <n> Portlet Requests (by count, descending), by Hour

      Use these reports as a guide for what to attack first in the process of driving down portlet cost.

    • stat_portlet.sql

      Generates the following reports:

      • Portlet Requests, by Day
      • Portlet Requests, by Day, by User
      • Portlet Requests, by Hour
      • Portlet Requests, by Hour, by User

    • all_jpdk.sql

      Generates the following reports:

      • JPDK Requests, by Day
      • JPDK Requests, by Hour

    • top_jpdkpo.sql

      Generates the following reports:

      • Top <n> JPDK Portlet Requests (by average time, ascending), by Day
      • Top <n> JPDK Portlet Requests (by average time, descending), by Day
      • Top <n> JPDK Portlet Requests (by average time, ascending), by Hour
      • Top <n> JPDK Portlet Requests (by average time, descending), by Hour
      • Top <n> JPDK Portlet Requests (by total time, ascending), by Day
      • Top <n> JPDK Portlet Requests (by total time, descending), by Day
      • Top <n> JPDK Portlet Requests (by total time, ascending), by Hour
      • Top <n> JPDK Portlet Requests (by total time, descending), by Hour
      • Top <n> JPDK Portlet Requests (by count, ascending), by Day
      • Top <n> JPDK Portlet Requests (by count, descending), by Day
      • Top <n> JPDK Portlet Requests (by count, ascending), by Hour
      • Top <n> JPDK Portlet Requests (by count, descending), by Hour

    • top_urlusage.sql

      Generates the following reports:

      • Top <n> Document Requests (by average time, ascending), by Day
      • Top <n> Document Requests (by average time, descending), by Day
      • Top <n> Document Requests (by average time, ascending), by Hour
      • Top <n> Document Requests (by average time, descending), by Hour
      • Top <n> Document Requests (by total time, ascending), by Day
      • Top <n> Document Requests (by total time, descending), by Day
      • Top <n> Document Requests (by total time, ascending), by Hour
      • Top <n> Document Requests (by total time, descending), by Hour
      • Top <n> Document Requests (by count, ascending), by Day
      • Top <n> Document Requests (by count, descending), by Day
      • Top <n> Document Requests (by count, ascending), by Hour
      • Top <n> Document Requests (by count, descending), by Hour
      • Top <n> Path Alias Requests (by average time, ascending), by Day
      • Top <n> Path Alias Requests (by average time, descending), by Day
      • Top <n> Path Alias Requests (by average time, ascending), by Hour
      • Top <n> Path Alias Requests (by average time, descending), by Hour
      • Top <n> Path Alias Requests (by total time, ascending), by Day
      • Top <n> Path Alias Requests (by total time, descending), by Day
      • Top <n> Path Alias Requests (by total time, ascending), by Hour
      • Top <n> Path Alias Requests (by total time, descending), by Hour
      • Top <n> Path Alias Requests (by count, ascending), by Day
      • Top <n> Path Alias Requests (by count, descending), by Day
      • Top <n> Path Alias Requests (by count, ascending), by Hour
      • Top <n> Path Alias Requests (by count, descending), by Hour
      • Top <n> Style Requests (by average time, ascending), by Day
      • Top <n> Style Requests (by average time, descending), by Day
      • Top <n> Style Requests (by average time, ascending), by Hour
      • Top <n> Style Requests (by average time, descending), by Hour
      • Top <n> Style Requests (by total time, ascending), by Day
      • Top <n> Style Requests (by total time, descending), by Day
      • Top <n> Style Requests (by total time, ascending), by Hour
      • Top <n> Style Requests (by total time, descending), by Hour
      • Top <n> Style Requests (by count, ascending), by Day
      • Top <n> Style Requests (by count, descending), by Day
      • Top <n> Style Requests (by count, ascending), by Hour
      • Top <n> Style Requests (by count, descending), by Hour
      • Top <n> URL Requests (by average time, ascending), by Day
      • Top <n> URL Requests (by average time, descending), by Day
      • Top <n> URL Requests (by average time, ascending), by Hour
      • Top <n> URL Requests (by average time, descending), by Hour
      • Top <n> URL Requests (by total time, ascending), by Day
      • Top <n> URL Requests (by total time, descending), by Day
      • Top <n> URL Requests (by total time, ascending), by Hour
      • Top <n> URL Requests (by total time, descending), by Hour
      • Top <n> URL Requests (by count, ascending), by Day
      • Top <n> URL Requests (by count, descending), by Day
      • Top <n> URL Requests (by count, ascending), by Hour
      • Top <n> URL Requests (by count, descending), by Hour

    • top_desc.sql

      Generates the following reports:

      • Top <n> Described Procedures, by Day
      • Top <n> Described Procedures, by Hour

      This report shows procedures which must be described before they can be executed, which can result in significant overhead. Refer to the Oracle Application Server 10g "Using the PL/SQL Gateway" documentation on how to avoid this.

    • top_pmd.sql

      Generates the following reports:

      • Top <n> Page MetaData Requests (by average time, ascending), by Day
      • Top <n> Page MetaData Requests (by average time, descending), by Day
      • Top <n> Page MetaData Requests (by average time, ascending), by Hour
      • Top <n> Page MetaData Requests (by average time, descending), by Hour
      • Top <n> Page MetaData Requests (by total time, ascending), by Day
      • Top <n> Page MetaData Requests (by total time, descending), by Day
      • Top <n> Page MetaData Requests (by total time, ascending), by Hour
      • Top <n> Page MetaData Requests (by total time, descending), by Hour
      • Top <n> Page MetaData Requests (by count, ascending), by Day
      • Top <n> Page MetaData Requests (by count, descending), by Day
      • Top <n> Page MetaData Requests (by count, ascending), by Hour
      • Top <n> Page MetaData Requests (by count, descending), by Hour

    General Statistics

    Diagnostics

       

    • all_ora_errors.sql

      Generates the following reports:

      • Oracle Errors, by Day
      • Oracle Errors, by Hour

      An HTTP status code of 400 or greater is considered an error. Naturally, there should be as few errors as possible.

    Tuning

       

    • cust_connpool.sql

      Generates the following reports:

      • Connection Pool statistics - v3 DAD, by Day
      • Connection Pool statistics - v2 DAD, by Day
      • Connection Pool statistics - v3 DAD, by Hour
      • Connection Pool statistics - v2 DAD, by Hour

      In the reports, "SU" refers to the Portal main schema (e.g., PORTAL30), and "Pub" refers to the Portal public schema (e.g., PORTAL30_PUBLIC). Possible values are:

      • "HIT" - a database connection was already open
      • "STALE" - a database connection was already open, but was left in an error state, and had to be closed
      • "NEW" - a new database connection was created
      • no value - a database connection was not required

      The percentage of requests at "HIT/HIT" should be in the high 90s. If not, it is likely that tuning the number of Oracle HTTP Server processes can help.

    • stat_cache.sql

      Generates the following reports:

      • Content Cache Time, by Day
      • Content Cache Time, by Day, by Physical Host
      • Content Cache Time, by Hour
      • Content Cache Time, by Hour, by Physical Host
    Informational

    These reports are mostly for your information, as they concern areas of Portal which are not customer-tunable.

       

    • stat_logins.sql

      Generates the following reports:

      • Login-oriented Calls, by Day
      • Login-oriented Calls, by Hour

    • stat_cache.sql

      Generates the following reports:

      • Cookie Cache Time, by Day
      • Cookie Cache Time, by Day, by Physical Host
      • Cookie Cache Time, by Hour
      • Cookie Cache Time, by Hour, by Physical Host

      The average time for a cookie cache hit should be around 2ms, and a create should be 200-300ms.

    • stat_pmdoh.sql

      Generates the following reports:

      • Page MetaData Overhead, by Day
      • Page MetaData Overhead, by Day, by Physical Host
      • Page MetaData Overhead, by Hour
      • Page MetaData Overhead, by Hour, by Physical Host

    • summ_ppewait.sql

      Generates the following reports:

      • PPE Wait Time Summary, by Day
      • PPE Wait Time Summary, by Day, by Physical Host
      • PPE Wait Time Summary, by Hour
      • PPE Wait Time Summary, by Hour, by Physical Host

    • summ_pagecalls.sql

      Generates the following reports:

      • Repeat Page Calls Summary, by Day
      • Repeat Page Calls Summary, by Hour

      Note that this report does not include requests which were cache hits.

E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy