Oracle-L: RE: Long-running PL/SQL function (long)

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 07 Jan 2003 10:59:39 -0800
Message-ID: <F001.00528E40.20030107105939@fatcity.com>

Cherie,

I'd run this function with 2 events separately ...

first 10938 this will give you pl/sql profiling or simply use dbms_profiler package. This will tell you where (and at which line) you are spending most of your time. Metalink has some really good stuff on profiler ...

Then of course 10046 which will give you SQL profiling and you can concentrate on SQLS. I believe here you have to attack this problem on both fronts. I believe pl/sql tuning will be easier (I think) than SQL.

Are you running the code on the souce DB?

How big is acct_pay_type table? If it is less than 2000 rows, can you pre-load it as a pl/sql table so the selects can be avoided? I believe even some of pl/sql can be re-arranged to be a bit faster. But it is all relative. Have your developer sthought about using bulk-inserts and bulk selects?

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!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).



text/plain attachment: ESPN_Disclaimer.txt

Received on Tue Jan 07 2003 - 12:59:39 CST