| Oracle® Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10541-01 |
|
 Previous |
 Next |
| Oracle® Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10541-01 |
|
Previous |
Next |
This chapter provides information about ways to improve Oracle Business Intelligence query performance, including a performance tuning overview and information about monitoring system metrics. It also describes the how to manage and use the query cache, a feature that enables the Oracle BI Server to save the results of a query in cache files and then reuse those results later when a similar query is requested. Using cache, the cost of database processing only needs to be paid once for a query, not every time the query is run.
This chapter contains the following topics:
Section 7.3, "Setting Performance Parameters in Fusion Middleware Control"
Section 7.8, "Cache Event Processing with an Event Polling Table"
Section 7.9, "Managing the Oracle BI Presentation Services Cache Settings"
Understanding service levels typically involves monitoring process state and viewing system metrics.
Oracle Business Intelligence automatically and continuously measures run-time performance in real time. The performance metrics are automatically enabled; you do not need to set options or perform any extra configuration to collect them.
System metrics are available in Fusion Middleware Control for system components within a given Oracle Business Intelligence installation. If you encounter a problem, such as an application that is running slowly or is hanging, then you can view more detailed performance information to learn more information about the problem.
You can use WSLT commands to periodically save metric information to a file so that you have a record of past metric values. See "DMS Custom WLST Commands" in Oracle Fusion Middleware WebLogic Scripting Tool Command Reference for more information.
You can also view metrics for Java components using the Oracle WebLogic Server Administration Console.
This section contains the following topics:
Section 7.1.1, "Using Fusion Middleware Control to View Common Performance Metrics"
Section 7.1.2, "Using Fusion Middleware Control to View All Oracle Business Intelligence Metrics"
Section 7.1.3, "Using the Administration Console to View Metrics for Java Components"
You can access the most commonly viewed performance metrics from the Metrics tab of the Capacity Management page.
To use Fusion Middleware Control to view common performance metrics:
Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."
Display the Metrics tab of the Capacity Management page.
On the Metrics tab, you can view metrics that are related to responsiveness, load, and reliability. Click the Help button on the page to access the page-level help for the following metrics:
Request Processing Time (ms)
SOA Request Processing Time (ms)
Average Query Time (seconds)
Active Sessions
Requests (per minute)
SOA Requests (per minute)
Presentation Server Requests (per second)
Server Queries (per second)
Failed Queries
Errors Reported (in the last hour)
The metrics that are displayed on this tab enable you to determine the current responsiveness, load, and reliability for Oracle Business Intelligence components across the entire cluster.
You can view and graph all the available Oracle Business Intelligence metrics from the Performance Summary page in Fusion Middleware Control. The data is logged transiently (that is, logging starts when you go to the page and select a particular metric for display).
To use Fusion Middleware Control to view all performance metrics for Oracle Business Intelligence:
In the tree navigator, expand the Business Intelligence folder and right-click the coreapplication node.
Select Monitoring, then select Performance. The Performance Summary page appears, displaying a selection of metrics for this Oracle Business Intelligence installation.
|
Note: Alternatively, to view the Performance Summary page, you can go to the Metrics page of the Capacity Management tab and click View the full set of system metrics. |
To customize the metrics that are displayed on the Performance Summary page, click Show Metric Palette. Then, expand the appropriate metric category and select or deselect individual metrics. The metrics that you select are displayed on the Performance Summary page.
For information about a particular metric, right-click the metric and select Help.
Use the Administration Console to view metrics for Java components. You can view metrics on the Monitoring tab for the selected Managed Server, or you can use the Metric Browser.
To view metrics for Oracle Business Intelligence Managed Servers:
Log in to the Administration Console.
Expand the Environment node in the Domain Structure window.
Click Servers. The Summary of Servers page is displayed.
Click the Managed Server name (for example, oracle_bi1).
Click the Monitoring tab.
Click Help for more information about the metrics displayed on this tab.
To access the Administration Console Metric Browser:
Log in to the Administration Console.
Click Monitoring Dashboard under Charts and Graphs.
Click the Metric Browser tab.
Click Help for more information about using the Metric Browser.
This section describes some important considerations for improving query performance with the Oracle BI Server.
The following list summarizes methods that you can use to improve query performance:
Tuning and indexing underlying databases: For Oracle BI Server database queries to return in a timely manner, the underlying databases must be configured, tuned, and indexed correctly. Note that different database products have different tuning considerations.
If there are queries that return slowly from the underlying databases, then you can capture the SQL statements for the queries in the query log and provide them to your DBA for analysis. See Section 8.3, "Managing the Query Log" for more information about configuring query logging on the system.
Aggregate tables: It is extremely important to use aggregate tables to improve query performance. Aggregate tables contain precalculated summarizations of data. It is much faster to retrieve an answer from an aggregate table than to recompute the answer from thousands of rows of detail.
The Oracle BI Server uses aggregate tables automatically, if they have been properly specified in the repository. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for examples of setting up aggregate navigation.
Query caching: The Oracle BI Server can store query results for reuse by subsequent queries. Query caching can dramatically improve the apparent performance of the system for users, particularly for commonly-used dashboards, but it does not improve performance for most ad-hoc analysis.
See Section 7.4, "About the Oracle BI Server Query Cache" for more information about query caching concepts and setup.
Setting parameters in Fusion Middleware Control: You can set various performance configuration parameters using Fusion Middleware Control to improve system performance. See Section 7.3, "Setting Performance Parameters in Fusion Middleware Control" for more information.
Setting parameters in NQSConfig.INI: The NQSConfig.INI file contains additional configuration and tuning parameters for the Oracle BI Server, including parameters to configure disk space for temporary storage, set virtual table page sizes, and a number of other advanced configuration settings. See Appendix A, "NQSConfig.INI File Configuration Settings" for more information.
You can also improve the overall performance of the system by increasing throughput by scaling out system components. See Chapter 5, "Scaling Your Deployment" for more information.
This section describes performance options that you can set in Fusion Middleware Control.
This section contains the following topics:
Section 7.3.1, "Using Fusion Middleware Control to Disallow RPD Updates"
Section 7.3.2, "Using Fusion Middleware Control to Set the User Session Log-Off Period"
You can use Fusion Middleware Control to allow or disallow updates to the default repository file. Setting this parameter affects whether you can update the repository when the Administration Tool connects in both online and offline mode. It also affects whether you can perform other repository update operations using other utilities, such as biserverxmlcli. Note that aggregate persistence feature is not available when repository updates are disallowed.
Disallowing repository updates can improve Oracle BI Server performance, because in this mode, the Oracle BI Server does not need to handle lock control.
If you choose to disallow repository updates, then when the Administration Tool opens a repository in either online or offline mode, a message informs the user that the repository is read-only.
Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."
To use Fusion Middleware Control to disallow repository updates:
Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."
Display the Performance tab of the Capacity Management page.
Click Lock and Edit Configuration to allow changes to be made.
Select Disallow Online RPD Updates to disallow updates to the repository file.
Click the Help button on the page to access the page-level help.
Click Apply, then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
For information on using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 30, "Introducing the Oracle BI Systems Management API."
You can override the time to elapse, in minutes, before a user is automatically logged off. Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."
To use Fusion Middleware Control to set the client session log-off period:
Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."
Display the Performance tab of the Capacity Management page.
Click Lock and Edit Configuration to enable changes to be made.
Complete the User Session Expiry option using the description in the Help topic for the page.
Click the Help button on the page to access the page-level help for the box.
Click Apply, then click Activate Changes to execute your changes and release the lock to enable another system administrator to make changes.
Return to the Business Intelligence Overview page and click Restart.
For information on using methods in the Oracle BI Systems Management API to change settings, see Chapter 30, "Introducing the Oracle BI Systems Management API."
Advanced configuration settings are described in Section 19.3, "Configuring for Displaying and Processing Data in Views."
Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."
To use Fusion Middleware Control to set configuration options for views:
Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."
Display the Performance tab of the Capacity Management page.
Click Lock and Edit Configuration to enable changes to be made.
Complete the elements using the descriptions in the Help topic for the page. Click the Help button on the page to access the page-level help for the following options:
Maximum Number of Rows to Download to Excel option
Maximum Number of Rows Per Page to Include in Email option
Click Apply, then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
For information on using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 30, "Introducing the Oracle BI Systems Management API."
You can override the maximum number of rows that can be fetched and processed from the Oracle BI Server for rendering a table. Reducing the number of rows in a table can significantly improve performance by reducing the system resources that can be consumed by a given user session.
Advanced configuration settings are described in Section 19.3, "Configuring for Displaying and Processing Data in Views."
Note the following when setting this value:
This specification applies to tables, not to pivot tables.
The default value is 65000. The minimum value is 50. If the user exceeds the maximum value, then the server returns an error message when the table view is rendered. The maximum value is at least 16 bits, which varies by platform. The system will likely consume all its memory before approaching a number larger than this value.
Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."
To use Fusion Middleware Control to set the maximum number of rows that are processed to render a table:
Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."
Display the Performance tab of the Capacity Management page.
Click Lock and Edit Configuration to enable changes to be made.
Complete the Maximum Number of Rows Processed to Render A Table View option using the description in the Help topic for the page. Enter an integer value greater than 50.
Click the Help button on the page to access the page-level help for the box.
Click Apply, then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
For information on using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 30, "Introducing the Oracle BI Systems Management API."
You can configure the Oracle BI Server to maintain a local, disk-based cache of query result sets (query cache). The query cache allows the Oracle BI Server to satisfy many subsequent query requests without having to access back-end data sources (such as Oracle or DB2). This reduction in communication costs can dramatically decrease query response time.
As updates occur on the back-end databases, the query cache entries can become stale. Therefore, you must periodically remove entries from the query cache using one of the following methods:
Manually. In the Oracle BI Administration Tool, in the Manage menu, select Cache to open the Cache Manager. The Cache Manager provides the most flexibility in choosing which cache entries to purge and when to purge them, but it requires manual intervention. See Section 7.7.4, "Using the Cache Manager" for more information.
Automatically. In the Administration Tool, you can disable cache for the system, set caching attributes for a specific physical table, and use Oracle Business Intelligence event tables to purge cache automatically. See Section 7.6, "Monitoring and Managing the Cache" for additional information.
Programmatically. The Oracle BI Server provides ODBC-extension functions for purging cache entries programmatically. These functions give you the choice and the timing flexibility of the Cache Manager with the automation of event tables. You can write your own scripts to call these functions at times that fit your needs. See Section 7.6.2, "Purging and Maintaining Cache Using ODBC Procedures" for more information.
The parameters that control query caching are located in Fusion Middleware Control and in the NQSConfig.INI file, described in Appendix A, "NQSConfig.INI File Configuration Settings." See also Section 7.7.3, "Using Agents to Seed the Oracle BI Server Cache" for additional information.
This section contains the following topics:
The query cache consists of cache storage space, cache metadata, and cache detection in query compilation.
The process of the Oracle BI Server accessing the cache metadata is very fast. If the metadata shows a cache hit, then the bulk of the query processing is eliminated, and the results are immediately returned to the user. The process of adding the new results to the cache is independent of the results being returned to the user; the only effect on the running query is the resources that are consumed in the process of writing the cached results.
Query cache entries are portable across different operating systems, such as Windows or UNIX, and across 32-bit and 64-bit architectures. Incompatible cache entries are automatically removed. For example, you do not have to manually remove cache files when switching between 32-bit and 64-bit systems.
Note that query cache entries are not portable across different releases of Oracle Business Intelligence, such as between Version 10.1.3.2 and 11g Release 1 (11.1.1).
Caching occurs by default at the subrequest level, which results in multiple cache entries for some SQL statements. Caching subrequests improves performance and the cache hit ratio, especially for queries that combine real-time and historical data. To disable subrequest caching, set the NQSConfig.INI file parameter DISABLE_SUBREQUEST_CACHING to YES. See Appendix A, "NQSConfig.INI File Configuration Settings" for more information.
The fastest way to process a query is to skip the bulk of the processing and use a precomputed answer. With query caching, the Oracle BI Server stores the precomputed results of queries in a local cache. If another query can use those results, then all database processing for that query is eliminated. This can result in dramatic improvements in the average query response time.
In addition to improving performance, being able to answer a query from a local cache conserves network resources and processing time on the database server. Network resources are conserved because the intermediate results do not have to come over the network to the Oracle BI Server. Not running the query on the database frees the database server to do other work. If the database uses a charge back system, then it could save money in the budget as well.
Another benefit of using the cache to answer a query is savings in processing time on the Oracle BI Server, especially if the query results are retrieved from multiple databases. Depending on the query, there might be considerable join and sort processing in the server. If the query is already calculated, then this processing is avoided, freeing server resources for other tasks.
To summarize, query caching has the following advantages:
Dramatic improvement of query performance
Less network traffic
Reduction in database processing
Reduction in Oracle BI Server processing overhead
Query caching has many obvious benefits, but also certain costs:
Disk space for the cache
Administrative costs of managing the cache
Potential for cached results being stale
CPU and disk I/O on server computer
With cache management, the benefits typically far outweigh the costs.
The following sections discuss the costs of caching.
The query cache requires dedicated disk space. How much space depends on the query volume, the size of the query result sets, and how much disk space that you choose to allocate to the cache. For performance purposes, a disk should be used exclusively for caching, and it should be a high performance, high reliability type of disk system.
There are a few administrative tasks that are associated with caching. You must set the cache persistence time for each physical table appropriately, knowing how often data in that table is updated. When the frequency of the update varies, you must keep track of when changes occur and purge the cache manually when necessary. You can also create a cache event polling table and modify applications to update the polling table when changes to the databases occur, making the system event-driven.
The Oracle BI Server also provides ODBC-extension functions for purging cache entries programmatically. You can write your own scripts to call these functions at the appropriate times.
If the cache entries are not purged when the data in the underlying databases changes, then queries can potentially return results that are out of date. You must evaluate whether this is acceptable. It might be acceptable to allow the cache to contain some stale data. You must decide what level of stale data is acceptable and then configure (and follow) a set of rules to reflect those levels.
For example, suppose an application analyzes corporate data from a large conglomerate, and you are performing yearly summaries of the different divisions in the company. New data does not materially affect the queries because the new data affects only next year's summaries. In this case, the trade-offs for deciding whether to purge the cache might favor leaving the entries in the cache.
Suppose, however, that the databases are updated three times a day and you are performing queries on the current day's activities. In this case, you must purge the cache much more often, or perhaps consider not using the cache at all.
Another scenario is that you rebuild the data mart from scratch at periodic intervals (for example, once per week). In this example, you can purge the entire cache as part of the process of rebuilding the data mart, ensuring that you never have stale data in the cache.
Whatever your situation, you must evaluate what is acceptable for noncurrent information returned to the users.
If shared logon has been enabled for a particular connection pool, then the cache can be shared across users and does not need to be seeded for each user. If shared logon has not been enabled and a user-specific database login is used, then each user generates their own cache entries.
See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information about enabling shared logon for connection pools.
Typically, XML data sources are updated frequently and in real time. Setting a refresh interval for XML data sources is analogous to setting cache persistence for database tables. The refresh interval is a time interval after which the XML data sources are to be queried again directly, rather than using results in cache. This refresh interval is specified on the XML tab of the Connection Pool dialog.
The default interval setting is Infinite, meaning that the XML data source is not automatically refreshed.
The refresh interval setting determines the time interval after which the Oracle BI Server XML Gateway connection is refreshed, as follows:
For URLs that begin with http:// or https://, the gateway is refreshed when it detects that the interval has expired.
For URLs that reside on a local or network drive, the gateway is refreshed when the interval has expired and the system detects that the URLs have been modified.
For more information about XML data sources, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
In a clustered environment, Oracle BI Servers can be configured to access a shared cache called the global cache. This global cache resides on a shared file system storage device and stores purging events, seeding events (often generated by agents), and result sets that are associated with seeding events. The seeding and purging events are sorted by time and stored on the shared storage as a logical event queue. Individual Oracle BI Server nodes push to and pull from the logical event queue. Each Oracle BI Server still maintains its own local query cache for regular queries.
Figure 7-1 depicts global caching in a clustered environment. It shows three Oracle BI Server nodes sharing a global cache. The global cache stores seeding or purging events held in a logical event queue. The arrows from Node 2 and Node 3 to the shared cache show Oracle BI Server Node 2 pushing a seeding event to the queue and Oracle BI Server Node 3 pushing a purging event to the queue. The arrows from the shared storage to each Oracle BI Server node show each node pulling from the common location. This occurs on a periodic basis and enables participating Oracle BI Server nodes to obtain updates to the logical event queue made by other Oracle BI Servers.
The Oracle BI Server node processes a seeding or purging event locally first in its caching system. It then pushes the event to the global cache on the shared storage. During the push event, the active Oracle BI Server node locks the logical event queue on the shared storage and then pushes in the seeding or purging event. If there is a conflict between seeding and purging (for example, one node wants to seed a query and another node wants to purge the same query), then the event that comes in last wins.
The logical event queue in the global cache on the shared storage is composed of seeding and purging events from individual Oracle BI Server nodes. The queue is sorted according to the timestamp of the events. Hence, clocks on all Oracle BI Server nodes participating in cluster must be synchronized.
Each Oracle BI Server node polls the global cache on a periodic basis for new cache entries. This polling frequency is configurable. A snapshot of the queued logical events on the shared storage are pulled back to the node and a local logical event queue is constructed and then processed.
|
Note: The process of populating or purging seeded caches across all Oracle BI Server nodes that participate in the cluster does not occur in real time, and the elapse of the process is affected by multiple factors, such as the predefined polling interval, network bandwidth, and CPU loads. |
Because the query cache result set tends to get large, network bandwidth might pose a constraint. Therefore, the following must be chosen carefully:
The set of caches that qualify for seeded cache
The time interval for BI nodes to pick up seeded caches from shared storage (to avoid network congestion)
The primary global cache parameters are configured in Fusion Middleware Control. Additional, optional parameters are configured in the NQSConfig.INI file for each Oracle BI Server node that participates in the cluster. For more information about configuring these parameters, see Section 7.5.4, "Using Fusion Middleware Control to Set Global Cache Parameters" and Section 7.5.5, "Manually Editing Additional Global Cache Parameters."
A seeding or purging procedure is submitted to a specific Oracle BI Server node. If that Oracle BI Server is a node in a BI cluster and the global cache parameters have been defined in Oracle BI Server configuration files, then the seeding or purging events are propagated across all Oracle BI Server nodes that participate in the same clustered environment.
You configure cache storage and other parameters in Fusion Middleware Control and in the NQSConfig.INI file, for both the query cache and the global cache. You also must decide on a strategy for flushing outdated cache entries; see Section 7.6, "Monitoring and Managing the Cache" for more information.
This section contains the following topics:
Section 7.5.1, "Using Fusion Middleware Control to Enable and Disable Query Caching"
Section 7.5.2, "Using Fusion Middleware Control to Set Query Cache Parameters"
Section 7.5.3, "Manually Editing Additional Query Cache Parameters"
Section 7.5.4, "Using Fusion Middleware Control to Set Global Cache Parameters"
Section 7.5.5, "Manually Editing Additional Global Cache Parameters"
You can use Fusion Middleware Control to enable or disable query caching. The query cache is enabled by default.
Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."
To use Fusion Middleware Control to enable or disable query caching:
Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."
Display the Performance tab of the Capacity Management page.
Click Lock and Edit Configuration to allow changes to be made.
To enable query caching, select Cache enabled. To disable query caching, deselect Cache enabled.
Click the Help button on the page to access the page-level help.
Click Apply, then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
For information on using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 30, "Introducing the Oracle BI Systems Management API."
You can use Fusion Middleware Control to set the maximum number of cache entries in the query cache and the maximum size for a single cache entry.
Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."
To use Fusion Middleware Control to set query cache parameters:
Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."
Display the Performance tab of the Capacity Management page.
Click Lock and Edit Configuration to allow changes to be made.
Complete the elements using the descriptions in the Help topic for the page. Click the Help button on the page to access the page-level help for the following options:
Maximum cache entry size
Maximum cache entries
Click Apply, then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
For information on using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 30, "Introducing the Oracle BI Systems Management API."
You can set additional query cache parameters in the NQSConfig.INI file, including the following:
The DATA_STORAGE_PATHS parameter specifies one or more directories for query cache storage, and the maximum size for each storage directory. These directories are used to store the cached query results and are accessed when a cache hit occurs. See Section 7.7.1, "About Cache Hits" for more information about when cache is hit.
The cache storage directories should reside on high performance storage devices, ideally devoted solely to cache storage. When the cache storage directories begin to fill up, the entries that are least recently used (LRU) are discarded to make space for new entries.
The MAX_ROWS_PER_CACHE_ENTRY parameter controls the maximum number of rows for any cache entry. Limiting the number of rows is a useful way to avoid using up the cache space with runaway queries that return large numbers of rows. If the number of rows a query returns is greater than the value specified in the MAX_ROWS_PER_CACHE_ENTRY parameter, then the query is not cached.
Typically, if a query gets a cache hit from a previously executed query, then the new query is not added to the cache. The POPULATE_AGGREGATE_ROLLUP_HITS parameter overrides this default when the cache hit occurs by rolling up an aggregate from a previously executed query.
See Appendix A, "NQSConfig.INI File Configuration Settings" for more information about the additional query cache parameters.
Before you begin this procedure, ensure that you are familiar with the information in Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."
To use Fusion Middleware Control to set global cache parameters:
Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."
Display the Performance tab of the Capacity Management page.
Click Lock and Edit Configuration to allow changes to be made.
Complete the elements using the descriptions in the Help topic for the page. Click the Help button on the page to access the page-level help for the following options:
Global cache path
Global cache size
Click Apply, then click Activate Changes.
Return to the Business Intelligence Overview page and click Restart.
For information on using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 30, "Introducing the Oracle BI Systems Management API."
You can set additional global cache parameters in the NQSConfig.INI file, including the following:
The MAX_GLOBAL_CACHE_ENTRIES parameter controls the maximum number of entries that are allowed in the global cache store.
The CACHE_POLL_SECONDS parameter specifies the interval in seconds at which the Oracle BI Server pulls from the logical event queue to synchronize with other server nodes in the cluster.
The CLUSTER_AWARE_CACHE_LOGGING parameter controls whether logging is turned on for the global cache. Change this setting to YES only for debugging purposes.
Log entries appear in nqquery.log. You can find this file at:
ORACLE_INSTANCE\diagnostics\logs\OracleBIServerComponent\coreapplication_obisn
See Appendix A, "NQSConfig.INI File Configuration Settings" for more information about the additional global cache parameters.
To manage the changes in the underlying databases and to monitor cache entries, you must develop a cache management strategy. You need a process to invalidate cache entries when the data in the underlying tables that compose the cache entry have changed, and a process to monitor, identify, and remove any undesirable cache entries.
This section contains the following topics:
Section 7.6.2, "Purging and Maintaining Cache Using ODBC Procedures"
Section 7.6.3, "How Repository Changes Affect the Query Cache"
The choice of a cache management strategy depends on the volatility of the data in the underlying databases and the predictability of the changes that cause this volatility. It also depends on the number and types of queries that comprise your cache and the usage those queries receive. This section provides an overview of the various approaches to cache management.
You can disable caching for the entire system to stop all new cache entries and stop any new queries from using the existing cache. Disabling caching lets you enable it at a later time without losing any entries that are already stored in the cache.
Temporarily disabling caching is a useful strategy in situations where you might suspect having stale cache entries, but want to verify if they are actually stale before purging those entries or the entire cache. If you find that the data stored in the cache is still relevant, or after you have safely purged problem entries, then you can safely enable the cache. If necessary, purge the entire cache or the cache that is associated with a particular business model before enabling the cache again.
See Section 7.5.1, "Using Fusion Middleware Control to Enable and Disable Query Caching" for more information.
You can set a cacheable attribute for each physical table, enabling you to specify whether queries for that table are added to the cache to answer future queries. If you enable caching for a table, then any query involving the table is added to the cache. All tables are cacheable by default, but some tables might not be good candidates to include in the cache unless you use the Cache Persistence Time settings. For example, suppose that you have a table that stores stock ticker data that is updated every minute. You could use the Cache Persistence Time settings to purge the entries for that table every 59 seconds.
You can also use the Cache persistence time field to specify how long the entries for this table should be kept in the query cache. This is useful for data sources that are updated frequently.
To set the caching attributes for a specific physical table:
In the Administration Tool, in the Physical layer, double-click the physical table.
In the Physical Table properties dialog, in the General tab, make one of the following selections:
To enable caching, select Cacheable.
To prevent a table from being cached, deselect Cacheable.
To set a cache expiration time, specify a Cache persistence time and specify a unit of measure (days, hours, minutes, or seconds). If you do not want cache entries to automatically expire, select Cache never expires.
Click OK.
Oracle BI Server event polling tables store information about updates in the underlying databases. An application (such as one that loads data into a data mart) could be configured to add rows to an event polling table each time a database table is updated. The Oracle BI Server polls this table at set intervals and invalidates any cache entries corresponding to the updated tables. Event polling tables can be the sole method of cache management, or they can be used with other cache management schemes. Event tables offer less flexibility about choice of cache entries and the timing of purges. See Section 7.8.1, "Setting Up Event Polling Tables on the Physical Databases" for more information about event polling tables.
The Oracle BI Server provides ODBC-extension functions for purging cache entries.
Some of these functions are particularly useful for embedding in an Extract, Transform, and Load (ETL) task. For example, after a nightly ETL is performed, all Oracle BI Server cache entries can be purged. If only the fact table was modified, then only cache related to that table can be purged. In some cases, you might need to purge the cache entries associated with a specific database.
Only administrators have the right to purge cache. Therefore, scripts that call these ODBC-extension functions must run under credentials with administrator privileges.
The following ODBC functions affect cache entries that are associated with the repository specified by the ODBC connection:
SAPurgeCacheByQuery. Purges cache entries that exactly match a specified query. For example, using the following query, you would have one or more query cache entries that retrieve the names of all employees earning more than $100,000:
SELECT lastname, firstname FROM employee WHERE salary > 100000;
The following call purges the cache entries that are associated with this query:
Call SAPurgeCacheByQuery('SELECT lastname, firstname FROM employee WHERE salary > 100000' );
SAPurgeCacheByTable. Purges all cache entries that are associated with a specified physical table name (fully qualified) for the repository to which the client has connected.
This function takes up to four parameters that represent the four components (database, catalog, schema, and table name proper) of a fully qualified physical table name. For example, you might have a table with the fully qualified name of DBName.CatName.SchName.TabName. To purge the cache entries that are associated with this table in the physical layer of the Oracle Business Intelligence repository, run the following call in a script:
Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );
|
Note: Wildcards are not supported by the Oracle BI Server for this function. In addition, DBName and TabName cannot be null. If either one is null, then an error message is displayed. |
SAPurgeAllCache. Purges all cache entries. The following is an example of this call:
Call SAPurgeAllCache();
SAPurgeCacheByDatabase. Purges all cache entries associated with a specific physical database name. A record is returned as a result of calling any of the ODBC procedures to purge the cache. This function takes one parameter that represents the physical database name, and the parameter cannot be null. The following shows the syntax of this call:
Call SAPurgeCacheByDatabase( 'DBName' );
If there is a single quote within the string argument of a procedure, then you must use another single quote to escape it. For example:
Call SAPurgeCacheByQuery('SELECT TOPN("- Currency"."Markdown %", 10) saw_0,
"XX Line"."Order No" saw_1, "- Bill-To Site"."Customer Name" saw_2, "-
Currency"."Net USD" saw_3, "- Currency"."Markdown USD" saw_4, "-
Currency"."Markdown %" saw_5 FROM "Apps 11i - XX Lines" WHERE
("XX Line"."Open Flag" = ''Y'') AND ("Operating Unit"."Group Name" = ''Group'')
AND ("- Currency"."Net USD" >= 10000) ORDER BY saw_0');
The line in bold highlights the extra single quotes used as escape characters for the items ''Y'' and ''Group''.
When users access Answers to run queries, Presentation Services caches the results of the queries. Presentation Services uses the request key and the logical SQL string to determine if subsequent queries can use cached results. If the cache can be shared, then subsequent queries are not stored.
SAGetSharedRequestKey: An ODBC procedure that takes a logical SQL statement from Presentation Services and returns a request key value.
The following shows the syntax of this procedure:
SAGetSharedRequestKey('sql-string-literal')
The value of the request key is affected by the following factors:
Whether the Virtual Private Database option has been selected in the repository physical database object
Whether any session variables have been marked as Security Sensitive in the repository
Presentation Services takes security sensitive variable values into consideration when computing the request key for logical requests against database objects marked as Virtual Private Databases.
See Section 7.9, "Managing the Oracle BI Presentation Services Cache Settings" for more information about the Presentation Services query cache.
A result record is returned after you issue a purge cache command. The result record contains two columns. The first column is a result code and the second column is a short message that describes the result of the purge operation. Table 7-1 shows examples of result records.
In Microsoft Analysis Services, member caption name is the same as member unique name. However, in SAP/BW data sources, member caption name is different from member unique name. Therefore, the Oracle BI Server maintains a cache subsystem for SAP/BW member unique names. This subsystem is turned off by default. For configuration information, see the topic about the MDX Member Name Cache Section in Appendix A, "NQSConfig.INI File Configuration Settings."
When a query is received for member unique name, the subsystem checks the cache to determine whether cache exists for this query. If cache exists, then the record for the cached unique name is returned. If there is no cache that matches the query, then the subsystem sends a probing query to SAP/BW.
The probing query is logged when the log level is equal or greater than 2. The status of the subsystem, such as if the subsystem is enabled and events such as start and shutdown events, are also written to the server log.
|
Caution: With each increased logging level, performance is impacted. Use caution when increasing the log level for users. |
Be aware of the following cache purge issues:
The size of multidimensional cache entries can grow very large. Therefore, a limit on the size of each member set has been established in the MDX_MEMBER_CACHE section of the NQSConfig.INI file.
The format of persisted cache might not be consistent after an upgrade. Therefore, you should purge all cache before a software upgrade.
The cache is populated the first time that the query runs. You should arrange to populate the cache during off-peak hours, to minimize performance impact.
|
Note: In the Administration Tool, you can purge cache for an individual cube table by right-clicking the cube table, and then selecting Purge Member Cache. This must be performed in online mode by a user with administrator privileges. |
The following purge procedures are specific to SAP/BW data sources:
SAPurgeALLMCNCache. Purges all SAP/BW cache entries.
The following shows the syntax of this procedure:
SAPurgeALLIMCNCache ()
SAPurgeMCNCacheByCube. Purges all cache entries that are associated with the specified physical cube. The database name and cube name are the external names of the repository objects. The following shows the syntax of this procedure:
SAPurgeMCNCacheByCube( 'DBName', 'CubeName')
The following messages is returned.
Table 7-2 SAP Purge Cache Return Codes and Messages
| Return Code | Return Message |
|---|---|
|
1 |
SAPurgeALLMCNCache returns successfully. |
|
1 |
SAPurgeMCNCacheByCube returns successfully. |
|
59116 |
The database specified does not exist. Note: If the database and physical cube are both wrong, then this result code is returned. |
|
85025 |
The physical cube specified does not exist. |
Only users with administrative privileges can run ODBC purge procedures.
When you modify Oracle Business Intelligence repositories, the changes can have implications for entries that are stored in the cache. For example, if you change the definition of a physical object or a dynamic repository variable, cache entries that reference that object or variable might no longer be valid. These changes might result in the need to purge the cache. There are three scenarios to be aware of: when the changes occur in online mode, when they occur in offline mode, and when you are switching between repositories.
When you modify an Oracle Business Intelligence repository in online mode, any changes that you make that affect cache entries automatically result in a purge of all cache entries that reference the changed objects. The purge occurs when you check in the changes. For example, if you delete a physical table from a repository, then all cache entries that reference that table are purged upon check in. Any changes made to a business model in the Business Model and Mapping layer purge all cache entries for that business model.
When you modify an Oracle Business Intelligence repository in offline mode, you might make changes that affect queries that are stored in the cache and render those cached results obsolete. Because the repository is not loaded by the server during offline mode edits, the server has no way of determining if the changes made affect any cached entries. The server therefore does not automatically purge the cache after offline changes. If you do not purge the cache, then there might be invalid entries when the repository is next loaded. Unless you are sure that there are no entries in the cache that are affected by your offline changes, then you should purge the cache for any business model that you have modified.
If you intend to remove a repository from the configuration of the Oracle BI Server, then ensure that you purge the cache of all cache entries that reference the repository. Failure to do so results in a corrupted cache. See Section 7.7.4.2, "Purging Cache in the Administration Tool" for more information.
The values of dynamic repository variables are refreshed by data that is returned from queries. When you define a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You also configure a schedule for the Oracle BI Server to follow to execute the query and periodically refresh the value of the variable.
When the value of a dynamic repository variable changes, all cache entries that are associated with a business model that reference the value of that variable are purged automatically. The cache entries are purged when the repository variable refresh rate is reached, if its value has changed.
Note that if a business model is not associated with a changed dynamic repository variable, then no cache purging action occurs. For example, suppose an initialization block has been defined with a repository variable and a refresh rate of 5 minutes. But, no logical column has been defined that references the variable. When the value of the dynamic repository variable changes, cache is not purged because no logical column exists within a business model that uses the variable.
One of the main advantages of query caching is to improve apparent query performance. It might be valuable to seed the cache during off hours by running queries and caching their results. A good seeding strategy requires that you know when cache hits occur.
If you want to seed the cache for all users, then you might seed the cache with the following query:
SELECT User, SRs
After seeding the cache using SELECT User, SRs, the following queries will be cache hits:
SELECT User, SRs WHERE user = valueof(nq_SESSION.USER) (and the user was USER1) SELECT User, SRs WHERE user = valueof(nq_SESSION.USER) (and the user was USER2) SELECT User, SRs WHERE user = valueof(nq_SESSION.USER) (and the user was USER3)
This section contains the following topics:
Section 7.7.2, "Running a Suite of Queries to Populate the Cache"
Section 7.7.3, "Using Agents to Seed the Oracle BI Server Cache"
When caching is enabled, each query is evaluated to determine whether it qualifies for a cache hit. A cache hit means that the server was able to use cache to answer the query and did not go to the database at all. The Oracle BI Server can use the query cache to answer queries at the same or higher level of aggregation.
Many factors determine whether cache is hit. Table 7-3 describes these factors.
Table 7-3 Factors That Determine Whether Cache Is Hit
| Factor or Rule | Description |
|---|---|
|
A subset of columns in the |
All of the columns in the This rule describes the minimum requirement to hit the cache, but meeting this rule does not guarantee a cache hit. The other rules listed in this table also apply. |
|
Columns in the |
The Oracle BI Server can calculate expressions on cached results to answer the new query, but all the columns must be in the cached result. For example, the query: SELECT product, month, averageprice FROM sales WHERE year = 2000 will hit cache on the query: SELECT product, month, dollars, unitsales FROM sales WHERE year = 2000 because |
|
|
For the query to qualify as a cache hit, the A
|
|
Dimension-only queries must be an exact match |
If a query is dimension only, meaning that no fact or measure is included in the query, then only an exact match of the projection columns of the cached query hits the cache. This behavior prevents false positives when there are multiple logical sources for a dimension table. |
|
Queries with special functions must be an exact match |
Other queries that contain special functions such as time series functions ( |
|
Set of logical tables must match |
To qualify as a cache hit, all incoming queries must have the same set of logical tables as the cache entry. This rule avoids false cache hits. For example, |
|
Session variable values must match, including security session variables |
If the logical SQL or physical SQL statement refers to any session variable, then the session variable values must match. Otherwise, the cache is not hit. In addition, the value of session variables that are security sensitive must match the security session variable values that are defined in the repository, even though the logical SQL statement itself does not reference session variables. See "Ensuring Correct Cache Results When Using Row-Level Database Security" for more information. |
|
Equivalent join conditions |
The resultant joined logical table of a new query request has to be the same as (or a subset of) the cached results to qualify for a cache hit. |
|
|
If a cached query eliminates duplicate records with |
|
Queries must contain compatible aggregation levels |
Queries that request an aggregated level of information can use cached results at a lower level of aggregation. For example, the following query requests the quantity sold at the supplier and region and city level: SELECT supplier, region, city, qtysold FROM suppliercity The following query requests the quantity sold at the city level: SELECT city, qtysold FROM suppliercity The second query results in a cache hit on the first query. |
|
Limited additional aggregation |
For example, if a query with the column |
|
|
Queries that order by columns that are not contained in the select list result in cache misses. |
|
Avoiding cache misses using advanced hit detection |
You can avoid some cache misses by setting the parameter |
When using a row-level database security strategy, such as a Virtual Private Database (VPD), the returned data results are contingent on the authorization credentials of the user. Because of this, the Oracle BI Server must know whether a data source is using row-level database security and which variables are relevant to security.
To ensure that cache hits only occur on cache entries that include and match all security-sensitive variables, you must correctly configure the database object and session variable objects in the Administration Tool, as follows:
Database object. In the Physical layer, in the General tab of the Database dialog, select Virtual Private Database to specify that the data source is using row-level database security.
If you are using row-level database security with shared caching, then you must select this option to prevent the sharing of cache entries whose security-sensitive variables do not match.
Session Variable object. For variables that you are using for authentication, in the Session Variable dialog, select Security Sensitive to identify them as sensitive to security when using a row-level database security strategy. This option ensures that cache entries are marked with the security-sensitive variables, enabling security-sensitive variable matching on all incoming queries.
Refer to the following resources for more information:
"Setting Up Row-Level Security in the Database" in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition
"Managing Session Variables" in Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition
Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for general information about database and session variable objects
To maximize potential cache hits, one strategy is to run a suite of queries just for the purpose of populating the cache. The following are some recommendations for the types of queries to use when creating a suite of queries with which to seed the cache.
Common prebuilt queries. Queries that are commonly run, particularly ones that are expensive to process, are excellent cache seeding queries. Queries whose results are embedded in dashboards are good examples of common queries.
SELECT lists with no expressions. Eliminating expressions on SELECT list columns expands the possibility for cache hits. A cached column with an expression can only answer a new query with the same expression; a cached column with no expressions can answer a request for that column with any expression. For example, a cached request such as:
SELECT QUANTITY, REVENUE...
can answer a new query such as:
SELECT QUANTITY/REVENUE...
but not the reverse.
No WHERE clause. If there is no WHERE clause in a cached result, then it can be used to answer queries that satisfy the cache hit rules for the select list with any WHERE clause that includes columns in the projection list.
In general, the best queries to seed cache with are queries that heavily consume database processing resources and that are likely to be reissued. Be careful not to seed the cache with simple queries that return many rows. These queries (for example, SELECT * FROM PRODUCTS, where PRODUCTS maps directly to a single database table) require very little database processing. Their expense is network and disk overhead, which are factors that caching does not alleviate.
When the Oracle BI Server refreshes repository variables, it examines business models to determine if they reference those repository variables. If they do, the Oracle BI Server then purges all cache for those business models. See Section 7.6.3.4, "Changes to Dynamic Repository Variables" for more information.
You can configure agents to seed the Oracle BI Server cache. Seeding the cache can improve response times for users when they run analyses or view analyses that are embedded on their dashboards. You can accomplish this by scheduling agents to execute requests that refresh this data.
To configure an agent to seed the Oracle BI Server cache:
Log in to Oracle Business Intelligence and select New, then select Agent.
On the General tab, select Recipient for the Run As option. Personalized cache seeding uses the data visibility of each recipient to customize agent delivery content for each recipient.
On the Schedule tab, specify when you want the cache to be seeded.
Optionally, select Condition and create or select a conditional request. For example, you might have a business model that determines when the ETL process is complete. You could use a report based on this business model to be the conditional trigger for the cache seed to begin.
On the Delivery Content tab, select an individual request or an entire dashboard page for which you want to seed the cache. Selecting a dashboard page can save time.
On the Recipients tab, select individual users or groups to be the recipients.
On the Destinations tab, clear all user destinations and select Oracle BI Server Cache.
Save the agent by selecting the Save button in the upper-right corner.
The only difference between cache seeding agents and other agents is that they clear the previous cache automatically and do not appear on the dashboard as Alerts.
Note that cache seeding agents only purge exact match queries, so stale data might still exist. Your caching strategy should always include cache purging, because agent queries do not address ad-hoc queries or drills.
The Cache Manager lets you view information about the entire query cache and information about individual entries in the query cache that are associated with the open repository. You can also use it to select specific cache entries and perform various operations on those entries, such as viewing and saving the cached SQL statement, or purging them.
To open the Cache Manager:
In the Administration Tool toolbar, select Manage > Cache.
Select the Cache tab on the left explorer pane to view the cache entries for the current repository, business models, and users. The associated cache entries are reflected in the right pane, with the total number of entries shown in the view-only field at the top.
The cache entry information and its display sequence is controlled by the Options settings (select Edit, then select Options from the Cache Manager, or select Tools > Options > Cache Manager from the Administration Tool menu). Information can include the options that are described in Table 7-4.
Table 7-4 Cache Options
| Option | Description |
|---|---|
|
User |
The ID of the user who submitted the query that resulted in the cache entry. |
|
Created |
The time the cache entry's result set was created. |
|
Last used |
The last time the cache entry's result set satisfied a query. (After an unexpected shutdown of the Oracle BI Server, the last used time might temporarily have a stale value—a value that is older than the true value.) |
|
Creation elapsed time |
The time, in seconds, that is needed to create the result set for this cache entry. Note: The value that is stored in the cache object descriptor on disk is in units of milliseconds. The value is converted to seconds for display purposes. |
|
Row count |
The number of rows generated by the query. |
|
Row size |
The size of each row (in bytes) in this cache entry's result set. |
|
Full size |
Full size is the maximum size used, considering variable length columns, compression algorithm, and other factors. The actual size of the result set will be smaller than Full size. |
|
Column count |
The number of columns in each row of this cache entry's result set. |
|
Logical Request |
The logical request that is associated with this cache entry. If subrequests are being cached, then this column shows the text of the subrequest. |
|
Use count |
The number of times that this cache entry's result set has satisfied a query (since Oracle BI Server startup). |
|
Business model |
The name of the business model that is associated with the cache entry. |
|
Repository |
The name of the Oracle Business Intelligence repository that is associated with this cache entry. |
|
SQL |
The SQL statement that is associated with this cache entry. If subrequests are being cached, then there might be multiple cache entries that are associated with a single SQL statement. |
|
Query Server |
The Oracle BI Server that serviced the query. |
|
Fact Table Source |
The fact table that is associated with the logical request for this cache entry. |
Expand the repository tree to display all the business models with cache entries, and expand the business models to display all users with cache entries. The right pane displays only the cache entries associated with the selected item in the hierarchical tree.
Select Action, then select Show Info to display global cache information. Table 7-5 describes the information that appears in the Global Cache Information window.
Table 7-5 Global Cache Information
| Column | Description |
|---|---|
|
Amount of space still available for cache storage use |
The amount of space, in megabytes, still available for cache storage. |
|
Amount of space used on disks containing cache related files |
The total amount of space, in megabytes, used on the disk that contains cache-related files (not just space used for the cache-related files). |
|
Maximum allowable number of entries in cache |
The maximum number of entries that can be in the cache, from the |
|
Maximum allowable number of rows per cache entry result set |
The maximum number of rows that are allowed for each cache entry's result set, from the |
|
Number of entries currently in cache |
The current number of entries in the global cache. These entries might relate to multiple repositories. |
|
Number of queries not satisfied from cache since startup of Oracle BI Server |
Cache misses, since the last time the Oracle BI Server was started. |
|
Number of queries satisfied from cache since startup of Oracle BI Server |
Cache hits, since the last time the Oracle BI Server was started. |
With the Cache Manager as the active window, press F5, or select Action > Refresh to refresh the display. This retrieves the current cache entries for the repository that you have open and the current global cache information. If the DSN is clustered, then information about all repositories in the cluster is displayed.
Purging cache is the process of deleting entries from the query cache. You can purge cache entries in the following ways:
Manually, using the Administration Tool Cache Manager facility (in online mode).
Automatically, by setting the Cache Persistence Time field in the Physical Table dialog for a particular table.
Automatically, by setting up an Oracle BI Server event polling table.
Automatically, as the cache storage space fills up.
|
Note: You can also purge the cache programmatically using ODBC-extension functions. See Section 7.6.2, "Purging and Maintaining Cache Using ODBC Procedures" for more information.In addition, cache can be purged when the value of dynamic repository variables changes. See Section 7.6.3.4, "Changes to Dynamic Repository Variables" for more information. |
To manually purge cache entries in the Cache Manager:
Use the Administration Tool to open a repository in online mode.
Select Manage > Cache to open the Cache Manager dialog.
Select Cache or Physical mode by selecting the appropriate tab in the left pane.
Browse the explorer tree to display the associated cache entries in the right pane.
Select the cache entries to purge, and then select Edit > Purge to remove them. Or, right-click the selected entries and then select Purge.
In Cache mode, select the entries to purge from those displayed in the right pane.
In Physical mode, select the database, catalog, schema or tables to purge from the explorer tree in the left pane.
In Cache mode, you can purge:
One or more selected cache entries that are associated with the open repository.
One or more selected cache entries that are associated with a specified business model.
One or more selected cache entries that are associated with a specified user within a business model.
In Physical mode, you can purge:
All cache entries for all tables that are associated with one or more selected databases.
All cache entries for all tables that are associated with one or more selected catalogs.
All cache entries for all tables that are associated with one or more selected schemas.
All cache entries that are associated with one or more selected tables.
Purging deletes the selected cache entries and associated metadata. Select Action > Refresh or press F5 to refresh your cache display.
You can use an Oracle BI Server event polling table (event table) as a way to notify the Oracle BI Server that one or more physical tables have been updated. Each row that is added to an event table describes a single update event, such as an update occurring to the Product table in the Production database. The Oracle BI Server cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges stale cache entries that reference those physical tables.
The event table is a physical table that resides on a database accessible to the Oracle BI Server. Regardless of whether it resides in its own database, or in a database with other tables, it requires a fixed schema, that is described in Table 7-6. It is normally exposed only in the Physical layer of the Administration Tool, where it is identified in the Physical Table dialog as an Oracle BI Server event table.
Using event tables is one of the most accurate ways of invalidating stale cache entries, and it is probably the most reliable method. It does, however, require the event table to be populated each time that a database table is updated. Also, because there is a polling interval in which the cache is not completely up to date, there is always the potential for stale data in the cache. See Section 7.8.3, "Populating the Oracle BI Server Event Polling Table" for more information.
A typical method of updating the event table is to include SQL INSERT statements in the extraction and load scripts or programs that populate the databases. The INSERT statements add one row to the event table each time that a physical table is modified. After this process is in place and the event table is configured in the Oracle Business Intelligence repository, cache invalidation occurs automatically. As long as the scripts that update the event table are accurately recording changes to the tables, stale cache entries are purged automatically at the specified polling intervals.
This section contains the following topics:
Section 7.8.1, "Setting Up Event Polling Tables on the Physical Databases"
Section 7.8.3, "Populating the Oracle BI Server Event Polling Table"
Section 7.8.4, "Troubleshooting Problems with Event Polling Tables"
This section describes how to configure the Oracle BI Server event polling tables on physical databases.
You can configure a physical event polling table on each physical database to monitor changes in the database. You can also configure the event table in its own database. The event table should be updated every time a table in the database changes.
If the event polling table is on an Oracle Database, you should configure the event table in its own database object in the Physical layer of the Administration Tool. Then, ensure that the feature PERF_PREFER_IN_LISTS is not selected in the Features tab of the Database dialog for the event polling table. Following these guidelines avoids errors related to exceeding the maximum number of allowed expressions in a list.
Event tables must have the structure that is shown in Table 7-6. Some columns can contain null values, depending on where the event table resides. The names for the columns must match the column names that are shown in Table 7-6.
See Section 7.8.1.2, "Sample Event Polling Table CREATE TABLE Statements" for samples. Alternatively, you can use the sample event polling table in the database that you installed for use with Oracle Business Intelligence.
Table 7-6 Event Polling Table Column Names
| Event Table Column | Data Type | Description |
|---|---|---|
|
CatalogName |
CHAR or VARCHAR |
The name of the catalog where the physical table that was updated resides. Populate the CatalogName column only if the event table does not reside in the same database as the physical tables that were updated. Otherwise, set it to the null value. |
|
DatabaseName |
CHAR or VARCHAR |
The name of the database where the physical table that was updated resides. This is the name of the database as it is defined in the Physical layer of the Administration Tool. For example, if the physical database name is 11308Production, and the database name that represents it in the Administration Tool is SQL_Production, then the polled rows in the event table must contain SQL_Production as the database name. Populate the DatabaseName column only if the event table does not reside in the same database as the physical tables that were updated. Otherwise, set it to the null value. |
|
Other |
CHAR or VARCHAR |
Reserved for future enhancements. This column must be set to a null value. |
|
SchemaName |
CHAR or VARCHAR |
The name of the schema where the physical table that was updated resides. Populate the SchemaName column only if the event table does not reside in the same database as the physical tables being updated. Otherwise, set it to a null value. |
|
TableName |
CHAR or VARCHAR |
The name of the physical table that was updated. The name must match the name that is defined for the table in the Physical layer of the Administration Tool. Values cannot be null. |
|
UpdateTime |
DATETIME |
The time when the update to the event table occurs. This must be a key (unique) value that increases for each row that is added to the event table. To ensure a unique and increasing value, specify the current timestamp as a default value for the column. For example, specify Values cannot be null. Note: Because this column must be a unique value that increases for each row that is added to the event table, you might need to set a very high precision if you require many inserts per second. Because of this, you might want to adjust the database feature For example, for Oracle Database or Teradata, you might want to change |
|
UpdateType |
INTEGER |
Specify a value of 1 in the update script to indicate a standard update. (Other values are reserved for future use.) Values cannot be null. |
The Oracle BI Server must have read and write permission on the event polling table. The server reads the event table at specified intervals to look for changed data. Applications add rows to the event table when database tables are modified (for example, during a load operation). When there are rows in the event table, there is changed data in the underlying databases. The server then invalidates any cache entries that correspond to the changed physical tables and periodically deletes obsolete rows from the event table. The next time it checks the event table, the process repeats.
|
Note: In a clustered Oracle Business Intelligence deployment, a single event polling table is shared by every Oracle BI Server node in the cluster. However, a single event polling table cannot be shared by multiple Oracle BI Server clusters. |
To enable the Oracle BI Server to have write access to the event polling table but not to any other tables in a database, perform the following tasks:
Create a separate physical database in the Physical layer of the Administration Tool with a privileged connection pool.
Assign a user to the connection pool that has delete privileges.
Populate the privileged database with the event table.
The Oracle BI Server has write access to the event polling table, but not to any tables that are used to answer user queries.
This section provides sample CREATE TABLE statements for SQL Server and Oracle Database. These CREATE TABLE statements create the structure that is required for an Oracle BI Server event polling table. In these statements, the table that is created is named UET. It resides in the same database as the physical tables that are being updated.
|
Note: The column lengths must be large enough to represent the object names in the repository. |
The following is a sample CREATE TABLE statement for SQL Server:
// SQL Server Syntax create table UET ( UpdateType Integer not null, UpdateTime datetime not null DEFAULT CURRENT_TIMESTAMP, DBName char(40) null, CatalogName varchar(40) null, SchemaName varchar(40) null, TableName varchar(40) not null, Other varchar(80) null DEFAULT NULL )
The following is a sample CREATE TABLE statement for Oracle Database:
// Oracle Database syntax create table UET ( UpdateType Integer not null, UpdateTime date DEFAULT SYSDATE not null, DBName char(40) null, CatalogName varchar(40) null, SchemaName varchar(40) null, TableName varchar(40) not null, Other varchar(80) DEFAULT NULL );
You might need to modify these CREATE TABLE statements slightly for different versions of SQL Server and Oracle Database, or for other databases. Additionally, if you want to specify any explicit storage clauses, then you must add the appropriate clauses to the statements. Similarly, if you need international characters, then you must modify the DDL to handle international characters.
After the table is created on the physical database, you can make it active in the Oracle BI Server. To do this, you first import the physical table, and then you mark the table object as an event polling table.
To import the physical table:
In the Administration Tool, open the repository and import metadata from the physical database. To do this, select File, then select Import Metadata.
Follow the wizard steps. Be sure to select the Tables option in the Select Metadata Types screen to import the table metadata.
See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for detailed information about the Import Metadata wizard.
If you have multiple event polling tables, then repeat steps 1 and 2 for each event table. Be sure the data source that is specified for the event table has read and write access to the event table. The repository both reads the table and deletes rows from it, so it needs write permission. Event tables do not need to be exposed in the Business Model and Mapping layer.
To mark the table object as an event polling table:
Select the Tools > Utilities menu item.
Select the option Oracle BI Event Tables from the list of options.
Click Execute.
Select the table that you want to register as an Event Table and click the >> button.
Specify the polling frequency in minutes, and click OK.
The default value is 60 minutes. You should not set the polling frequency to less than 10 minutes. If you want a very short polling interval, then consider marking some or all of the tables non cacheable.
When a table has been registered as an Oracle BI Server event table, the table properties change. Registration as an event table removes the option to make the table cacheable, as there is no reason to cache results from an event polling table.
The Oracle BI Server does not populate the event polling table. The event table is populated by inserting rows into it each time that a table is updated. This process is normally configured by the database administrator, who typically modifies the load process to insert a row into the polling table each time a table is modified. This can be done from the load script, using database triggers (in databases that support triggers), from an application, or manually. If the process of populating the event table is not done correctly, then the Oracle BI Server cache purging is affected, because the server assumes the information in the polling table is correct and up to date.
If you experience problems with cache polling, then you can search the Oracle BI Server activity logs for any entries regarding the server's interaction with the event table.
The nqserver.log file logs activity automatically about the Oracle BI Server. Log entries are self-explanatory and can be viewed in Fusion Middleware Control or in a text editor.
When the Oracle BI Server polls the event table, it logs queries in the nqquery.log file using the administrator account (set upon installation) unless the logging level for the administrator account is set to 0. You should set the logging level to 2 for the administrator account to provide the most useful level of information.
You can find the nqserver.log and the nqquery.log in the following location:
ORACLE_INSTANCE/diagnostics/logs/OracleBIServerComponent/coreapplication_obisn
When users run analyses, Presentation Services can cache the results of those analyses. Presentation Services determines if subsequent analyses can use cached results. If the cache can be shared, then subsequent analyses are not stored.
The files for the Presentation Services cache have names such as nQS_xxxx_x_xxxxxx.TMP. The files are created by the ODBC driver but generally do correspond to ODBC requests that the Presentation Services cache keeps open. The files are stored in the following directory:
ORACLE_INSTANCE\tmp\OracleBIPresentationServices\coreapplication_obipsn\obis_temp
The files for the cache are removed whenever Presentation Services shuts down cleanly. If Presentation Services shuts down unexpectedly, then various cache files might be left on disk. You can delete the files when Presentation Services is not running.
The Presentation Services cache is not the same cache that is accessed by the Oracle BI Server. You can change the defaults for the Presentation Services cache by modifying the instanceconfig.xml file to include the cache entries.
The following procedure provides information on configuration changes with which you can manage the Presentation Services cache.
See Section 7.6.2.2, "About Sharing the Presentation Services Query Cache" for information on an ODBC procedure to use for sharing the cache.
Before you begin this procedure, ensure that you are familiar with the information in Section 3.4, "Using a Text Editor to Update Oracle Business Intelligence Configuration Settings".
To manually edit the settings for managing the cache:
Open the instanceconfig.xml file for editing, as described in Section 3.6, "Where are Configuration Files Located?"
Locate the section in which you must add the elements that are described in Table 7-7.
|
Note: Avoid specifying values of less than 3 minutes for the elements that affect minutes. At such a low amount of time, refreshes can occur frequently, which can negatively affect performance and cause flickering on the screen. |
Include the elements and their ancestor elements as appropriate, as shown in the following example:
<ServerInstance>
<Cache>
<Query>
<MaxEntries>100</MaxEntries>
<MaxExpireMinutes>60</MaxExpireMinutes>
<MinExpireMinutes>10</MinExpireMinutes>
<MinUserExpireMinutes>10</MinUserExpireMinutes>
</Query>
</Cache>
<ServerInstance>
Save your changes and close the file.
Table 7-7 Elements for Configuring the Cache for Presentation Services
| Element | Description | Default Value |
|---|---|---|
|
MaxEntries |
Specifies the maximum number of open record sets that Presentation Services keeps open at any one time. The minimum value is 3. For systems under significant loads, you can increase this value to 700 or 1000. |
500 |
|
MaxExpireMinutes |
Specifies the maximum amount of time, in minutes, that an entry in the cache can exist before it is removed. Depending on the number of analyses being run, an entry might be removed before the time limit expires. |
60 (one hour) |
|
MinExpireMinutes |
Specifies the minimum amount of time, in minutes, that an entry in the cache can exist before it is removed. The setting for CacheMinUserExpireMinutes can force an entry for a particular user to exist for a longer time than that specified by the CacheMaxExpireMinutes element. |
10 |
|
MinUserExpireMinutes |
Specifies the minimum amount of time, in minutes, that an entry in the cache can exist after it has been viewed by a user. For example, if CacheMaxExpireMinutes is set to 60 minutes and a user views the entry during the 59th minute, the entry exists for that user for an additional 10 minutes. The user can continue paging through the data without requiring a new analysis to be run. |
10 |
This section describes recommendations to improve performance for the Oracle Business Intelligence Web client.
This section contains the following topics:
You can improve the performance of the Oracle BI Web client by caching small, frequently used static files such as .javascript, .gif, and .css files. By enabling caching and content expiration on the Web server, Web browsers can determine how often to reload the static files from the server.
This section contains the following topics:
Section 7.10.1.1, "Setting Up Static File Caching for Microsoft IIS Server"
Section 7.10.1.2, "Setting Up Static File Caching for Oracle HTTP Server/Apache HTTP Server"
Follow these steps to configure static file caching and content expiration if you are using Microsoft IIS Server with Oracle Business Intelligence.
To set up static file caching for Microsoft IIS Server:
On the Web server computer, from the Start menu, select Settings, then Control Panel, then Administrative Tools.
Run Internet Service Manager.
In Internet Service Manager, right-click the Default Web Site. You can specify content expiration at the individual Web site folder level, the virtual directory level, or for a file.
In Default Web Site Properties, click the HTTP Headers tab.
Select Enable Content Expiration.
Select Expire After, and specify the value appropriate for your deployment (for example, seven).
Restart IIS.
Follow these steps to configure static file caching and content expiration if you are using Oracle HTTP Server or Apache HTTP Server with Oracle Business Intelligence.
To set up static file caching for Oracle HTTP Server or Apache HTTP Server:
On the Web server computer, open the file httpd.conf for editing.
This file is located in the Web server installation directory (for example, ORACLE_HOME/Apache/Apache/conf).
Verify that the following directive is included and not commented out:
LoadModule expires_module modules/mod_expires.so
|
Note: For Apache versions prior to 1.3.15 on Windows, the directive is LoadModule expires_module modules/ApacheModuleExpires.dll. |
Add the following lines to the file below the directive specified in step 2:
ExpiresActive On <IfModule mod_expires.c> ExpiresByType image/gif "access plus 7 days" ExpiresByType image/jpeg "access plus 7 days" ExpiresByType application/x-javascript "access plus 7 days" ExpiresByType text/css "access plus 7 days" </IfModule>
|
Note: In this example, content is set to expire in seven days. Set a value that is appropriate for your deployment. |
Save the file.
Restart the HTTP Server.
Performance can be improved by configuring the HTTP Server to serve the static files. By default, the static file requests for Oracle Business Intelligence are served by the WebLogic Server.
Because the Oracle Business Intelligence static files reside outside the HTTP Server's document root, you must configure the HTTP Server to access the files from a file system that is not the document root for the HTTP Server. Consult your vendor documentation for more information on configuring the HTTP Server to access files not residing in the document root.
On Apache HTTP Servers, the Alias directive might be used to map file systems that lie outside documentroot to the web space.
Use the following procedure to configure the bypass of Oracle Business Intelligence static files from the WebLogic Server and directing Oracle HTTP Server to serve the static file requests.
To bypass static files when using Oracle HTTP Server:
Open the httpd.conf file for editing.
This file is located in ORACLE_HOME/Apache/Apache/conf.
Verify that the following directive is not commented out in the httpd.conf file:
LoadModule expires_module modules/mod_expires.so
|
Note: For Apache versions prior to 1.3.15 on Windows, the directive is LoadModule expires_module modules/ApacheModuleExpires.dll |
Add the configuration into the httpd.conf file, as shown in the following example:
<Directory $ORACLE_HOME/bifoundation/web/app/res> Order allow,deny Allow from all </Directory> <Directory $ORACLE_HOME/bifoundation/web/app/olh> Order allow,deny Allow from all </Directory> Alias /OBIContent_res $ORACLE_HOME/bifoundation/web/app/res Alias /OBIContent_olh $ORACLE_HOME/bifoundation/web/app/olh <VirtualHost *:*> #ServerName bi.example.com RewriteEngine on RewriteRule ^/analytics/res/(.*)$ /OBIContent_res/$1 [PT] RewriteRule ^/analytics/olh/(.*)$ /OBIContent_olh/$1 [PT] </VirtualHost>
where:
The aliases OBIContent_res and OBIContent_olh are used to map the static files located in the following directories on the application server:
ORACLE_HOME/bifoundation/web/app/res
ORACLE_HOME/bifoundation/web/app/olh
|
Note: Replace bi.example.com with the virtual host name for your deployment. |
If SSL has been enabled on the HTTP Server, then add the following lines to the ssl.conf file that is located in the same directory as httpd.conf:
RewriteEngine on RewriteRule ^/analytics/res/(.*)$ /OBIContent_res/$1 [PT] RewriteRule ^/analytics/olh/(.*)$ /OBIContent_olh/$1 [PT] As shown below: <VirtualHost _default_:443> # General setup for the virtual host DocumentRoot "C:\OAS10.1.3\OracleAS_1\Apache\Apache\htdocs" ServerName bi.example.com RewriteEngine on RewriteRule ^/analytics/res/(.*)$ /OBIContent_res/$1 [PT] RewriteRule ^/analytics/olh/(.*)$ /OBIContent_olh/$1 [PT] ServerAdmin you@your.address
Restart Oracle HTTP Server.
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|