Transaction, Heal Thyself!  Part 2
Darryl Hurley, Pinnacle Publishing

Oracle 9i introduces a long awaited feature that suspends certain transactions instead of failing them straight away. In last month’s article Darryl discussed how to augment resumable statements to automatically resolve storage issues. In this second article of the series he focuses on a key area of resumable statements, namely, responding to timeouts and terminations.

Introduction

While resumable statements are a boon to DBAs and developers alike they do make one application concept more complicated; exception handling. Consider this overly simplistic exception-handling block.

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -01536 THEN
      DBMS_OUTPUT.PUT_LINE('Space Quota Exceeded!');
    ELSE
      DBMS_OUTPUT.PUT_LINE(‘Something Else!’);
    END IF;
END;

If the code raises ORA-1536 (space quota exceeded) this simplified exception handler will display a mundane message and allow processing to continue. While hardly an effective scheme for handling exceptions it illustrates how resumable statements can complicate things. What will occur if the block of code enters a suspended state that is terminated or times out? In this case it will be “Something Else!”.

Thus the vital question is what to do when resumable statements terminate or time out. This article begins with a discussion of possible options for managing that and concludes with details of how the Transaction, Heal Thyself (THY) architecture handles success and failure.

Termination

Terminating suspended statements deemed beyond repair is done using the ABORT procedure in the DBMS_RESUMABLE package.

DBMS_RESUMABLE.ABORT(<session ID>);

Statements halted in this manner receive a rather deceptive error message.

SQL> create table demo ( col1 number );
create table demo ( col1 number )
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

While technically correct (a user did request the operation be cancelled, it just wasn’t the current user) I’m left with no way to determine what actually caused the problem. Should the transaction continue? Should I roll everything back? More information is certainly required.

The deceptiveness of ORA-01013 is depicted even further within exception handlers as shown in Listing 1.

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'CREATE TABLE demo ( col1 NUMBER )';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5      IF SQLCODE = -1013 THEN
  6        DBMS_OUTPUT.PUT_LINE('What should I do?');
  7      ELSE
  8        RAISE;
  9      END IF;
 10  END;
 11  /
What should I do?

PL/SQL procedure successfully completed.

Listing 1. Trapping ORA-1013 for timeouts

Timeout

A much friendlier termination is provided by another procedure in the DBMS_RESUMABLE package, SET_SESSION_TIMEOUT.

DBMS_RESUMABLE.SET_SESSION_TIMEOUT(<session id>,<value>);

The new timeout value takes effect “immediately” in the selected session. Setting this value to 1 causes an almost simultaneous timeout.

Note that a session timeout value of zero is invalid and will be greeted by ORA-24280: invalid input value for parameter TIMEOUT

The error message provided to “un-resumable” statements is now more forthcoming.

SQL> CREATE TABLE demo ( col1 NUMBER );
CREATE TABLE demo ( col1 NUMBER )
*
ERROR at line 1:ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace 'USERS'

The tricky part is harvesting details of the error. A starting point is shown in Listing 2.

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'CREATE TABLE demo ( col1 NUMBER )';
  3  EXCEPTION  
  4    WHEN OTHERS THEN
  5      IF SQLCODE = -30032 THEN
  6        DBMS_OUTPUT.PUT_LINE('I know what to do?');
  7      ELSE
  8        RAISE;
  9      END IF;
 10  END;
 11  /
I know what to do?

PL/SQL procedure successfully completed.

Listing 2. Trapping suspended statement timeout

The real error can be gleaned by parsing the error stack as demonstrated in Listing 3.

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30032 THEN
      DECLARE
        v_error_stack VARCHAR2(2000) := 
        DBMS_UTILITY.FORMAT_ERROR_STACK;
      BEGIN
        v_error_stack := SUBSTR(v_error_stack,
        INSTR(v_error_stack,CHR(10))+1,1000);
        DBMS_OUTPUT.PUT_LINE(v_error_stack);
      END;
    END IF;
END;

Listing 3. Exception handler extracting the real error message

Now the real error number and message displays..

ORA-01536: space quota exceeded for tablespace 'USERS'

