HASH_AREA_SIZE

Oracle Database Tips by Donald Burleson

The HASH_AREA_SIZE value determines the amount of area available to prepare the hash tables for a hash join operation. If there is insufficient space then the hash areas are created on disk. In Oracle9i Oracle suggests setting the value of PGA_AGGREGATE_TARGET instead, but offer no methodology for setting it.

SORT_AREA_SIZE

The SORT_AREA_SIZE value determines the amount of area available to prepare the hash tables for a hash join operation. If there is insufficient space then the sort areas are created on disk. In Oracle9i Oracle suggests setting the value of PGA_AGGREGATE_TARGET instead, but offer no methodology for setting it. For startup the value of HASH_AREA_SIZE is defaulted to twice the setting of SORT_AREA_SIZE if PGA_AGGREGATE_TARGET is not set.

DB_FILE_MULTIBLOCK_IO_COUNT, SORT_MULTIBLOCK_READ_COUNT, HASH_MULTIBLOCK_IO_COUNT and DIRECT_IO_COUNT

In Oracle8i and previous release the DBA could control the IO characteristics of various types of operations by how the parameters DB_FILE_MULTIBLOCK_IO_COUNT, SORT_MULTIBLOCK_READ_COUNT and HASH_MULTIBLOCK_IO_COUNT were set. The default values for these parameters are:

Parameter	Default Setting
DB_FILE_MULTIBLOCK_IO_COUNT	8
SORT_MULTIBLOCK_READ_COUNT	2
HASH_MULTIBLOCK_IO_COUNT	0 (same as DB_FILE_MULTIBLOCK_IO_COUNT)
DIRECT_IO_COUNT	64

In Oracle9i all but DB_FILE_MULTIBLOCK_IO_COUNT become undocumented parameters and are supposedly controlled by the PGA_AGGREGATE_TARGET variable, but that they have gone to undocumented with the same exact settings instead of 0 which is used to indicate calculated variables tells me this is probably a documentation error.

If you determine from explain plans that sorts are being done and then confirm with a select against v$sysstat that disk sorts are also happening dynamically increase the SORT_AREA_SIZE and if this doesn't force sorts into memory, then have the DBA increase the value of SORT_MULTIBLOCK_READ_COUNT to optimize the reading of the disk sort areas.

If you determine that your queries are using hash joins, dynamically increase the parameter HASH_AREA_SIZE to prevent them from going to disk, unfortunately there are no hash tracking statistics, but if you do an IO balance against the temporary tablespace using the V$FILESTAT table you should be able to see if hashes are being written o disk by ratioing the blocks read by the number of read operations. If the ratio is greater than 2 but less than the DB_FILE_MULTIBLOCK_IO_COUNT then hashes are being written to disk (note: HASH_JOIN_ENABLED must be set to TRUE to use hash joins). 

If you are using global temporary tables the ratio of blocks read to read operations for the temporary tablespace may be between DB_FILE_MULTIBLOCK_IO_COUNT and DIRECT_IO_COUNT.

Note that in Oracle9i the parameters except for DB_FILE_MULTIBLOCK_IO_COUNT have been deprecated which means they can still be used but not for long.

Assign rollback segments

Using the ALTER TRANSACTION call or a call to DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT allows the developer to place a transaction into a rollback segment sized for the transaction. You need to COMMIT, bring the rollback segment online, issue the call and then take the rollback segment offline to prevent other transactions from using it. IN Oracle9i using automated UNDO this is not required. However, there have been significant bugs reported against automated rollbacks in 9i R1, so you should wait until 9i R2 to use this feature.  A short procedure can be placed in a utility package similar to the one shown in Figure 27 to facilitate this rollback assignment.

create or replace procedure use_rollback(
   rbk_name IN VARCHAR2,
   online_offline IN BOOLEAN) is
sql_text VARCHAR2(255);
Begin
IF online_offline THEN
 commit;
 sql_text:='ALTER ROLLBACK SEGMENT '||rbk_name||' online';
  EXECUTE IMMEDIATE sql_text;
 sql_text:='ALTER TRANSACTION USE ROLLBACK SEGMENT '||rbk_name;
  EXECUTE IMMEDIATE sql_text;
 sql_text:='ALTER ROLLBACK SEGMENT '||rbk_name||' OFFLINE';
  EXECUTE IMMEDIATE sql_text;
END IF;
end;

Figure 27: Example Procedure to control Rollback segment Usage