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
