Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Oracle System Global Area (SGA)
Oracle Tips by Burleson
 

Oracle’s System Global Area ( SGA ) is essentially short-term memory for the database. As the database references data and stored PL/SQL objects, the database stores information in the SGA on the assumption that you will be referencing this information again.

Figure 2.6 illustrates an SGA composed of these four distinct areas:

  • Shared SQL pool stores SQL statements. When you execute an SQL statement, it is matched against statements stored in the shared SQL pool.

  • Data block buffers contain the data being accessed by the users.

  • Dictionary cache contains information about the structures that hold the data in the data block buffers.

  • Redo log buffers store information about changes made to the data in the database.

When the database needs to add more information to the SGA and no more memory is available, the database removes the oldest data and objects from the SGA until there is sufficient free memory to hold the new data and objects.

Oracle also allows you to pin data and stored PL/SQL objects into the SGA. For instance, lookup tables are often very small; the contents of these tables can often be pinned entirely in the SGA to make accesses to the descriptions and codes contained in the table extremely fast. Objects that are pinned in the SGA are not removed from the SGA when Oracle needs to make room for new data and code.


TIP:  Standardizing SQL And PL/SQL Code

Oracle allows you to pin data and stored PL/SQL objects into the SGA. To take maximum advantage of this ability, you’ll need to create some common stored PL/SQL objects for your system that you and other developers can reuse. Have the DBA modify the database startup script so that these common objects get pinned into the SGA when your database is started. By making frequent use of these objects, you can improve the performance of your applications.

In addition, you can improve your system performance by implementing and following a set of coding standards for your system. Appendix D contains a sample SQL and PL/SQL coding standard, which can be modified to suit the needs of your organization. 


This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter