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:
Using Oracle Enterprise Manager to manage and monitor your Reports Server is discussed in Chapter 6, "Administering Oracle Reports Services Using Oracle Enterprise Manager".
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
See Table 23-3 for default location of log files.
Table 23-3 Default Location of Log Files
Component | Location, file Name |
---|---|
Reports standalone server |
Server log file:
Engine log files:
Communication log files:
|
Reports In-process server and Servlet log files |
In-process server:
In-process server engines:
Servlet:
|
Reports Tools log files (Reports Builder, Reports Runtime, Reports Client) |
Runtime:
Communication log files:
|
Reports Bridge log files |
|
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 23-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 |
Errors |
WARNING: 1 |
TRACE_WRN |
Warnings |
NOTIFICATION: 1 |
TRACE_LOG |
Default level |
NOTIFICATION: 16 |
TRACE_STA |
Server / engine state info |
TRACE: 1 |
TRACE_PRF |
Profiling information |
TRACE: 8 |
TRACE_DST |
Functional areas tracing (distribution / font handling / printing etc) |
TRACE: 16 |
TRACE_DBG |
Server - debug traces |
TRACE: 32 |
TRACE_ALL equivalent to |
All trace messages |
The Oracle Reports log files contain the attributes listed in Table 23-5:
Table 23-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 23-6:
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. |
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 6.11.1, "Viewing and Searching Log Files" in Chapter 6, "Administering Oracle Reports Services Using Oracle Enterprise Manager".
See Section 23.3.4, "Logging-Related WLST Commands".
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 23.3.7, "Tracing Report Execution".
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 6.18.1, "Specifying Logging Information" in Chapter 6, "Administering Oracle Reports Services Using Oracle Enterprise Manager".
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
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 6.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"/>
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
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" - - - - - -
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()
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()
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
Use the following WLST commands to configure logs:
Use the following WLST commands to view logs:
You can use the listLoggers
command to view the list of loggers and the level of each logger.
Table 23-7 describes the parameters supported by listLoggers
command.
Table 23-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. |
A PyDictionary object where the keys are logger names and the associated values are the logger levels.
Following are the examples for listLoggers
command:
listLoggers()
listLoggers(pattern="oracle.*")
listLoggers(runtime=0)
listLoggers(target="server1")
Note:
The listLoggers 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.
You can use the getLogLevel
command to obtain the log level for a given logger.
Table 23-8 describes the parameters supported by getLogLevel
command.
Table 23-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. |
The logger level as a string.
The following are the examples of getLogLevel command
getLogLevel(logger="oracle")
getLogLevel(logger="oracle")
getLogLevel(logger="oracle", target="server2")
Note:
The getLogLevelcommand 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.
You can use the setLogLevel
command to set the log level for a given logger.
Table 23-9 describes the parameters supported by the setLogLevel
command.
Table 23-9 Parameters of setLogLevel
parameter | Description |
---|---|
|
The name of a WebLogic server. The default value is the server to which WLST is connected |
|
A logger name. There is not default value. |
|
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. |
|
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. |
|
a Jython boolean value (0 or 1) that determines if the level should be saved to the configuration file. The default value is 1. |
The return value for the setLogLevel
command is none.
The following are the examples for setLogLevel
command.
setLogLevel(logger="oracle.my.logger", level="NOTICATION:1")
setLogLevel(logger="oracle.my.logger", level="TRACE:1", persist=0)
setLogLevel(target="server1", logger="oracle.my.logger", level="WARNING", runtime=0)
Note:
The setLogLevel 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.
You can use the listLogHandlers
command to view the configuration of one or more log handlers.
Table 23-10 describes the parameters supported by listLogHandlers
command.
Table 23-10 Parameters of listLogHandlers
Parameters | Description |
---|---|
|
the name of a WebLogic server. The default value is the server to which WLST is connected. |
|
the name of a log handler. If the name is not provided then all handlers are listed. |
A java.util.List with one entry for each handlers. Each entry is a javax.management.openmbean.CompositeData object describing the handler.
The following are the examples for listLogHandlers
command
listLogHandlers()
listLogHandlers(name="odl-handler")
listLogHandlers(target="server1")
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.
You can use the configureLogHandler
command to configure and existing log handler, add a new log handler, or remove existing handlers.
Table 23-11 describes the parameters supported by configure LogHandler
command.
Table 23-11 Parameters for configure LogHandler
Parameter | Description |
---|---|
|
The name of a WebLogic server. The default value is the server to which WLST is connected. |
|
The name of a log handler |
|
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). |
|
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). |
|
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. |
|
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. |
|
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. |
|
The format for the ODL handler. The value must be the string "ODL-Text" or "ODL-XML". The default format is ODL-Text |
|
the character encoding for the log file. |
|
the log file path. |
|
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. |
|
the name of a handler property to be added or update. The property value is specified with the propertyValue parameter. |
|
the new value for the handler property defined by the propertyName parameter. |
|
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. |
|
a list of one or more handler properties to be removed. |
|
the name of a handler to be added. |
|
the name of a handler to be removed. |
|
a list of logger names. The handler is added to the given logger names. |
|
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.
You can use the listLogs
command to view the list of one or more components.
Table 23-12 describes the parameters supported by listLoggers
command.
Table 23-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 |
A PyArray with one element for each log. The elements of the array are javax.management.openmbean.CompositeData objects describing each log.
Following are the examples for listLoggers
command:
listLogs()
listLogs(target="server1")
listLogs(target="opmn:instance1/ohs1")
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.
You can use the displayLogs
command to view the contents of diagnostic logs.
Table 23-13 describes the parameters supported by listLoggers
command.
Table 23-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 " |
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.
Following are the examples for displayLogs command:
displayLogs(tail=100)
displayLogs(target='opmn:instance1/ohs1', last=60)
displayLogs(groupBy=['COMPONENT_ID', 'MSG_TYPE'])
displayLogs(query='MSG_TYPE equals ERROR or MSG_TEXT contains Exception")
displayLogs(query='APP equals myApp', last=60)
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.
Use the following WLST commands to view metrics.
You can use the displayMetricTableNames
command to view the list of metric table names available, including the native WebLogic metrics.
Table 23-14 describes the parameter for displayMetricTableNames
command.
Table 23-14 Parameter for displayMetricTableNames
Parameter | Description |
---|---|
|
It specifies the managed servers to retrieve metrics from. It is optional. Its possible values are a list of managed server names. |
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.
You can use the dumpMetrics
command to view all available metrics, including native WebLogic metrics and internal DMS metrics.
Table 23-15 describes the parameters supported by dumpMetrics
command
Table 23-15 Parameters for dumpMetrics
Parameter | Description |
---|---|
|
It defines the command output format. It is optional and the possible values are "raw", "xml". The default is "raw" format. |
|
It specifies the managed servers to retrieve metrics from. It is optional and the possible values are a list of managed server names. |
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.
You can use the displayMetricTables
command to view the specified metric tables.
Table 23-16 describes the parameters supported by displayMetricTables
command.
Table 23-16 Parameters for displayMetricTables
Parameter | Description |
---|---|
an optional list of metric table names. It displays all-available metrics by default |
|
|
It defines the metric aggregation parameters. It is optional. Its possible values are a set of name value pairs in a Jython map. |
|
It specifies the managed servers to retrieve metrics from. It is optional. Its possible values are a list of managed server names. |
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.
Use the WLST commands listed in Table 23-17 to view and manage audit policies and the audit repository configuration.
Table 23-17 WLST Audit Commands
Use this command... | To... | Use with WLST... |
---|---|---|
Display audit policy settings. |
Online |
|
Update audit policy settings. |
Online |
|
List audit events for one or all components. |
Online |
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. |
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
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. |
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
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. |
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 ...
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 23-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 23-18 Oracle Reports Builder
Field | Description |
---|---|
Total Elapsed Time |
Time spent in executing the report. |
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 |
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 23-1, focus your tuning efforts on time formatting (Reports Time) the data rather than on querying and fetching it.
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 23-19 lists and describes the information contained in the RW_SERVER_JOB_QUEUE
table:
Table 23-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 |
JOB_NAME |
Job submission name (or file name if no value for JOBNAME is specified). |
STATUS_CODE |
Current status of job. See Table 23-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 23-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 23-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.
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:
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
.
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 7.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
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.8, "SHOWJOBS".
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.
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.
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.
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.
SRW.DO_SQL (`CREATE TABLE' || :tname ||`(ACCOUNT NUMBER NOT NULL PRIMARY KEY, COMP NUMBER (10,2))');
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.
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.