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.
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:
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.
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:
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 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)) |
SELECT /*+ hint [text] [hint[text]]... */ |
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.
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, |
SELECT /*+ ORDERED */ |
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]]... */
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.
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.
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 is a performance diagnostic tool provided by Oracle. A usage scenario is the following: