Adapters > Oracle Optimization Settings

Oracle Optimization Settings

Topics:

Adapter optimization allows the RDBMS to perform the work for which it is best suited, reducing the volume of RDBMS-to-server communication and improving response time. It also enables the RDBMS to exploit its own internal optimization techniques.

For more information, see Optimizing Requests and Optimizing Requests to Pass Virtual Fields Defined as Constants.

Optimizing Requests if a Virtual Field Contains Null Values

How to:

The SET OPTNOAGGR command provides finely-tuned control of adapter behavior for optimization. Users who for any reason wish to prevent passing aggregation to the RDBMS can use this command. An example of such a reason might be where NULL values occur in aggregated data with calculations. The SET OPTNOAGGR command causes the adapter to generate SQL without passing aggregation to the DBMS. Aggregation is instead performed internally by the server while JOIN and SORT operations are handled by the RDBMS.

If any DEFINE field contains calculations with NULL fields then such operations cannot be translated to SQL and pass to DBMS because always return NULL. It has to be processed by FOCUS.

This can be achieved by SET OPTIMIZATION OFF.

However, in some cases it is preferable to use the off-load JOIN and SORT operation to DBMS for better performance while leaving AGGREGATION to FOCUS.

Syntax: How to Set Enhanced Aggregation Control 

SQL SQLORA SET OPT {AGGR|NOAGGR}

where:

AGGR

Directs the adapter to off-load aggregated DEFINE fields to the DBMS. This is default setting.

NOAGGR

Directs the adapter to generate SQL without passing aggregation to the DBMS. Aggregation is, instead, performed internally by the server, while JOIN and SORT operations are handled by the RDBMS. This setting can also be used to provide backwards compatibility for applications that were written based on the functionality of the previous release, when less SQL was off-loaded to the RDBMS. For example, when a calculation on aggregated fields may have contained NULL data that was not processed by the RDBMS NVL( ) function.

Example: Optimizing Aggregation Requests

The following example is based on the SCOTT.EMP SCOTT.DEPT demo Oracle tables. 

JOIN CLEAR
JOIN DEPTNO IN EMP TO DEPTNO IN DEPT TAG D AS J1
DEFINE FILE EMP
LOC_FLAG/I2 = IF LOC EQ 'CHICAGO' THEN 1 ELSE 0 ;
BONUS_NEW/D15.2 = ((SAL + COMM)/2) * .2 ;
BONUS_BASE/D15.2 = (SAL + COMM) ;
END
TABLE FILE EMP
SUM COMM SAL BONUS_NEW MAX.LOC
FST.LOC_FLAG BONUS_BASE
-*
BY DNAME 
BY ENAME
-*
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
PRINT *
END

With OPTIMIZATION ON, the default OPT AGGR adapter generates SQL, and if COMM is NULL, performs calculations in dbms and return 0 to report: 

18.01.35 AE SELECT T2."DNAME",T1."ENAME", SUM(T1."COMM"), SUM(T1."SAL"),
18.01.35 AE SUM((((T1."SAL" + T1."COMM") / 2) * .2)), MAX(T2."LOC"),
18.01.35 AE MIN((CASE (T2."LOC") WHEN 'CHICAGO' THEN 1 ELSE 0 END)),
18.01.35 AE SUM((T1."SAL" + T1."COMM")) FROM EMP T1,DEPT T2 WHERE
18.01.35 AE (T2."DEPTNO" = T1."DEPTNO") GROUP BY T2."DNAME",T1."ENAME"
18.01.35 AE ORDER BY T2."DNAME",T1."ENAME";

This behavior can be overcome in two ways:

  • Use SET OPTIMIZATION OFF to direct the adapter sent to dbms to use the most basic (atomic) SQL and put maximum processing on FOCUS.

    or

  • Use SET OPT NOAGGR to pass JOIN to DBMS and leave aggregation to FOCUS, avoiding NULL calculations. Generated SQL still passes JOIN to Oracle to significantly improve performance: 
