RE: Stored Procedure Performance Problem --- Please Help

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:Viral.Amin_at_originindia.com]
> 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: kirti.deshpande_at_verizon.com

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: ListGuru_at_fatcity.com (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).