Re: Stored Procedure Performance Problem --- Please Help
On Friday 31 August 2001 13:40, Stephane Faroult wrote:
> > Procedure:
> >
> > CREATE OR REPLACE PROCEDURE transform_prc IS
> > CURSOR cur_main
> > IS
> > SELECT distinct idserver,
> > to_char(dtreadingtime, 'DD-MON- HH24:MI') dttime
> > FROM tblcounter_reading a
> >WHERE ysntransformflag IS NULL
> > AND to_char(a.dtreadingtime, 'DAY') NOT IN ('SUNDAY ',
> > 'SATURDAY ');
>
> All right, mandatory table scan (functions make index usage impossible
> unless, as somebody suggested, you create an index-based index). In your
> case, I would not advise it since I guess that the bulk of your activity
> occurs from Monday to Friday inclusive, so you are going to exclude with
> the date criterion a ridiculously small fraction of your data - you are
> better off scanning. Concerning the flag, you may be aware that NULL
> value are often not stored at all, and are therefore not indexed. If the
> number of rows you expect to fetch with this restriction is small (i.e.
> the flag is not null in say 95% of cases) I suggest you make the column
> NOT NULL and set a default (unused) value, say '#' - which will make you
> able to index the column. If few rows contain this value and if it is
> indexed, WHERE YWNTRANSFORMFLAG = '#' will fly.
Since this cursor is the main loop, I don't see a problem with the full
table scan, unless the where clause actually limits the return set to
a small percentage of the total. In any case, it doesn't take 20 hours
to read 13 million rows. It's not *that* big.
>
> I have trouble understanding your logic. Why the PL/SQL table?
> Wouldn't it be possible to do a join or whatever to retrieve the column
> name you want? Why do you want to scan the array? If Oracle can do it
> for you, do not hesitate, it may do it more efficiently.
>
I'll agree to that, for sure. I read this code for a few minutes and
decided that was enough. It appears very convoluted to me. What are you
really trying to accomplish here?
And as others have said, get rid of the dynamic SQL. Or at least
use bind variables.
Jared
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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).
Re: Stored Procedure Performance Problem --- Please Help
Viral,
There has been a few sensible advices but no global critic, so I think
that I can have a stab at it.
> Viral Amin wrote:
>
> 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
> -
>
> IDNOT NULL NUMBER --
> Primary Key
> IDSERVER NOT NULL NUMBER
> IDCOUNTER NOT NULL NUMBER
> DTREADINGTIME NOT NULL DATE
> NUMREADINGVALUENUMBER(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)
Index 2 could have some interest if, and only if, your queries could
be answered by looking at those two columns only (you would only have to
look at the index). This is not the case, since you have conditions on
other columns which force you to fetch the table row anyway. Believe me,
you can drop it. If you really feel sentimentally attached to it, at
least rebuild it in reverse order (dtreadingtime, id), otherwise it's
totally useless.
> 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- HH24:MI') dttime
> FROM tblcounter_reading a
>WHERE ysntransformflag IS NULL
> AND to_char(a.dtreadingtime, 'DAY') NOT IN ('SUNDAY ',
> 'SATURDAY ');
All right, mandatory table scan (functions make index usage impossible
unless, as somebody suggested, you create an index-based index). In your
case, I would not advise it since I guess that the bulk of your activity
occurs from Monday to Friday inclusive, so you are going to exclude with
the date criterion a ridiculously small fraction of your data - you are
better off scanning. Concerning the flag, you may be aware that NULL
value are often not stored at all, and are therefore not indexed. If the
number of rows you expect to fetch with this restriction is small (i.e.
the flag is not null in say 95% of cases) I suggest you make the column
NOT NULL and set a default (unused) value, say '#' - which will make you
able to index the column. If few rows contain this value and if it is
indexed, WHERE YWNTRANSFORMFLAG = '#' will fly.
>
> 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- 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;
>
I have trouble understanding your logic. Why the PL/SQL table?
Wouldn't it be possible to do a join or whatever to retrieve the column
name you want? Why do you want to scan the array? If Oracle can do it
for you, do not hesitate, it may do it more efficiently.
Nothing outrageously shocking in the rest of the code, but :
1) as somebody remarked, parsing dynamically hard-coded statements
kills. I don't know your data, but if the (f
RE: Stored Procedure Performance Problem --- Please Help
Another important thing your insert statement should be rewritten to use bind variables i.e. EXECYTE IMMEDIATE USING clause. This will help reduce parsing as well. HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra 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).
RE: Stored Procedure Performance Problem --- Please Help
Title: Stored Procedure Performance Problem --- Please Help Viral, Are you saying your entire database is on one 13GB drive? If so, it's no wonder this operation takes a very long time - you're I/O bound in a big way! The inserts and updates of tables and indexes, not to mention disk sorts in the Temp segment, as well as rollback, redo, etc. are thrashing the heck out of that drive! If you have multiple 13GB drives, then you need to tell us more about table, index and tablespace distribution. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Viral AminSent: Friday, August 31, 2001 9:00 AMTo: Multiple recipients of list ORACLE-LSubject: 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 ... Regards Viral Amin
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:[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
> -
>
> IDNOT NULL NUMBER -- Primary
> Key
> IDSERVER NOT NULL NUMBER
> IDCOUNTER NOT NULL NUMBER
> DTREADINGTIME NOT NULL DATE
> NUMREADINGVALUENUMBER(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- 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- 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;
>
RE: Stored Procedure Performance Problem --- Please Help
Without TKPROF output it is difficult to pinpoint exact statement which is
causing all the problems.
BUT wait .. you can optimize few things
* You mention you are on 817, make use of BULK BINDING for handling all the
inserts, this will help speedup the process.
* If you create a Function based index for to_char(a.dtreadingtime, 'DY')
you can change the where clause to
"where to_char(a.dtreadingtime, 'DY') NOT IN ('SAT','SUN')" all while
using the index. By using DAY, and by specifying 'SUNDAY ' the compiler may
be doing more work
* Looks like tblcounter.id will be unique ... in which case make use of
intelligent keys for pl/sql table you are using. Instead of inserting rows
starting at location 1 onwards ... insert a row where the index value is
tbcounter.id. This way, you won't need the inline function GET_COL_NAME
because you can replace the function calls as
"v_strrptcolname := tblcnt(cur_trans_rec.idcounter).strrptcolname;" there by
removing the in-line function and repeated scans on the pl/sql table.
* For all your insert statements use bulk binds ... thae means you save the
data to be inserted in pl/sql tables and then in one statement you insert
all rows.
HTH
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art !
*1
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
*1
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jamadagni, Rajendra
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).
RE: Stored Procedure Performance Problem --- Please Help
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- 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- 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