But what if the code calling this block is designed to respond to specific error numbers? What should it do with the generic 1536 error number? An actual exception throwing ORA-1536 must occur. This can be accomplished using Native Dynamic SQL (NDS) to re-raise the exception as revealed in Listing 4.

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30032 THEN
      DECLARE
        v_error_stack VARCHAR2(2000) := 
        DBMS_UTILITY.FORMAT_ERROR_STACK;
      BEGIN
        v_error_stack := SUBSTR(v_error_stack,
                       INSTR(v_error_stack,CHR(10))+1,
                                10000);
        v_error_stack := SUBSTR(v_error_stack,4,
                       INSTR(v_error_stack,':') - 4);
        EXECUTE IMMEDIATE 'DECLARE ' ||
                          ' v_exception EXCEPTION;' ||
              ' PRAGMA EXCEPTION_INIT(v_exception,' ||
                              v_error_stack || ');' ||
                         ' BEGIN' ||
                           ' RAISE v_exception;' ||
                         ' END;';
      END;
    END IF;
END;

Listing 4.Re-raising the real exception

The real exception is indeed re-raised although some details are lost such as the tablespace name (see Listing 5). But at least a calling program can determine what went wrong based on the Oracle error number.

BEGIN
*
ERROR at line 1:ORA-01536: space quota exceeded for tablespace ''
ORA-06512: at line 1
ORA-06512: at line 11
ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace 'USERS'

Listing 5. The re-raised exception.

The whole point of this first section has been to make it very clear that adding resumable statements to applications requires a lot of thought with regards to exception handling.

The second section of this article switches gears to deal with failure of another sort in THY, specifically determining if an attempted fix was successful.

Triumph or Disappointment?

In the initial article of this series I assumed all fixes applied by THY were triumphant in allowing suspended statements to resume. However, plenty of proverbs and past experiences dissuade me from sticking to this assumption. I think I’ll enhance the application instead.

Recall that all sessions registering for resumable statements find themselves listed in the DBA_RESUMABLE view. This view includes a column denoting the current status of the session. Thus the easiest way to check success or failure is to query its status in this view. To do this I need to capture the session ID (SID) within the AFTER SUSPEND trigger and record it in the FIXER_INFO table as demonstrated in Listing 6.

CURSOR curs_get_sid IS
SELECT sid
  FROM v$mystat;
