10
Optimizing OracleAS Discoverer performance and scalability

10.1 Optimizing OracleAS Discoverer performance and scalability

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

• Section 10.2, "About Discoverer and performance"

• Section 10.3, "About Discoverer and scalability"

• Section 10.4, "How to improve Discoverer performance"

• Section 10.5, "How to take advantage of Discoverer's scalable architecture"

10.2 About Discoverer and performance

The performance of a Discoverer system refers to the time Discoverer takes to complete a specific task. Performance time might be the time taken to:

• return the results of a query (i.e. display a worksheet)

• perform a pivot or drill

• add a new user to the system

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

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 Section 5.1, "Installing OracleAS Discoverer in a multiple machine environment".

The main factors that determine Discoverer's scalability are:

• the number of server CPUs

• the distribution of processing across CPUs

• the total server memory (both RAM and virtual memory)

• the specific tasks requested by each user (individual workload)

• the number of Discoverer middle tier machines

10.4 How to improve Discoverer performance

Discoverer 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:

• summary tables and materialized views

• indexes

• database parameter settings

• network bandwidth

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:

• Section 10.4.1, "How to improve Discoverer performance by using worksheets and page items appropriately"

• Section 10.4.2, "How to improve Discoverer performance by reducing the time taken to display business areas and folders"

• Section 10.4.3, "How to improve Discoverer performance by using summary folders"

• Section 10.4.4, "How to improve query performance by optimizing the SQL that Discoverer generates"

• Section 10.4.5, "How to improve Discoverer performance by providing a 'Select all parameter values' option that does not use a DECODE statement"

• Section 10.4.6, "How to improve Discoverer performance by using Discoverer Administrator hints"

• Section 10.4.7, "How to improve Discoverer performance by setting the Case Storage item property appropriately"

• Section 10.4.8, "How to improve Discoverer performance by increasing the size of the array used to fetch rows from the database"

• Section 10.4.9, "How to improve Discoverer performance by basing lists of values on tables containing distinct values"

• Section 10.4.10, "How to improve Discoverer performance by changing cache settings for your system"

• Section 10.4.11, "How to improve Discoverer performance by scheduling worksheets to run overnight"

• Section 10.4.12, "How to improve Discoverer Viewer performance by using OracleAS"

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

The time that Discoverer takes to query and display data depends on the worksheet layout (i.e. table or crosstab) and whether the worksheet layout uses page items.

For example:

• When populating a tabular worksheet that does not contain page items, Discoverer uses incremental fetch (e.g. retrieves rows 100 at a time) and therefore does not need to load the entire results set, which might be much larger.

  Data for tabular layouts that do not contain page items is displayed faster because Discoverer does not index cached items as it would if the page items were displayed.

• When populating a table worksheet that contains page items or a crosstab worksheet, Discoverer will take longer to display the data, regardless of the number of rows fetched at a time. The data takes longer to display because Discoverer spends additional time creating an index by page item on the cached results set.

To enhance performance, encourage Discoverer Plus users to follow these guidelines when designing Discoverer workbooks:

• use tabular reports rather than cross-tabular reports

• minimize the number of page items in reports

• avoid wide cross tabular report

• avoid creating reports that return tens of thousands of rows

• provide parameters to reduce the amount of data produced

• minimize the number of worksheets in workbooks

