1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Executing a stored procedure in multi threaded way as in java

Discussion in 'SQL PL/SQL' started by himani khandelwal, Jul 10, 2017.

  1. himani khandelwal

    himani khandelwal Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    pune
    Hi all,

    I have a job which picks a record from a cursor and then it calls a stored procedure which processes the record picked up from the cursor.

    The stored procedure has multiple queries to process the record. In all, procedure takes about 0.3 seconds to process a single record picked up by the cursor but since cursor contains more than 1 Lac records it takes hours to complete the job.

    The queries in the stored procedure are all optimized

    I was thinking of making the procedure run in multi threaded way as in java and other programming language.
    Can it be done in oracle? or is there any other way I can reduce the run time of my job.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    699
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation
    What exactly is the performance problem?
    Need to establish the cause of problems with performance : long waits on latches,competition and blocking, etc.

    If it is impossible to optimize the process , then you can use :
    dbms_parallel_execute
    example dbms_parallel_execute
     
    himani khandelwal and Sadik like this.
  3. himani khandelwal

    himani khandelwal Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    pune
    I have tried for DBMS_PARALLEL_EXECUTE but getting error ''Invalid state for resume task"
    Here is the code I am using :

    Code (SQL):
    DECLARE
      l_task     VARCHAR2(3000) := 'CBR_ACC1';
      l_sql_stmt VARCHAR2(32767);
      l_try      NUMBER;
      l_status   NUMBER;
    BEGIN
      DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);

      DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col(task_name    => l_task,
                                                        table_owner  => 'REX1',
                                                        TABLE_NAME   => 'CUSTOMER',
                                                        table_column => 'CUSTOMER_PK',
                                                        chunk_size   => 20000);

      l_sql_stmt := 'DECLARE V_ERR VARCHAR(1000) := NULL; BEGIN BATCH_PG.REFRESH_ACCOUNT_STATUS_PR

    (1740,'
    '1'',:start_id,:end_id,NULL,V_ERR); END';

      DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                     sql_stmt       => l_sql_stmt,
                                     language_flag  => DBMS_SQL.NATIVE,
                                     parallel_level => 6);

      -- If there is error, RESUME it for at most 2 times.
      l_try := 0;
      l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
      dbms_output.put_line('status : ' || l_status);
      WHILE(l_try < 2 AND l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
      Loop
        l_try := l_try + 1;
        DBMS_PARALLEL_EXECUTE.resume_task(l_task);
        l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
      END LOOP;
    --DBMS_PARALLEL_EXECUTE.drop_task(l_task);
    END;
    /

    And here is the Error report I am getting on executing my plsql block :

    Error report -
    ORA-29495: invalid state for resume task
    ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 459
    ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 495
    ORA-06512: at line 31
    29495. 00000 - "invalid state for resume task"
    *Cause: An attempt was made to resume execution but the task was not the FINISHED_WITH_ERROR or CRASHED state.
    *Action: Make sure that the task is in the FINISHED_WITH_ERROR or CRASHED
    state.
    status : 4

    TASK_STATUS : CHUNKED
    CHUNK_STATUS : UNASSIGNED

    Please Help...!!
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    699
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation
    You missed the symbol ';'
    Code (Text):

    l_sql_stmt := 'DECLARE V_ERR VARCHAR(1000) := NULL; BEGIN BATCH_PG.REFRESH_ACCOUNT_STATUS_PR

    (1740,''1'',:start_id,:end_id,NULL,V_ERR); END ;';
     
    Code (Text):

    DECLARE
      l_task     VARCHAR2(3000) := 'CBR_ACC1';
      l_sql_stmt VARCHAR2(32767);
      l_try      NUMBER;
      l_status   NUMBER;
    BEGIN
      DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);

      DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col(task_name    => l_task,
                                                        table_owner  => 'REX1',
                                                        TABLE_NAME   => 'CUSTOMER',
                                                        table_column => 'CUSTOMER_PK',
                                                        chunk_size   => 20000);

      l_sql_stmt := 'DECLARE V_ERR VARCHAR(1000) := NULL; BEGIN BATCH_PG.REFRESH_ACCOUNT_STATUS_PR
    (1740,''1'',:start_id,:end_id,NULL,V_ERR); END;';

      DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                     sql_stmt       => l_sql_stmt,
                                     language_flag  => DBMS_SQL.NATIVE,
                                     parallel_level => 6);

      -- If there is error, RESUME it for at most 2 times.
      l_try := 0;
      l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
      dbms_output.put_line('status : ' || l_status);
      WHILE(l_try < 2 AND l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
      Loop
        l_try := l_try + 1;
        DBMS_PARALLEL_EXECUTE.resume_task(l_task);
        l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
      END LOOP;
      DBMS_PARALLEL_EXECUTE.drop_task(l_task);
    END;  
     
     
    himani khandelwal likes this.
  5. himani khandelwal

    himani khandelwal Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    pune
    Thanks a lot for your help...!!
    For now I am stuck in a different problem
    The plsql block is getting executed but on querying the below query -

    Code (SQL):
    SELECT  chunk_id, STATUS, start_id, end_id,error_message
    FROM   USER_PARALLEL_EXECUTE_TASKS
    WHERE  task_name = 'CBR_ACC1';
    ORDER BY chunk_id;
    STATUS : 'PROCESSED_WITH_ERROR'
    Error_message :
    1. ORA-01003: no statement parsed
    2. "ORA-06550: line 1, column 44: PLS-00201: identifier 'BATCH_PG.REFRESH_ACCOUNT_STATUS_PR' must be declaredORA-06550: line 1, column 44: PL/SQL: Statement ignored"

    I have tried running the procedure independently and it is running fine so I feel there is no issue in batch_pg.refresh_account_status_pr

    Code (SQL):
    DECLARE
    v_err VARCHAR(1000) := NULL;
    BEGIN
    batch_pg.REFRESH_ACCOUNT_STATUS_PR(1740,'1',10020,10040,NULL,v_err);
    END;
    /
    What do you think the reason for the error_message is ?? Please help...!!
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    699
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation
    Try this: replace the your line l_sql_stmt on this
    Code (Text):

    l_sql_stmt :=q '<DECLARE V_ERR VARCHAR(1000) := NULL; BEGIN BATCH_PG.REFRESH_ACCOUNT_STATUS_PR
    (1740,'1',:start_id,:end_id,NULL,V_ERR); END;>';