v_sid NUMBER;
…
OPEN curs_get_sid;
FETCH curs_get_sid INTO v_sid;
CLOSE curs_get_sid;
…
INSERT INTO fixer_info(fixer_id,
                       sid,
…
VALUES(fixer_info_seq.nextval,
       v_sid,

Listing 6. Gathering session ID (SID) in the AFTER SUSPEND trigger

Later the FIXER package evaluates success as shown in Listing 7.

/*--------------------------------------------------*/
FUNCTION normal_status ( p_sid NUMBER ) RETURN BOOLEAN
 IS
/*--------------------------------------------------*/

  CURSOR curs_check_normal ( cp_sid NUMBER ) IS
  SELECT NULL
    FROM dba_resumable
   WHERE session_id = cp_sid
     AND status = 'NORMAL';

  v_dummy   VARCHAR2(1);
  v_ret_val BOOLEAN := FALSE;

BEGIN

  OPEN curs_check_normal(p_sid);
  FETCH curs_check_normal INTO v_dummy;
  v_ret_val := curs_check_normal%FOUND;
  CLOSE curs_check_normal;

  RETURN(v_ret_val);

END normal_status;

Listing 7. Checking fix success (session back to NORMAL)

Note there will be a lag time between the fix completing and the suspended statement (and session) actually resuming. Therefore a pause is required before evaluating status. My testing revealed a pause of two seconds (using DBMS_LOCK.SLEEP) sufficed but I recommend establishing what is best in your own environment.

Wasted Suspension

Now that the outcome of a fix can be evaluated within THY it’s time to respond to the outcome. Specifically when a suspended statement is deemed beyond help. Consider this example.
 

10:00:00	Session established with timeout of 120 seconds (2 minutes)
10:30:00	Session suspended due to space quota exceeded situation
10:30:01	Space quota increased by 1 MB
10:30:03	Statement remains suspended because 1MB increase was not enough
10:32:00	Statement failure finally signaled to session

Almost two minutes (117 seconds) is wasted waiting for a fix that is just not coming. In the grand scheme of things that’s not a large loss but remember the session holds all of its locks, temporary tablespace extents, rollback extents, etc. for the whole time it is suspended. This can cause other sessions to wait for these resources unnecessarily. It’s obviously better to terminate the suspended statement as soon as it is deemed beyond help.

Unabated Timeouts

As detailed in the first section of this article the upside of timing out over terminating is better exception handling. The downside is the sessions timeout value remains reset leaving subsequent statements with only 1 second to be repaired. Consider this example.
 

10:00:00	Session established with timeout of 120 seconds (2 minutes)
10:30:00	Session suspended due to space quota exceeded situation
10:30:01	Space quota increased by 1 MB
10:30:03	Statement remains suspended because 1MB increase was not enough
10:32:04	Session timeout reset to 1 second, statement fails but session continues
10:38:00	Session suspended due to space quota exceeded situation (again)
10:38:01	Statement times out

Even the fastest DBA or automated device requires more than one second to resolve problems.

A solution is to reset the session’s timeout value within exception or error handling routines whenever a session timeout (ORA-30032) is processed. This requires easy access to the original value. One way that can be provided is using an application context as created in Listing 8.

CREATE OR REPLACE CONTEXT suspend_context 
  USING suspend_context_pkg;

CREATE OR REPLACE PACKAGE suspend_context_pkg AS

  PROCEDURE set_timeout ( p_timeout NUMBER );

END suspend_context_pkg;
/

CREATE OR REPLACE PACKAGE BODY suspend_context_pkg AS

  /*---------------------------------------------*/
  PROCEDURE set_timeout ( p_timeout NUMBER ) AS
  /*---------------------------------------------*/
  BEGIN
    DBMS_SESSION.SET_CONTEXT('suspend_context',
      'TIMEOUT',p_timeout); 
  END set_timeout;

END suspend_context_pkg;

Listing 8. Application context handling session timeout value

The AFTER LOGON trigger calls the SET_TIMEOUT procedure directly using the appropriate value from the RESUMABLE_USER table as shown in Listing 9.

CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON
ON DATABASE
DECLARE

  -- cursor to get settings for user
  CURSOR curs_get_setting ( cp_user VARCHAR2 ) IS
  SELECT *
    FROM resumable_user
   WHERE username = cp_user;
  v_setting_rec curs_get_setting%ROWTYPE;

BEGIN

  -- check if the user is allowed to use
  -- resumable statements
  OPEN curs_get_setting(ORA_LOGIN_USER);
  FETCH curs_get_setting INTO v_setting_rec;

  -- alter the session to enable resumable statements
  -- if the user is allowed to
  IF curs_get_setting%FOUND THEN
    EXECUTE IMMEDIATE
       'ALTER SESSION ENABLE RESUMABLE NAME '
       || '''' || v_setting_rec.name
       || '''' || ' TIMEOUT '
       || v_setting_rec.timeout;
    suspend_context_pkg.set_timeout
      (v_setting_rec.timeout);
  END IF;

  CLOSE curs_get_setting;

END;

Listing 9. AFTER LOGON trigger calling SET_TIMEOUT

Public access to the context and its underlying package is required so it can be used in exception and error handling routines. This is provided by a new procedure in the FIXER package called RESET_TIMEOUT revealed in Listing 10.

/*-------------------------------------------------*/
PROCEDURE reset_timeout IS
/*-------------------------------------------------*/

  v_context DBMS_SESSION.APPCTXTABTYP;
  v_counter INTEGER;

BEGIN

  DBMS_SESSION.LIST_CONTEXT(v_context,v_counter);

    FOR counter IN 1..v_counter LOOP

      IF v_context(counter).namespace =
        'SUSPEND_CONTEXT' AND
         v_context(counter).attribute = 'TIMEOUT' THEN

        DBMS_RESUMABLE.SET_TIMEOUT
          (v_context(counter).value);

      END IF;

    END LOOP;

END reset_timeout;

Listing 10. RESET_TIMEOUT procedure in FIXER package

Since public access is only required for this one procedure I’ll create a stand-alone calling procedure and give it a public synonym.

/*-------------------------------------------------*/
CREATE OR REPLACE PROCEDURE reset_timeout AS
/*-------------------------------------------------*/
BEGIN
  fixer.reset_timeout;
END;
/

GRANT EXECUTE ON reset_timeout TO PUBLIC;
CREATE OR REPLACE PUBLIC SYNONYM reset_timeout
  FOR reset_timeout;

Now exception and error handling code only needs to call RESET_TIMEOUT to reset the session’s timeout value.

WHEN OTHERS THEN
  IF SQLCODE = -30032 THEN
    RESET_TIMEOUT;
    handle_exception(…

Coming Up Next Time

The next article in this series flushes out the complete SPACE QUOTA EXCEEDED procedure and completes the FIXER package (both introduced in the first article) including logging, maximum and repeated fix attempts. It also presents the first set of downloadable code for this series of articles.