Project: Project (1. SOFTWAROVÁ s.r.o.)

Description: Interface Package for Transactional Parallel Processing

Author: Petr Jezek

Version: 2.0.1

Date: 2012-09-18



The Transactional Parallel Processing package is useful primary in tasks where is required parallel processing to use maximal CPU with minimal effort to rewrite current applications.

It can be also used like first aid in case of low performance PLSQL code.

The most interesting benefit is transactional behavior. It allows you rewrite only part where is required to use parallel processing without rewriting rest of the code. There's detection of COMMIT operation.

When is after the part of parallel processing in the main session COMMIT, then is while finalization provided COMMIT in all parallel processed sessions.

Otherwise is proceed after processing in all parallel sessions to execute ROLLBACK operation.

The transactional behavior minimalize effort to rewrite current PLSQL code.



Example of using:

Let's assume we have current plsql code.



DECLARE

  X NUMBER:= 4;

  Y VARCHAR2(10) := 'TEST';

BEGIN

  FOR I IN (1..NODE_ID)

  LOOP

    -- BEGIN PROCESSING

    INSERT INTO TEST_TO_PROCESS (X, Y, NODE_ID) VALUES (TO_NUMBER(X), Y, NODE_ID);

    DBMS_LOCK.SLEEP(20);

    -- END PROCESSING

  END LOOP;



  IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) > 22 THEN

    -- EVERY PROCESSING AFTER 22 HOUR SHOULD BE ROLLED BACK

    ROLLBACK;

  ELSE

    -- OTHERWISE IS VALID AND CAN BE COMMITED

    COMMIT;

  END IF;

END;

/



It can be using the Transactional Parallel Processing package rewrite following way.

1) The processing part in the loop should be sparated into procedure.

2) Grant the new procedure to Transactional Parallel Processing package. (We recommand to keep Transactional Parallel Processing in separate schema under separate user.)

3) Rewite the processing.



Add 1) Result will be following:

CREATE OR REPLACE PROCEDURE TO_EXECUTE (X IN NUMBER, Y IN VARCHAR2, NODE_ID IN NUMBER DEFAULT 0)

AS

  P VARCHAR2(100) := 'TO_EXECUTE';

BEGIN

  -- BEGIN PROCESSING

  INSERT INTO TEST_TO_PROCESS (X, Y, NODE_ID) VALUES (TO_NUMBER(X), Y, NODE_ID);

  DBMS_LOCK.SLEEP(20);

  -- END PROCESSING

EXCEPTION

  WHEN OTHERS THEN

  &&TPP_OWNER..TPP.ADD_LOG(P, 'ERR: '||TO_CHAR(SQLCODE)||' '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END TO_EXECUTE;

/



Add 2) Grant of the procedure.

GRANT EXECUTE ON TO_EXECUTE TO &&TPP_OWNER;



Add 2) Update current processing.

DECLARE

  L_TASK VARCHAR2(30) := 'TEST_TASK';

  L_SQL_EXECUTE_STMT VARCHAR2(32767);

BEGIN



  -- PROCEDURE TO BE EXECUTE IN PARALLEL

  L_SQL_EXECUTE_STMT := 'BEGIN &&TPP_USER..TO_EXECUTE (1, ''TEST'', :NODE_ID); END;';



  &&TPP_OWNER..TPP.INITIATE(

    P_TASK_NAME => L_TASK

    ,P_COMMENT => 'EXAMPLE OF PROCESSING BY THE TRANSACTIONAL PARALLEL PROCESSING'

    ,P_SQL_STMT => L_SQL_EXECUTE_STMT

    ,P_LANGUAGE_FLAG => DBMS_SQL.NATIVE

    ,P_PARALLEL_LEVEL => 4 -- THERE IS REQUIRED TO USE 4 PRALLEL INSTANES

  );



  &&TPP_OWNER..TPP.WAIT_FOR_END_OF_PROCESSING(L_TASK);



  IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) > 22 THEN

    -- EVERY PROCESSING AFTER 22 HOUR SHOULD BE ROLLED BACK

    ROLLBACK;

  ELSE

    -- OTHERWISE IS VALID AND CAN BE COMMITED

    COMMIT;

  END IF;



  &&TPP_OWNER..TPP.FINALIZATION(L_TASK);



END;

/



Note: For the example to demonstrate the transactinal behavior we define following table



CREATE TABLE TEST_TO_PROCESS (

  X NUMBER,

  Y VARCHAR2(100),

  NODE_ID NUMBER

)

TABLESPACE &&SMALL_DATA;



COMMENT ON TABLE TEST_TO_PROCESS IS

