Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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.


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.


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:


Default Setting









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);
IF online_offline THEN
 sql_text:='ALTER ROLLBACK SEGMENT '||rbk_name||' online';
 sql_text:='ALTER ROLLBACK SEGMENT '||rbk_name||' OFFLINE';

Figure 27: Example Procedure to control Rollback segment Usage

Learn More about Oracle Tuning:

This is an excerpt from the top selling book "Oracle PL/SQL Tuning" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the online  code depot of working PL/SQL examples.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.