18.02.36 AE SELECT T1."EMPNO",T1."ENAME",T1."SAL",T1."COMM",T1."DEPTNO",
18.02.36 AE T2."DEPTNO",T2."DNAME",T2."LOC" FROM EMP T1,DEPT T2 WHERE
18.02.36 AE (T2."DEPTNO" = T1."DEPTNO") ORDER BY T1."EMPNO",T2."DEPTNO";

Specifying Block Size for Retrieval Processing

How to:

xThe Adapter for Oracle supports array retrieval from result sets produced by executing SELECT queries or stored procedures. This technique substantially reduces network traffic and CPU utilization.

Using high values increases the efficiency of requests involving many rows, at the cost of higher virtual storage requirements. A value higher than 100 is not recommended because the increased efficiency it would provide is generally negligible.

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

Syntax: How to Specify the Block Size for Array Retrieval

The block size for a SELECT request applies to TABLE FILE requests, MODIFY requests, MATCH requests, and DIRECT SQL SELECT statements. 

ENGINE SQLORA SET FETCHSIZE n

where:

SQLORA

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is the number of rows to be retrieved at once using array retrieval techniques. Accepted values are 1 to 5000. The default varies by adapter. If the result set contains a column that has to be processed as a CLOB or a BLOB, the FETCHSIZE value used for that result set is 1.

Syntax: How to Specify the Block Size for Insert Processing

xIn combination with LOADONLY, the block size for an INSERT applies to MODIFY INCLUDE requests. INSERTSIZE is also supported for parameterized DIRECT SQL INSERT statements. 

ENGINE SQLORA SET INSERTSIZE n

where:

SQLORA

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is the number of rows to be inserted using array insert techniques. Accepted values are 1 to 5000. 1 is the default value. If the result set contains a column that has to be processed as a BLOB, the INSERTSIZE value used for that result set is 1.

Improving Efficiency With Aggregate Awareness

How to:

xAggregate awareness substantially improves the efficiency of queries.

For details about this feature, see Aggregate Awareness Support.

Syntax: How to Set Aggregate Awareness 

SET AGGREGATE_AWARENESS {FRESH_ONLY|OLD_OK|OFF}

where:

FRESH_ONLY

Sets different values for the parameters associated with each RDBMS.

OLD_OK

Sets different values for the parameters associated with each RDBMS.

OFF

If no option is selected, the behavior of the target RDBMS is determined by the database configuration options. There is no default for this setting.

For details about adapter-specific settings, see Usage Notes for Aggregate Awareness.

Optimizing Non-equality WHERE-based Left Outer Joins

How to:

Reference:

A left outer join selects all records from the host table and matches them with records from the cross-referenced table. When no matching records exist, the host record is still retained, and default values (blank or zero) are assigned to the cross-referenced fields. The adapter can optimize any WHERE-based left outer join command in which the conditional expression is supported by the RDBMS.

Syntax: How to Specify a Conditional Left Outer JOIN 

