036-039 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

The System Global Area

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.

Figure 2.6 The structure of Oracle's system global area.

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.

What Is SQL?

Structured Query Language (SQL) is a relatively young language compared to languages like COBOL and Fortran, and it s quite different from those early ancestors .

SQL is a fourth-generation programming language (4GL), which means that it allows the user to describe the data he or she wants without giving precise instructions to the computer about how to retrieve the data. This was a remarkable idea when SQL was first introduced, and SQL remains the only 4GL in widespread use today. All of the major relational database systems use SQL for accessing data (although all of them, like Oracle s SQL*Plus, include proprietary modifications to the language).

SQL statements come in two varieties: data definition language and data manipulation language.

Data Definition Language

Data definition language (DDL) is language that defines how data is stored within the relational database. A simple example of a DDL statement is shown in Listing 2.5.

Listing 2.5 A simple DDL statement.

 CREATE TABLE STUDENTS AS (ssn                   NOT NULL   number(9),  first_name            NOT NULL   varchar2 (10),  last_name             NOT NULL   varchar2 (12),  middle_name                      varchar2 (10),  street_address        NOT NULL   varchar2 (30),  apartment_number                 varchar2 (4),  city                  NOT NULL   varchar2 (30),  state                 NOT NULL   varchar2 (2),  zip_code              NOT NULL   number (5),  home_phone            NOT NULL   number (10));

This statement creates a table within the Oracle database to hold information about students. Obviously, this is a very simple example. As you become a more advanced developer, you ll learn more about DDL.

Data Manipulation Language

Data manipulation language (DML) is the most common type of SQL that you ll encounter. In case you haven t already figured it out by reading the name , this is the SQL that creates, deletes, reads, and modifies data. Listing 2.6 displays a simple DML statement.

Listing 2.6 A simple DML statement.

 SELECT last_name  ', '  first_name  ' '  middle_name FROM   STUDENTS WHERE  ssn = 999999999;

This statement returns the name of the student whose social security number is 999-99-9999 from the STUDENTS table, in the format Doe, John Adam . In addition to querying data from tables using the SELECT statement, you can add data to tables with the INSERT statement, modify the data in tables with the UPDATE statement, and remove data from tables with the DELETE statement.

Table of Contents