With the shared pool covered, it is time to move on to the fourth area within the SGA. This area does not have a fancy name. It is used to store messages transmitted between various processes associated with the operation of the Oracle database. Many features, such as locks applied to objects by a process, are conveyed using this area. The good news is that there are no special tuning parameters and intervention required by the DBA for these message areas. As long as there is sufficient size in the shared pool, you’re set.

The final component that can take up space within the SGA is space for the multi-threaded server queues. One of the difficulties in writing a book that discusses a tightly integrated system such as Oracle is that sometimes you have to introduce a topic before its time. Basically, the multi-threaded server is a feature of Oracle that enables users to share the memory areas and processes used to connect user applications to the Oracle database. The logic is, why waste memory duplicating these relatively common areas for perhaps hundreds of users? For purposes of this chapter, all you need to know is that if you are using the multi-threaded server option, these memory areas are stored in the SGA and affect its size.

As DBA, you may be the only one aware that there is such a thing as the SGA; therefore, you will be the one to manage it. Many business book writers argue that you cannot manage anything you cannot monitor, so the first task is to monitor the SGA. My favorite way is to issue the following command in Server Manager:

show sga

This command produces a result similar to what is shown in the following example. Those of you familiar with large Oracle databases on such machines as Sequent, Sun, and VAX may wonder why the numbers are so small. This printout comes from Personal Oracle. This command works the same on the large boxes (although most of them have much larger memory areas than I could afford for my PC):

SQLDBA> show sga

Total System Global Area       4767460 bytes

Fixed Size                       36432 bytes

Variable Size                  3846292 bytes

Database Buffers                819200 bytes

Redo Buffers                     65536 bytes

The final issue to discuss regarding the SGA is how to control it. In Chapter 25, you learn how to tune Oracle instances. For now, know that there are variables within Oracle you can set using initialization files. These control the space allocated to the redo log buffer, database buffer, and other areas within the SGA discussed in this chapter. You will not be ready to alter these parameters until you learn how to determine when you need to adjust them in Chapter 25.

Program Global Area (PGA)

The System Global Area provides memory for those things all users need to share. There are several things users need to keep to themselves, and that is the purpose of the Program Global Area. All users are allocated PGAs when they connect to the Oracle database. The size of this memory area is fixed by the operating system and remains constant as long as the users are connected to Oracle.

For Personal Oracle users and those that have a small number of users, this is a workable arrangement. However, many of the larger transaction processing systems may have dozens or hundreds of users connected to the database at a given time. When you map out the large amounts of memory required for the SGA on such large databases and then add in space for each of the user processes and the operating system itself, there is not much space available for a large number of PGAs.

That is where the multithreaded server comes into play. In most situations, there may be a large number of users logged on the database computer at a given time, but a much smaller number are actively executing queries or updates. Most of the users are either reading the outputs of the system, typing at rates that seem slow to the computer, or just thinking. Oracle’s multithreaded server is designed to allocate a specific number of spaces for the information involved with a particular transaction. This information includes the private SQL areas and other such items that relate to a particular question or update. The users still retain space, known as stack space, dedicated to their individual sessions, to hold variables and other data associated with their work. The data stored in the PGA, with and without the multithreaded server, is show in Figure 10.3.

Figure 10.3.  The contents of the Program Global Area (PGA).

A few final notes about the PGA seem appropriate at this point:

•  The PGA is owned by a single-user process and only that user can read from or write to it.

•  If you do not use the multithreaded server and there is not enough memory available on your computer, you will receive an error message from Oracle to that effect.

•  If you are running a client/server configuration, the PGA for a user will be allocated on the machine that acts as the database server.

•  Some literature refers to the PGA as the Process Global Area rather than the Program Global Area. Either way, it works the same.

•  The size of the PGA varies between versions of Oracle that run on different operating systems. Three parameters affect the size of the PGA in a given instance: the number of open database links, the number of database files, the number of enabled roles and the number of log files.

This is enough for most DBAs to understand about the PGA. DBAs typically do not have to do much with the PGA unless they are running short on memory in their computer system. Because the parameters mentioned are controlled by application needs, the DBA usually has to acquire additional memory or use the multithreaded server option to solve problems with PGA memory limitations.