'Test table to transction on TPP processing example'

;

COMMENT ON COLUMN TEST_TO_PROCESS.X IS 'An value known as X number type';

COMMENT ON COLUMN TEST_TO_PROCESS.Y IS 'An value known as Y varchar type';

COMMENT ON COLUMN TEST_TO_PROCESS.NODE_ID IS 'Identification of node';



Result of processing before 22 PM are records in table test_to_process.

See result of following command:

SELECT FROM TEST_TO_PROCESS;



Result of processing before 22 PM is no record in table test_to_process.

See result of following command:

SELECT FROM TEST_TO_PROCESS;



@headcom
@throws CHUNK_NOT_FOUND ORA-29499 Specified chunk does not exist
@throws DUPLICATE_TASK_NAME ORA-29497 Same task name has been used by an existing task
@throws INVALID_STATE_FOR_CHUNK ORA-29492 Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
@throws INVALID_STATE_FOR_REDSUME ORA-29495 Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
@throws INVALID_STATE_FOR_RUN ORA-29494 Attempts to execute the task that is not in CHUNKED state
@throws INVALID_STATUS ORA-29493 Attempts to set an invalid value to the chunk status
@throws INVALID_TABLE ORA-29491 Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
@throws MISSING_ROLE ORA-29490 User does not have the necessary ADM_PARALLEL_EXECUTE role
@throws TASK_NOT_FOUND ORA-29498 Specified task_name does not exist
@throws INCONSIST_DATA ORA-20001 When missing definition of any chunk. Value of p_parallel_level should be at least 1
@throws END_OF_EVALUATION_PERIOD ORA-20002 When missing definition of any chunk. Value of p_parallel_level should be at least 1
@throws ERROR_WHILE_EXECUTION ORA-20003 Error while execution of user defined procedure.Most likely is not the procedure granted for execution of TPP.
@throws ERROR_WHILE_COMMIT ORA-20004 An error raise while processing commit of transansaction in subprocess.


Method Summary
 add_log( p_procedure in varchar2 , p_message in varchar2 )
          
           Add user trace into table trace_log

          
 cancel_processing( p_task_name in varchar2 )
          
           Goal of the procedure is to cancel processing of the task and rollback all already processed parts.
          

          
 clean_task( p_task_name in varchar2 )
          
           Goal of the procedure is to wait in the main session since the Transactional Parallel Processing will finish processing
          

          
 finalization( p_task_name in varchar2 )
          
           The procedure is detecting transactional bahvior in the main session.

           When there is between initiate and finalization commit, then it commits work in each parallel session.

           Otherwise is the detect rollback trancastion in parallel sesssions is rollbacking

          

          
 initiate( p_task_name in varchar2 , p_comment in varchar2 , p_sql_stmt in clob , p_parallel_level in number )
          
           Initiate and run the Transactional Paralel Processsing
          

          
 licence_id( p_company_name out varchar2 , p_invoice_id out varchar2 , p_paymenent_type out varchar2 , p_paymenent_date out varchar2 )
          
           The procedure is returning details of licence TPP

          

          
 wait_for_end_of_processing( p_task_name in varchar2 )
          
           Goal of the procedure is remove task from queue and stop with rollback if it is required
          

          

Method Detail

add_log

          add_log( p_procedure in varchar2 , p_message in varchar2 ) 
          
           Add user trace into table trace_log

          
Parameters:
p_procedure - Name of procedure to better searching in the log
p_message - Content of the message

cancel_processing

          cancel_processing( p_task_name in varchar2 ) 
          
           Goal of the procedure is to cancel processing of the task and rollback all already processed parts.
          

          
Parameters:
p_task_name - Name of the task to create
Throws:
CHUNK_NOT_FOUND - 29499 Specified chunk does not exist
DUPLICATE_TASK_NAME - 29497 Same task name has been used by an existing task
INVALID_STATE_FOR_CHUNK - 29492 Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
INVALID_STATE_FOR_REDSUME - 29495 Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
INVALID_STATE_FOR_RUN - 29494 Attempts to execute the task that is not in CHUNKED state
INVALID_STATUS - 29493 Attempts to set an invalid value to the chunk status
INVALID_TABLE - 29491 Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
MISSING_ROLE - 29490 User does not have the necessary ADM_PARALLEL_EXECUTE role
TASK_NOT_FOUND - 29498 Specified task_name does not exist
INCONSIST_DATA - 20001 When missing definition of any chunk. Value of p_parallel_level should be at least 1

clean_task

          clean_task( p_task_name in varchar2 ) 
          
           Goal of the procedure is to wait in the main session since the Transactional Parallel Processing will finish processing
          

          
