Version 9.0.2
Part Number A95458-02
 Home |  Solution Area |  Contents |
 Index |
| Oracle9iAS Discoverer Configuration Guide Version 9.0.2 Part Number A95458-02 |
|
This chapter describes how to optimize Discoverer performance and scalability, and contains the following topics:
The performance of a Discoverer system refers to the time Discoverer takes to complete a specific task. This might be the time taken to return the results of a query, to perform a pivot or drill, or to add a new user to the system.
An example of a performance issue might be if Discoverer cannot complete a specified task for a given number of users in an acceptable time.
The main factors that determine Discoverer's performance are:
The scalability of a Discoverer system refers to Discoverer's ability to handle increasing numbers of users or tasks without compromising performance.
An example of a scalability issue might be if Discoverer's performance degrades as more users attempt the same task.
To take advantage of Discoverer's inherently scalable architecture, you can install Discoverer on multiple machines and share the workload between those machines. For more information, see Chapter 5.1, "Installing Oracle9iAS Discoverer in a multiple machine environment".
The main factors that determine Discoverer's scalability are:
Discoverer's performance is largely determined by how well the database has been designed and tuned for queries. A well-designed database will perform significantly better than a poorly designed database. In addition, you can achieve significant performance improvements by making appropriate use of:
For more information about tuning the database for queries, see Oracle8i Designing and Tuning for Performance Release 2 (8.1.6).
In addition to a well-designed and well-tuned database, there are some things you can do within Discoverer to improve performance, as described in the following sections:
The time that Discoverer takes to fetch and display data can differ depending on the type of worksheet (i.e. table or crosstab) and whether or not the worksheet uses page items.
For example:
To enhance performance, try to avoid the following:
To enhance performance, try to do the following:
When a Discoverer Plus end user builds a query, Discoverer displays a list of the business areas, folders, and items to which that user has access. Before displaying the list, Discoverer makes a database security check to confirm that the user has access to the tables referenced in the folders. Although the security check makes sure that user cannot create workbooks that they cannot run, it can increase the time taken to display the list.
To override the database security check, edit the pref.txt file and set ObjectsAlwaysAccessible to 1 as follows:
If the value of ObjectAlwaysAccessible is not 0, Discoverer does not perform the security check and assumes that the tables are accessible. As a result, Discoverer displays the list of folders more quickly. Disabling the security check is likely to be more appropriate on systems where users' access rights change relatively infrequently.
Note: If a user selects a folder based on a table to which they do not have database access, Discoverer always displays an error message when the query runs.
When used correctly, Discoverer summary folders improve query response times by several orders of magnitude. Queries that use summary folders might take only seconds to run, whereas queries that return the same result set but do not use summary folders might take several hours. Summary folder management is the key to good performance with Discoverer implementations.
Summary folders can be based on materialized views or summary tables, with the following differences in behavior:
Use the SQL Inspector dialog to view the path taken by the query, and to find out whether the database has rewritten the query.
For more information about how Discoverer manages summary folders, see Oracle9i Discoverer Administrator Administration Guide.
By default, Discoverer does not provide users with a 'select all parameter values' option for a worksheet parameter. It is possible to provide users with a 'select all parameter values' option by placing a DECODE function around the parameter. However, using a DECODE function makes it impossible to use a database index (and the performance benefits of using an index are therefore not available).
An alternative approach (which is equally effective and which does not prevent the use of database indexes) is to create a custom folder based on SQL that includes a 'SELECT '<ALL>' from DUAL' statement.
For example, to give users the ability to select all regions from the Region dimension of the Video Stores data, you might:
select region from store union select '<All>' from dual
For example, 'Region Parameter'.
Region IN :Region Parameter OR :Region Parameter = '<All>'
When a user includes the Region item in a worksheet, the user can select regions from the LOV or can select the <All> value to select all regions.
For more information about how to create a custom folder, how to create a condition, and how to edit an item class, see Oracle9i Discoverer Administrator Administration Guide. For more information about how to create a parameter, see Oracle9iAS Discoverer Plus User's Guide
To improve Discoverer performance, you can add hints to SQL statements in Discoverer Administrator to force the database optimizer to use a specific path. For more information, see Oracle9i Discoverer Administrator Administration Guide.
When a user adds a condition to a query, they can select the Match Case option. If the Match Case option is cleared, Discoverer performs a case insensitive search by placing an Upper function around both sides of the condition. For example, a condition such as:
where Department in ('VIDEO SALES', 'VIDEO RENTALS')
becomes:
where Upper(Department) in (Upper('VIDEO SALES'), Upper('VIDEO RENTALS'))
However, using an UPPER function makes it impossible to use a database index (and the performance benefits of using an index are therefore not available).
If you know that the data is stored in the database as all uppercase or all lowercase, you can manage this issue by using Discoverer Administrator to set the Case Storage property of items.
For example, you might know that all the Region data is stored in uppercase in the database. In Discoverer Administrator, set the Case Storage property of the Region item to Uppercase. Discoverer now assumes that the data is stored in uppercase and does not put the UPPER function on the left hand side of the condition (i.e. around the column name). So the original condition:
where Department in ('VIDEO SALES', 'VIDEO RENTALS')
becomes:
where Department in (Upper('VIDEO SALES'), Upper('VIDEO RENTALS'))
Because the UPPER function is not around the column name, the query will use whatever database indexes are available.
If it is likely that Discoverer will fetch large numbers of rows (e.g. in the order of thousands) from the database, you can improve performance by increasing the size of the array that Discoverer uses to fetch the rows.
To change the default size of the array, edit the pref.txt file and set RowsPerFetch to the required value, as follows:
If you anticipate Discoverer retrieving thousands of rows, set RowsPerFetch to 500 or 1000 to increase the size of the array.
Note that Discoverer end users can override the default value specified by RowsPerFetch in pref.txt as follows:
Note: In the case of table worksheets, there is a trade off between perceived performance and actual performance. Table worksheets display the data immediately after the first array is retrieved. If RowsPerFetch is set to 100, a Discoverer end user sees the first 100 rows more quickly than if RowsPerFetch is set to 1000.
By default, a Discoverer list of values (LOV) is populated using a SELECT DISTINCT statement in the query on the underlying data (or 'fact') table. To populate the LOV, all of the rows must be scanned before the list of distinct values can be displayed. However, this default query is inefficient if the LOV is populated from a column that has a large number of rows but relatively few distinct values.
To improve performance, avoid creating LOVs on items based on columns in the fact table. Instead, consider the following options:
For example, to create an LOV that contains all regions in the Video Stores data, you might:
Select `NORTH' Region FROM sys.dual UNION Select `SOUTH' Region FROM sys.dual UNION Select `EAST' Region FROM sys.dual UNION Select `WEST' Region FROM sys.dual
For more information about how to create custom folders and item classes, see Oracle9i Discoverer Administrator Administration Guide.
Data retrieved from the database is stored in the Discoverer cache. The cache supports Discoverer's rotation, drilling, and local calculation capabilities.
You can control the performance of the cache using the following settings in the pref.txt file:
The default settings for the cache are large to enable Discoverer to take advantage of the available memory. If the system has more resources available, you can increase the default memory values (although this is only likely to be beneficial for users whose queries return large result sets). Note that the default values are the requirements for each user, but you can change the values for specific users using the dis51pr command line utility (for more information, see Section 7.6, "How to set individual preferences for specific users").
Be aware that an increase in the system resources available to one Discoverer user might have a detrimental impact on other Discoverer users, or on users of other applications.
If your data needs to be accurate on a daily basis, you can improve Discoverer performance by scheduling worksheets to be processed at off-peak times, avoiding overburdening the server during peak times. For more information about scheduling workbooks, see the Oracle9iAS Discoverer Plus User's Guide.
If your workbooks remain relatively stable, Web Cache can greatly improve Discoverer performance. For more information about Oracle9iAS Web Cache, see Section 9.1, "Using Discoverer with Oracle9iAS Web Cache".
You can take advantage of Discoverer's inherently scalable architecture by:
The scalable architecture of Oracle9iAS enables you to install the Discoverer Services tier on multiple machines (for more information about installing Oracle9iAS Discoverer on multiple machines, see Chapter 5.1, "Installing Oracle9iAS Discoverer in a multiple machine environment").
You can balance the load between the different machines using:
To further enhance load balancing, you can run multiple instances of Oracle HTTP Server and OC4J. For more information about:
For more information, see Chapter 6 "Optimizing J2EE Applications in OC4J" in the Oracle9i Application Server Performance Guide.
For more information, see Chapter 6 "Optimizing J2EE Applications in OC4J" in the Oracle9i Application Server Performance Guide.
Discoverer Viewer uses XSL stylesheets to define the layout of Discoverer Viewer pages. However, processing and storing XSL stylesheets is both CPU and memory intensive.
Note: XSL stylesheet pooling is also used by the Discoverer Portlet Provider. For more information about the Discoverer Portlet Provider, see Section 11.1, "Using Discoverer with Oracle9iAS Portal".
Pooling XSL stylesheets enables one or more Discoverer Viewer sessions to share one or more stylesheets, significantly reducing CPU usage, memory consumption, and transaction time. Pooling XSL stylesheets also increases the number of concurrent Discoverer Viewer users that the Discoverer Services tier will support. In addition, stylesheet pooling provides control over exactly how many stylesheets are in memory, and therefore how many requests for HTML pages can be processed at any given time.
The text below shows an extract from a configuration.xml file showing XSL example stylesheet pooling settings:
<!-- configuration for xsl processing. --> - <xsl_translator path="../common/xsl/discoverer.xsl" processor="oracle" cache="true"> <stylesheet_pool minimum="1" maximum="1" log="false" tolerance="1.0" period="-1" /> </xsl_translator>
Stylesheet pooling settings are held in the configuration.xml file and are shown in the table below:
If you set log=true in the configuration.xml file, stylesheet pooling statistics are written to the servlet log file when one or more of the following events occur:
The following stylesheet pooling statistics are written to the servlet log file when one of the above events occurs:
For example, you might see the following stylesheet pooling information output to the servlet log file:
discoverer pool: optimize pool removing 1 objects discoverer pool: remove object oracle.xml.parser.v2.XSLStylesheet@b9bc9519 discoverer pool: dumpstats=http://machine.oracle.com/viewer/discoverer.xsl active objects=0 passive objects=2 total objects=2 peak objects=3 hits=7 misses=0 hit rate=100 optimal objects=1 maximum capacity=5 minimum capacity=1 starvations=0 starving threads=0
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|
