Oracle7 Server Tuning
Steps for Tuning Memory Allocation
This section outlines the process of tuning memory allocation. For best results, you should follow these steps in the order they are presented. Each step is described in more detail in the remainder of this chapter.
Tuning Your Operating System
Ensuring that your operating system runs smoothly and efficiently establishes a solid basis for allocating memory to Oracle. This step also gives you a good idea of the amount of memory on your operating system that is available for Oracle.
Tuning Private SQL and PL/SQL Areas
The use of SQL and PL/SQL areas and the frequency of parse calls are primarily determined by your application. Since parsing affects the frequency of access to the data dictionary, you should tune private SQL and PL/SQL areas before tuning the data dictionary cache in the shared pool.
Tuning the Shared Pool
Tuning the shared pool involves allocating memory for these memory structures:
- the library cache containing shared SQL and PL/SQL areas
- the data dictionary cache
- information for sessions connected through shared server processes
A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, you should allocate sufficient memory for the shared pool first.
Tuning the Buffer Cache
After tuning private SQL and PL/SQL areas and the shared pool, you can devote the remaining available memory to the buffer cache.
It may be necessary to repeat the steps of memory allocation after the initial pass through the process. Subsequent passes allow you to make adjustments in earlier steps based on changes in later steps. For example, if you increase the size of the buffer cache, you may need to allocate more memory to Oracle to avoid paging and swapping.