If you are running Oracle Business Intelligence on the Oracle Exalytics Machine, you can use the Oracle BI Summary Advisor feature to identify which aggregates increase query performance and to generate a script for creating the recommended aggregates.
Note:
If you are not running Oracle Business Intelligence on the Oracle Exalytics Machine, the Oracle BI Summary Advisor feature is not available.
This section contains the following topics:
To reduce query time, you can create aggregate tables that store precomputed results for queries that include rolled-up data.
Before creating aggregates, however, you need to analyze usage tracking statistics to identify which aggregates will increase query performance. As an alternative to manually identifying aggregates, which can be a slow and laborious process, you can use the Summary Advisor feature, which intelligently recommends an optimal list of aggregate tables based on query patterns that will achieve maximum query performance gain while meeting specific resource constraints. Summary Advisor then generates an aggregate creation script that can be run to create the recommended aggregate tables.
There are several parts to the Summary Advisor feature:
Statistics collection into a designated statistics database. You must enable the Usage Tracking feature to collect Summary Advisor statistics.
Summary Advisor execution through the Summary Advisor Wizard in the Administration Tool to evaluate the collected statistics and recommend aggregates.
This section contains the following topics:
Before Summary Advisor can generate recommendations, you must obtain a representative sample of usage statistics for Summary Advisor to use.
Enabling Usage Tracking and Summary Advisor Logging has a minor system performance impact on production systems.
Use one of the following approaches to gather Summary Advisory statistics:
Enable Usage Tracking and Summary Advisor Logging on a production system, and let users run queries against Oracle BI Server for several days. The Summary Advisor Statistics Table is populated with usage statistics. See Turning On Usage Tracking and Turning On Summary Advisor Logging.
In a test environment, run a representative workload against the Oracle BI Server to gather Summary Advisor statistics. A representative workload is a list of commonly requested Logical SQL statements. You typically obtain a representative workload from your production environment.
After you have the representative workload, enable Usage Tracking and Summary Advisor Logging on the Oracle BI Server in your test environment, and use the nqcmd utility to run the workload against the Oracle BI Server. See Using nqcmd to Test and Refine the Repository. The Summary Advisor Statistics Table is populated with usage statistics.
After the Summary Advisor Statistics table is populated with representative data, the Summary Advisor can analyze the data and generate aggregate recommendations to speed up queries.
Run the Oracle BI Summary Advisor Wizard in the Administration Tool to generate an aggregate specification, and then use the aggregate specification to create aggregates using nqcmd. See Generating an Aggregate Specification Script and Running the Aggregate Specification Against the Oracle BI Server.
Oracle BI Summary Advisor supports aggregate creation on Oracle TimesTen In-Memory Database, Oracle BI EE, or when using Oracle Database In-Memory on Oracle Exalytics. Refer to System Requirements and Certification.
You can also save your Summary Advisor options to a file, and re-run the Oracle BI Summary Advisor Wizard later without re-entering the same options.
Learn about using the Summary Advisor with aggregates and specific measures.
When the Only include measures used in queries option is set in the Summary Advisor wizard’s Miscellaneous page, the Summary Advisor only recommends aggregates that contain specific measures that are both present in the analyzed query workload, and that can optimize the query workload if aggregates are created.
Note:
The following information about measure subset recommendations:
Summary Advisor does not include measures that are not used in the query workload in its recommended aggregates.
Size estimation of aggregate fact tables is based on the recommended measure subset instead of using all the measures of a logical fact table during estimation.
Summary Advisor does not include measures that are invalid for aggregate persistence in its recommended aggregates.
Before you can use the Oracle BI Summary Advisor feature, you must set up a database to store the collected statistics.
You must run the Repository Creation Utility (RCU) on the target database to create the required statistics schema.
See Installing and Configuring Oracle Business Intelligence.
You use the database you installed for use with Oracle Business Intelligence as the statistics database because this database already has the RCU-created schemas. The RCU-created table name for Summary Advisor is S_NQ_SUMMARY_ADVISOR.
You also need to import the database into the Physical layer of the Oracle BI repository.
You must use the same database for Summary Advisor that you use for usage tracking. If you already have a database and schema set up for usage tracking, you can skip the steps in this section.
Use the Upload Repository Command to upload the repository and make it available for queries. See Making the Repository Available for Queries.
Review the columns in the S_NQ_SUMMARY_ADVISOR table.
| Column | Description |
|---|---|
|
GROUPBYCOLUMNIDVECTOR |
Upgrade IDs for logical column objects that represent group-by columns in a processing path. A processing path is an internal Oracle BI Server term. It represents a subquery that involves a single fact logical table source. |
|
LOGICALFACTTABLEID |
Upgrade ID of the logical fact table. |
|
LOGICALTABLESOURCEIDVECTOR |
Upgrade IDs of the logical table sources. |
|
LOGICAL_QUERY_ID |
Foreign key that references the ID column in |
|
MEASURECOLUMNIDVECTOR |
Upgrade IDs for logical column objects that represent measures in a processing path. |
|
PROCESSINGTIMEINMILLISEC |
Time spent on this processing path, in milliseconds. |
|
QUERYLEVELIDVECTOR |
Upgrade IDs of the logical levels in a processing path. |
|
QUERYSTATUS |
For internal use only. |
|
ROW_COUNT |
The number of rows retrieved in a processing path. Data in this column is reserved for use by Oracle BI Summary Advisor. |
|
SOURCECELLLEVELIDVECTOR |
Upgrade IDs of the logical levels in the logical table source. |
|
VERSION |
Version number of the Oracle BI Server. |
You must enable usage tracking before collecting Summary Advisor statistics.
See Managing Usage Tracking in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
When you are ready to collect statistics, you can enable Summary Advisor logging. For new (non-upgraded) installations, the Summary Advisor parameters are centrally managed.
Enabling Summary Advisor logging
You can manage the Summary Advisor parameters using NQSConfig.INI.
To enable Summary Advisor logging in NQSConfig.INI when central management is disabled for these parameters, follow these steps:
NQSConfig.INI file in a text editor. You can find this file at:
ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn
Make a backup copy of the file before editing.
In the [USAGE_TRACKING] section, update the following parameters:
Set SUMMARY_STATISTICS_LOGGING to one of the following options:
YES: Enables Summary Advisor logging.
LOG_OUTER_JOINT_QUERIES_ONLY: Enables Summary Advisor logging only for logical queries that contain outer joins. Consider using this option when the minor performance impact of enabling full Summary Advisor logging is a concern.
Set SUMMARY_ADVISOR_TABLE_NAME to the name of the fully-qualified database table for collecting statistics, as it appears in the Physical layer of the Oracle BI repository. For example:
SUMMARY_ADVISOR_TABLE_NAME = "My_DB"."DEV_BIPLATFORM"."S_NQ_SUMMARY_ADVISOR";
The table name you specify must belong to the same database object and connection pool that you are using for usage tracking.
Save and close the file.
Restart the Oracle BI Server.
If you have multiple Oracle BI Server instances, then repeat these steps in each NQSConfig.INI file for all Oracle BI Server instances.
Adding Summary Advisor to the Administration Tool Menu
Open bi-config.xml
UNIX: $DOMAIN_HOME/config/fmwconfig/biconfig/core/bi-config.xml
Windows: %DOMAIN_HOME%\config\fmwconfig\biconfig\core\bi-config.xml
Search for the following attribute: <bi:hw-acceleration>.
Set this attribute to true.
Restart the Business Intelligence services. The menu option should now be visible.
After generating Summary Advisor statistics, you can run the Oracle BI Summary Advisor Wizard to generate an aggregate specification script that you can later run to create the aggregates.
You can only run the Summary Advisor Wizard in online mode. You can also run the Oracle BI Summary Wizard from the command line. See Using the nqaggradvisor Utility to Run the Oracle BI Summary Advisor.
Before you run the Summary Advisor Wizard, you must map the target database, used for creating the aggregates, into the Physical layer. You must manually create the necessary database, connection pool, and physical schema objects.
If you have used the Oracle BI Summary Advisor Wizard previously and saved your filter criteria, targets, and other options as an XML file, you can click Load Parameters from File to load the previously saved options into your current wizard session.
The Oracle BI Summary Advisor Wizard is available if you are running Oracle BI EE or when using Oracle Database In-Memory on Oracle Exalytics. You can execute the aggregate script, recommended by Summary Advisor or manually defined aggregates using Oracle Database In-Memory on Oracle Exalytics as the target.
If your Summary Advisor table, specified in the SummaryAdvisorTableName in the System MBean Browser, or the SUMMARY_ADVISOR_TABLE_NAME parameter in NQSConfig.INI is empty, Summary Advisor cannot proceed.
Summary Advisor Setting Recommendations
In the Summary Advisor’s Miscellaneous page, Oracle recommends:
Selecting Use surrogate keys to improve the performance of queries using the aggregates.
Selecting Prefer optimizer estimates to improve performance during the Summary Advisor process.
The Prefer optimizer estimates option enables using cardinality estimates that originate out of the database query optimizer whenever possible, rather than issuing actual count queries. You can use the Prefer optimizer estimates option with Oracle Database, Microsoft SQL Server, and IBM DB2.
For Summary Advisor to use database query optimizer estimates, obtain up-to-date statistics on the concerned database objects. See the Oracle Database documentation for more information.
If you do not select the Prefer optimizer estimates option, Summary Advisor issues count queries to the back-end data sources to obtain row counts (cardinality) for certain queries on the data sources that can sometimes take a long time to execute. Refer the appropriate database documentation for guidelines on how to obtain the best estimates. For example, when using Oracle Database, you might want to use the column group feature to improve cardinality estimates for multiple column queries.
A query that attempts to sample a particular grain is not issued by Summary Advisor if an entry for that particular grain already exists in the Summary Advisor cache files, regardless of whether it is an actual count query or a cardinality estimate query.
You should remove the Summary Advisor cache files when selecting or deselecting the Prefer optimizer estimates option. To do this, delete NQAggregate.Stats.Cache.txt and NQAggregate.LTS.Stats.Cache.txt in the following directory on the Oracle BI Administration Tool computer:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\ coreapplication_obisn\aggr
Select Only include measures used in queries to include measures used in queries. See About Measure Subset Recommendations.
If you do not select this option, all measures in a logical fact table are included in the recommendation, including measures that were not used in the workload analyzed by Summary Advisor.
Oracle recommends running the Model Check Manager to ensure that your repository does not contain modeling problems that could affect Oracle BI Summary Advisor performance and results. See Using Model Check Manager to Check for Modeling Problems.
Oracle recommends running the Model Check during off-peak periods. The Model Check Manager runs queries against back-end data sources for some checks. Running the Model Check Manager for large repositories can take a long time. Use Filtered by Statistics, or run it only for selected objects, to improve performance.
In the Summary Advisor wizard Stopping Criteria page, you can specify run constraints for the set of recommendations.
Consider the following:
You can specify the maximum time that Summary Advisor runs before returning results.
You can specify a minimum percentage improvement to performance gain of all affected queries in the workload when adding a new aggregate.
Summary Advisor uses an iterative optimization algorithm. For each round of the iteration, Summary Advisor Summary Advisor evaluates a different set of aggregates. When you specify a minimum percentage improvement on this screen, Summary Advisor compares the estimated gain between two consecutive rounds, and stops when the incremental improvement is less than the specified minimum percentage.
The following formula describes the estimated gain between rounds:
Estimated Gain = [(total query time at the beginning of the round) - (total query time at the end of the round)] / (Initial total query time prior to the first round)
For example:
Initial total query time = 1000s
End of Round 1:
Total query time = 500s
Gain = (1000 - 500)/1000 = 50%
End of Round 2:
Total query time = 250s
Gain = (500 - 250)/1000 = 25%
You can use the Oracle BI Server utility nqaggradvisor to run the Summary Advisor from the command line instead of using the Oracle BI Administration Tool.
After Summary Advisor statistics have been generated, use nqaggradvisor to generate an aggregate specification script that you can then run to create the aggregates. The nqaggradvisor utility is only available if you are running Oracle Business Intelligence on the Oracle Exalytics Machine.
The location of the nqaggradvisor utility is:
BI_DOMAIN/bi/bitools/bin
Syntax
The nqaggradvisor utility takes the following parameters.
nQAggrAdvisor -d dataSource | -u userName | -o outputFile | -c tupleInQuotes [-p password] [-F factFilter] [-z maxSizeAggr] [-g gainThreshold] [-l minQueryTime] [-t timeoutMinutes] [-s startDate] [-e endDate] [-C on/off] [-M on/off] [-K on/off]
Where:
dataSource is the ODBC data source name for the Oracle BI Server to which you want to connect and run Summary Advisor.
userName is the user name with which to log into the data source. The specified user must have the privilege required to open the Administration Tool in online mode and use the Oracle BI Summary Advisor Wizard.
outputFile is the fully qualified path and file name of the output aggregate specification script.
tupleInQuotes is the aggregate persistence target. You must specify the fully qualified connection pool, fully qualified schema name, and capacity in megabytes.
password is the password corresponding to the userName. If not specified, the user is prompted for a password when executing nQAggrAdvisor.
factFilter is the fact filter file name. The fact filter file contains the fully qualified names of logical fact tables for which to generate Summary Advisor recommendations. Add each logical fact table's fully qualified name on a separate line. If a fact filter file is not specified, then all logical fact tables in the repository are included in the analysis.
maxSizeAggr is the maximum size of an aggregate in megabytes.
gainThreshold is the minimum percentage improvements to performance gain of all affected queries in the workload required by Summary Advisor when adding a new aggregate in its iterative optimization algorithm. Summary Advisor stops when this value is not satisfied. The default value is 1.
minQueryTime is the minimum query time threshold in seconds for each logical table source before it is included in the Summary Advisor execution. The default value is 0.
timeoutMinutes is the maximum time in minutes that Summary Advisor runs before returning results. Specify 0 for unlimited. The default value is 0.
startDate is the start date for statistics to include in the Summary Advisor execution.
endDate is the end date for statistics to include in the Summary Advisor execution.
-C specifies whether to use optimizer estimates. Specify on or off. The default is off.
-M specifies which measures to include in the recommendation. Specify on to include measures used in the workload. Specify off to include all measures in a logical fact table including those measures that were not used in the workload analyzed by Summary Advisor. The default is off.
-K specifies whether to use surrogate keys. Specify on or off. The default is on.
Examples
The following example shows how to correctly specify the tupleInQuotes parameter:
nQAggrAdvisor -d "AnalyticsWeb" -u "Administrator" -p "ADMIN" -o "C:\temp\aggr_advisor.out.txt" -c "DW_Aggr"."Connection Pool","DW_Aggr".."AGGR",1000
The following example shows how to correctly specify the gainThreshold, startDate, and endDate parameters.
nQAggrAdvisor -d "AnalyticsWeb" -u "Administrator" -p "ADMIN" -o "C:\temp\aggr_advisor.out.txt" -F "C:\temp\fact_filter.txt" -g 10 -c "TimesTen_instance1"."Connection Pool","dbo",2000 -s "2011-05-02 08:00:00" -e "2011-05-07 18:30:00" -C on -M on -K off