This book is a quick-reference guide for tuning Oracle SQL. This is not a comprehensive Oracle tuning book. The purpose of this book is to give you some light reading material on my "real world" tuning experiences and those of my company, Mark Gurry & Associates. We tune many large Oracle sites. Many of those sites, such as banks, large financial institutions, stock exchanges, and electricity markets, are incredibly sensitive to poor performance. With more and more emphasis being placed on 24/7 operation, the pressure to make SQL perform in production becomes even more critical.
SQL statements are not case sensitive.
SQL statements can be on one or more lines.
Keywords cannot be abbreviated or splitacross lines.
Clauses are usually placed on separate lines.
Indents are used to enhance readability.
Overview of Primary Components
The Oracle architecture includes a number of primary components, which are discussed further in this lesson.
Oracle server: There are several files, processes, and memory structures in an Oracle server; however, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, to ensure that the database can be recovered in the event of a software or hardware error, or to perform other tasks necessary to maintain the database. The Oracle server consists of an Oracle instance and an Oracle database.
A privilege is a right to execute a particular type of SQL statement or to access another user’s object. These include the right to:
Connect to a database
Create a table
Select rows from another user’s table
Execute another user’s stored procedure
Each system privilege allows a user to perform a particular database operation or class of database operations; for example, the privilege to create tablespaces is a system privilege.