24.3 Performance Analysis Tools

The first step towards tuning your report is determining where your report spends most of its execution time. Does it spend a large portion of the time retrieving the data, formatting the retrieved data, or waiting for runtime resources/distribution? Even if your report has the most streamlined and tuned layout possible, it may be of little consequence if most of the time is spent in retrieving data, due to inefficient SQL.

This section discusses the tools you can use to monitor the performance of your report:

24.3.1 Oracle Enterprise Manager

Using Oracle Enterprise Manager to manage and monitor your Reports Server is discussed in Chapter 7, "Administering Oracle Reports Services Using Oracle Enterprise Manager".

24.3.2 Log Files

All Oracle Reports log files follow Oracle Diagnostic Logging (ODL) format, the standard across Oracle Fusion Middleware, for log format, message types, and log management directives. The log file entries are in Text format (default) or XML format. For detailed information, refer to Oracle Fusion Middleware Administrator's Guide

Default Location of Log Files

See Table 24-3 for default location of log files.

Table 24-3 Default Location of Log Files

Component Location, file Name

Reports standalone server

Server log file:

ORACLE_INSTANCE/diagnostics/logs/ReportsServerComponent/<server name>/rwserver_diagnostic.log

Engine log files:

ORACLE_INSTANCE/diagnostics/logs/ReportsServerComponent/<server name>/rwEng-<num>_diagnostic.log

Communication log files:

ORACLE_INSTANCE/diagnostics/logs/ReportsServerComponent/<server name>/zrclient_diagnostic.log

Reports In-process server and Servlet log files

In-process server:

$DOMAIN_HOME/servers/WLS_REPORTS/logs/reports/rwserver_diagnostic.log

In-process server engines:

$DOMAIN_HOME/servers/WLS_REPORTS/logs/reports/rwEng-<num>_diagnostic.log

Servlet:

$DOMAIN_HOME/servers/WLS_REPORTS/logs/reports/rwservlet_diagnostic.log

Reports Tools log files

(Reports Builder, Reports Runtime, Reports Client)

Runtime:

ORACLE_INSTANCE/diagnostics/logs/ReportsToolsComponent/ReportsTools/runtime_diagnostic.log

Communication log files:

ORACLE_INSTANCE/diagnostics/logs/ReportsToolsComponent/ReportsTools/zrclient_diagnostic.log

Reports Bridge log files

ORACLE_INSTANCE/diagnostics/logs/ReportsBridgeComponent/<bridge name>/diagnostic.log

Tip:

If you are running multiple instances of Reports Tools Components like rwrun, rwbuilder, rwclient, you must add the property name keepOpen with the value false to the log handler element in the logging.xml file which is present at the following location:

ORACLE_INSTANCE/config/ReportsToolsComponent/ReportsTools/logging.xml

This enables the opening and closing of the log files every time a log entry is written.

Table 24-4 11g ODL Message Types vs 10.1.2 Trace Options

ODL Message Types: Levels (11g) Equivalent Trace Options (10g Release 2 (10.1.2)) Notes

INCIDENT_ERROR: 1

TRACE_EXC

Exceptions (unexpected internal errors)

ERROR: 1

TRACE_ERR (lower trace levels)

Errors

WARNING: 1

TRACE_WRN (lower trace levels)

Warnings

NOTIFICATION: 1

TRACE_LOG (lower trace levels)

Default level Important events for server / engine

NOTIFICATION: 16

TRACE_STA TRACE_INFO (lower trace levels)

Server / engine state info Configuration change notifications Successful / failed jobs

TRACE: 1

TRACE_PRF (lower trace levels)

Profiling information

TRACE: 8

TRACE_DST (lower trace levels)

Functional areas tracing (distribution / font handling / printing etc)

TRACE: 16

TRACE_DBG (lower trace levels)

Server - debug traces Engine - engine diagnostics

TRACE: 32

TRACE_ALL equivalent to {TRACE_APP +TRACE_BRK+TRACE_PLS +TRACE_SQL +TRACE_TMS +(lower trace levels) }

All trace messages

The Oracle Reports log files contain the attributes listed in Table 24-5:

Table 24-5 Log File Attributes

Attribute Description

Time stamp

Date and time when the message was generated.

Component ID

Reports messages have component ID of REP.

Message Type

The messages are categorized into the following 5 types: Error, Incident Error, Warning, Notification, Trace. (see below for more details).

Message Level

Each message is qualified by an integer value from 1 to 32 indicating the level.

Message Text

The message body.

Message ID

A unique numeric ID used in conjunction with the component ID (e.g., REP 50127). These IDs will be well documented and have proper Cause and Action associated with them.

Execution Context ID

A globally unique sequence number of the thread of execution in which the originating component participates. This is used to correlate messages from several components involved in the same thread of execution. This ID is included with all messages sent to other components. Oracle Reports generates this in case it is not passed from the originating component.

Module ID

The particular module that originated the messages. This can be any functional module in Oracle Reports (e.g., server, engine, builder).

Process ID

The operating system PID that is provided to identify the process that generated the message.

Thread ID

Identifier of the thread of execution that generated the message.

The Oracle Reports logging mechanism supports the 5 pre-defined ODL message types described in Table 24-6:

Table 24-6 ODL Message Types

Message Type Description

Incident Error

Occurs when the program experiences an error for some internal or unexpected reason and the issue must be reported to Oracle Support.

Error

Occurs when there is any known problem that requires attention from System Administrator.

Warning

Occurs if an action occurs or a condition is discovered that should be reviewed and may require some action (else may lead to an error).

Notification

Occurs when reporting a normal action or event, such as successful login.

Trace

Refers to all the debug statements.

24.3.2.1 Viewing Log Files

You can view log files in any of the following ways:

Note:

By default, you cannot open the log file in Microsoft Internet Explorer if the log file is in XML format. To open an XML file in Microsoft Internet Explorer, you must create a wrapper file with the top level element and include the log file(s) in it as follows: 
.
<?xml version="1.0"?>
<!DOCTYPE LOG [
<!ENTITY log0 SYSTEM "log.xml">
]>
<LOG>
&log0;
</LOG>
.

Using Oracle Enterprise Manager

See Section 7.11.1, "Viewing and Searching Log Files" in Chapter 7, "Administering Oracle Reports Services Using Oracle Enterprise Manager".

Using WLST Commands

