2 SQL Processing for Application Developers

Deciding How to Group Operations in Transactions

In general, only application designers who use the programming interfaces to Oracle Database are concerned with which types of actions should be grouped together as one transaction. You should use the following principles when deciding how to group transactions:

• Transactions must be defined properly so that work is accomplished in logical units and data is kept consistent.

• Data in all referenced tables should be in a consistent state before the transaction begins and after it ends.

• Transactions should consist of only the SQL statements or PL/SQL blocks that comprise one consistent change to the data.

For example, suppose that you write a Web application that enables users to transfer funds between accounts. The transaction should include the debit to one account, which is executed by one SQL statement, and the credit to another account, which is executed by a second SQL statement. Both statements should fail or succeed together as a unit of work; the credit should not be committed without the debit. Other non-related actions, such as a new deposit to one account, should not be included in the same transaction.

Improving Transaction Performance

As an application developer, you should consider whether you can improve performance. Consider the following performance enhancements when designing and writing your application:

• Use the SET TRANSACTION command with the USE ROLLBACK SEGMENT clause to explicitly assign a transaction to a rollback segment. This technique can eliminate the need to allocate additional extents dynamically, which can reduce system performance. Note that this clause is relevant and valid only if you use rollback segments for undo. If you use automatic undo management, then Oracle Database ignores this clause.

• Establish standards for writing SQL statements so that you can take advantage of shared SQL areas. Oracle Database recognizes identical SQL statements and allows them to share memory areas. This reduces memory usage on the database server and increases system throughput.

• Use the ANALYZE command to collect statistics that can be used by Oracle Database to implement a cost-based approach to SQL statement optimization. You can supply additional "hints" to the optimizer as needed.

• Call the DBMS_APPLICATION_INFO.SET_ACTION procedure before beginning a transaction to register and name a transaction for later use when measuring performance across an application. You should specify which type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources.

• Increase user productivity and query efficiency by including user-written PL/SQL functions in SQL expressions as described in "Calling Stored Functions from SQL Expressions".

• Create explicit cursors when writing a PL/SQL application.

• Reduce frequency of parsing and improve performance in precompiler programs by increasing the number of cursors with MAX_OPEN_CURSORS.

• Use the SET TRANSACTION command with the ISOLATION LEVEL set to SERIALIZABLE to get ANSI/ISO serializable transactions.

Committing Transactions

To commit a transaction, use the COMMIT statement. The following two statements are equivalent and commit the current transaction:

COMMIT WORK;
COMMIT;

The COMMIT statements lets you include the COMMENT parameter along with a comment that provides information about the transaction being committed. This option is useful for including information about the origin of the transaction when you commit distributed transactions:

COMMIT COMMENT 'Dallas/Accts_pay/Trans_type 10B';

COMMIT_WRITE = BATCH, NOWAIT

ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT

COMMIT WRITE BATCH NOWAIT

Rolling Back Transactions

To roll back an entire transaction, or to roll back part of a transaction to a savepoint, use the ROLLBACK statement. For example, either of the following statements rolls back the entire current transaction:

ROLLBACK WORK;
ROLLBACK;

The WORK option of the ROLLBACK command has no function.

To roll back to a savepoint defined in the current transaction, use the TO option of the ROLLBACK command. For example, either of the following statements rolls back the current transaction to the savepoint named POINT1:

SAVEPOINT Point1;
...
ROLLBACK TO SAVEPOINT Point1;
ROLLBACK TO Point1;

Defining Transaction Savepoints

To define a savepoint in a transaction, use the SAVEPOINT command. The following statement creates the savepoint named ADD_EMP1 in the current transaction:

SAVEPOINT Add_emp1;

If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After creating a savepoint, you can roll back to the savepoint.

There is no limit on the number of active savepoints for each session. An active savepoint is one that has been specified since the last commit or rollback.

Declaring and Opening Cursors

There is no absolute limit to the total number of cursors one session can have open at one time, subject to two constraints:

• Each cursor requires virtual memory, so a session's total number of cursors is limited by the memory available to that process.

• A systemwide limit of cursors for each session is set by the initialization parameter named OPEN_CURSORS found in the parameter file (such as INIT.ORA).

  See Also:

  Oracle Database Reference for descriptions of parameters

