Raj, Just got this code this morning so I haven't had a chance to run a trace yet. Will have to schedule that for tonight.
Yes, code is being run on the source DB.
Yes, ACCT_PAY_TYPE is less than 2000 rows. You are the second person who
has recommended a PL/SQL table. I will run a test and see if that will
help here.
No, I don't believe the developers have actively considered bulk selects
and/or bulk inserts. I forgot to mention that we are using 9.2.0.1 on the
source database and 8.1.7.2 on the target (remote) database. Could we
still do bulk inserts if that is the case?
Thanks for your reply and ideas. I will investigate and pass them on.
Cherie
"Jamadagni,
Rajendra" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<Rajendra.Jamadagni cc:
@espn.com> Subject: RE: Long-running PL/SQL
function (long)
Sent by:
[EMAIL PROTECTED]
01/07/03 12:59 PM
Please respond to
ORACLE-L
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! (See
attached file: ESPN_Disclaimer.txt)
ESPN_Disclaimer.txt
Description: Binary data
