Re: What is the SGA...

In article <4nv3mv$i8k_at_nadine.teleport.com>, jingram_at_teleport.com (Jonathan W. Ingram) writes:
|> 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.
|>

This is an excellent description of the shared pool. The shared pool is only one of the areas allocated as part of the SGA. The SGA consists of other data structures and caches besides the shared pool, and is fully documented in the server manuals.

|> 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
|>
 

-- 
------------------------------------------------------------------------
Scott Urman            Oracle Corporation           surman_at_us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
 Oracle Corporation"
------------------------------------------------------------------------