SQL generally goes through
* PARSE
* BIND
* EXECUTE
* FETCH
You may have multiple fetches per execution (fetch first 10 rows, next 10 rows and etc). Equally some SQLs do not have a fetch (eg an insert).
A transaction consists of a numbers of SQLs. If you have 20-30 SQLs per transaction, then you got some reasonable complexity. Not every statement is an isolated transaction in its own right.
How Oracle processes a query
1. Parse: In this stage, the user process sends the query to the server process with a request to parse or compile the query. The server process checks the validity of the command and uses the area in the SGA known as the shared pool to compile the statement. At the end of this phase, the server process returns the status—that is, success or failure of the parse phase—to the user process.
2. Execute: During this phase in the processing of a query, the server process prepares to retrieve the data.
3. Fetch: The rows that are retrieved by the query are returned by the server to the user during this phase. Depending on the amount of memory used for the transfer, one or more fetches are required to transfer the results of a query to the user.
The
following example describes Oracle database operations at the most basic level.
It illustrates an Oracle database configuration in which the user and associated
server process are on separate computers, connected through a network.
1. An
instance has started on a node where Oracle Database is installed, often called
the host or database server.
2. A user starts an application spawning a user process. The application
attempts to establish a connection to the server. (The connection may be local,
client/server, or a three-tier connection from a middle tier.)
3. The server runs a listener that
has the appropriate Oracle Net Services handler. The listener detects the
connection request from the application and creates a dedicated server process
on behalf of the user process.
4. The user runs a DML-type SQL
statement and commits the transaction. For example, the user changes the
address of a customer in a table and commits the change.
5. The server process receives the
statement and checks the shared pool (an SGA component) for any shared SQL area
that contains an identical SQL statement. If a shared SQL area is found, the
server process checks the user’s access privileges to the user s requested
data, and the existing shared SQL area is used to process the statement. If a
shared SQL area is not found, a new shared SQL area is allocated for the
statement so that it can be parsed and processed.
6. The server process retrieves any
necessary data values, either from the actual data file (table) or from values
stored in the database buffer cache.
7. The server process modifies data
in the SGA. Because the transaction is committed, the Log Writer process (LGWR)
immediately records the transaction in the redo log file. The Database Writer
process (DBWn) writes modified blocks permanently to disk when it is efficient
to do so.
8. If the transaction is
successful, the server process sends a message across the network to the
application. If it is not successful, an error message is transmitted.
9. Throughout this entire
procedure, the other background processes run, watching for conditions that
require intervention. In addition, the database server manages other users’
transactions and prevents contention between transactions that request the same
data.
