Understanding Memory Advisor

The Memory Advisor helps you tune the size of your memory structures. You can use the Memory Advisor only when automatic memory tuning is disabled in the database. When ASMM is disabled, the Memory Advisor is responsible for optimizing system memory for the database instance. Using the Memory Advisor, you can let Oracle tune the memory according to the database needs or use the SGA Advisor or the PGA Advisor to set the total size of the SGA or PGA and optimal values for their components.

The Memory Advisor consists of three advisors to give you recommendations on shared pool (SGA), buffer cache (SGA), and PGA. The main components of the shared pool are the library cache and the dictionary cache. The library cache stores the compiled form of recently used SQL and PL/SQL code, while the dictionary cache stores data accessed from the data dictionary. Buffer cache is used to store blocks read from disk. The PGA region has data and control information for a server process.

Memory Advisors Through OEM

You can use OEM to invoke the Memory Advisors. On OEM, access the Advisor Central page and click Memory Advisor in the Advisors list. The Memory ParametersSGA page appears, as shown in Figure 5.1. This shows all the details on memory usage of system global area.

Figure 5.1. The Memory ParametersSGA page in the OEM.

The shared pool and buffer cache are part of the SGA. If you need further information on any memory structure shown on this page, click Help. Click the Advice button to view a graphical representation of SGA size advice, as shown in Figure 5.2.

Figure 5.2. SGA size advice.

Assume for the sake of example that the Automatic Shared Memory Management is disabled on your database. If not, disable it on the SGA page. Then choose either Shared Pool or Buffer Cache from the list of SGA components and click the Advice button next to it (See Figure 5.3). You will get graphical representation of the memory and advice on its usage. (Note that all screenshots are not given here, as this description itself is self-explanatory.)

Figure 5.3. SGABuffer Cache Size Advice.

To run the PGA Advisor, click on the PGA tab (see Figure 5.4). You can get advice on PGA usage in a similar manner as with the SGA (see Figure 5.5), by clicking the Advice button. To see a graphical representation of the PGA memory and its usage, click the PGA Memory Usage Details button (see Figure 5.6).

Figure 5.4. The Memory ParametersPGA page in the OEM.

Figure 5.5. PGA aggregate target advice.

Figure 5.6. PGA memory usage.