Re: What is the SGA...

From: Jonathan W. Ingram <jingram_at_teleport.com>
Date: 1996/05/22
Message-ID: <4nv3mv$i8k_at_nadine.teleport.com>#1/1


Before I could do anything about it, Peter Rak <po080016_at_pop.ke.sanet.sk> wrote:

>What is SGA???

Well, I'll tell you what I can tell without cracking a book.

The Oracle7 shared global area (SGA) is a block of memory which holds SQL statements that have been parsed. When an SQL statement is executed, Oracle7 first checks the SGA to determine if the statement already resides in the SGA; if so, the new statement is not parsed and executed, since the old one has already been parsed and is ready to be executed (yielding better response time).

If the statement has not been previously parsed, the statement is parsed and then moved into the SGA. If there is not enough space in the SGA for the statement, something that is already in the SGA is pushed out (the statement residing in the SGA that was used least recently).

In addition to SQL statements, PL/SQL blocks (including database triggers and stored procedures/functions) are stored in the SGA, as well as actually data (table structure, data, and indices).

The fact that only exactly the same SQL statements will find a match in the SGA means that having a set of coding standards and making sure that those standards are followed extremely important. It's also important to create stored procedures and functions to carry out commonly used tasks, as these produce SGA "hits" more often than individually written SQL statements in applications.

Of course, this a very high level overview of what I know of the SGA. To learn the 'textbook' answers, you should pick up the Kevin Loney book on Oracle Database Administration.

Jonathan Received on Wed May 22 1996 - 00:00:00 CEST

Original text of this message