0684-0686

Previous Table of Contents Next

Page 684

This parameter affects all SQL statements issued from within the transaction, including functions and stored procedures that are called. OPTIMIZER_MODE is still used for any recursive SQL calls issued by Oracle on behalf of the transaction, though.

Parsing Statements

A parsed statement is not to be confused with an execution plan of a statement. Whereas an execution plan examines the most efficient way to execute a statement, parsing the statement creates the actual executable statements to be used in retrieving the data. Parsing a statement is a one-step process by the optimizer to do the following:

• Check semantics and syntax
• Verify that the user has the appropriate privileges to execute this statement
• Allocate private SQL space to store the statement
• Check for duplicate statements in the shared SQL area
• Generate an executable version of parsed SQL if necessary
• Allocate and store SQL in shared library cache if it does not already exist

When checking the syntax and semantics, the instance is verifying that no keywords or necessary parameters are missing. If the statement is in correct form, the instance then verifies that the user has the correct privileges required to carry out the execution of the statement. Once these have been verified , space is allocated in the private SQL area for the user's statement. This statement is saved until either it is needed again or the memory space is required to store another parsed statement.

After allocating space in the private SQL area, the instance searches through the shared SQL area for any duplicate statements. If a duplicate statement is found, the executable version of the statement is retrieved from memory and executed by the process, and the private SQL area is pointed to the statement in the shared area. If it is not found, an executable version is created and stored in the private SQL area only.

TIP

When parsing a SQL statement, all characters are not treated equally. Uppercase and lowercase letters are considered different characters. Two SQL statements, identical in nature but differing in case (even if by only one character) will be treated as separate statements. The "different" statement will be parsed and placed in the private SQL area as would a completely different SQL statement.
The following two statements are completely different statements, according to the parser:

 SELECT * FROM V$LOCK SELECT * FROM v$lock 

This is a major reasoning behind packaged procedures. There is only one procedure to maintain, and all users execute that same code.

Page 685

Handling Locks

The locking of data rows and/or tables is completely automated and transparent to the user. Once the executable version of the SQL statement is run, Oracle automatically attempts to lock data at the lowest level required. This means that, if possible, a row will be locked instead of the entire table. This is dependent solely on how the SQL statement was written and what types of access are required (full table scan versus single rows).

A form of manual, or explicit, locking can take place by using the LOCK TABLE command. By default, these commands are not necessary in day-to-day processing. Oracle recommends that you allow the database to handle all locking of data whenever possible.

TIP

One of the most overlooked causes of locking problems in a database is failing to create indexes on the columns you have used as foreign keys. If the foreign keys are un-indexed and you attempt to modify the child table (the table where the foreign keys reside), the database may require a shared lock on the parent table, or the table where the keys reference in order to modify the child table. When the foreign keys have corresponding indexes, the database can do row-level locking on the parent table.

TIP

One other overlooked cause of locking problems is the PCTFREE parameter on the table. If the value of PCTFREE is set too low, many concurrent transactions are executing DML statements against the table, and the data blocks are filled, a shared lock may be requested on the entire table. This transaction is not waiting for a lock, but instead is either waiting on free space or a release of an INITRANS within the transaction layer of the data block.

Generating Redo Logs

Each transaction that is committed has a corresponding redo log entry generated. This entry records just the changes applied to the database files, as well as rollback segment information. These entries in the redo logs are not traceable to a user process. Should the database be brought offline by a system or database failure, you can use these redo logs to reconstruct the database files to a usable state.

Redo log entries are written to the redo log buffer in the SGA. These entries are then written to the online redo logs by the LGWR process. If the instance is running in archive log mode, the redo log files, once filled, are then written to corresponding archived redo log files, which are separate from the data files that hold tables and data. These archived redo logs are the

Page 686

primary recovery method when the online redo logs have been cycled through or corrupted and no longer hold the data needed for recovery.

CAUTION

Although the results of a DDL statement ”DROP TABLE, for example ”have associated redo logs generated for them, they have no rollback segments. Therefore, it is not easy to recover from an accidentally dropped table. To recover from a dropped table or tablespace that contains data, the tablespace in question must be brought offline, an older version of the data file(s) must be restored from a previous backup, and then archived redo logs must be used to recover the tablespace to a specific point in time just before the drop command was issued.

Stepping Through the Transaction

From this point, there are several paths that a transaction can take to completion. Most commonly, the transaction is committed. Still, handling must be taken into account for transactions that are rolled back. Following are the steps taken during a commit:

1. The instance's transaction table marks the transaction as complete.
2. A unique SCN (system change number) is generated.
3. Redo log entries are written to disk.
4. Any acquired locks are released.
5. The transaction is marked as having completed.

If any of these steps fail, the transaction cannot be committed. Depending on the nature of the error, the transaction will either wait for the problem to be fixed so it can complete the transaction or it will be rolled back.

NOTE

The DBWR process is responsible for writing updated data from the buffer cache in the SGA back to the database. It is possible that even though a transaction has not been committed, its data has been written to disk. Uncommitted data is written to prevent all the buffers in the SGA from filling up. Should the buffers fill, the database cannot continue modifying data until they are freed. The writing of uncommitted data is not a problem, however. Should a process be rolled back, its data is taken from the rollback segment and reapplied to the modified table(s).

Previous Table of Contents Next