This page provides general tuning advice related to BR Execution Parameters and the intention is to give more detailed information about some of the available parameters and how to use them to performance tune a IFS Business Reporter report.
Disable Use of Bind Variables
The star schema model provides a lot of flexibility but it also very easily leads to execution plans with many joins between a fact table and its related dimensions.
The standard way of handling SQL queries is to always use bind variables for the parameters. This means that the SQL statements built will never contain specific values but rather so called bind variables.
A statement using bind variables typically looks as follows:
SELECT SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0)), DIM_COMPANY_OL.COMPANY, DIM_ACCOUNT_OL.CODE FROM FACT_GL_BALANCE_OL, DIM_COMPANY_OL, DIM_ACCOUNT_OL WHERE DIM_ACCOUNT_OL.COMPANY=FACT_GL_BALANCE_OL.COMPANY_KEY AND DIM_ACCOUNT_OL.CODE=FACT_GL_BALANCE_OL.ACCOUNT AND DIM_COMPANY_OL.COMPANY=FACT_GL_BALANCE_OL.COMPANY_KEY AND DIM_COMPANY_OL.COMPANY = :c1 AND DIM_ACCOUNT_OL.COMPANY = :c2 GROUP BY DIM_COMPANY_OL.COMPANY, DIM_ACCOUNT_OL.CODE
The condition on company is represented by the bind variables :c1 and :c2.
If bind variables are not used, the statement looks as follows:
SELECT SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0)), DIM_COMPANY_OL.COMPANY, DIM_ACCOUNT_OL.CODE FROM FACT_GL_BALANCE_OL, DIM_COMPANY_OL, DIM_ACCOUNT_OL WHERE DIM_ACCOUNT_OL.COMPANY=FACT_GL_BALANCE_OL.COMPANY_KEY AND DIM_ACCOUNT_OL.CODE=FACT_GL_BALANCE_OL.ACCOUNT AND DIM_COMPANY_OL.COMPANY=FACT_GL_BALANCE_OL.COMPANY_KEY AND DIM_COMPANY_OL.COMPANY = '900' AND DIM_ACCOUNT_OL.COMPANY = '900' GROUP BY DIM_COMPANY_OL.COMPANY, DIM_ACCOUNT_OL.CODE
Using bind variables instead of true conditional values is done for two reasons:
- Avoiding SQL injections. With SQL injections we mean that there is a potential security vulnerability where a user can use the conditional values in different ways to get classified information from the database. It can also in some cases be possible to inject statements that can affect the database in a bad way.
- Oracle recommendations. The general recommendation from Oracle is to always use bind variables.
However using bind variables might lead to unwanted performance problems. For all SQL executions it is very important that the statistics is up-to-date in the database. When a statement is using bind variables, the Oracle optimizer must figure out via the used tables, conditional columns and bind variables etc. how to come up with the best execution plan. Probably the optimizer can learn if the same statement is executed many times. However for BR report executions, most of the generated statements differ from each other, so the same statement will very seldom be executed many times in the same session.
In many practical cases the Oracle optimizer has for some reason selected a very bad execution path, many times leading to MERGE JOIN CARTESIAN which in most cases for BR generated queries should be avoided. For many of these cases the solution was to disable the use of bind variables.
Bind variable usage is controlled by the execution parameter SELECT_STMT_WITH_BIND and it can be controlled on three levels:
- Global level, affecting all reports
- Reports level, affecting all statements for a given report
- Data set level, affecting only a specific data set statement
Consider testing the use of this parameter when some data sets or reports take a very long time.
Note: Since disabling bind variable usage means opening up a potential security hole, it is important to get the customers acceptance for doing this action in a production environment. If the number of BR users are few and they are controllers, CFOs etc., then disabling bind variables should not be a big issue.
Optimizer hints are a common methodology for tuning SQL queries by modifying the default execution plan of a SQL statement.
Custom specific hints can be specified both at report level and data set level for a given report.
Note: Hint defined at data set level is superior.
An Oracle SQL hint is defined according to:
/*+ hint [text] [hint[text]]... */
When the SQL statement is executed the Original Statement will be modified as follows:
|Original Statement||Statement After Applying Hint|
Note: Hints specified at Report Level will be added to all individual SQL statements in the report, i.e. each data set SQL statement will be affected.
Note: If a general hint is specified in the Information Source Data Access Details - Hint Definition, that hint will be applied if no hint is defined at Report Level or Data Set LevelNote: Concatenation of hints will not take place even if there are hints specified on more than one level. The only exception is if the execution parameter ORDERED_HINT_VALUE is used.
The Ordered Hint
Oracle supports a hint named ORDERED, having the purpose to force the optimizer to consider the order of the sources in the
FROM clause in a
WHERE statement. The ORDERED hint can be added using the Execution parameter ORDERED_HINT_VALUE.
The execution parameter value should contain the view names in the required order separated by commas.
ORDERED_HINT_VALUE = DIM_COMPANY_OL,DIM_ACCOUNTING_PERIOD_OL,DIM_ACCOUNT_OL,FACT_GL_BALANCE_OL
|Original Statement||Statement After Applying the Parameter||Three|
In the example above the
/*+ ORDERED */ hint has been applied to the new statement. Also the order of the views in the
FROM clause has been modified according to the order of the views as supplied for the execution parameter ORDERED_HINT_VALUE.
Note: It is not necessary to specify all views used in the
FROMclause when defining the parameter value.
Note: If hints are defined on report or data set level, these hints will be added to the ORDERED hint if the ORDERED_HINT_VALUE execution parameter is used. Assume that we have the following hint defined for a data set:
/*+ hint [text] [hint[text]]... */.
If we now add the ORDERED_HINT_VALUE execution parameter with one or more source views specified as the parameter value, we will get a final hint looking as follows:
/*+ ORDERED hint [text] [hint[text]]... */
Oracle Session Specific Execution Parameters
These parameters only alter the Oracle database session that executes the SQL statement.
The session parameters are set before starting each data set execution and will be reset to the original value right after the report execution.
Examining the Previous and Next Statements
The current and next executions can be obtained from the BR Execution Parameters for Data Set page.
The SQL Statement field contains the SQL statement used during the last execution.
Clicking on Statement for Next Execution command opens a read-only dialog that displays the statement for next execution considering the hints and all the execution parameters defined at different levels.
These two SQL statements can be further examined using external tools such as PL/SQL Developer.
Using the Runtime Log
The client log contains all server related information logged during a IFS Business Reporter report execution.
To enable the log, use the BR Execution Parameter LOG_DURING_EXECUTION that is a global parameter for all report executions.
The log provides detail information regarding e.g. BR Execution Parameters, used SQL statements and parameter values.
The following log labels are important when working with performance tuning:
|Log Label||Contents in Information Message|
|GET_SES_EXE_PARAMETER||Displays the Oracle Session type BR execution parameters|
|SET_SESSION_PARAMETER||Displays information about setting Oracle Session parameters|
|RESET_SESSION_PARAMETER||Displays the information about resetting Oracle Session parameters to its original value|
|LOG_SET_STMT||The SQL statement|
|LOG_SET_STMT_BIND_VAR||Displays parameters (bind variables) and associated values used by a SQL statement|
TKPROF is a performance diagnostic tool provided by Oracle. A usage scenario is the following:
- A specific IFS Business Reporter report has bad performance.
- Enable the execution parameter ENABLE_TKPROF_OUTPUT on report level.
- Make sure that the Oracle instance has a defined output directory for trace files. This is handled by the Oracle parameter USER_DUMP_DEST, i.e. it must fully specify the destination for the trace file according to the conventions of the operating system. The parameter is defined in the Oracle instance configuration file.
- Execute the report. The following will happen:
- The Oracle sessions parameters timed_statistics and sql_trace are set to TRUE.
- The data set specific SQL statements are executed.
- When execution is ready, the Oracle sessions parameters timed_statistics and sql_trace are reset to the initial value, normally FALSE.
- During execution a dump file has been created in the USER_DUMP_DEST directory.
- Find the latest dump file in the USER_DUMP_DEST directory and then run the TKPROF utility. This tool can now provide useful information about executed SQL statements, used time, buffer reads, IO etc. The most time consuming SQL statements can be located and associated with a specific data set in the report. To do this mapping, it is necessary to investigate the SQL statements for all data sets in order to find out the associated data set.
- The TKPROF information can be used to take measures like e.g. adding indexes, trying out different hints, testing different parameters. Next try to use and set the necessary BR Execution Parameters for the performance candidate data sets.
- After setting parameters, one option is to enable for execution only the data sets that were tuned with parameters. First make sure that the Execute SQL Statements field in the BR Report Level Execution Parameters page header has been enabled. Next use the Execute SQL Statement field in the DATA SETS tab to make sure that only the necessary data sets are active for SQL execution.
- Now run the report to find out if the actions have given the expected improvements.
- If not ok, try to analyze again.
- When done, make sure to set the execution parameter ENABLE_TKPROF_OUTPUT to FALSE for the report. Also make sure to enable all data sets for SQL execution.