See Section 24.3.4, "Logging-Related WLST Commands".

Using the Command Line

From the command line, navigate to the following directories to open and view the log files:

  • For Reports Server:

    ORACLE_INSTANCE/diagnostics/logs/ReportsServerComponent/<servername>

  • For Oracle Reports Bridge:

    ORACLE_INSTANCE/diagnostics/logs/ReportsBridgeComponent/<bridgename>

    Note:

    The Reports Bridge component directory is not created by default. Instead, the directory is created when a Reports Bridge is created.

  • For Reports Tools components(which includes rwrun, rwbuilder, rwclient):

    ORACLE_INSTANCEdiagnostics/logs/ReportsToolsComponent/ReportsTools

  • For Oracle Reports Servlet (For Reports Application):

    $FMW_HOME/user_projects/domains/<domain_name>/servers/WLS_REPORTS/logs/reports

    Note:

    You must modify the logging.xml file to enable trace information for Reports components.

    For more information on the location of the logging.xml file, see Section 24.3.7, "Tracing Report Execution".

24.3.2.2 Managing Log Files

You can manage log files in Oracle Enterprise Manager, which provides capabilities such as:

  • Specifying logging information.

  • Searching inside log files based on various attributes of log entries.

  • Viewing trend metrics; that is, how many errors of a particular type there are in log files (for example, 1 INCIDENT_ERROR, 20 ERROR, 35 WARNING, and so on).

For more information, see Section 7.18.1, "Specifying Logging Information" in Chapter 7, "Administering Oracle Reports Services Using Oracle Enterprise Manager".

24.3.2.3 Audit Log Files

Oracle Reports audits important events, such as the following:

  • Success or failure of authentication and authorization for In-process Servers and standalone servers

  • Success or failure of webcommands check based on user's role for Reports servlet

Configuring Audit Log Files

To configure audit logs, do the following:

  • For J2EE application (In-process Server): Use Enterprise Manager to modify the audit log configuration files. For more information, see Section 7.12, "Modifying Reports Server Audit Configuration".

  • For J2SE application (Standalone Servers): Edit the audit.filterPreset property in the $DOMAIN_HOME/config/fmwconfig/jps-config-jse.xml file as follows:

    <property name="audit.filterPreset" value="None"/>

    to

    <property name="audit.filterPreset" value="All"/>

Location of Audit Log Files

Oracle Reports audit log files are located at :

  • For J2EE applications: $DOMAIN_HOME/servers/WLS_REPORTS/logs/auditlogs/ReportsServer/audit.log

  • For J2SE components: $ORACLE_INSTANCE/auditlogs/ReportsServer/<ReportsServerComponent>/audit.log

Audit Log Example

Following is an example for audit log:

2008-09-10 13:15:32.263  - "CheckAuthorization" true "Authorization of user <username> passed for webcommand showjobs." - "0000HlCPYyE2VOJpMkH7ie18lwWx000001,0" - - - - "12" - - - - - - 
2008-09-10 13:15:25.247  - "UserLogin" true "Authentication of user <username>  passed." - "0000HlCPYyE2VOJpMkH7ie18lwWx000001,0" - - - - "12" - - - - - - 
2008-09-10 13:15:25.310  - "CheckAuthorization" true "Authorization of user <username>  passed for webcommand showjobs." - "0000HlCPYyE2VOJpMkH7ie18lwWx000001,0" - - - - "12" - - - - - - 
 
2008-09-10 13:15:32.263  - "CheckAuthorization" true "Authorization of user <username> failed  for webcommand showjobs." - "0000HlCPYyE2VOJpMkH7ie18lwWx000001,0" - - - - "12" - - - - - - 
2008-09-10 13:15:25.247  - "UserLogin" true "Authentication of user <username>  failed." - "0000HlCPYyE2VOJpMkH7ie18lwWx000001,0" - - - - "12" - - - - - - 
2008-09-10 13:15:25.310  - "CheckAuthorization" true "Authorization of user <username>  failed for webcommand showjobs." - "0000HlCPYyE2VOJpMkH7ie18lwWx000001,0" - - - - "12" - - - - - -

24.3.3 About WLST

The WebLogic Scripting Tool (WLST) is a command-line scripting interface, that helps you to perform administrative tasks and initiate WebLogic Server configuration changes to WebLogic Server instances and domains.

You can invoke the WLST shell by running the following command:

ORACLE_HOME/common/bin/wlst.sh
wls:/offline> connect("weblogic","weblogic", "hostname:7001")
wls:/domain2/serverConfig> domainRuntime()

24.3.3.1 Using WLST Commands for System Components

You can use the following WLST commands to run the Reports Components:

You can invoke the WLST shell by running the following command:

ORACLE_HOME/common/bin/wlst.sh
wls:/offline> connect("weblogic","weblogic", "hostname:7001")
wls:/domain2/serverConfig> domainRuntime()
wls:/domain2/domainRuntime> listLogs(target="opmn:cinst56/ReportsServer_hostname_cinst56")
file://hostname/scratch/rrpai/cinst56/diagnostics/logs/ReportsServerComponent/ReportsServer_hostname_cinst56/server_diagnostic.log
2008-10-27 02:59:47                    302K server_diagnostic.log
file://hostname/scratch/rrpai/cinst56/diagnostics/logs/ReportsServerComponent/ReportsServer_hostname_cinst56/rwEng-0_diagnostic.log
2008-10-27 02:59:13                    7.3K rwEng-0_diagnostic.log
 
wls:/domain2/domainRuntime> displayLogs(target="opmn:cinst56/ReportsServer_hostname_cinst56", tail=10)
 