JOIN LEFT_OUTER FILE hostfile AT hfld1 [TAG tag1]
     [WITH hfld2]
     TO {UNIQUE|MULTIPLE} 
     FILE crfile AT crfld [TAG tag2] [AS joinname]
     [WHERE expression1;
     [WHERE expression2;
     ...]
 
END

where:

LEFT_OUTER

Specifies a left outer join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.

hostfile

Is the host Master File.

AT

Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are used as segment references.

hfld1

Is the field name in the host Master File whose segment will be joined to the cross-referenced data source. The field name must be at the lowest level segment in its data source that is referenced.

tag1

Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.

WITH hfld2

Is a data source field with which to associate a DEFINE-based conditional JOIN. For a DEFINE-based conditional join, the KEEPDEFINES setting must be ON, and you must create the virtual fields before issuing the JOIN command.

MULTIPLE

Specifies a one-to-many relationship between from_file and to_file. Note that ALL is a synonym for MULTIPLE.

UNIQUE

Specifies a one-to-one relationship between hostfile and crfile. Note that ONE is a synonym for UNIQUE.

Note: Unique returns only one instance and, if there is no matching instance in the cross-referenced file, it supplies default values (blank for alphanumeric fields and zero for numeric fields).

The unique join is a WebFOCUS concept. The RDBMS makes no distinction between unique and non-unique situations. It always retrieves all matching rows from the cross-referenced file.

If the RDBMS processes a join that the request specifies as unique, and if there are, in fact, multiple corresponding rows in the cross-referenced file, the RDBMS returns all matching rows. If, instead, optimization is disabled so that WebFOCUS processes the join, a different report results because WebFOCUS, respecting the unique join concept, returns only one cross-referenced row for each host row.

crfile

Is the cross-referenced Master File.

crfld

Is the join field name in the cross-referenced Master File. It can be any field in the segment.

tag2

Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.

joinname

Is the name associated with the joined structure.

expression1, expression2

Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.

Reference: Conditions for WHERE-Based Outer Join Optimization

  • In order for a WHERE-based left outer join to be optimized, the expressions must be optimizable for the RDBMS involved and at least one of the following conditions must be true:
    • The JOIN WHERE command contains at least one field1 EQ field2 predicate in which field1 is in table1 and field2 is in table2.

      or

    • The right table has a key or a unique index that does not contain NULL data.

      or

    • The right table contains at least one "NOT NULL" column that does not have a long data type (such as TEXT or IMAGE).
  • The adapter SQLJOIN OUTER setting must be ON (the default).

Example: Optimizing a Non-Equality Left-Outer Join

The following request creates a left outer conditional join between two Oracle data sources and reports against the joined data sources. The STMTRACE is turned on in order to view the SQL generated for this request: 

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
JOIN LEFT_OUTER FILE baseapp/EQUIP AT CARS
TO ALL FILE baseapp/CARREC AT CARC
WHERE CARS NE CARC;
END
TABLE FILE baseapp/EQUIP
PRINT CARS CARC STANDARD
BY MODEL
END

The WebFOCUS request is translated to a single Oracle SELECT statement that incorporates the left outer join, and the non-equality condition is passed to the RDBMS in the ON clause: 

SELECT T1."CARS"(CHAR( 16)),T1."STANDARD"(CHAR( 40)),           
T2."CARC"(CHAR( 16)),T2."MODEL"(CHAR( 24)) FROM ( EQUIP T1 LEFT  
OUTER JOIN CARREC T2 ON (T1."CARS" <> T2."CARC") ) ORDER BY      
T2."MODEL";

Improving Optimizer Efficiency With Hints

How to:

DBMS Optimizer hints can be used to alter an execution plan. The adapter provides a setting which enable the TABLE command to place the hints after the SELECT keyword for Oracle.

This occurs when the adapter constructs a single SELECT statement. It does not occur in the case of a FOCUS-managed Join when multiple SELECTs are generated.

To reverse the setting, use SET HINT without the hint_text parameter.

Syntax: How to Setting Specific Hints

Use the following syntax to set specific hints: 

SQL SQLORA SET HINT /* +hint_text */

Where

SQLORA

Is the target RDBMS. You can omit this value if you previously issued the SET SQLENGINE command.

+hint_text

Is the text of the hint or hints combination. Note that Oracle returns no error if the syntax is invalid. The Optimizer just ignores such hints. The end-user is responsible for the syntax. Omitting +hint_text resets the hint to none.

Example: Setting an Oracle Hints Combination 

SQL SQLORA SET HINT /* +USE_HASH PARALLEL(EMPNO) INDEX_ASC */
TABLE FILE EMP 
  PRINT EMPNO SAL BY DEPTNO
  IF DEPTNO GE 5
END

The WebFOCUS request is translated to a Oracle SELECT statement that incorporates the specified hints combination 

SELECT
  /* +USE_HASH PARALLEL(EMPNO) INDEX_ASC */
  T1."EMPNO",
  T1."SAL",
  T1."DEPTNO"
FROM
  SCOTT.EMP T1
WHERE
  (T1."DEPTNO" >= 5)
ORDER BY
  T1."DEPTNO";

 WebFOCUS

Feedback