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

From: <Cherie_Machler_at_gelco.com>
Date: Tue, 07 Jan 2003 11:53:33 -0800
Message-ID: <F001.00528F93.20030107115333@fatcity.com>

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 <ORACLE-L_at_fatcity.com>    
                    <Rajendra.Jamadagni       cc:                                                                    
                    @espn.com>                Subject:     RE: Long-running PL/SQL function (long)                   
                    Sent by:                                                                                         
                    root_at_fatcity.com                                                                                 
                                                                                                                     
                                                                                                                     
                    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)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Cherie_Machler_at_gelco.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).



application/octet-stream attachment: ESPN_Disclaimer.txt

Received on Tue Jan 07 2003 - 13:53:33 CST