• remove extraneous worksheets from workbooks (especially if end users frequently use Discoverer's export option)

  Note: When end users export data in Discoverer Plus or Discoverer Viewer, they can export either the current worksheet or all the worksheets. In other words, they cannot selectively choose the worksheets to be exported. Remove extraneous worksheets so that extra data is not included when end users export all worksheets.

The following worksheet setups all increase the overhead in index creation and therefore affect Discoverer performance:

• wide crosstabs

• page axis items

• page axis items with a large number of values

10.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 in the Discoverer Plus Item Navigator (for more information, see Oracle Application Server Discoverer Plus User's Guide). 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 the user cannot create workbooks that they cannot run, the security check can increase the time taken to display the list, because the check is done for all business areas and folders.

To defer the database security check, edit the pref.txt file and set ObjectsAlwaysAccessible to 1 as follows:

ObjectsAlwaysAccessible = 1

Note: After editing the pref.txt file, you must run the applypreferences script to apply the preference changes you have made, then stop and restart the OracleAS Discoverer service (for more information, see Section 4.4, "About starting and stopping the Discoverer Service").

If the value of ObjectAlwaysAccessible is not 0, Discoverer does not perform the security check at the time that the query is built and assumes that the tables are accessible.

Note: Database security is always respected. The security check made when the query runs, instead of when the query is built.

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 ensures database security when the query runs and no rows are returned.

10.4.3 How to improve Discoverer performance by using summary folders

When used correctly, Discoverer summary folders significantly improve query response times. For example, 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:

• if a Discoverer query includes a summary folder that is based on a materialized view, the database automatically rewrites the query to use the materialized view

• if a Discoverer query includes a summary folder that is based on a summary table, Discoverer automatically rewrites the query to use the summary table (providing the summary table has been registered with the EUL)

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 Oracle Discoverer Administrator Administration Guide.

10.4.4 How to improve query performance by optimizing the SQL that Discoverer generates

To improve query performance Discoverer optimizes the SQL that it generates, in the following three areas:

• item trimming

  Discoverer removes references to irrelevant or unused columns and expressions in the query SQL, improving performance. Item trimming is controlled by the user preference SQLItemTrim.

• join trimming

  Discoverer detects and eliminates joins (where possible) from queries without affecting the result set, which improves query performance. Join trimming is controlled by the user preference SQLJoinTrim.

• flattening

  Discoverer minimizes the use of inline views in the query SQL, which makes it easier for the database to efficiently parse the SQL and select an optimal execution path. Flattening is controlled by the user preference SQLFlatten.

For more information about:

• the user preferences SQLItemTrim, SQLJoinTrim, SQLFlatten, see Section 8.8, "List of Discoverer user preferences".

• the join properties to choose to improve performance, see the Oracle Discoverer Administrator Administration Guide.

10.4.5 How to improve Discoverer performance by providing 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 for a database index to be used (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 as a parameter value, you might:

1. Create a custom folder to list all regions 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 Discoverer end 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 Oracle Discoverer Administrator Administration Guide. For more information about how to create a parameter, see Oracle Application Server Discoverer Plus User's Guide

10.4.6 How to improve Discoverer performance by using Discoverer Administrator hints

To improve Discoverer performance, you can add hints to SQL statements (e.g. using a custom folder) in Discoverer Administrator to force the database optimizer to use a specific path. For more information, see Oracle Discoverer Administrator Administration Guide.

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

10.4.8 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:

RowsPerFetch = <array size>

If you anticipate Discoverer retrieving thousands of rows, set RowsPerFetch to 500 or 1000 to increase the size of the array.

Note: Discoverer end users can override the default value specified by RowsPerFetch in pref.txt as follows:

• in Discoverer Plus, by choosing Tools | Options | Query Governor

• in Discoverer Viewer, by choosing Options

Notes

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

• After editing the pref.txt file, you must run the applypreferences script to apply the preference changes you have made, then stop and restart the OracleAS Discoverer service (for more information, see Section 4.4, "About starting and stopping the Discoverer Service").

10.4.9 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 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:

• Create LOVs for items based on columns in small 'dimension' tables (containing only distinct allowable values) attached to the fact table. If such tables do not already exist, create and populate them using SQL*PLus.

• If the list of allowable values is small and/or changes relatively infrequently, define the LOV within Discoverer Administrator by creating a custom folder based on SQL statements that select the allowable values from SYS.DUAL.

  For example, to create an LOV that contains all regions in the Video Stores data, you might:

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

     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
     
     

  2. Edit the item class for the Region item so that it uses the above custom folder for its list of values.

     For more information about how to create custom folders and item classes, see Oracle Discoverer Administrator Administration Guide.

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

Data retrieved from the database is stored in a middle tier Discoverer cache for each user session. 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 (for more information about the location of configuration files, see Section A.2, "List of Discoverer file locations"):

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 Discoverer preferences command line utility dis51pr or dis51pre (for more information, see Section 8.7, "How to set individual preferences for specific users").

Notes

• Note: After editing the pref.txt file, you must run the applypreferences script to apply the preference changes you have made, then stop and restart the OracleAS Discoverer service (for more information, see Section 4.4, "About starting and stopping the Discoverer Service").

• 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 OracleAS applications.

10.4.11 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, which avoids overburdening the server during peak times. For more information about scheduling workbooks, see the Oracle Application Server Discoverer Plus User's Guide.

10.4.12 How to improve Discoverer Viewer performance by using OracleAS

If your workbooks remain relatively stable, Web Cache can greatly improve Discoverer Viewer performance. For more information about OracleAS Web Cache, see Section 6.1, "Using OracleAS Discoverer Viewer with OracleAS Web Cache".

10.5 How to take advantage of Discoverer's scalable architecture

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

• exploiting the scalability features of OracleAS (for more information, see Section 10.5.1, "How to take advantage of Discoverer's scalable architecture using the scalability features of OracleAS")

• making use of Discoverer Viewer scalability features (for more information, see Section 10.5.2, "How to enhance Discoverer Viewer scalability using XSL stylesheet pooling")

10.5.1 How to take advantage of Discoverer's scalable architecture using the scalability features of OracleAS

The scalable architecture of OracleAS enables you to install the Discoverer Services tier on multiple machines (for more information about installing OracleAS Discoverer on multiple machines, see Chapter 5.1, "Installing OracleAS Discoverer in a multiple machine environment").

You can balance the load between the different machines using:

• OracleAS Web Cache

• standard commercial HTTP/IP Router load balancers

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

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

10.5.1.2 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 Oracle Application Server Performance Guide.

10.5.2 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 can be both CPU and memory intensive.

Note: XSL stylesheet pooling is also used by Discoverer Portlet Provider. For more information about Discoverer Portlet Provider, see Section 9.1, "Using OracleAS Discoverer with OracleAS 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 can 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.

Stylesheet pooling settings are held in the configuration.xml file and are shown in the table below (for more information about the location of configuration files, see Section A.2, "List of Discoverer file locations"):

Setting	Use this setting to:	Default	Allowed	Recommended
cache	specify that you want to use stylesheet pooling	true	true or false	true
log	specify that stylesheet pooling statistics are 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	false
maximum	specify the maximum number of XSL stylesheets to hold in memory at any given time Note: Oracle recommends that the maximum value equals the number of CPUs on the machine.	1	zero or positive integer up to 32768	1 per CPU
minimum	specify the minimum number of XSL stylesheets to hold in memory at any given time	1	zero or positive integer up to 32768	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.	-1
retirementAge	specify how many times a stylesheet is used before it is discarded. Use this value to control Discoverer's memory usage. Suggested values are between 50 and 100.	0	zero or positive integer up to 32768 Note: 0 = do not retire.	0
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	floating point number between 0.0 and 1	1

The text below shows an extract from a configuration.xml file showing XSL example XSL 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" retirementAge="50" /> 
  </xsl_translator>

10.5.2.1 About the stylesheet pooling statistics in the servlet log file

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
activate object	a stylesheet has been activated (taken out of the pool to be used)
create object	a stylesheet has just been created
optimize pool	the optimizer has detected an opportunity to add or remove stylesheets
passivate object	a stylesheet has been passivated (put back in the pool ready for use)
remove object	a stylesheet has been removed
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)

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)
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)
hits	the number of pool hits (i.e. the number of times a stylesheet was activated with a passive object)
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)
misses	the number of times no passive object was available, the stylesheet had to be created, or had to wait for passivation
optimal objects	the optimal number of stylesheets to pool (maintained at the number of sessions + 1)
passive objects	the number of stylesheets ready for use
peak objects	the highest number of objects created in the lifetime of the pool (i.e. since the servlet started)
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)
total objects	the number of all created stylesheets (i.e. the total number of active and passive objects)

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