Viral,
Have you tried to run this with tracing turned on? The trace file can show
you areas of concern.
Also, you may want to look into using DBMS_PROFILER package to find
performance bottlenecks in your PL/SQL procedures. Read more about it in the
Oracle8i Supplied PL/SQL Packages Reference Guide.
Regards,
- Kirti Deshpande
Verizon Information Services
http://www.superpages.com
> -----Original Message-----
> From: Viral Amin [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, August 31, 2001 9: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
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
