0690-0692

Previous Table of Contents Next

Page 690

The SET TRANSACTION Command Reference

SET TRANSACTION is used to alter the current transaction's properties. There are three options available, and the command must be the first command in your transaction. If you have
already issued commands in the current transaction, you must either issue a COMMIT or
ROLLBACK before the SET TRANSACTION command will be accepted.

SET TRANSACTION has the following options:

  • READ ONLY. Sets the current transaction to read only.
  • READ WRITE. The default.
  • USE ROLLBACK SEGMENT rollback_segment_name. Explicitly selects a rollback segment.

READ ONLY Option

READ ONLY refers to a read-only process where no updates can be performed on any tables in the database. It also sets the read-consistency level to transaction level, where all data viewed is a snapshot of the data as it existed when the transaction first started. This option is helpful for sessions that will only query data because the processing overhead for this type of transaction is smaller than that of a normal transaction. It is also helpful for transactions, such as reports , that require a snapshot of the current data. For these types of queries, though, processing overhead may be higher because other transactions that modify data will force the reporting transaction to search through rollback segments for the original unmodified data.

In a read-only transaction, the command set is limited to five groups of commands:

  • ALTER SESSION
  • ALTER SYSTEM
  • LOCK TABLE
  • SELECT (with the exception of SELECT FOR UPDATE)
  • SET ROLE

READ WRITE Option

This type of transaction is the default, where the user has the ability to update and delete as well as query tables if he or she has the appropriate database privileges, and the read consistency is set to statement level. No SET TRANSACTION command must take place for this option to be in effect because it is the default.

USE ROLLBACK SEGMENT Option

This option is used to set the rollback segment for transactions that update large amounts of data and therefore create larger-than-normal rollback segments. This rollback segment may have been created with larger initial and next extents to prevent the maximum number of extents

Page 691


being reached in the rollback segment. The OPTIMAL parameter may also have been set higher, or not used, to prevent extents from being reclaimed and causing a read-consistency error for other concurrent transactions.

Transaction Space

Three parameters affect how transactions can work with objects. These parameters affect snapshots, clusters, indexes, and tables. These parameters are

  • PCTFREE. Controls the amount of free space in each block set aside for the expansion of columns
  • INITRANS. Sets aside space for each transaction that will concurrently access this table
  • MAXTRANS. Specifies the maximum number of concurrent processes that can update a table (does not affect SELECT statements)

The PCTFREE parameter is used to set aside a percentage of a data block for work space in the object. This space is usually used for extending columns (such as VARCHAR2 datatypes). If this value is set too high, the result is wasted space in your data file that cannot be reclaimed without extra work by the database administrator. If the value is too low, the result is either relocated or chained rows. Chained rows are rows that span more than one data block. This creates a problem in that the database must do an additional seek to read the second data block for the row.

INITRANS specifies initial transactions, or the average number of concurrent transactions. For each transaction that will concurrently update a given object, 23 bytes of space are set aside for each data block to keep track of each transaction against the row(s) located within that block. If this value is set too low, the database must dynamically allocate space from the free space in the object's data blocks to use as temporary storage. Dynamically allocating this space can slow down the execution time of a transaction. If no free space can be allocated, the transaction will hang until free space can be allocated. The process may time-out if the wait is long enough.

MAXTRANS is the maximum number concurrent processes that can update a data block in use by the object. If this value is reached, further transactions cannot continue until other transactions have completed.

Summary

As you can see, knowing the steps that must be taken to process a transaction can greatly affect how a custom application is developed. If a transaction is querying data only, using a read-only transaction can greatly reduce the amount of processing overhead required to run an application. If many users are running the same read-only query, this savings on overhead can be a considerable amount.

Page 692

Likewise, knowing more about how statements are optimized can save a great deal of time in reaching the goals set for a new application. Because optimization methods take a critical role in meeting those goals, it is imperative that you take this into account.

Overall, knowing transaction processing steps is just plain helpful for administrators and developers alike.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net