Explicitly creating cursors for precompiler programs can offer some advantages in tuning those applications. For example, increasing the number of cursors can often reduce the frequency of parsing and improve performance. If you know how many cursors may be required at a given time, then you can make sure you can open that many simultaneously.

Using a Cursor to Execute Statements Again

After each stage of execution, the cursor retains enough information about the SQL statement to reexecute the statement without starting over, as long as no other SQL statement has been associated with that cursor. The statement can be reexecuted without including the parse stage.

By opening several cursors, the parsed representation of several SQL statements can be saved. Repeated execution of the same SQL statements can thus begin at the describe, define, bind, or execute step, saving the repeated cost of opening cursors and parsing.

To understand the performance characteristics of a cursor, a DBA can retrieve the text of the query represented by the cursor using the V$SQL catalog view. Because the results of EXPLAIN PLAN on the original query might differ from the way the query is actually processed, a DBA can get more precise information by examining the V$SQL_PLAN, V$SQL_PLAN_STATISTICS, and V$SQL_PLAN_STATISTICS_ALL catalog views.:

• The V$SQL_PLAN view contains the execution plan information for each child cursor loaded in the library cache.

• The V$SQL_PLAN_STATISTICS view provides execution statistics at the row source level for each child cursor.

• The V$SQL_PLAN_STATISTICS_ALL view contains memory usage statistics for row sources that use SQL memory (sort or hash-join). This view concatenates information in V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA.

  See Also:

  Oracle Database Reference for details on these views

Closing Cursors

Closing a cursor means that the information currently in the associated private area is lost and its memory is deallocated. Once a cursor is opened, it is not closed until one of the following events occurs:

• The user program terminates its connection to the server.

• If the user program is an OCI program or precompiler application, then it explicitly closes any open cursor during the execution of that program. (However, when this program terminates, any cursors remaining open are implicitly closed.)

Cancelling Cursors

Cancelling a cursor frees resources from the current fetch.The information currently in the associated private area is lost but the cursor remains open, parsed, and associated with its bind variables.

Choosing a Locking Strategy

A transaction explicitly acquires the specified table locks when a LOCK TABLE statement is executed. A LOCK TABLE statement manually overrides default locking. When a LOCK TABLE statement is issued on a view, the underlying base tables are locked. The following statement acquires exclusive table locks for the EMP_TAB and DEPT_TAB tables on behalf of the containing transaction:

LOCK TABLE Emp_tab, Dept_tab
    IN EXCLUSIVE MODE NOWAIT;

You can specify several tables or views to lock in the same mode; however, only a single lock mode can be specified for each LOCK TABLE statement.

You can also indicate if you do or do not want to wait to acquire the lock. If you specify the NOWAIT option, then you only acquire the table lock if it is immediately available. Otherwise an error is returned to notify that the lock is not available at this time. In this case, you can attempt to lock the resource at a later time. If NOWAIT is omitted, then the transaction does not proceed until the requested table lock is acquired. If the wait for a table lock is excessive, then you might want to cancel the lock operation and retry at a later time; you can code this logic into your applications.

LOCK TABLE Emp_tab IN ROW SHARE MODE;
LOCK TABLE Emp_tab IN ROW EXCLUSIVE MODE;

LOCK TABLE Emp_tab IN SHARE MODE;

CREATE TABLE dept_tab(
   deptno NUMBER(2) NOT NULL,
   dname VARCHAR2(14),
   loc VARCHAR2(13));

CREATE TABLE emp_tab (
   empno NUMBER(4) NOT NULL,
   ename VARCHAR2(10),
   job VARCHAR2(9),
   mgr NUMBER(4),
   hiredate DATE,
   sal NUMBER(7,2),
   comm NUMBER(7,2),
   deptno NUMBER(2));

CREATE TABLE Budget_tab (
   totsal NUMBER(7,2),
   deptno NUMBER(2) NOT NULL);

LOCK TABLE Dept_tab IN SHARE MODE;
UPDATE Emp_tab
    SET sal = sal * 1.1
    WHERE deptno IN 
      (SELECT deptno FROM Dept_tab WHERE loc = 'DALLAS');
