Previous Table of Contents Next


Chapter 17
Query and Transaction Processing

by Edward Whalen

In This Chapter
•  Transaction Processing
•  SQL Statement Processing
•  Using the Oracle Optimizer
•  Using the ANALYZE Command
•  Analyzing SQL Statements
•  Using EXPLAIN PLAN and SQL Trace
•  Designing New SQL Statements
•  Using Hints

Transaction Processing

Transaction processing is the heart of any RDBMS. In fact, without transaction processing there would be no reason to have an RDBMS. According to the ANSI/ISO SQL standard, a transaction is one or more SQL statements executed by a single user that either succeed or fail as a whole. The transaction starts with the execution of the first SQL statement and ends when they are explicitly committed or rolled back. In many cases, the commit or rollback statement is specifically executed; in others, the application such as SQL*Plus does the commit on behalf of the user automatically.

It is important that a transaction be thought of as a single logical unit of work. A transaction must either succeed or fail as a whole. A transaction that only partially works will probably leave your database in an unknown state. For example, a bank transaction that transfers money from one account to another may use SQL statements that withdraw money from one account, and then add that money to another account. If only the first part of that transaction worked, your money could be completely lost.

In a typical transaction, the following steps are executed:

A.  Application Connection (Optional)
1.  The application processes the user input and creates a connection to the server through Net8.
2.  The server picks up the connection request and creates a server process on behalf of the user or passes the request to a shared server process via the dispatcher.


Note:  
The application-connection process occurs only when the application is signing on. The application does not have to connect each time a statement is processed.
B.  Transaction Processing
1.  The user executes an SQL statement or statements and commits the transaction. For example, the user changes the value of a row in a table.
2.  The server process takes this SQL statement and checks the shared pool to see whether there is a shared SQL area that has this identical SQL statement. If it finds an identical shared SQL area, the server process checks to see whether the user has access privileges to the data and uses the shared SQL area to process the request. If a shared SQL area is not found, a new shared SQL area is allocated, the statement is parsed, and it is finally executed.
3.  The server process retrieves the data from the SGA (if present) or retrieves it from the data file into the SGA.
4.  The server process modifies the data in the SGA. Remember that the server processes can only read from the data files.
5.  The LGWR process writes out the redo information. Not until this redo information has been written to the log is the statement considered committed. At some later time, the DBWR process writes the modified blocks to permanent storage.
6.  If the transaction is successful, a completion code is returned across the network to the client process. If a failure occurs, an error message is returned.
7.  Return to phase B, “Transaction Processing,” and submit more transactions until you are finished and want to exit the application.
The Application Processing phase is repeated indefinitely until the user is finished with this particular application and exits the application.


Note:  
A transaction is not considered committed until the write to the redo log file has been completed. This write may be triggered by the COMMIT statement or may have already been done based on changes to the database. This arrangement ensures that a committed transaction is recoverable in the event of a system failure. When a transaction has been committed and the redo entry has been written, the transaction is considered finished.
C.  Application Termination
1.  The application logs off the RDBMS. This event signals Oracle that all the associated resources can be deallocated.
2.  The Oracle PMON process makes sure that the server process has been terminated.
3.  All resources are released. Any memory resources the application has allocated are released.


Note:  
The application-disconnect process occurs only when the application or user is signing off. The application does not have to connect each time a statement is processed.

While this process is occurring, the Oracle background processes are doing their jobs keeping the system running smoothly. Keep in mind that while your application is being processed, hundreds of other users may be doing similar tasks. It is Oracle’s job to keep the system in a consistent state, managing contention and locking and performing at the necessary rate.

Even though your application may have modified some data in the database, that data may not yet be written to the data files. It may be some time later that the DBWR process writes those changes out to permanent storage.

With this overview of how the application is processed, you are ready to focus on what happens in step 2 of phase B: how the SQL statement is parsed and the execution plan is formed.

SQL Statement Processing

By understanding how Oracle processes SQL statements, you can have a better understanding of how to optimize these statements. The following sections look at the SQL statement parsing process and how an execution plan is formed. For each SQL statement that is executed, several steps occur:

1.  A cursor is created.
2.  The statement is parsed, if it is not already in the shared pool.
3.  Any query in the statement is processed.
4.  Variables are bound.
5.  The statement is executed. If possible, the statement is parallelized.
6.  Rows to be returned are fetched.

Cursor Creation

Each time an SQL statement is executed, a cursor is automatically created on behalf of the statement. If you want, you can declare the cursor manually. Remember that a cursor is a handle to a specific private SQL area. You can think of a cursor as a pointer to, or the name of, a particular area of memory associated with an SQL statement.


Previous Table of Contents Next