Stored Procedure Performance Problem --- Please Help

From: Viral Amin <Viral.Amin_at_originindia.com>
Date: Fri, 31 Aug 2001 06:40:50 -0700
Message-ID: <F001.0037F07B.20010831055935@fatcity.com>

Hi All,

Hardware : Dell server - 256MB RAM, 13GB Hard Disk, Single CPU Software : Oracle 8.1.7 (Non-parallel server option) OS : Windows NT 4.0 SP6

BackGround: Following is the table structure and record count

desc tblcounter_reading
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER   -- Primary Key
IDSERVER                                  NOT NULL NUMBER
IDCOUNTER                                 NOT NULL NUMBER
DTREADINGTIME                             NOT NULL DATE
NUMREADINGVALUE                                    NUMBER(38,20)
YSNTRANSFORMFLAG                                   VARCHAR2(1)
SQL> select count(*) from tblcounter_reading;

COUNT(*)

----------

13283499

Indexes on table TBLREPORTCOUNTER_READING

Index on (id) PRIMARY KEY
Index on (id, dtreadingtime)

Problem Description:

Following is the procedure which reads the above table and insert rows in the another table . This procedure execution takes very long time -- like 2-3 min for inserting one row in the other table. We need ways to optimize this in the best possible manner so that the executiuon time is reduced.

Total time of execution - NOT KNOWN, could be ridiculously high like 20hrs or so.

Please help...In a very desparate situation.

Procedure:

CREATE OR REPLACE PROCEDURE transform_prc IS
CURSOR cur_main
IS
SELECT distinct idserver,
         to_char(dtreadingtime, 'DD-MON-YYYY HH24:MI') dttime
    FROM tblcounter_reading a
   WHERE ysntransformflag IS NULL
     AND to_char(a.dtreadingtime, 'DAY') NOT IN ('SUNDAY   ', 'SATURDAY ');

CURSOR cur_trans_main(pi_idserver IN NUMBER, pi_dtreadingtime IN DATE) IS
SELECT numreadingvalue, idcounter
    FROM tblcounter_reading
   WHERE idserver       = pi_idserver
     AND dtreadingtime = pi_dtreadingtime
     FOR UPDATE OF ysntransformflag ;

CURSOR cur_tblcounter
    IS
SELECT   id, strrptcolname
    FROM   tblcounter
   WHERE   nvl(ysnrptflag, 'X') = 'Y';

v_strrptcolname   TBLCOUNTER.STRRPTCOLNAME%TYPE;
v_collist   LONG   := Null; -- Variable to store column list generated form TBLCOUNTER_READING table
v_valuelist LONG   := Null; -- Variable to store value list generated form TBLCOUNTER_READING table
v_sql_stmt LONG   := Null; -- Variable to store Dynamic DML
v_alter_sess_stmt VARCHAR2(1000)   := 'ALTER SESSION SET NLS_DATE_FORMAT = ' || '''' || 'DD-MON-RRRR HH24:MI' || '''' ;

v_rowcnt Number := 1;
v_prev_srv TBLCOUNTER_READING.IDSERVER%TYPE;

Declare plsql table to store counter information from TBLCOUNTER table

TYPE tblcounter_TabType IS TABLE OF tblcounter%ROWTYPE INDEX BY BINARY_INTEGER;
tblcnt tblcounter_TabType;

Function to return the coulumn name for the counter flagged 'Y' in TBLCOUNTER table

FUNCTION get_col_name(pi_idcounter IN Number) RETURN VARCHAR2
    IS
BEGIN
    FOR counter IN 1..v_rowcnt LOOP
      IF tblcnt(counter).id = pi_idcounter THEN
        RETURN tblcnt(counter).strrptcolname;
        EXIT;
      END IF;
    END LOOP;
    RETURN 'XXX';
END;

BEGIN

Set the date format for the user session

EXECUTE IMMEDIATE v_alter_sess_stmt;

Populate the plsql table with values from tblcounter table

FOR tblcounter_rec IN cur_tblcounter LOOP
    tblcnt(v_rowcnt).id := tblcounter_rec.id;
    tblcnt(v_rowcnt).strrptcolname := tblcounter_rec.strrptcolname;
    v_rowcnt := v_rowcnt + 1;
END LOOP;
Start transformation process for each server id in the cursor

FOR server_rec IN cur_main LOOP
    --
    -- Commit Records after transforming records for each server id
    --
    IF NVL(v_prev_srv, server_rec.idserver) != server_rec.idserver THEN
        COMMIT;
    END IF;
Store the value of current serverid

v_prev_srv := server_rec.idserver;

Constructs the column and value pair list for all counters flagged 'Y' in the TBLCOUNTER table

    FOR cur_trans_rec IN cur_trans_main(server_rec.idserver, server_rec.dttime) LOOP
      BEGIN
        v_strrptcolname := get_col_name(cur_trans_rec.idcounter);
        IF v_strrptcolname <> 'XXX' THEN
          v_collist   := v_collist || ',' || v_strrptcolname ;
          v_valuelist := v_valuelist || ',' || cur_trans_rec.numreadingvalue;
        END IF;
       EXCEPTION
            WHEN OTHERS THEN
              NULL;
       END;
Update the TBLCOUNTER_READING.YSNTRANSFORMFLAG to 'Y' for the transformed record.

         UPDATE tblcounter_reading
       SET ysntransformflag = 'N'
       WHERE CURRENT OF cur_trans_main;
    END LOOP;

Build the Insert statement and store it in a variable with coulm and value pair list created above

    v_sql_stmt := 'INSERT
                     INTO tblreportcounter_reading(IDSERVER, DTREADINGTIME, STRINTERVAL' || v_collist || ')
                   VALUES ('|| server_rec.idserver ||','
                            || '''' || to_date(server_rec.dttime,'DD-MON-YYYY HH24:MI')|| '''' ||','
                            || '''' || 'BASE' || ''''
                            || v_valuelist || ')';

    BEGIN
Execute the insert statement prepared

      EXECUTE IMMEDIATE v_sql_stmt ;
    EXCEPTION
      WHEN OTHERS THEN
On error rollback data, log the database error in SFERROR table and exit process.

        ROLLBACK;
        logerror_prc(SQLERRM, 'Transformation' );
        EXIT;
    END;

Re-Initialize variables

    v_collist    := Null;
    v_valuelist := Null;
    v_sql_stmt   := Null;

END LOOP;
END transform_prc;

Regards

Viral Amin Received on Fri Aug 31 2001 - 08:40:50 CDT