Skip Headers

Oracle9iAS Discoverer Configuration Guide
Version 9.0.2

Part Number A95458-02
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

12
Optimizing Discoverer performance and scalability

12.1 Optimizing Discoverer performance and scalability

This chapter describes how to optimize Discoverer performance and scalability, and contains the following topics:

12.2 About Discoverer and performance

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:

12.3 About Discoverer and scalability

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:

12.4 How to improve Discoverer's performance

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:

12.4.1 How to improve Discoverer performance by using worksheets and page items appropriately

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:

12.4.2 How to improve Discoverer performance by reducing the time taken to display business areas and folders

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.

12.4.3 How to improve Discoverer performance by using summary folders

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.

12.4.4 How to improve Discoverer performance by providing a method to provide a select all parameter values' option that does not use a DECODE statement

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:

  1. Create a custom folder to list all regions and based on the following SQL statement:

    select region from store
    union
    select '<All>' from dual 
    
    
  2. Edit the item class for the Region item so that it uses the above custom folder for its list of values.

  3. Create a parameter for Region in Discoverer Plus.

    For example, 'Region Parameter'.

  4. Create an associated condition in Discoverer Plus as follows:

    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

12.4.5 How to improve Discoverer performance by using Discoverer Administrator hints

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.

12.4.6 How to improve Discoverer performance by setting the Case Storage item property appropriately

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.

12.4.7 How to improve Discoverer performance by increasing the size of the array used to fetch rows from the database

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.

12.4.8 How to improve Discoverer performance by basing lists of values on tables containing distinct values

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:

12.4.9 How to improve Discoverer performance by changing cache settings for your system

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:

Setting name  Default value  Description 

CacheFlushPercentage 

25 

Percentage of cache flushed, if cache is full. 

MaxVirtualDiskMem 

1024000000 

Maximum amount of disk memory allowed for the data cache 

MaxVirtualHeapMemory 

5120000 

Maximum amount of heap memory allowed for the data cache. 

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.

12.4.10 How to improve Discoverer performance by scheduling worksheets to run overnight

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.

12.4.11 How to improve Discoverer performance by using Oracle9iAS Web Cache

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".

12.5 How to take advantage of Discoverer's scalable architecture

You can take advantage of Discoverer's inherently scalable architecture by:

12.5.1 How to take advantage of Discoverer's scalable architecture using the scalability features of Oracle9iAS

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:

12.5.2 How to enhance Discoverer scalability by specifying OC4J memory usage parameters

For more information, see Chapter 6 "Optimizing J2EE Applications in OC4J" in the Oracle9i Application Server Performance Guide.

12.5.3 How to enhance Discoverer scalability by specifying the number of OC4J processes

For more information, see Chapter 6 "Optimizing J2EE Applications in OC4J" in the Oracle9i Application Server Performance Guide.

12.5.4 How to enhance Discoverer Viewer scalability using XSL stylesheet pooling

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:

Setting  Use this setting to:  Default  Allowed 

cache 

specify that you want to use stylesheet pooling 

true 

true or false 

minimum 

specify the minimum number of XSL stylesheets to hold in memory at any given time 

zero or positive integer up to 32768 

maximum 

specify the maximum number of XSL stylesheets to hold in memory at any given time 

zero or positive integer up to 32768 

tolerance 

specify how much to optimize the pool for concurrent processing of user requests for HTML pages:

  • a low number optimizes the number of stylesheets in memory towards the number of concurrent requests (i.e. low memory, low concurrency)

  • a high number optimizes the number of stylesheets in memory towards the number of concurrent user sessions (i.e. high memory, high concurrency)

 

1.0 

floating point number between 0.0 and 0.1

 

period 

specify (in milliseconds) the period over which pool optimization takes place 

-1 

zero or positive integer up to 4294967296

if set to -1, uses algorithm to optimize this period. 

log 

specify that the stylesheet pooling statistics be written to the servlet log file.

Note: This can significantly degrade performance. Do not set this option to true unless performing scalability and performance diagnostics in a non-production environment 

false 

true or false 

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:

Event  Description 

create object 

a stylesheet has just been created 

remove object 

a stylesheet has been removed  

passivate object 

a stylesheet has been passivated (put back in the pool ready for use) 

activate object 

a stylesheet has been activated (taken out of the pool to be used) 

starvation 

a request was unable to retrieve a stylesheet from the pool (because all available stylesheets are active) and could not create one because the pool is at capacity (the request therefore has to wait until a stylesheet is passivated) 

optimize pool 

the optimizer has detected an opportunity to add or remove stylesheets 

The following stylesheet pooling statistics are written to the servlet log file when one of the above events occurs:

Stylesheet pooling information  Description 

active objects 

the number of activated stylesheets (i.e. the stylesheets in use) 

passive objects 

the number of stylesheets ready for use 

total objects 

the number of all created stylesheets (i.e. the total number of active and passive objects) 

peak objects 

the highest number of objects created in the lifetime of the pool (i.e. since the servlet started) 

hits 

the number of pool hits (i.e. the number of times a stylesheet was activated with a passive object)  

misses 

the number of times no passive object was available, the stylesheet had to be created, or had to wait for passivation 

hitrate 

hits / (hits + misses)

i.e. the number of hits divided by the sum of the number of hits and the number of misses (the higher the number, the better) 

optimal objects 

the optimal number of stylesheets to pool (maintained at the number of sessions + 1) 

max capacity  

the capacity of the pool (i.e. the same as the 'maximum' stylesheet pool setting in configuration.xml) 

min capacity  

the minimum number of stylesheets to pool (i.e. the same as the 'minimum' stylesheet pool setting in configuration.xml) 

starvations 

the number of times that starvation events occurred in the lifetime of the pool (i.e. since the servlet started) 

starving threads 

the number of threads currently waiting for a passivation (because the pool is at capacity) 

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 

Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index