| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
> ----------------------------------------- --------
> ----------------------------
> 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)
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-YYYY 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 ;
>> WHERE nvl(ysnrptflag, 'X') = 'Y';
> CURSOR cur_tblcounter
> IS
> SELECT id, strrptcolname
> FROM tblcounter
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 (full) list of columns is constant and not extravagantly long, you'd be better off I believe with values explictly set to null for unconcerned columns and have a single statement with parameters.
-- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax: +44 (0) 7050-696-449 Performance Tools & Free Scripts -------------------------------------------------------------- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -------------------------------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.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).Received on Fri Aug 31 2001 - 16:16:13 CDT
 |
 |