UPDATE Budget_tab
    SET Totsal = Totsal * 1.1
    WHERE Deptno IN
      (SELECT Deptno FROM Dept_tab WHERE Loc = 'DALLAS');

COMMIT; /* This releases the lock */

LOCK TABLE Emp_tab IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE Emp_tab IN EXCLUSIVE MODE;

Letting Oracle Database Control Table Locking

Letting Oracle Database control table locking means your application needs less programming logic, but also has less control, than if you manage the table locks yourself.

Issuing the command SET TRANSACTION ISOLATION LEVEL SERIALIZABLE or ALTER SESSION ISOLATION LEVEL SERIALIZABLE preserves ANSI serializability without changing the underlying locking protocol. This technique allows concurrent access to the table while providing ANSI serializability. Getting table locks greatly reduces concurrency.

The settings for these parameters should be changed only when an instance is shut down. If multiple instances are accessing a single database, then all instances should use the same setting for these parameters.

Explicitly Acquiring Row Locks

You can override default locking with a SELECT statement that includes the FOR UPDATE clause. This statement acquires exclusive row locks for selected rows (as an UPDATE statement does), in anticipation of updating the selected rows in a subsequent statement.

You can use a SELECT... FOR UPDATE statement to lock a row without actually changing it. For example, several triggers in Chapter 9, "Coding Triggers", show how to implement referential integrity. In the EMP_DEPT_CHECK trigger (see "Foreign Key Trigger for Child Table"), the row that contains the referenced parent key value is locked to guarantee that it remains for the duration of the transaction; if the parent key is updated or deleted, referential integrity would be violated.

SELECT... FOR UPDATE statements are often used by interactive programs that allow a user to modify fields of one or more specific rows (which might take some time); row locks are acquired so that only a single interactive program user is updating the rows at any given time.

If a SELECT... FOR UPDATE statement is used when defining a cursor, the rows in the return set are locked when the cursor is opened (before the first fetch) rather than being locked as they are fetched from the cursor. Locks are only released when the transaction that opened the cursor is committed or rolled back, not when the cursor is closed.

Each row in the return set of a SELECT... FOR UPDATE statement is locked individually; the SELECT... FOR UPDATE statement waits until the other transaction releases the conflicting row lock. If a SELECT... FOR UPDATE statement locks many rows in a table, and if the table experiences a lot of update activity, it might be faster to acquire an EXCLUSIVE table lock instead.

When acquiring row locks with SELECT... FOR UPDATE, you can specify the NOWAIT option to indicate that you are not willing to wait to acquire the lock. If you cannot acquire then lock immediately, an error is returned to signal that the lock is not possible at this time. You can try to lock the row again later.

By default, the transaction waits until the requested row lock is acquired. If the wait for a row lock is too long, you can code logic into your application to cancel the lock operation and try again later.

When to Use User Locks

User locks can help to:

• Provide exclusive access to a device, such as a terminal

• Provide application-level enforcement of read locks

• Detect when a lock is released and cleanup after the application

• Synchronize applications and enforce sequential processing

Example of a User Lock

The following Pro*COBOL precompiler example shows how locks can be used to ensure that there are no conflicts when multiple people need to access a single device.

***************************************************************** 
* Print Check                                                   * 
* Any cashier may issue a refund to a customer returning goods. * 
* Refunds under $50 are given in cash, more than $50 by check.  * 
* This code prints the check. The one printer is opened by all  * 
* the cashiers to avoid the overhead of opening and closing it  * 
* for every check. This means that lines of output from multiple* 
* cashiers could become interleaved if we don't ensure exclusive* 
* access to the printer. The DBMS_LOCK package is used to       * 
* ensure exclusive access.                                      * 
***************************************************************** 
CHECK-PRINT 
*    Get the lock "handle" for the printer lock. 
   MOVE "CHECKPRINT" TO LOCKNAME-ARR. 
   MOVE 10 TO LOCKNAME-LEN. 
   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.ALLOCATE_UNIQUE ( :LOCKNAME, :LOCKHANDLE ); 
      END; END-EXEC. 
