Tuning Queries with Blocking Operations

Issuing a query with more than one predicate can cause a blocking operation in the execution plan. For example, consider the following mixed query:

select docid from myindex where contains(text, 'oracle', 1) > 0 
  AND colA > 5 
  AND colB > 1 
  AND colC > 3; 

Assume that all predicates are unselective and colA, colB, and colC have bitmap indexes. The Oracle cost-based optimizer chooses the following execution plan:

TABLE ACCESS BY ROWIDS
  BITMAP CONVERSION TO ROWIDS
    BITMAP AND
      BITMAP INDEX COLA_BMX
      BITMAP INDEX COLB_BMX
      BITMAP INDEX COLC_BMX
      BITMAP CONVERSION FROM ROWIDS
        SORT ORDER BY
          DOMAIN INDEX MYINDEX

Since the BITMAP AND is a blocking operation, Oracle must temporarily save the rowid and score pairs returned from the interMedia Text domain index before executing the BITMAP AND operation.

Oracle attempts to save these rowid and score pairs in memory. However, when the size of the result set containing these rowid and score pairs exceeds the SORT_AREA_SIZE initialization parameter, Oracle spills these results to temporary segments on disk.

Since saving results to disk causes extra overhead, you can improve performance by increasing the SORT_AREA_SIZE parameter using ALTER SESSION as follows:

alter session set SORT_AREA_SIZE = <new memory size in bytes>;

For example, to set the buffer to approximately 8 megabytes, you can issue:

alter session set SORT_AREA_SIZE = 8300000;

See Also: For more information on SORT_AREA_SIZE, see Oracle8i Reference.