Previous | Table of Contents | Next |
by Edward Whalen
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:
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.
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.
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 Oracles 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.
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:
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 |