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
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
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
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
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
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.
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