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