*   Lock the printer in exclusive mode (default mode).
   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.REQUEST ( :LOCKHANDLE ); 
      END; END-EXEC. 
*   We now have exclusive use of the printer, print the check. 
  ... 
*   Unlock the printer so other people can use it 
EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.RELEASE ( :LOCKHANDLE ); 
      END; END-EXEC. 

Viewing and Monitoring Locks

Table 2-5 describes Oracle Database facilities to display locking information for ongoing transactions within an instance.

How Serializable Transactions Interact

Figure 2-1 shows how a serializable transaction (Transaction B) interacts with another transaction (A, which can be either SERIALIZABLE or READ COMMITTED).

When a serializable transaction fails with an ORA-08177 error ("cannot serialize access"), the application can take any of several actions:

• Commit the work executed to that point

• Execute additional, different, statements, perhaps after rolling back to a prior savepoint in the transaction

• Roll back the entire transaction and try it again

Oracle Database stores control information in each data block to manage access by concurrent transactions. To use the SERIALIZABLE isolation level, you must use the INITRANS clause of the CREATE TABLE or ALTER TABLE command to set aside storage for this control information. To use serializable mode, INITRANS must be set to at least 3.

Figure 2-1 Time Line for Two Transactions

Description of "Figure 2-1 Time Line for Two Transactions"

Setting the Isolation Level of a Transaction

You can change the isolation level of a transaction using the ISOLATION LEVEL clause of the SET TRANSACTION command, which must be the first command issued in a transaction.

Use the ALTER SESSION command to set the transaction isolation level on a session-wide basis.

Referential Integrity and Serializable Transactions

Because Oracle Database does not use read locks, even in SERIALIZABLE transactions, data read by one transaction can be overwritten by another. Transactions that perform database consistency checks at the application level should not assume that the data they read will not change during the execution of the transaction (even though such changes are not visible to the transaction). Database inconsistencies can result unless such application-level consistency checks are coded carefully, even when using SERIALIZABLE transactions. Note, however, that the examples shown in this section are applicable for both READ COMMITTED and SERIALIZABLE transactions.

Figure 2-2 shows two different transactions that perform application-level checks to maintain the referential integrity parent/child relationship between two tables. One transaction checks that a row with a specific primary key value exists in the parent table before inserting corresponding child rows. The other transaction checks to see that no corresponding detail rows exist before deleting a parent row. In this case, both transactions assume (but do not ensure) that data they read will not change before the transaction completes.

Figure 2-2 Referential Integrity Check

Description of "Figure 2-2 Referential Integrity Check"

The read issued by transaction A does not prevent transaction B from deleting the parent row, and transaction B's query for child rows does not prevent transaction A from inserting child rows. This scenario leaves a child row in the database with no corresponding parent row. This result occurs even if both A and B are SERIALIZABLE transactions, because neither transaction prevents the other from making changes in the data it reads to check consistency.

As this example shows, sometimes you must take steps to ensure that the data read by one transaction is not concurrently written by another. This requires a greater degree of transaction isolation than defined by SQL92 SERIALIZABLE mode.

READ COMMITTED and SERIALIZABLE Isolation

Oracle Database gives you a choice of two transaction isolation levels with different characteristics. Both the READ COMMITTED and SERIALIZABLE isolation levels provide a high degree of consistency and concurrency. Both levels reduce contention, and are designed for deploying real-world applications. The rest of this section compares the two isolation modes and provides information helpful in choosing between them.

Application Tips for Transactions

When a transaction runs in serializable mode, any attempt to change data that was changed by another transaction since the beginning of the serializable transaction causes an error:

ORA-08177: Can't serialize access for this transaction.

When you get this error, roll back the current transaction and execute it again. The transaction gets a new transaction snapshot, and the operation is likely to succeed.

To minimize the performance overhead of rolling back transactions and executing them again, try to put DML statements that might conflict with other concurrent transactions near the beginning of your transaction.

Examples of Autonomous Transactions

The two examples in this section illustrate some of the ways you can use autonomous transactions.

As these examples illustrate, there are four possible outcomes that can occur when you use autonomous and main transactions. Table 2-7 presents these possible outcomes. As you can see, there is no dependency between the outcome of an autonomous transaction and that of a main transaction.

