Skip to content

Tuning Advice

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:

  1. 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.
  2. 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:

  1. Global level, affecting all reports
  2. Reports level, affecting all statements for a given report
  3. 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.

Adding Hints

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
SELECT DIM_ACCOUNT_OL.CODE, SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0))<br/> FROM DIM_ACCOUNT_OL, FACT_GL_BALANCE_OL, DIM_COMPANY_OL<br/> WHERE DIM_ACCOUNT_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT<br/> AND DIM_COMPANY_OL.CODE = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_COMPANY_OL.CODE = :c1<br/> AND DIM_ACCOUNT_OL.COMPANY = :c2<br/> GROUP BY DIM_ACCOUNT_OL.CODE
 
SELECT /*+ hint [text] [hint[text]]... */<br/> DIM_ACCOUNT_OL.CODE, SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0))<br/> FROM DIM_ACCOUNT_OL, FACT_GL_BALANCE_OL, DIM_COMPANY_OL<br/> WHERE DIM_ACCOUNT_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT<br/> AND DIM_COMPANY_OL.CODE = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_COMPANY_OL.CODE = :c1<br/> AND DIM_ACCOUNT_OL.COMPANY = :c2<br/> GROUP BY DIM_ACCOUNT_OL.CODE
 

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 Level Note: 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.

E.g. 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
SELECT DIM_ACCOUNTING_PERIOD_OL.CODE,<br/> DIM_ACCOUNT_OL.CODE,<br/> DIM_ACCOUNT_OL.ACCOUNT_TYPE,<br/> DECODE(MIN(DIM_ACCOUNT_OL.DESCRIPTION),<br/> MAX(DIM_ACCOUNT_OL.DESCRIPTION),<br/> MIN(DIM_ACCOUNT_OL.DESCRIPTION),<br/> MIN(DIM_ACCOUNT_OL.DESCRIPTION) || '-=@=-' ||<br/> MAX(DIM_ACCOUNT_OL.DESCRIPTION)),<br/> SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0)),<br/> DIM_CODE_B_OL.CODE<br/> FROM DIM_ACCOUNTING_PERIOD_OL,<br/> DIM_ACCOUNT_OL,<br/> FACT_GL_BALANCE_OL,<br/> DIM_CODE_B_OL,<br/> DIM_COMPANY_OL<br/> WHERE DIM_ACCOUNTING_PERIOD_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_ACCOUNTING_PERIOD_OL.ACCOUNTING_YEAR =<br/> FACT_GL_BALANCE_OL.ACCOUNTING_YEAR_KEY<br/> AND DIM_ACCOUNTING_PERIOD_OL.ACCOUNTING_PERIOD =<br/> FACT_GL_BALANCE_OL.ACCOUNTING_PERIOD_KEY<br/> AND DIM_ACCOUNT_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT<br/> AND DIM_CODE_B_OL.COMPANY(+) = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_CODE_B_OL.CODE(+) = FACT_GL_BALANCE_OL.CODE_B<br/> AND DIM_COMPANY_OL.CODE = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT<br/> GROUP BY DIM_ACCOUNT_OL.ACCOUNT_TYPE,<br/> DIM_ACCOUNTING_PERIOD_OL.CODE,<br/> DIM_CODE_B_OL.CODE,<br/> DIM_ACCOUNT_OL.CODE
 
SELECT /*+ ORDERED */<br/> DIM_ACCOUNTING_PERIOD_OL.CODE,<br/> DIM_ACCOUNT_OL.CODE,<br/> DIM_ACCOUNT_OL.ACCOUNT_TYPE,<br/> DECODE(MIN(DIM_ACCOUNT_OL.DESCRIPTION),<br/> MAX(DIM_ACCOUNT_OL.DESCRIPTION),<br/> MIN(DIM_ACCOUNT_OL.DESCRIPTION),<br/> MIN(DIM_ACCOUNT_OL.DESCRIPTION) || '-=@=-' ||<br/> MAX(DIM_ACCOUNT_OL.DESCRIPTION)),<br/> SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0)),<br/> DIM_CODE_B_OL.CODE<br/> FROM DIM_COMPANY_OL,<br/> DIM_ACCOUNTING_PERIOD_OL,<br/> DIM_ACCOUNT_OL,<br/> FACT_GL_BALANCE_OL,<br/> DIM_CODE_B_OL<br/> WHERE DIM_ACCOUNTING_PERIOD_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_ACCOUNTING_PERIOD_OL.ACCOUNTING_YEAR =<br/> FACT_GL_BALANCE_OL.ACCOUNTING_YEAR_KEY<br/> AND DIM_ACCOUNTING_PERIOD_OL.ACCOUNTING_PERIOD =<br/> FACT_GL_BALANCE_OL.ACCOUNTING_PERIOD_KEY<br/> AND DIM_ACCOUNT_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT<br/> AND DIM_CODE_B_OL.COMPANY(+) = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_CODE_B_OL.CODE(+) = FACT_GL_BALANCE_OL.CODE_B<br/> AND DIM_COMPANY_OL.CODE = FACT_GL_BALANCE_OL.COMPANY_KEY<br/> AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT<br/> GROUP BY DIM_ACCOUNT_OL.ACCOUNT_TYPE,<br/> DIM_ACCOUNTING_PERIOD_OL.CODE,<br/> DIM_CODE_B_OL.CODE,<br/> DIM_ACCOUNT_OL.CODE<br/>  

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 FROM clause 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 = /*+ 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

TKPROF is a performance diagnostic tool provided by Oracle. A usage scenario is the following:

  1. A specific IFS Business Reporter report has bad performance.
  2. Enable the execution parameter ENABLE_TKPROF_OUTPUT on report level.
  3. 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.
  4. Execute the report. The following will happen:
    1. The Oracle sessions parameters timed_statistics and sql_trace are set to TRUE.
    2. The data set specific SQL statements are executed.
    3. When execution is ready, the Oracle sessions parameters timed_statistics and sql_trace are reset to the initial value, normally FALSE.
    4. During execution a dump file has been created in the USER_DUMP_DEST directory.
  5. 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.
  6. 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.
  7. 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.
  8. Now run the report to find out if the actions have given the expected improvements.
  9. If not ok, try to analyze again.
  10. 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.