Parameters:
p_task_name - Name of the task to create
Throws:
CHUNK_NOT_FOUND - 29499 Specified chunk does not exist
DUPLICATE_TASK_NAME - 29497 Same task name has been used by an existing task
INVALID_STATE_FOR_CHUNK - 29492 Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
INVALID_STATE_FOR_REDSUME - 29495 Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
INVALID_STATE_FOR_RUN - 29494 Attempts to execute the task that is not in CHUNKED state
INVALID_STATUS - 29493 Attempts to set an invalid value to the chunk status
INVALID_TABLE - 29491 Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
MISSING_ROLE - 29490 User does not have the necessary ADM_PARALLEL_EXECUTE role
TASK_NOT_FOUND - 29498 Specified task_name does not exist
INCONSIST_DATA - 20001 When missing definition of any chunk. Value of p_parallel_level should be at least 1

finalization

          finalization( p_task_name in varchar2 ) 
          
           The procedure is detecting transactional bahvior in the main session.

           When there is between initiate and finalization commit, then it commits work in each parallel session.

           Otherwise is the detect rollback trancastion in parallel sesssions is rollbacking

          

          
Parameters:
p_task_name - Name of the task to create
Throws:
CHUNK_NOT_FOUND - 29499 Specified chunk does not exist
DUPLICATE_TASK_NAME - 29497 Same task name has been used by an existing task
INVALID_STATE_FOR_CHUNK - 29492 Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
INVALID_STATE_FOR_REDSUME - 29495 Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
INVALID_STATE_FOR_RUN - 29494 Attempts to execute the task that is not in CHUNKED state
INVALID_STATUS - 29493 Attempts to set an invalid value to the chunk status
INVALID_TABLE - 29491 Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
MISSING_ROLE - 29490 User does not have the necessary ADM_PARALLEL_EXECUTE role
TASK_NOT_FOUND - 29498 Specified task_name does not exist
ERROR_WHILE_EXECUTION - 20003 Error while execution of user defined procedure.Most likely is not the procedure granted for execution of TPP.

initiate

          initiate( p_task_name in varchar2 , p_comment in varchar2 , p_sql_stmt in clob , p_parallel_level in number ) 
          
           Initiate and run the Transactional Paralel Processsing
          

          
Parameters:
p_task_name - Name of the task to create
p_comment - Comment of the task to easier search
p_sql_stmt - Statement to parallel run for example begin '&&TPP_USER..to_execute (1, ''test'', :node_id); end;'
p_parallel_level - Number of parallel jobs; Should be number >= 1. *
Throws:
CHUNK_NOT_FOUND - ORA-29499 Specified chunk does not exist
DUPLICATE_TASK_NAME - ORA-29497 Same task name has been used by an existing task
INVALID_STATE_FOR_CHUNK - ORA-29492 Attempts to chunk a table that is not in CREATED or CHUNKING_FAILED state
INVALID_STATE_FOR_REDSUME - ORA-29495 Attempts to resume execution, but the task is not in FINISHED_WITH_ERROR or CRASHED state
INVALID_STATE_FOR_RUN - ORA-29494 Attempts to execute the task that is not in CHUNKED state
INVALID_STATUS - ORA-29493 Attempts to set an invalid value to the chunk status
INVALID_TABLE - ORA-29491 Attempts to chunk a table by rowid in cases in which the table is not a physical table, or the table is an IOT
MISSING_ROLE - ORA-29490 User does not have the necessary ADM_PARALLEL_EXECUTE role
TASK_NOT_FOUND - ORA-29498 Specified task_name does not exist
INCONSIST_DATA - ORA-20001 When missing definition of any chunk. Value of p_parallel_level should be at least 1
END_OF_EVALUATION_PERIOD - ORA-20002 When missing definition of any chunk. Value of p_parallel_level should be at least 1

licence_id

          licence_id( p_company_name out varchar2 , p_invoice_id out varchar2 , p_paymenent_type out varchar2 , p_paymenent_date out varchar2 ) 
          
           The procedure is returning details of licence TPP

          

          
Parameters:
p_company_name - Name of company licenced to use TPP
p_invoice_id - The ID of invoice
p_paymenent_type - Type of payment
p_paymenent_date - Date of payment
Throws:
ERROR_WHILE_COMMIT - 20004 An error raise while processing commit of transansaction in subprocess.

wait_for_end_of_processing

          wait_for_end_of_processing( p_task_name in varchar2 ) 
          
           Goal of the procedure is remove task from queue and stop with rollback if it is required
          

          
Parameters:
p_task_name - Name of the task to create