Entering a Buy Order

In this example, illustrated by Figure 2-5, a customer enters a buy order. That customer's information (such as name, address, phone) is committed to a customer information table—even though the sale does not go through.

Figure 2-5 Example: A Buy Order

Description of "Figure 2-5 Example: A Buy Order"

Example: Making a Bank Withdrawal

In this example, a customer tries to make a withdrawal from a bank account. In the process, a main transaction calls one of two autonomous transaction scopes (AT Scope 1, and AT Scope 2).

The following diagrams illustrate three possible scenarios for this transaction.

• Scenario 1: There are sufficient funds to cover the withdrawal and therefore the bank releases the funds

• Scenario 2: There are insufficient funds to cover the withdrawal, but the customer has overdraft protection. The bank therefore releases the funds.

• Scenario 3: There are insufficient funds to cover the withdrawal, the customer does not have overdraft protection, and the bank therefore withholds the requested funds.

Scenario 1

There are sufficient funds to cover the withdrawal and therefore the bank releases the funds. This is illustrated by Figure 2-6.

Figure 2-6 Example: Bank Withdrawal—Sufficient Funds

Description of "Figure 2-6 Example: Bank Withdrawal—Sufficient Funds"

Scenario 2

There are insufficient funds to cover the withdrawal, but the customer has overdraft protection. The bank therefore releases the funds. This is illustrated by Figure 2-7.

Figure 2-7 Example: Bank Withdrawal—Insufficient Funds WITH Overdraft Protection

Description of "Figure 2-7 Example: Bank Withdrawal—Insufficient Funds WITH Overdraft Protection"

Scenario 3

There are insufficient funds to cover the withdrawal, the customer does not have overdraft protection, and the bank therefore withholds the requested funds. This is illustrated by Figure 2-8.

Figure 2-8 Example: Bank Withdrawal—Insufficient Funds WITHOUT Overdraft Protection

Description of "Figure 2-8 Example: Bank Withdrawal—Insufficient Funds WITHOUT Overdraft Protection"

Defining Autonomous Transactions

To define autonomous transactions, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to mark the procedure, function, or PL/SQL block as autonomous (independent).

You can code the pragma anywhere in the declarative section of a procedure, function, or PL/SQL block. But, for readability, code the pragma at the top of the section. The syntax follows:

PRAGMA AUTONOMOUS_TRANSACTION;

In the following example, you mark a packaged function as autonomous:

CREATE OR REPLACE PACKAGE Banking AS
    FUNCTION Balance (Acct_id INTEGER) RETURN REAL;
    -- add additional functions and packages
END Banking;

CREATE OR REPLACE PACKAGE BODY Banking AS
    FUNCTION Balance (Acct_id INTEGER) RETURN REAL IS
        PRAGMA AUTONOMOUS_TRANSACTION;
        My_bal REAL;
    BEGIN
       --add appropriate code
    END;
    -- add additional functions and packages...
END Banking;

Restrictions on Autonomous Transactions

Note the following restrictions on autonomous transactions.

• You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. For example, the following pragma is illegal:

  CREATE OR REPLACE PACKAGE Banking AS
      PRAGMA AUTONOMOUS_TRANSACTION; -- illegal
      FUNCTION Balance (Acct_id INTEGER) RETURN REAL;
      END Banking;
  
  

• You cannot execute a PIPE ROW statement in your autonomous routine while your autonomous transaction is open. You must close the autonomous transaction before executing the PIPE ROW statement. This is normally accomplished by committing or rolling back the autonomous transaction before executing the PIPE ROW statement.

  See Also:

  Oracle Database PL/SQL User's Guide and Reference

What Operations Can Be Resumed After an Error Condition?

Queries, DML operations, and certain DDL operations can all be resumed if they encounter an out-of-space error. The capability applies if the operation is performed directly by a SQL statement, or if it is performed within a stored procedure, anonymous PL/SQL block, SQL*Loader, or an OCI call such as OCIStmtExecute().

Operations can be resumed after these kinds of error conditions:

• Out of space errors, such as ORA-01653.

