12 Optimizing OracleBI Discoverer performance and scalability

12.3.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 crosstab reports

• minimize the number of page items in reports

• avoid wide crosstab reports

• 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

12.3.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 Business Intelligence 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 (for more information, see Section 10.4, "How to set default user preferences for all users"). Then stop and restart the OracleBI Discoverer service (for more information, see Section 5.3, "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 is 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.

12.3.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 Business Intelligence Discoverer Administration Guide.

12.3.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. To enable item trimming, set the value of the user preference SQLItemTrim to 1.

• join trimming

  Discoverer detects and eliminates joins (where possible) from queries without affecting the result set, which improves query performance. To enable join trimming, set the value of the user preference SQLJoinTrim to 1.

• 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. To enable flattening, set the value of the user preference SQLFlatten to 1.

For more information about:

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

• the join properties to choose to improve performance, see the Oracle Business Intelligence Discoverer Administration Guide

12.3.5 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 Business Intelligence Discoverer Administration Guide.

12.3.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.3.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:

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, and using the Retrieve data incrementally in groups of field

• in Discoverer Viewer, by selecting the Preferences link to display the Preferences page, and using the Retrieve data incrementally in groups of field

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 (for more information, see Section 10.4, "How to set default user preferences for all users"). Then stop and restart the OracleBI Discoverer service (for more information, see Section 5.3, "About starting and stopping the Discoverer Service").

12.3.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 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 Business Intelligence Discoverer Administration Guide.

12.3.9 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.1, "List of Discoverer file locations"):

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 (for more information, see Section 10.5, "How to set individual preferences for specific users").

Notes

• 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 OracleBI Discoverer service (for more information, see Section 5.3, "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.

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

12.3.11 How to improve Discoverer Viewer performance by using OracleAS Web Cache

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

12.3.12 How to improve Discoverer Portlet Provider performance

To improve the performance of Discoverer Portlet Provider, you adjust the value of the following settings on the Discoverer Portlet Provider Configuration page in Oracle Application Server Control:

• Maximum Sessions (e.g. 20)

• Maximum Session Inactivity (e.g. 10)

• Maximum Session Age (e.g. 2)

• Maximum Wait Time (e.g. 120)

For more information, see Section 5.6.1, "How to change configuration options for individual Discoverer client tier components".

Note: You can also specify the maximum number of Discoverer sessions that can run at the same time (for Discoverer Plus, Discoverer Viewer, and Discoverer Portlet Provider) by setting the value of the maxprocs setting in opmn.xml (for more information, see Section A.2, "List of configuration settings in configuration.xml" and Section A.3, "List of configuration settings in opmn.xml").

12.3.13 Troubleshooting Discoverer performance and scalability

If you have tried the performance tips in this section and still have performance issues, you might want to try one or more of the following:

• If Discoverer's query prediction feature is affecting Discoverer's performance, first try changing the query prediction mode to use an explain plan. If changing the query prediction mode to use an explain plan does not improve performance, turn off query prediction.

  To change query prediction mode to use an explain plan in Discoverer Plus, set the value of the QPPObtainCostMethod preference to 0 (for more information, see Section 10.4, "How to set default user preferences for all users").

  To turn off query prediction in Discoverer Plus, set the value of the QPPEnable preference to 0 ( for more information, see Section 10.4, "How to set default user preferences for all users"). For more information about query prediction, see Oracle Business Intelligence Discoverer Administration Guide.

• Make sure that summaries are refreshed when necessary in Discoverer Administrator. For more information, see Oracle Business Intelligence Discoverer Administration Guide.

• Increase the amount of memory available for the Discoverer data cache (using the MaxVirtualDiskMem preference in pref.txt). For more information, see Section 10.4, "How to set default user preferences for all users".

• If Discoverer's workbook dialogs are slow to update (e.g. the Open Workbook from Database dialog), make sure that Discoverer end users delete their old workbooks and worksheets when they are no longer required.

• If Discoverer's user dialogs are slow to update (e.g. the Share Workbook dialog in Discoverer Plus Relational), make sure that the Discoverer manager removes old database accounts and roles, when they are no longer required.

• Minimize the amount of Discoverer log information being recorded. For more information about how to change the amount of log information recorded, see Section D.2.5, "How to enable the Discoverer Services log file" and Section D.2.6, "How to enable the Discoverer Servlet log files".

• If the Discoverer server is under-performing, you might want to increase the amount of memory available or the amount of swap space on the Discoverer middle tier machine. For more information, refer to the operating system documentation for the Discoverer middle tier machine.

Notes

• Some Discoverer EUL Command Line for Java commands can take longer to run than their equivalent command or operation in Discoverer Administrator.

• When diagnosing Discoverer performance issues, be aware that performance is affected by the network type being used, and the distance between the OracleBI machine and the client browser machine. For example, using Discoverer over a LAN will typically be quicker than using Discoverer over the Internet.

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

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