Query Performance Optimization

A configuration file, OracleDB.conf has been introduced to accommodate any configurable parameter related to operations for Oracle database. If you do not want to set a parameter to a specific value, then the respective parameter entry can be removed/commented from the OracleDB.conf file which resides in the path $FIC_DB_HOME/conf.

The following table details the configurable OFSAA parameters in OracleDB.conf file with its purpose and the way it maps to Oracle Database Parallelism settings.

Table 12-2 Query Performance Optimization parameters and their description

Parameters	Description
CNF_PARALLEL_DEGREE_POLICY	Sets the parallel degree policy. Possible values – MANUAL, LIMITED, or AUTO. Query fired on the database - ALTER SESSION SET PARALLEL_DEGREE_POLICY=<<CNF_PARALLEL_DEGREE_POLICY>>
CNF_PARALLEL_QUERY	Sets parallelism for queries. Possible values – DISABLE, ENABLE, or FORCE. Query fired on the database - ALTER SESSION <<CNF_PARALLEL_QUERY>> PARALLEL QUERY
CNF_PARALLEL_DML	Sets parallelism for DML operations. Possible values – DISABLE, ENABLE, or FORCE. Query fired on the database - ALTER SESSION <<CNF_PARALLEL_QUERY>> PARALLEL DML
CNF_DEGREE_OF_PARALLELISM	Sets the degree of parallelism. Possible values – Value can be any positive integer. The default mode of a session is DISABLE PARALLEL DML. If CNF_DEGREE_OF_PARALLELISM is not set, then the default degree, as decided by Oracle will be used. Queries fired on the database - ALTER SESSION <<CNF_PARALLEL_QUERY>> PARALLEL QUERY PARALLEL <<CNF_DEGREE_OF_PARALLELISM>> ALTER SESSION <<CNF_PARALLEL_QUERY>> PARALLEL DML PARALLEL <<CNF_DEGREE_OF_PARALLELISM>>

For more information, see the Using Parallel Execution section in Oracle Database VLDB and Partitioning Guide.