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!

oracle store procedure execution time takes too long

Discussion in 'SQL PL/SQL' started by ashwanth, Apr 10, 2014.

  1. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    I have a stored procedure in which i select a column(with numerous record) from one table and insert into another table using loop

    here the stored procedure takes nearly one and half hours to run


    CREATE OR REPLACE PROCEDURE sp_insert_cust_parents_test
    IS
    l_error_msg varchar2(1000);
    BEGIN
    sp_log_entry (current_timestamp,'sp_insert_cust_parents_test','START',NULL,NULL,NULL);


    dbms_output.put_line('first loop');

    FOR i IN (SELECT true_gcdb_source_key
    FROM customer
    WHERE true_gcdb_source_key IS NOT NULL AND cust_cust_id = 1)
    LOOP
    BEGIN
    INSERT INTO TT_PAR_ID (ID)
    SELECT PARENT_GCDB_ID
    FROM SAP_CUST_REL
    START WITH to_char(CHILD_GCDB_ID) = i.true_gcdb_source_key
    CONNECT BY CHILD_GCDB_ID = PRIOR PARENT_GCDB_ID
    AND PARENT_GCDB_ID IS NOT NULL;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    sp_log_entry (current_timestamp,'sp_insert_cust_parents_test','START',NULL, 'THE PARENT_GCDB_ID NOT FOUND : '||i.true_gcdb_source_key,NULL);
    END;
    END LOOP;

    dbms_output.put_line('inserted into temp table');
    EXCEPTION
    WHEN OTHERS
    THEN
    null;

    end ;
    /


    CREATE GLOBAL TEMPORARY TABLE TT_PAR_ID
    (
    ID VARCHAR2(30 BYTE)
    )ON COMMIT PRESERVE ROWS
    RESULT_CACHE (MODE DEFAULT)
    NOCACHE;

    CREATE TABLE SAP_CUST_REL
    (
    CHILD_GCDB_ID NUMBER(10),
    PARENT_GCDB_ID NUMBER(10),
    VALID_FROM DATE,
    VALID_TILL DATE
    )

    TRUE_GCDB_SOURCE_KEY in cstomer table is varchar2(20)
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    there are some questions.

    1) Oracle version ?
    2) how many rows the sql-query returns : SELECT true_gcdb_source_key
    FROM customer
    WHERE true_gcdb_source_key IS NOT NULL AND cust_cust_id = 1

    4)
    http://docs.oracle.com/cd/E16655_01/server.121/e15857/tune_result_cache.htm#TGDBA645

    why you for GTT set the option RESULT_CACHE ?

    3)
    why you try to process no_data_found in that unit where it never will arise
    Code (SQL):

    .......
    BEGIN
          INSERT INTO TT_PAR_ID (ID)
                 SELECT PARENT_GCDB_ID
                   FROM SAP_CUST_REL
             START WITH to_char(CHILD_GCDB_ID) = i.true_gcdb_source_key
             CONNECT BY CHILD_GCDB_ID = PRIOR PARENT_GCDB_ID
             AND PARENT_GCDB_ID IS NOT NULL;
             EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
             sp_log_entry (CURRENT_TIMESTAMP,'sp_insert_cust_parents_test','   START',NULL, 'THE PARENT_GCDB_ID  NOT FOUND :  '||i.true_gcdb_source_key,NULL);
             END;
    .....
     
    Note:
    it is possible to use, for example, sql%rowcount for check of an insertion of records after execution of dml of the operator




    Possible candidate solution if the sql-query

    Code (SQL):
    SELECT -- if char -> then convert to_number : to_number(true_gcdb_source_key)
     true_gcdb_source_key
    FROM customer
    WHERE true_gcdb_source_key  IS NOT NULL AND                                cust_cust_id = 1
     
    returns acceptable quantity of rows :

    Code (SQL):


    CREATE OR REPLACE PROCEDURE sp_insert_cust_parents_test
    IS
    l_error_msg varchar2(1000);
    BEGIN
    sp_log_entry (CURRENT_TIMESTAMP,'sp_insert_cust_parents_test',' START',NULL,NULL,NULL);

    INSERT INTO
           TT_PAR_ID (ID)
    SELECT
           PARENT_GCDB_ID
    FROM
           SAP_CUST_REL
    START  WITH
           CHILD_GCDB_ID IN (SELECT -- if char -> then convert to_number : to_number(true_gcdb_source_key)
                                    true_gcdb_source_key
                            FROM customer                        
                            WHERE
                                 true_gcdb_source_key IS NOT NULL
                                 AND
                                 cust_cust_id = 1
                          )
    CONNECT BY CHILD_GCDB_ID = PRIOR PARENT_GCDB_ID;

    IF sql%rowcount = 0 THEN
       sp_log_entry (CURRENT_TIMESTAMP,'sp_insert_cust_parents_test',' START',NULL, 'THE PARENT_GCDB_ID NOT FOUND : '||i.true_gcdb_source_key,NULL);
    END IF;  

    sp_log_entry (CURRENT_TIMESTAMP,'sp_insert_cust_parents_test',' STOP',NULL,NULL,NULL);

    dbms_output.put_line('inserted into temp table passed');

    END ;
    /
     
     
  3. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    oracle version is 11g,

    select query returns 1860 rows

    SELECT true_gcdb_source_key
    FROM customer
    WHERE true_gcdb_source_key IS NOT NULL AND cust_cust_id = 1

    i tried with removing no_data_found also, still it takes time to execute
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    For test :
    Try to execute this operator :we will try to construct a tree on 100 keys sources
    Code (SQL):


      INSERT INTO
           TT_PAR_ID (ID)
    SELECT
           PARENT_GCDB_ID
    FROM
           SAP_CUST_REL
    START WITH
           CHILD_GCDB_ID IN  (SELECT -- if char -> then convert to_number : to_number(true_gcdb_source_key)
                                    true_gcdb_source_key
                            FROM customer                        
                            WHERE
                                 true_gcdb_source_key IS NOT NULL
                                 AND
                                 cust_cust_id = 1
                                 AND
                                 ROWNUM <= 100
                          )
    CONNECT BY CHILD_GCDB_ID = PRIOR PARENT_GCDB_ID;

     
     
  5. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    if you do it this you will get single row subquery returns more than 1 row,cz the select query has 1000+ records this will fail
    START WITH
    CHILD_GCDB_ID = (SELECT -- if char -> then convert to_number : to_number(true_gcdb_source_key)
    true_gcdb_source_key
    FROM customer
    WHERE
    true_gcdb_source_key IS NOT NULL
    AND
    cust_cust_id = 1
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    yes, I didn't replace "=" with "IN"....


    Code (SQL):

    INSERT INTO
           TT_PAR_ID (ID)
    SELECT
           PARENT_GCDB_ID
    FROM
           SAP_CUST_REL
    START WITH
           CHILD_GCDB_ID IN (SELECT -- if char -> then convert to_number : to_number(true_gcdb_source_key)
                                    true_gcdb_source_key
                            FROM customer                        
                            WHERE
                                 true_gcdb_source_key IS NOT NULL
                                 AND
                                 cust_cust_id = 1
                                 AND
                                 ROWNUM <= 100
                          )
    CONNECT BY CHILD_GCDB_ID = PRIOR PARENT_GCDB_ID;

     

    the error about which you write won't be....
    simple example :
    Code (SQL):

    WITH
     tab1 AS
     (SELECT level id1 FROM dual CONNECT BY  level < 1002)
    ,
    tab2 AS
     (SELECT level id2 FROM dual CONNECT BY  level < 1002)
     
     SELECT COUNT(*) FROM tab1  WHERE id1 IN (SELECT id2 FROM tab2);

     
     
  7. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    so the above query constructs a tree fro 100 key sources,but i have more than 100 in that case how do i do that
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    the error "if you do it this you will get single row subquery returns more than 1 row,cz the select query has 1000+ records this will fail"
    about which you write won't be....
    simple example :


    Code (SQL):

    WITH
     tab1 AS
     (SELECT level id1 FROM dual CONNECT BY  level < 1002)
    ,
    tab2 AS
     (SELECT level id2 FROM dual CONNECT BY  level < 1002)
     
     SELECT COUNT(*) FROM tab1  WHERE id1 IN (SELECT id2 FROM tab2);


    SQL>
    SQL>
     
      COUNT(*)
    ----------
          1001
     

    provide the test of this query....
    Code (SQL):

    INSERT INTO
           TT_PAR_ID (ID)
    SELECT
           PARENT_GCDB_ID
    FROM
           SAP_CUST_REL
    START WITH
           CHILD_GCDB_ID IN (SELECT -- if char -> then convert to_number : to_number(true_gcdb_source_key)
                                    true_gcdb_source_key
                            FROM customer                        
                            WHERE
                                 true_gcdb_source_key IS NOT NULL
                                 AND
                                 cust_cust_id = 1
                          )
    CONNECT BY CHILD_GCDB_ID = PRIOR PARENT_GCDB_ID;

     
     
     
     
  9. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    the test showed 3066385 rows inserted replace child_gcd_id with to_char(child_gcd_id ) instead of converting to_number(true_gcdb_source_key) ,because true_gcdb_source_key contains non numeric value it cant be converted to number
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    if true_gcdb_source_key may contain non-numerical characters, then use your conditions of connection(in create tree), i.e. those conditions about which you didn't write...

    I.е. you can easily adapt example which shown for the solution of the task on exact conditions

    using your original conditions in build tree :

    Code (SQL):


    INSERT INTO
           TT_PAR_ID (ID)
    SELECT
           PARENT_GCDB_ID
    FROM
           SAP_CUST_REL
    START WITH
           to_char(CHILD_GCDB_ID) IN (SELECT
                                    true_gcdb_source_key
                            FROM customer                        
                            WHERE
                                 true_gcdb_source_key IS NOT NULL
                                 AND
                                 cust_cust_id = 1
                          )
    CONNECT BY CHILD_GCDB_ID = PRIOR PARENT_GCDB_ID;

     
     
  11. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    this part throws an error.i just inserting into temp table


    BEGIN sp_insert_cust_parents_t1; END;

    *
    ERROR at line 1:
    ORA-02291: integrity constraint (FRONTROOM.CUST_CUST_FK) violated - parent key
    not found
    ORA-06512: at "FRONTROOM.SP_INSERT_CUST_PARENTS_T1", line 40
    ORA-06512: at line 1

    INSERT INTO
    TT_PAR_ID (ID)
    SELECT
    PARENT_GCDB_ID
    FROM
    SAP_CUST_REL
    START WITH
    to_char(CHILD_GCDB_ID) IN (SELECT
    true_gcdb_source_key
    FROM customer
    WHERE
    true_gcdb_source_key IS NOT NULL
    AND
    cust_cust_id = 1
    )
    CONNECT BY CHILD_GCDB_ID = PRIOR PARENT_GCDB_ID;
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Show the source code of procedure sp_insert_cust_parents_t1.
    Most likely you broke an insertion order in the table: at first in child-table, and then in the parent-table.