Sets the type of antijoin that the Oracle Server uses. The system checks to verify that it is legal to perform an antijoin; if it is, the system processes the subquery depending on the value of this parameter. When set to the value NESTED_LOOPS, the Oracle Server uses a nested loop antijoin algorithm. When set to the value MERGE, the Oracle Server uses the sort merge antijoin algorithm. When set to the value HASH, the Oracle Server uses the hash antijoin algorithm to evaluate the subquery.

Allows the optimizer to use bitmap index plans even though a table only has B-Tree indexes.

Specifies the amount of memory used to merge bitmaps retrieved from a range scan of the index. A larger value should improve performance because the bitmap segments must be sorted before being merged into a single bitmap.

Determines whether complex views and subqueries are evaluated on their own or are merged into the entire query for evaluation.

Specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on factors such as the size of the table, the value of MULTI_BLOCK_READ_COUNT, and whether Parallel Query is being utilized for the operation. Batch environments typically have values for this parameter in the range of 4 to 16. Decision Support System (DSS) and data warehouse database environments tend to benefit from maximizing the value for this parameter. The actual maximum varies by operating system and is always less than the operating system’s maximum I/O size expressed as Oracle blocks (maximum I/O size divided by DB_BLOCK_SIZE). Attempts to set this parameter to a value greater than the maximum will cause the maximum to be used.

Allows the optimizer to perform a full index scan rather than a full table scan if all the necessary columns are in the index.

Specifies the maximum amount of memory, in bytes, to be used for hash joins.

Determines whether or not hash joins are allowed to be used by the optimizer.

Specifies how many sequential blocks a hash join reads and writes in one I/O. When operating in Multi-Threaded Server mode, however, this parameter is ignored (a value of 1 is used even if you set the parameter to another value). The maximum value is always less than the operating system’s maximum I/O size expressed as Oracle blocks.

This parameter collectively enables or disables B_TREE_BITMAP_PLANS, COMPLEX_VIEW_MERGING, FAST_FULL_SCAN_ENABLED, and PUSH_ JOIN_PREDICATE.

Specifies the behavior of the optimizer. When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement; otherwise, the rule-based approach is used. FIRST_ROWS causes the cost-based optimizer to choose execution plans that minimize response time. ALL_ROWS causes the cost-based optimizer to choose execution plans that minimize total execution time.

Specifies the amount of parallelism that the optimizer uses in its cost functions. The default of means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object’s degree of parallelism in computing the cost of a full table scan operation. Low values favor indexes; high values favor table scans.

Specifies the search limit for the optimizer.

Allows the optimizer to push certain predicates into a view on the right side of an outer join. This can result in a more efficient access path being generated.

Specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. If MTS is enabled, the sort area is allocated from the SGA. After the sort is complete, when all that remains to be done is to fetch the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched, all memory is freed. The memory is released back to the PGA or SGA, not to the operating system. Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time. The default is usually adequate for most OLTP (online transaction processing) operations, but it may be desirable to adjust this parameter for decision support systems, batch jobs, or large CREATE INDEX operations.

SORT_DIRECT_WRITES can improve sort performance if memory and temporary space are abundant on your system. This parameter controls whether sort data will bypass the buffer cache to write intermediate sort results to disk. When it is set to the default of AUTO, and when the sort area size is greater than ten times the block size, memory is allocated from the sort area to write intermediate results. When SORT_DIRECT_WRITES is TRUE, additional buffers are allocated from memory during each sort, and additional temporary segment space may be required. When SORT_DIRECT_WRITES is set to FALSE, the sorts that write to disk write through the buffer cache.