This chapter only applies to Discoverer Plus and Discoverer Viewer. For more information about configuring Discoverer Plus OLAP, see Chapter 5, "Configuring Discoverer Plus OLAP".
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. Performance time might be the time taken to:
return the results of a query (that is, display a worksheet)
perform a pivot or drill
add a new user to the system
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 Chapter 6, "Managing Discoverer in a Multi-Machine or Multi-Instance 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
Discoverer performance is largely determined by how well the database has been designed and tuned for queries. A well designed database performs 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.3.3, "How to improve Discoverer performance by using summary folders"
Section 10.3.4, "How to improve query performance by optimizing the SQL that Discoverer generates"
Section 10.3.5, "How to improve Discoverer performance by using Discoverer Administrator hints"
Section 10.3.9, "How to improve Discoverer performance by changing cache settings for your system"
Section 10.3.10, "How to improve Discoverer performance by scheduling worksheets to run overnight"
Section 10.3.11, "How to improve Discoverer Viewer performance by using Oracle Web Cache"
Section 10.3.12, "How to improve Discoverer Portlet Provider performance"
Section 10.3.13, "Troubleshooting Discoverer performance and scalability"
The time that Discoverer takes to query and display data depends on the worksheet layout (that is, 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 (for example, retrieves rows 100 at a time) and therefore does not have 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 takes 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
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 Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus). 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 9.4, "How to set default user preferences for all users"). Then stop and restart the Oracle BI Discoverer Service (for more information, see Section 4.3, "How to start, stop, and restart the Discoverer preferences server component using opmnctl").
If the value of ObjectAlwaysAccessible is not 0, Discoverer does not perform the security check when 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.
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 Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer.
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 9.6, "List of Discoverer user preferences"
the join properties to choose to improve performance, see the Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer
To improve Discoverer performance, you can add hints to SQL statements (for example, using a custom folder) in Discoverer Administrator to force the database optimizer to use a specific path. For more information, see Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer.
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 (that is, 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 uses whatever database indexes are available.
If it is likely that Discoverer fetches large numbers of rows (for example, 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 MaxRowsPerFetch to the required value, as follows:
MaxRowsPerFetch = <array size>
If you anticipate Discoverer retrieving thousands of rows, set MaxRowsPerFetch to 500 or 1000 to increase the size of the array.
Note: Discoverer end users can override the default value specified by MaxRowsPerFetch 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
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 MaxRowsPerFetch is set to 100, a Discoverer end user sees the first 100 rows more quickly than if MaxRowsPerFetch 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 9.4, "How to set default user preferences for all users"). Then stop and restart the Oracle BI Discoverer service (for more information, see Section 4.3, "How to start, stop, and restart the Discoverer preferences server component using opmnctl").
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 exist, create and populate them using SQL*PLus.
If the list of allowable values is small 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:
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
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 Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer.
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, "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 |
| MaxVirtualHeapMem | 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 (for more information, see Section 9.5, "How to set individual preferences for specific users").
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 Oracle BI Discoverer service (for more information, see Section 4.3, "How to start, stop, and restart the Discoverer preferences server component using opmnctl").
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 Oracle Fusion Middleware 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, which avoids overburdening the server during peak times. For more information about scheduling workbooks, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.
If your workbooks remain relatively stable, Oracle Web Cache can greatly improve Discoverer Viewer performance. For more information about Oracle Web Cache, see Chapter 7, "Using Discoverer Viewer with Oracle Web Cache".
To improve the performance of Discoverer Portlet Provider, you adjust the value of the following settings on the Portlet Provider Configuration page in Oracle Enterprise Manager Fusion Middleware Control:
Maximum Sessions (for example, 20)
Maximum Session Inactivity (for example, 10)
Maximum Session Age (for example, 2)
Maximum Wait Time (for example, 120)
For more information, see Section 4.6.1, "How to configure 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, Discoverer Portlet Provider and Discoverer Web Services) by setting the value of the maxSessions setting in the <server> <activation> section in configuration.xml.
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 9.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 9.4, "How to set default user preferences for all users"). For more information about query prediction, see Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer.
Ensure that summaries are refreshed when necessary in Discoverer Administrator. For more information, see Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer.
Increase the amount of memory available for the Discoverer data cache (using the MaxVirtualDiskMem preference in pref.txt). For more information, see Section 9.4, "How to set default user preferences for all users".
If Discoverer's workbook dialogs are slow to update (for example, the Open Workbook from Database dialog), ensure 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 (for example, the Share Workbook dialog in Discoverer Plus Relational), ensure 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 11.3, "How to view Discoverer log files using Fusion Middleware Control" and Section 11.4, "How to set the log level for Discoverer component loggers".
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.
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 Oracle BI machine and the client browser machine. For example, using Discoverer over a LAN is typically quicker than using Discoverer over the Internet.
The scalable architecture of Oracle enables you to install the Discoverer Services tier on multiple machines (for more information about installing Oracle BI Discoverer on multiple machines, see Chapter 6, "Managing Discoverer in a Multi-Machine or Multi-Instance Environment").
You can balance the load between the different machines using:
Oracle Web Cache
Standard commercial HTTP/IP Router load balancers
For more information, see Oracle Fusion Middleware Performance and Tuning Guide.