• Space limit errors, such as ORA-01628.

• Space quota errors, such as ORA-01536.

Limitations on Resuming Operations After an Error Condition

Certain storage errors cannot be handled using this technique. In dictionary-managed tablespaces, you cannot resume an operation if you run into the limit for rollback segments, or the maximum number of extents while creating an index or a table. Use locally managed tablespaces and automatic undo management in combination with this feature.

Writing an Application to Handle Suspended Storage Allocation

When an operation is suspended, your application does not receive the usual error code. Instead, perform any logging or notification by coding a trigger to detect the AFTER SUSPEND event and call the functions in the DBMS_RESUMABLE package to get information about the problem. Using this package, you can:

• Parse the error message with the DBMS_RESUMABLE.SPACE_ERROR_INFO function. For details about this function, refer to Oracle Database PL/SQL Packages and Types Reference.

• Set a new timeout value with the SET_TIMEOUT procedure.

Within the body of the trigger, you can perform any notifications, such as sending a mail message to alert an operator to the space problem.

Alternatively, the DBA can periodically check for suspended statements using the data dictionary views DBA_RESUMABLE, USER_RESUMABLE, and V$_SESSION_WAIT.

When the space condition is corrected (usually by the DBA), the suspended statement automatically resumes execution. If it is not corrected before the timeout period expires, the operation causes a SERVERERROR exception.

To reduce the chance of out-of-space errors within the trigger itself, you must declare it as an autonomous transaction so that it uses a rollback segment in the SYSTEM tablespace. If the trigger encounters a deadlock condition because of locks held by the suspended statement, the trigger is aborted and your application receives the original error condition, as if it was never suspended. If the trigger encounters an out-of-space condition, the trigger and the suspended statement are rolled back. You can prevent the rollback through an exception handler in the trigger, and just wait for the statement to be resumed.

Example of Resumable Storage Allocation

This trigger handles applicable storage errors within the database. For some kinds of errors, it aborts the statement and alerts the DBA that this has happened through a mail message. For other errors that might be temporary, it specifies that the statement should wait for eight hours before resuming, with the expectation that the storage problem will be fixed by then.

CREATE OR REPLACE TRIGGER suspend_example
  AFTER SUSPEND
  ON DATABASE
  DECLARE
  cur_sid NUMBER;
  cur_inst NUMBER;
  err_type VARCHAR2(64);
  object_owner VARCHAR2(64);
  object_type VARCHAR2(64);
  table_space_name VARCHAR2(64);
  object_name VARCHAR2(64);
  sub_object_name VARCHAR2(64);
  msg_body VARCHAR2(64);
  ret_value boolean;
  error_txt varchar2(64);
  mail_conn utl_smtp.connection;
  BEGIN
  SELECT DISTINCT(sid) INTO cur_sid FROM v$mystat;
  cur_inst := userenv('instance');
  ret_value := dbms_resumable.space_error_info(err_type, object_owner, 
  object_type, table_space_name, object_name, sub_object_name);
  IF object_type = 'ROLLBACK SEGMENT' THEN
  INSERT INTO sys.rbs_error ( SELECT sql_text, error_msg, suspend_time 
  FROM dba_resumable WHERE session_id = cur_sid AND instance_id = cur_inst);
  SELECT error_msg into error_txt FROM dba_resumable WHERE session_id = cur_sid AND instance_id = cur_inst;
  msg_body := 'Subject: Space error occurred: Space limit reached for rollback
 segment  '|| object_name || ' on ' || to_char(SYSDATE, 'Month dd, YYYY, HH:MIam')
 || '. Error message was: ' || error_txt;
  mail_conn := utl_smtp.open_connection('localhost', 25);
  utl_smtp.helo(mail_conn, 'localhost');
  utl_smtp.mail(mail_conn, 'sender@localhost');
  utl_smtp.rcpt(mail_conn, 'recipient@localhost');
  utl_smtp.data(mail_conn, msg_body);
  utl_smtp.quit(mail_conn);
  dbms_resumable.abort(cur_sid);
  ELSE
  dbms_resumable.set_timeout(3600*8);
  END IF;
  COMMIT;
  END;