SQL Tuning  «Prev  Next»

Lesson 2Oracle SQL Tuning Prerequisites
ObjectivePrerequisites for this course.

Oracle SQL Tuning Prerequisites

To get the most from this course, you should have a basic understanding of the UNIX operating system, PL/SQL coding techniques, and the following Oracle database concepts:
  1. Oracle tables and tablespaces
  2. Oracle instances
  3. The Oracle data dictionary

If you do not feel comfortable with one or more of these topics, techniques, or procedures, you may want to consider taking the course Basic Structured Query Language
The next lesson will discuss the course resources.

Review of Oracle Tablespaces and the Structure of the Database

People who have worked with computers for any period of time are familiar with the concept of a file; it is place on disk where information is stored, and it has a name. Its size is usually not fixed: If you add information to the file, it can grow larger and take up more disk space, up to the maximum available. This process is managed by the operating system, and often involves distributing the information in the file over several smaller sections of the disk that are not physically near each other. The operating system handles the logical connection of these smaller sections without your being aware of it at all. To the user, the file looks like a single whole. Oracle uses files as a part of its organizational scheme, but its logical structure goes beyond the concept of a file.


A datafile is an operating system file used to store Oracle data. Each datafile is assigned to a tablespace, a logical division within the database. Tablespaces commonly include
  1. SYSTEM (for Oracle's internal data dictionary),
  2. SYSAUX (for auxiliary internal objects),
  3. USERS (for user objects), and others for application tables, indexes, and additional database structures.
The datafiles can have a fixed size or can be allowed to extend themselves automatically when they are filled, up to a defined limit. To add more space to a tablespace, you can manually extend your datafiles or add new datafiles. New rows can then be added to existing tables, and those tables may then have rows in multiple datafiles.

Disk Space

Each table has a single area of disk space, called a segment, set aside for it in the tablespace. Each segment, in turn, has an initial area of disk space, called the initial extent, set aside for it in the tablespace. Once the segment has used up this space, the next extent, another single area of disk space, is set aside for it. When it has used this up as well, yet another next extent is set aside. This process continues with every table until the whole tablespace is full. At that point, someone has to add a new file to the tablespace or extend the tablespace's files before any more growth in the tables can take place.
Every database also contains a SYSTEM tablespace, which contains the data dictionary as well as the names and locations of all the tablespaces, tables, indexes, and clusters for this database. The objects within the SYSTEM tablespace are owned by the SYS and SYSTEM users; no other users should own objects in this tablespace because they may impact the rest of the database.