Title: RE: Stored Procedure Performance Problem --- Please Help

Where's your tkprof output? First step always is to trace.

    -----Original Message-----
    From:   Viral Amin [SMTP:[EMAIL PROTECTED]]
    Sent:   Friday, August 31, 2001 10:00 AM
    To:     Multiple recipients of list ORACLE-L
    Subject:        Stored Procedure Performance Problem --- Please Help

    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

    1) Index on (id) PRIMARY KEY
    2) 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

Reply via email to