[2008-10-27T02:31:52.133-07:00] [reports] [NOTIFICATION] [] [oracle.reports.server] [host: hostname] [nwaddr: 144.20.207.149] [tid: 10] [ecid: 0000HoxdIF14EwQRyaQ5T0191Oe7000001,0] ServerConfig:logConf  <server version="11.1.1.0.0" xmlns="http://xmlns.oracle.com/reports/server">[[
   
[2008-10-27T02:44:38.684-07:00] [reports] [INCIDENT_ERROR] [REP-50125] [oracle.reports.engine] [host: hostname] [nwaddr: 144.20.207.149] [pid: 18787] [tid: 10] [ecid: 0000HoxgDPr4EwQRyaQ5T00004_Z000000,0] [EngineName: rwEng-0] REP-50125 : org.omg.CORBA.COMM_FAILURE:   vmcid: SUN  minor code: 201  completed: No  [[
……
 
wls:/domain2/domainRuntime> displayLogs(target="opmn:cinst56/ReportsServer_hostname_cinst56", tail=10, query="MODULE_ID equals oracle.reports.server")
 
[2008-10-27T02:31:01.085-07:00] [reports] [NOTIFICATION] [] [oracle.reports.server] [host: hostname] [nwaddr: 144.20.207.149] [tid: 10] [ecid: 0000Hoxd5lP4EwQRyaQ5T0191OdK000001,0] ServerConfig:logConf  <server version="11.1.1.0.0" xmlns="http://xmlns.oracle.com/reports/server">[[
 
[2008-10-27T02:59:47.383-07:00] [reports] [NOTIFICATION] [] [oracle.reports.server] [host: hostname] [nwaddr: 144.20.207.149] [tid: 10] [ecid: 0000HoxjfEr4EwQRyaQ5T0191P4I000001,0] ServerConfig:logConf  Reading server config file
 
wls:/domain2/domainRuntime> exit()

24.3.3.2 Using WLST Commands for Java EE Components

You can use the following WLST commands to run the Reports Components:

You can invoke the WLST shell by running the following command:

ORACLE_HOME/common/bin/wlst.sh
wls:/offline> connect("weblogic","weblogic", "hostname:7001")
wls:/domain2/serverConfig> domainRuntime()
wls:/domain2/domainRuntime> listLoggers(target="WLS_REPORTS") 
-------------------------------------------------------------------------------------+-----------------
Logger                                                                               | Level           
-------------------------------------------------------------------------------------+-----------------
<root>                                                                               | WARNING:1
com.sun.xml.bind.v2.ClassFactory                                                     | <Inherited>
....
oracle.reports.adminlogconfig                                                        | <Inherited>
oracle.reports.configMbeans                                                          | <Inherited>
oracle.reports.engine                                                                | NOTIFICATION:1
oracle.reports.server                                                                | NOTIFICATION:16
oracle.reports.servlet                                                               | NOTIFICATION:1
.....
 
wls:/domain2/domainRuntime> listLoggers(pattern="oracle.reports.*", target="WLS_REPORTS")
------------------------------+-----------------
Logger                        | Level           
------------------------------+-----------------
oracle.reports.adminlogconfig | <Inherited>
oracle.reports.configMbeans   | <Inherited>
oracle.reports.engine         | NOTIFICATION:1
oracle.reports.server         | NOTIFICATION:16
oracle.reports.servlet        | NOTIFICATION:1
 
wls:/domain2/domainRuntime> listLoggers(pattern="oracle.reports.server.*", target="WLS_REPORTS")
----------------------+-----------------
Logger                | Level           
----------------------+-----------------
oracle.reports.server | NOTIFICATION:16
 
wls:/domain2/domainRuntime> listLogHandlers(target="WLS_REPORTS")
 
Handler Name: rwservlet_handler
type:   ODL
path:   /scratch/rrpai/wls55/user_projects/domains/domain2/servers/WLS_REPORTS/logs/reports/rwservlet_diagnostic.log
format: ODL-Text
maxFileSize:    1M
maxLogSize:     10M
Handler Name: wls-domain
type:   oracle.core.ojdl.weblogic.DomainLogHandler
Handler Name: odl-handler
type:   ODL
path:   /scratch/rrpai/wls55/user_projects/domains/domain2/servers/WLS_REPORTS/logs/WLS_REPORTS-diagnostic.log
maxFileSize:    10M
maxLogSize:     100M

24.3.4 Logging-Related WLST Commands

Use the following WLST commands to configure logs:

Use the following WLST commands to view logs:

24.3.4.1 listLoggers

You can use the listLoggers command to view the list of loggers and the level of each logger.

Table 24-7 describes the parameters supported by listLoggers command.

Table 24-7 Parameters of listLoggers

Parameter Description

target

The name of the WebLogic Server.

The default value is the server to which WLST is connected.

pattern

A regular expression pattern that is used to filter logger names. There is no default pattern and all loggers are returned if the pattern is not provided.

runtime

A Jython boolean value (0 or 1) that determines if the operation is to list runtime loggers or config loggers.

The default value is 1.

Return Value

A PyDictionary object where the keys are logger names and the associated values are the logger levels.

Examples

Following are the examples for listLoggers command:

  1. listLoggers()

  2. listLoggers(pattern="oracle.*")

  3. listLoggers(runtime=0)

  4. listLoggers(target="server1")

Note:

24.3.4.2 getLogLevel

You can use the getLogLevel command to obtain the log level for a given logger.

Table 24-8 describes the parameters supported by getLogLevel command.

Table 24-8 Parameters of getLogLevel

Parameter Description

target

The name of the WebLogic Server.

The default value is the server to which WLST is connected

logger

A logger name.

runtime

A Jython boolean value (0 or 1) that determines if the operation is to list runtime loggers or config loggers.

The default value is 1.

Return Value

The logger level as a string.

Examples

The following are the examples of getLogLevel command

  1. getLogLevel(logger="oracle")

  2. getLogLevel(logger="oracle")

  3. getLogLevel(logger="oracle", target="server2")

Note:

24.3.4.3 setLogLevel

You can use the setLogLevel command to set the log level for a given logger.

Table 24-9 describes the parameters supported by the setLogLevel command.

Table 24-9 Parameters of setLogLevel

parameter Description

target

The name of a WebLogic server.

The default value is the server to which WLST is connected

logger

A logger name.

There is not default value.

level

The level name. This can be either a Java level (INFO, FINE, etc), or an ODL level (NOTIFICATION:1, TRACE:1, etc).

There is no default value.

runtime

A Jython boolean value (0 or 1) that determines if the operation is to list runtime loggers or config loggers.

The default value is 1.

persist

a Jython boolean value (0 or 1) that determines if the level should be saved to the configuration file.

The default value is 1.

Return Value

The return value for the setLogLevel command is none.

Examples

The following are the examples for setLogLevel command.

  1. setLogLevel(logger="oracle.my.logger", level="NOTICATION:1")

  2. setLogLevel(logger="oracle.my.logger", level="TRACE:1", persist=0)

  3. setLogLevel(target="server1", logger="oracle.my.logger", level="WARNING", runtime=0)

    Note:

24.3.4.4 listLogHandlers

You can use the listLogHandlers command to view the configuration of one or more log handlers.

Table 24-10 describes the parameters supported by listLogHandlers command.

Table 24-10 Parameters of listLogHandlers

Parameters Description

target

the name of a WebLogic server.

The default value is the server to which WLST is connected.

name

the name of a log handler. If the name is not provided then all handlers are listed.

Return Value

A java.util.List with one entry for each handlers. Each entry is a javax.management.openmbean.CompositeData object describing the handler.

Examples

The following are the examples for listLogHandlers command

  1. listLogHandlers()

  2. listLogHandlers(name="odl-handler")

  3. listLogHandlers(target="server1")

Note:

24.3.4.5 configureLogHandlers

You can use the configureLogHandler command to configure and existing log handler, add a new log handler, or remove existing handlers.

Table 24-11 describes the parameters supported by configure LogHandler command.

Table 24-11 Parameters for configure LogHandler

Parameter Description

target

The name of a WebLogic server.

The default value is the server to which WLST is connected.

name

The name of a log handler

maxFileSize

The value of the maxFileSize attribute for an ODL handler.

The value is a string representing a numeric value, possibly followed by a suffix indicating a size unit (k for kilobytes, m for megabytes, g for gigabytes).

maxLogSize

The value of the maxLogSize attribute for an ODL handler.

The value is a string representing a numeric value, possibly followed by a suffix indicating a size unit (k for kilobytes, m for megabytes, g for gigabytes).

rotationFrequency

The value of the rotationFrequncy for an ODL handler.

The value is a string representing a numeric value, possibly followed by a suffix indicating a time unit (m for minutes, h for hours, d for days). The default unit is minutes. The following special values are also accepted and are converted to a numeric value in minutes: HOUR, HOURLY, DAY, DAYLY, WEEK, WEEKLY, MONTH, MONTHLY.

baseRotationTime

the base rotation time, to be used with the rotation frequency parameter.

The value must be a string representing a date/time values. It can be a full date/time in ISO 8601 date/time format, or a short form including only hours and minutes. The default baseRotationTime is 00:00.

retentionPeriod

The retention period in minutes.

The value must be string representing a numeric value, possibly followed by a suffix indicating a time unit (m for minutes, h for hours, d for days). The default unit is minutes. The following special values are also accepted and are converted to a numeric value in minutes: HOUR, HOURLY, DAY, DAYLY, WEEK, WEEKLY, MONTH, MONTHLY.

format

The format for the ODL handler.

The value must be the string "ODL-Text" or "ODL-XML". The default format is ODL-Text

encoding

the character encoding for the log file.

path

the log file path.

handlerType

the name of the Java class that provides the handler implementation.

It must be an instance of java.util.logging.Handler or oracle.core.ojdl.logging.HandlerFactory.

propertyName

the name of a handler property to be added or update.

The property value is specified with the propertyValue parameter.

propertyValue

the new value for the handler property defined by the propertyName parameter.

addProperty

a Jython boolean value. Used in conjunction with the propertyName and propertyValue parameters to define that a new property is to be added to the handler.

removeProperty

a list of one or more handler properties to be removed.

addHandler

the name of a handler to be added.

removeHandler

the name of a handler to be removed.

addToLogger

a list of logger names. The handler is added to the given logger names.

removeFromLogger

a list of logger names. The handler is removed from the given loggers.

Note:

The listLogHandlers command is not supported for system components, such as Reports Server and Reports Bridge.

For more information about WLST commands, see Oracle Fusion Middleware WebLogic Scripting Tool Command Reference guide.

24.3.4.6 listLogs

You can use the listLogs command to view the list of one or more components.

Table 24-12 describes the parameters supported by listLoggers command.

Table 24-12 Parameters of listLogs

Parameter Description

target

The name of the WebLogic Server, or a system component.

In connected mode, the default target is the WebLogic domain, and in disconnected mode there is no default.

oracleInstance

in disconnected mode, oracleInstance defines the path to the ORACLE_INSTANCE, either a WebLogic domain home, or a non-Java EE instance home.

This parameter is ignored in connected mode, required in disconnected mode.

unit

defines the unit to use for reporting file size.Valid values are B (bytes), K (kilobytes), M (megabytes), G (gigabytes), or H (display size in a human-readable form, similar to Unix's "ls -h" option

The default value is H.

fulltime

a Jython Boolean value.

The default value is false

Return Value

A PyArray with one element for each log. The elements of the array are javax.management.openmbean.CompositeData objects describing each log.

Examples

Following are the examples for listLoggers command:

  1. listLogs()

  2. listLogs(target="server1")

  3. listLogs(target="opmn:instance1/ohs1")

  4. listLogs(oracleInstance="/middleware/user_projects/domains/base_domain", target="server1")

Note:

  • The listLogs command is supported for both Java EE and system components.

  • For more information about WLST commands, see Fusion Middleware WLST Command Reference Guide.

24.3.4.7 displayLogs

You can use the displayLogs command to view the contents of diagnostic logs.

Table 24-13 describes the parameters supported by listLoggers command.

Table 24-13 Parameters of displayLogs

Parameter Description

target

The name of the WebLogic Server, or a system component.

In connected mode, the default target is the WebLogic domain, and in disconnected mode there is no default.

oracleInstance

in disconnected mode, oracleInstance defines the path to the ORACLE_INSTANCE, either a Weblogic domain home, or a non-Java EE instance home.

This parameter is ignored in connected mode, required in disconnected mode.

query

a string that specify an expression used to filter the contents of log files.

A simple expression has the form "<field-name> <operator> <value>", where <field-name> is a log record field name and <operator> is an appropriate operator for the field type.

Return Value

The command returns a value only when the returnData parameter is set to true. By default it will not return any data. The return value depends on the option used.

Examples

Following are the examples for displayLogs command:

  1. displayLogs(tail=100)

  2. displayLogs(target='opmn:instance1/ohs1', last=60)

  3. displayLogs(groupBy=['COMPONENT_ID', 'MSG_TYPE'])

  4. displayLogs(query='MSG_TYPE equals ERROR or MSG_TEXT contains Exception")

  5. displayLogs(query='APP equals myApp', last=60)

  6. displayLogs(query='ECID equals 0000Hl9TwKUCslT6uBi8UH18lkWX000002')

Note:

  • The listLogs command is supported for both Java EE and system components.

  • For more information about WLST commands, see Fusion Middleware WLST Command Reference Guide.

24.3.5 Metrics Related WLST Commands

Use the following WLST commands to view metrics.

24.3.5.1 Viewing Metrics Tables

You can use the displayMetricTableNames command to view the list of metric table names available, including the native WebLogic metrics.

Table 24-14 describes the parameter for displayMetricTableNames command.

Table 24-14 Parameter for displayMetricTableNames

Parameter Description

Servers

It specifies the managed servers to retrieve metrics from. It is optional. Its possible values are a list of managed server names.

Example:

Invoke the WLST shell using the following Command:

ORACLE_HOME/common/bin/wlst.sh
> connect('weblogic','weblogic','hostname:7001')

wls:/domain1/serverConfig > displayMetricTableNames()
ContextManager
JVM
JVM_ClassLoader
JVM_Compiler
JVM_GC
JVM_Memory
JVM_MemoryPool
JVM_MemorySet
JVM_OS
JVM_Runtime
JVM_Thread
JVM_ThreadStats
Reports_Server_Information
Reports_Server_Performance
Reports_Server_Response
Reports_Servlet_Response
dms_cProcessInfo
opmn
opmn_component:ResourceUsage
opmn_component:ResourceUsage_by_component
opmn_component:ResourceUsage_no_rate
opmn_component:Response
opmn_connect
opmn_header
opmn_host_statistics
opmn_ias_component
opmn_ias_instance
opmn_ipmon
opmn_ons
opmn_ons_connections

Note:

For more information on using the displayMetricTableNames command, see Oracle Fusion Middleware WebLogic Scripting Tool Command Reference.

24.3.5.2 Viewing All Available Metrics

You can use the dumpMetrics command to view all available metrics, including native WebLogic metrics and internal DMS metrics.

Table 24-15 describes the parameters supported by dumpMetrics command

Table 24-15 Parameters for dumpMetrics

Parameter Description

format

It defines the command output format.

It is optional and the possible values are "raw", "xml".

The default is "raw" format.

servers

It specifies the managed servers to retrieve metrics from.

It is optional and the possible values are a list of managed server names.

Example:

Invoke the WLST shell using the following Command:

ORACLE_HOME/common/bin/wlst.sh
> connect('weblogic','weblogic','hostname:7001')
wls:/domain1/serverConfig > dumpmetrics()

ContextManager
JVM
JVM_ClassLoader
JVM_Compiler
JVM_GC
JVM_Memory
JVM_MemoryPool
JVM_MemorySet
JVM_OS
JVM_Runtime
JVM_Thread
JVM_ThreadStats
Reports_Server_Information
Reports_Server_Performance
Reports_Server_Response
Reports_Servlet_Response
dms_cProcessInfo
opmn
opmn_component:ResourceUsage
......

Note:

For more information on using the dumpMetrics command, see, Oracle Fusion Middleware WebLogic Scripting Tool Command Reference.

24.3.5.3 Viewing Specific Metric Tables

You can use the displayMetricTables command to view the specified metric tables.

Table 24-16 describes the parameters supported by displayMetricTables command.

Table 24-16 Parameters for displayMetricTables

Parameter Description
 

an optional list of metric table names.

It displays all-available metrics by default

variable

It defines the metric aggregation parameters. It is optional.

Its possible values are a set of name value pairs in a Jython map.

server

It specifies the managed servers to retrieve metrics from.

It is optional. Its possible values are a list of managed server names.

Example:

Invoke the WLST shell using the following Command:

ORACLE_HOME/common/bin/wlst.sh
> connect('weblogic','weblogic','hostname:7001')
wls:/domain1/serverConfig> displayMetricTables('Reports_Server_Performance')
--------------------------
Reports_Server_Performance
--------------------------
 
Active_Engines.value:   1
Available_Connections.value:    50
Average_Elapsed_Time.value:     0.0     msec
Average_Engines.value:  1.0
Average_Response_Time.value:    0.0     msec
CurrentJobLoad.value:   0
Current_Jobs.value:     0
Failed_Jobs.value:      0
Failed_Jobs_Ratio.value:        0.0
Finished_Jobs.value:    0
Host:   hostname
JobLoad.value:  0
Jobs_Queue_Time.value:  0.0
LongRunning_Jobs.value: 0
Maximum_Engines.value:  1
Maximum_Queue_Size.value:       1000
Name:   Reports_Server_Performance
Parent: /reports/rwserver
Potential_Runaway_Jobs.value:   0
Process:        ReportsServerComponent:8591386:/cinst48/ReportsServer_hostname_cinst48
Reports_Server_Auth_Time.value: 0.0
Reports_Server_Security.value:  0
Reports_Server_Start_Time.value:        1223884972408   msecs
Reports_Version.value:  11.1.1.0.0
Scheduled_Jobs.value:   0
ServerName:     /cinst48/ReportsServer_hostname_cinst48
Transferred_Jobs.value: 0
Used_Connections.value: 0
 
 
 
wls:/domain1/serverConfig> displayMetricTables('Reports_Server_Response')
-----------------------
Reports_Server_Response
-----------------------
 
Host:   hostname
Name:   Reports_Server_Response
Parent: /reports/rwserver
Process:        ReportsServerComponent:8591386:/cinst48/ReportsServer_hostname_cinst48
Reports_Server_Status.value:    1
Reports_Server_Type.value:      1
ServerName:     /cinst48/ReportsServer_hostname_cinst48

Note:

For more information on using the displayMetricTables command, see Oracle Fusion Middleware WebLogic Scripting Tool Command Reference.

24.3.6 Audit Configuration WLST Commands

Use the WLST commands listed in Table 24-17 to view and manage audit policies and the audit repository configuration.

Table 24-17 WLST Audit Commands

Use this command... To... Use with WLST...

getAuditPolicy

Display audit policy settings.

Online

setAuditPolicy

Update audit policy settings.

Online

listAuditEvents

List audit events for one or all components.

Online

24.3.6.1 getAuditPolicy

This online command displays audit policy settings including the filter preset, special users, custom events, maximum log file size, and maximum log directory size. The component mbean name is required for system components like Oracle Internet Directory and Oracle Virtual Directory.

Note:

You can obtain a system component's MBean name using the getNonJava EEAuditMBeanName command.

The syntax of the getAuditPolicy command is as follows:

getAuditPolicy(['mbeanName'])
Argument Definition
mbeanName Specifies the name of the component audit MBean for system components.

Example

The following command displays the audit settings for a Java EE component:

wls:/domain52/serverConfig> getAuditPolicy()
Location changed to domainRuntime tree. This is a read-only tree with DomainMBean as the root.
For more help, use help(domainRuntime)
 
FilterPreset:None
Max Log File Size:104857600
Max Log Dir Size:0

24.3.6.2 setAuditPolicy

This online command configures the audit policy settings. You can set the filter preset, add or remove users, and add or remove custom events. The component mbean name is required for system components like Oracle Internet Directory and Oracle Virtual Directory.

Note:

You can obtain a system component's MBean name using the getNonJava EEAuditMBeanName command.

The syntax of the setAuditPolicy command is as follows:

setAuditPolicy(['mbeanName'],['filterPreset'],['addSpecialUsers'],
['removeSpecialUsers'],['addCustomEvents'],['removeCustomEvents'])
Argument Definition
mbeanName Specifies the name of the component audit MBean for system components.
filterPreset Specifies the filter preset to be changed.
addSpecialUsers Specifies the special users to be added.
removeSpecialUsers Specifies the special users to be removed.
addCustomEvents Specifies the custom events to be added.
removeCustomEvents Specifies the custom events to be removed.

Example

The following command sets audit policies to All level.:

wls:/domain52/domainRuntime> setAuditPolicy(filterPreset='All');
Already in Domain Runtime Tree
Audit Policy Information updated successfully

24.3.6.3 listAuditEvents

This online command displays the attributes and audit events of a component. For system components, pass the component mbean name as a parameter. Java EE applications and services like Oracle Platform Security Services (OPSS) do not need the MBean parameter. Without a component type, all generic attributes applicable to all components are displayed.

Note:

You can obtain a system component's MBean name using the getNonJava EEAuditMBeanName command.

The syntax of the listAuditEvents command is as follows:

listAuditEvents(['mbeanName'],['componentType'])
Argument Definition
mbeanName Specifies the name of the component MBean.
componentType Specifies the component type.

Example

The following command displays all audit events:

wls:/domain52/domainRuntime> listAuditEvents();
Location changed to domainRuntime tree. This is a read-only tree with DomainMBean as the root.
For more help, use help(domainRuntime)
 
Components:
DIP
JPS
OIF
OWSM-AGENT
OWSM-PM-EJB
ReportsServer
WS-PolicyAttachment
WebCache
WebServices
Attributes applicable to all components:
ComponentType
InstanceId
HostId
HostNwaddr
ModuleId
ProcessId
OracleHome
HomeInstance
ECID
RID
...

24.3.7 Tracing Report Execution

Enabling report tracing generates a text file that describes the series of steps completed during the execution of the report. The trace file provides abundant information, which is useful not only for performance tuning but also for debugging reports and identifying performance bottlenecks.

  • For Reports Builder (rwbuilder) and Reports Runtime (rwrun), specify tracing options in the logging.xml file.

    For example:

    In $ORACLE_INSTANCE/diagnostics/logs/ReportsToolsComponent/ReportsTools/logging.xml, specify the trace level to Trace 32.

  • For Reports Server (rwserver), specify tracing options in the logging.xml file. Separate trace files are generated for Reports Server and the engine(s).

    For example:

    • In $ORACLE_INSTANCE/config/ReportsServerComponent/<servername>/logging.xml, specify tracing level to Trace 32.

    In 11g, job level tracing for an individual report does not exist. Instead, users can only search for a job in Enterprise Manager based on the job's ECID.

  • For Oracle Reports Servlet (rwservlet), specify tracing options in the logging xml file present at the following location:

    FMW_HOME/user_projects/domains/<domainname>/config/fmwconfig/servers/WLS_REPORTS/logging.xml

  • For Oracle Reports Bridge tracing, specify tracing options in Oracle Enterprise Manager. You can also manually specify the tracing options in the logging.xml file present at the following location:

    $ORACLE_INSTANCE/config/ReportsBridgeComponent/<ReportsBridgeName>/logging.xml

Following is the outline of the information output to the logging file.

Example 24-1 Oracle Reports Builder

+------------------------------------------+
| Report Builder Profiler statistics       |
+------------------------------------------+
Total Elapsed Time: 8.00 seconds
Reports Time:    7.00 seconds (87.50% of TOTAL)
ORACLE Time:     1.00 seconds (12.50% of TOTAL)
UPI:             0.00 seconds
SQL:             1.00 seconds
TOTAL CPU Time used by process: N/A

Table 24-18 Oracle Reports Builder

Field Description

Total Elapsed Time

Time spent in executing the report.

Reports Time

Time spent in formatting the retrieved data. Also displayed as a percentage of Total Elapsed Time.

ORACLE Time

Time spent in retrieving the data. Also displayed as a percentage of Total Elapsed Time.

UPI

SQL queries only. Time spent in establishing a database connection, then parsing and executing the SQL.

SQL

Time taken by the database server to fetch the data (percent of time spent executing SRW.DO_SQL statements, EXEC_SQL statements, PL/SQL cursors, and so on.)

Note:

If your data source is a non-SQL data source such as Text or an XML pluggable data source, the values for ORACLE Time, UPI, and SQL display as 0.

In Example 24-1, focus your tuning efforts on time formatting (Reports Time) the data rather than on querying and fetching it.

24.3.8 RW_SERVER_JOB_QUEUE Table

The RW_SERVER_JOB_QUEUE table provides another window (aside from that available through Enterprise Manager) into the Reports Server job queues.

The Reports Server posts information about the current report to the database when the job is enqueued and finished.

This information is inserted into the RW_SERVER_JOB_QUEUE table that includes the following data:

  • The name of the job

  • The job submitter

  • The output format

  • The job's current status

  • When the job was queued, started, and subsequently finished

Table 24-19 lists and describes the information contained in the RW_SERVER_JOB_QUEUE table:

Table 24-19 Structure of the RW_SERVER_JOB_QUEUE Table

Column Name Description

JOB_QUEUE

States whether the job listed is CURRENT, PAST, or SCHEDULED.

JOB_ID

System generated job identification number.

JOB_TYPE

Type of job, such as report, rwurl, and so on, as defined in the Reports Server configuration file, rwserver.conf.

JOB_NAME

Job submission name (or file name if no value for JOBNAME is specified).

STATUS_CODE

Current status of job. See Table 24-20 for more information about status codes.

STATUS_MESSAGE

Full message text relating to status code (includes error messages if report is terminated). See Table 24-20 for more information about status codes.

COMMAND_LINE

Complete command line submitted for this job submission.

OWNER

User who submitted the job. On the Web, the default user is the OS user who owns the Web server.

DESTYPE

Destination where report output is sent.

DESNAME

Name of the report output if not going to the Reports Server cache.

SERVER

Reports Server to which the report was submitted.

QUEUED

Date and time the job submission was received and queued by the given Reports Server.

STARTED

Date and time the job submission was run.

FINISHED

Date and time the submitted job completed.

RUN_ELAPSE

Elapsed time between started and finished time, in units of milliseconds.

TOTAL_ELAPSE

Elapsed time between queued and finished time, in units of milliseconds.

LAST_RUN

Date and time a scheduled job was last run.

NEXT_RUN

Date and time a scheduled job will run.

REPEAT_INTERVAL

Frequency on which to run a job.

REPEAT_PATTERN

Repeat pattern (for example, every minute, every hour, or every day).

CACHE_KEY

Cache key used to compare a request with an already cached result. The key is a string that uniquely indicates a report output result without considering the time the job was run. For example, if two requests have the same key, it means they will both generate the same output if they are running at the same time, although the outputs may be used for different purposes (for example, sent to e-mail or saved to a file).

CACHE_HIT

Indicates whether the job result was fetched from cache instead of running itself.

Table 24-20 Job Submission Status Codes

Status Code Defined PL/SQL Constant Description for Status Code

1

ENQUEUED

Job is waiting in queue.

3

RUNNING

Report is currently running.

4

FINISHED

Job submission has completed successfully. This code will not change once set.

5

TERMINATED_W_ERR

Job has ended with an error.

7

CANCELED

Job was canceled by user request.

8

SERVER_SHUTDOWN

Job was canceled due the Reports Server shutting down.

11

TRANSFERRED

Job is transferred to another server in the cluster (note: clustering was deprecated in 10g Release 2 (10.1.2)). This code will not change once set. In this case, the job is submitted to another Reports Server as a "new" job (so that the user can query the new Reports Server for the new job's status).

12

VOID_FINISHED

Job is finished but output is void because of reaching limit of cache capacity.

13

ERROR_FINISHED

Output is successfully generated but failed to send to destinations.

15

EXPIRED

Scheduled report has expired.

Users can view this table if you grant them SELECT access. This will enable them to query the job submission of interest and determine the job's current status. You can also give them a view of this data by implementing a Reports Server Queue screen. You can implement such a screen by creating a report based directly on this table. Doing so displays the queue report as a job submission by the user.

Conversely, the real-time update of the table with the status of job submissions makes it very easy for administrators to know exactly how many concurrent users have requested jobs to be run on the Reports Server.

By counting the number of entries in the RW_SERVER_JOB_QUEUE table that have a status code indicating that the job has been queued but not completed, it is possible to return an accurate number of the current active users on the server. For example, you could use the following query:

SELECT Count(*)
FROM   RW_SERVER_JOB_QUEUE
WHERE  STATUS_CODE IN (1,     -- ENQUEUED
                      2,      -- OPENING
                      3)      -- RUNNING
AND    JOB_TYPE != 'Scheduled'

Note:

While the table contains the date and time a report was queued, run, and finished, it is not a good idea to use a query based on the fact that a job has a defined QUEUED and STARTED time but no FINISHED value. If a report ends due to an unexpected error, such as invalid input, then the FINISHED column remains NULL. However, the STATUS_CODE and STATUS_MESSAGE both indicate there has been a failure and list the cause of that failure.

24.3.8.1 Updating the Database with Queue Activity

The Reports Server job queue is implemented through the use of a PL/SQL case API. It functions to update the queue table with the queue information as requests are made. This implementation is defined in the following path:

ORACLE_HOME\reports\admin\sql\rw_server.sql

This script is certified to worked against Oracle 10g database.

To implement the queue, perform the following steps:

  1. Load the rw_server.sql file to a database (this file is included with your Oracle Reports Services installation: ORACLE_HOME\reports\admin\sql).

    This creates a schema that owns the report queue information and has execute privileges on the server queue API. For backward compatibility with Oracle6i Reports, this also creates a view called RW_SERVER_QUEUE.

  2. Set the repositoryconn property of the jobStatusRepository element in the server configuration file (ORACLE_INSTANCE\config\ReportsServerComponent\server_name\rwserver.conf for Standalone servers and $DOMAIN_HOME/config/fmwconfig/servers/<WLS_SERVER_NAME>/applications/reports_<version>/configuration/rwserver.conf for In-process servers) to the connection string of the schema that owns the queue data. For more information, see Section 8.2.1.12, "jobStatusRepository".

    Alternatively, you can set the jobstatusRepository in Oracle Enterprise Manager.

When the server starts, it connects as the defined user and logs job submissions.

Note:

Oracle Reports uses the dbconn property of the jobstatusrepository element to connect to the database when updating the log information about job queues

24.3.9 SHOWJOBS Command Line Keyword

You can use SHOWJOBS on the command line to display a Web view of Reports Server queue status for reports run through rwservlet.

For more information, see Section A.8.9, "SHOWJOBS".

24.3.10 Efficient SQL

Oracle Reports uses SQL to retrieve data from the database.

Note:

Oracle Reports uses SQL for non-PDS queries only.

Inefficient SQL can cripple performance, especially in large reports. Thus, anyone tuning Oracle Reports must have a good working knowledge of SQL and understand how the database executes these statements. If you are less proficient in SQL, use the Data Wizard and Query Builder in the Oracle Reports Builder. However, the wizard cannot prevent inefficient SQL from being created, such as SQL that does not use available indexes.

To tune your report SQL, use the trace functionality available in the Oracle database. SQL tracing enables you to determine the SQL statement sent to the database as well as the time taken to parse, execute, and fetch data. Once a trace file is generated, use the TKPROF database utility to generate an EXPLAIN PLAN map. The EXPLAIN PLAN map graphically represents the execution plan used by Oracle Optimizer. For example, the Oracle Optimizer shows where full table scans have been used. This may prompt you to create an index on that table depending on the performance hit.

To turn on SQL tracing inside Oracle Reports Builder, add a report-level formula column named SQL_TRACE with the following code:

SRW.DO_SQL('ALTER SESSION SET SQL_TRACE=TRUE');
return(1);

Note:

You can also call SQL_TRACE using either a Before Report trigger, or a Before Parameter Form trigger.

The following EXPLAIN PLAN map was generated using the database's SQL trace facility. Refer to the Oracle Database PL/SQL Language Reference documentation for more information.

Example

The statement being executed is:

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno

The EXPLAIN PLAN generated is:

OPERATION            OPTIONS     OBJECT_NAME   POSITION
------------------ ----------- --------------- --------
SELECT STATEMENT
  MERGE JOIN     OUTER                                1
   SORT                  JOIN                         1
     TABLE ACCESS FULL                 DEPT           1
   SORT                  JOIN                         2
     TABLE ACCESS FULL                 EMP            1

When you tune data for Oracle Reports, understand that the Oracle RDBMS provides two optimizers: cost-based and rule-based. By default, the cost-based optimizer constructs an optimal execution plan geared towards throughput; that is, process all rows accessed using minimal resources. You can influence the optimizer's choice by setting the optimizer approach and goal, and gathering statistics for cost-based optimization. While the cost-based optimizer removes most of the complexity involved in tuning SQL, understanding the distribution of the data and the optimizer rules allow you to choose the preferred method and gives you greater control over the execution plan. For example, in your SQL statement, you could do one of the following:

  • Provide optimizer hints with the goal of best response time; that is, process the first row accessed using minimal resources.

  • Decide that an index is not needed.

    Note:

    For large queries, it is imperative to do one of the following:

    • Activate the cost-based optimizer and gather statistics by using the DBMS_STATS package, the COMPUTER STATISTICS option, or the ANALYZE command.

    • Optimize all SQL following the rules laid out by the rule-based optimizer.

The Oracle Fusion Middleware documentation provides more information on the database optimizer's functionality.

24.3.11 PL/SQL

Use the ORA_PROF built-in package to tune your report's PL/SQL program units. The procedures, functions, and exceptions in the ORA_PROF built-in package allow you to track the amount of time that pieces of your code takes to run.

Example

PROCEDURE timed_proc (test VARCHAR2) IS
  i PLS_INTEGER;
BEGIN
  ORA_PROF.CREATE_TIMER('loop2');
  ORA_PROF.START_TIMER('loop2');
  ColorBand_Program_Unit;
  ORA_PROF.STOP_TIMER('loop2');
  TEXT_IO.PUTF('Loop executed in %s seconds.\n',
  ORA_PROF.ELAPSED_TIME('loop2'));
  ORA_PROF.DESTROY_TIMER('loop2');
END;

This procedure creates a timer, starts it, runs a subprogram, stops the timer, and displays the time it took to run. It destroys the timer when finished.

Note:

For a description of the ORA built-in package see the Oracle Reports online Help.

Implement PL/SQL program units performing a significant amount of database operations as stored database procedures. Stored procedures run directly on the Oracle database and perform operations more quickly than local PL/SQL program units. Local PL/SQL program units use the Oracle Reports Builder's PL/SQL parser, then the database's SQL parser, and also include a network trip.

PL/SQL program units that do not perform any database operations should be coded as locally as possible using the Program Units node in the Object Navigator. Localizing the PL/ SQL program unit has a performance advantage over executing PL/SQL from an external PL/SQL library. Use external PL/SQL libraries only when the benefits of code sharing can be utilized.

The SRW.DO_SQL built-in procedure should be used as sparingly as possible. Each call to the SRW.DO_SQL built-in procedure necessitates parsing and binding the command and opening a new cursor like a normal query. Unlike a normal query, this operation will occur each time the object owning the SRW.DO_SQL built-in procedure fires.

For example, a PL/SQL block in a formula column calls the SRW.DO_SQL built-in procedure and the data model group returns 100 records. In this case, the parse/ bind/ create cursor operation occurs 100 times. Therefore, use the SRW.DO_SQL built-in procedure for operations that cannot be performed using normal SQL (for example, to create a temporary table or any other form of DDL), and in places where it will be executed sparingly (for example, in triggers that are only fired once per report).

The primary reason to use the SRW.DO_SQL built-in procedure is to perform DDL operations, such as creating or dropping temporary tables. For example, have the SRW.DO_SQL built-in procedure to create a table. The table's name is determined by a parameter entered in the Runtime Parameter Form.

Note:

For a description of the SRW built-in package, including the SRW.DO_SQL built-in procedure, see the Oracle Reports online Help.

Example

SRW.DO_SQL (`CREATE TABLE' || :tname ||`(ACCOUNT NUMBER
  NOT NULL PRIMARY KEY, COMP NUMBER (10,2))');

24.3.12 Java Stored Procedures

Java stored procedures enable you to implement business logic at the server level; thereby, improving application performance, scalability, and security. Oracle Database allows PL/SQL and Java stored procedures to be stored in the database. Typically, SQL programmers who want procedural extensions favor PL/SQL and Java programmers who want easy access to Oracle data favor Java. Although Java stored procedures offer extra flexibility, there is some overhead involved. Balance the trade off between performance and flexibility based on your individual needs.

Refer to the Oracle Database Java Developer's Guide for more information on Java stored procedures.

24.3.13 The Java Importer

Although Oracle PL/SQL provides a powerful and productive development environment, it is sometimes necessary to integrate with external application services and providers. As many of these external application services and providers are increasingly offering integration points in Java, Oracle Reports integrates with the Oracle Java Importer to facilitate the invocation of business logic contained in external middle-tier Java classes. The Java Importer declaratively creates a PL/SQL wrapper package for each class you select and exposes the methods identified in the class through PL/SQL functions and procedures. This enables you to instantiate, use, and destroy the Java object instances when the report is run. While this powerful extension insulates you from having to write Java code yourself, there is some overhead involved. Separate PL/SQL packages are generated for every class specified. The PL/SQL generator performs type translations when it generates the PL/SQL packages from the Java methods. Any time a Java object instance is created using the new function in the PL/SQL package and generated by the Java Importer, the result is stored in a variable of type JOBJECT. Java Object persistence must be carefully handled because accumulating large numbers of global references without removing them increases the JVM's memory consumption.