Ask the Expert

Completing processes without temp space

We have 16 GB of RAM on a test machine. We run some ETL processes, and it creates 12 GB of temp space used. We migrate that to a prod machine with 32 GB of RAM and run it. The process creates 12 GB of temp space used. How can we force processes to complete in memory, rather than using temp space? There are a number of different scripts at work, so I'm not asking for detailed analysis of our code, but rather the general question of how Oracle will allow all the stuff to happen without using temp space (given we have enough RAM).

    Requires Free Membership to View

Oracle uses temporary space for a few operations such as sorting, performing hash joins, and creating bitmap indexes. The amount of sort space that each Oracle process can use in memory is determined by the SORT_AREA_SIZE, HASH_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE parameters. In Oracle 9i, the new PGA_AGGREGATE_TARGET parameter takes the place of all of these. To increase the amount of space that Oracle can use in memory, before using disk space as staging areas for the work, increase the appropriate INIT.ORA parameter.

This was first published in November 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: