|
|
The end users of the applications you develop are almost never aware of the code used to retrieve
their data for them, or insert and update changes to the data back into the database. Your application
acts as a black box, handling all the interactions with the underlying database.
In some ways, we developers have our own black box, although the box is much smaller. We send
SQL statements off to the database and the database does its magic. The aim of this chapter is to
take a close look at what the Oracle database does in response to those SQL statements. Although
you may have some understanding of what takes place on the Oracle server, this chapter also provides
some practical explanations that will help you to avoid some of the most common pitfalls
facing developers accessing an Oracle database.
This chapter concentrates on what is going on in the Oracle server, rather than on the code you use
to trigger those actions from your application. These actions are the Oracle database responding to
your requests, regardless of what programming language you use. Most languages use some form
of application programming interface (API) calls to use SQL against the Oracle database, and those
calls and code will be introduced later in this book.
The Processing Cycle for SQL
Statements
To process an SQL statement you submit to your Oracle database, the database goes through several
phases as part of the execution of the statement:
? Connect to the database, to establish communication between your client application and
the Oracle database server.
? Create a cursor, to hold the context for an SQL statement.
? Submit an SQL statement to the Oracle database for processing, which includes checking
the validity of the statement and potentially creating an optimized execution plan for it.
? Receive data back from the Oracle database in response to the submission of a query.
This cycle of events is shown in Figure 2-1, with mandatory events shown in white and potentially
optional events shown as shaded. Each of these phases is discussed in more detail later in the chapter.Connecting to a Database
The first step in accessing an Oracle database from your application is to establish a connection to the
database. This connection is the path that acts as the path from your client application to a shadow process
in the Oracle database that handles the SQL requests from your application.
Normally, a connection goes from a client machine over a network to a server machine. The connection is
implemented on top of a network protocol, such as TCP/IP. The connection request is actually received by
the Oracle Listener, which listens on a port for connection traffic. Once the connection is established, the
Listener passes requests to the shadow process associated with the connection, as shown in Figure 2-2.
Connect to
database
Create
cursor
Validity
check
Optimization
Prepare for
result set
Bind variables
Execute statement
Return info
16
Chapter 2
Figure 2-2: Establisihng a connection to an Oracle database from a client application.
If you are using Java as your program language, the connection will be executed with a driver, which is
software designed to handle the complexities of communication over the network. The various types of
Java drivers are described in Chapter 7, “Installing Oracle.”
A connection to an Oracle database is always created on behalf of a user. Consequently, establishing a
connection requires identifying and authenticating that user with a username and password. The details
of authentication are described in Chapter 5, “Oracle Security.”
A connection to an Oracle database is referred to as a session. The session is the overriding context for all
SQL interactions that take place. When a connection is either terminated or is lost for any other reason,
the context for that session, including any information in any uncommitted transactions within that session,
is lost. Chapter 3, “Handling Multiple Users,” describes in detail the use of transaction in an Oracle database.
Every session is supported by a shadow process on the Oracle server. Normally, this means that every
session has its own process. But, as you can imagine, each shadow process uses some server memory
resources, so the scalability of an individual Oracle instance might be limited by the number of sessions
it can support. To address this issue, Oracle has a feature known as shared servers, which was referred to
as multithreaded servers, or MTS, before Oracle9i.SapereOnLine |
|
|
|
|
|
