User Work Spaces

So far you have explored the memory areas Oracle uses internally to perform database services for the user. Where are the actual applications that are designed to perform specific processing tasks? Every application needs memory areas in which to run—work spaces. There are many important bits of data stored in this area (see Figure 10.4):

Figure 10.4.  The user work spaces.

•  Software code areas for Oracle processes

•  Software code areas for user processes

•  Sort areas

Oracle Application Software Memory

The memory space used to store the Oracle application software code is owned by the Oracle user and should be protected by the operating system from modification by other users. These areas are usually installed in a shareable fashion so that users can access parts of the software when needed. It is usually desirable to have one copy of the Oracle software available to multiple Oracle instances on the same computer. The size of the Oracle application software code area usually changes only when the software is upgraded.

The next software area to discuss is the memory space used by regular user processes. Most of the Oracle tools and utilities the DBA will work with are shareable. However, most custom applications require separate work areas for each user. Each of these user areas is relatively small when compared with the SGA, for example; however, the DBA and system administrator may need to keep an eye on these processes when there are a large number of users on the system. Unlike the SGA, it is not an extreme performance problem if inactive user processes are swapped out to disk.

The final user memory area to consider is the sort area. One of the most common clauses in a SQL select statement is the order by clause. Very few people want to have to search a long list of output in the order that Oracle chooses to return it. Therefore, most applications perform a fair amount of sorting. The speed of memory is especially noticeable when performing sorts. It can easily take five to ten times longer (or more) for a sorted query if it will not fit in memory. However, especially in large data warehouse Oracle instances, you may have to sort data that will not fit within the physical memory of the system; therefore, you have to live with disk-based sorts.

The sort area size varies depending on application needs. Its maximum size is controlled by the SORT_AREA_SIZE parameter set either in the initialization file or the Oracle default for your operating system. Once the sort is completed, the size of the sort area is reduced to the value specified by the SORT_AREA_RETAINED_SIZE parameter. If the entire set of data to be sorted does not fit within the sort area, the data is sorted in chunks that do fit into memory and then the chunks are merged together.

Summary

This chapter has covered the composition of the various memory areas used by the Oracle DBMS. The goal of these memory areas is to store information likely to be needed in a format that can be accessed quickly by applications. It also serves as a store where key control information related to the database and user processes are stored. The sizing of these memory areas is one of the key factors that can be controlled to improve performance in database tuning.