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.

Contents

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 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 time. 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 BR Execution Parameter SELECT_STMT_WITH_BIND and it can be controlled on three levels:

  1. Gobal 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 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))
FROM DIM_ACCOUNT_OL, FACT_GL_BALANCE_OL, DIM_COMPANY_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.CODE = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_COMPANY_OL.CODE = :c1
AND DIM_ACCOUNT_OL.COMPANY = :c2
GROUP BY DIM_ACCOUNT_OL.CODE

 
SELECT /*+ hint [text] [hint[text]]... */
DIM_ACCOUNT_OL.CODE, SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0))
FROM DIM_ACCOUNT_OL, FACT_GL_BALANCE_OL, DIM_COMPANY_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.CODE = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_COMPANY_OL.CODE = :c1
AND DIM_ACCOUNT_OL.COMPANY = :c2
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 DetailsHint 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,
DIM_ACCOUNT_OL.CODE,
DIM_ACCOUNT_OL.ACCOUNT_TYPE,
DECODE(MIN(DIM_ACCOUNT_OL.DESCRIPTION),
MAX(DIM_ACCOUNT_OL.DESCRIPTION),
MIN(DIM_ACCOUNT_OL.DESCRIPTION),
MIN(DIM_ACCOUNT_OL.DESCRIPTION) || '-=@=-' ||
MAX(DIM_ACCOUNT_OL.DESCRIPTION)),
SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0)),
DIM_CODE_B_OL.CODE
FROM DIM_ACCOUNTING_PERIOD_OL,
DIM_ACCOUNT_OL,
FACT_GL_BALANCE_OL,
DIM_CODE_B_OL,
DIM_COMPANY_OL

WHERE DIM_ACCOUNTING_PERIOD_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_ACCOUNTING_PERIOD_OL.ACCOUNTING_YEAR =
FACT_GL_BALANCE_OL.ACCOUNTING_YEAR_KEY
AND DIM_ACCOUNTING_PERIOD_OL.ACCOUNTING_PERIOD =
FACT_GL_BALANCE_OL.ACCOUNTING_PERIOD_KEY
AND DIM_ACCOUNT_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT
AND DIM_CODE_B_OL.COMPANY(+) = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_CODE_B_OL.CODE(+) = FACT_GL_BALANCE_OL.CODE_B
AND DIM_COMPANY_OL.CODE = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT
GROUP BY DIM_ACCOUNT_OL.ACCOUNT_TYPE,
DIM_ACCOUNTING_PERIOD_OL.CODE,
DIM_CODE_B_OL.CODE,
DIM_ACCOUNT_OL.CODE

 
SELECT /*+ ORDERED */
DIM_ACCOUNTING_PERIOD_OL.CODE,
DIM_ACCOUNT_OL.CODE,
DIM_ACCOUNT_OL.ACCOUNT_TYPE,
DECODE(MIN(DIM_ACCOUNT_OL.DESCRIPTION),
MAX(DIM_ACCOUNT_OL.DESCRIPTION),
MIN(DIM_ACCOUNT_OL.DESCRIPTION),
MIN(DIM_ACCOUNT_OL.DESCRIPTION) || '-=@=-' ||
MAX(DIM_ACCOUNT_OL.DESCRIPTION)),
SUM(NVL(FACT_GL_BALANCE_OL.BALANCE, 0)),
DIM_CODE_B_OL.CODE
FROM DIM_COMPANY_OL,
DIM_ACCOUNTING_PERIOD_OL,
DIM_ACCOUNT_OL,
FACT_GL_BALANCE_OL,
DIM_CODE_B_OL

WHERE DIM_ACCOUNTING_PERIOD_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_ACCOUNTING_PERIOD_OL.ACCOUNTING_YEAR =
FACT_GL_BALANCE_OL.ACCOUNTING_YEAR_KEY
AND DIM_ACCOUNTING_PERIOD_OL.ACCOUNTING_PERIOD =
FACT_GL_BALANCE_OL.ACCOUNTING_PERIOD_KEY
AND DIM_ACCOUNT_OL.COMPANY = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT
AND DIM_CODE_B_OL.COMPANY(+) = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_CODE_B_OL.CODE(+) = FACT_GL_BALANCE_OL.CODE_B
AND DIM_COMPANY_OL.CODE = FACT_GL_BALANCE_OL.COMPANY_KEY
AND DIM_ACCOUNT_OL.CODE = FACT_GL_BALANCE_OL.ACCOUNT
GROUP BY DIM_ACCOUNT_OL.ACCOUNT_TYPE,
DIM_ACCOUNTING_PERIOD_OL.CODE,
DIM_CODE_B_OL.CODE,
DIM_ACCOUNT_OL.CODE
 
 

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 form.

The SQL Statement field contains the SQL statement used during the last execution.

Clicking on Statement for Next Execution link opens a read-only editor window which 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 Client 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 and the values used to set the Oracle session 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 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 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 check box in the BR Report Level Execution Parameters form header has been enabled. Next use the Execute SQL Statement check box in the Data Set 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.