RE: performance pl/sql for DW

From: Mohan, Ross <MohanR_at_STARS-SMI.com>
Date: Mon, 30 Jul 2001 12:28:01 -0700
Message-ID: <F001.00359256.20010730112602@fatcity.com>

about one a second, eh?

you should be able to do 200x that, even with PL/SQL.

having said that, I'll get out of the way and let the experts tell you how......but maybe

You can ditch PL/SQL and load pre-cleaned data with SQLLDR there's alot of new functionality there, now.
If you keep PL/SQL, dump indexes and constraints and retry. If timings are much different, consider temp-dumping yer indices and constraints whilst loading (assuming precleaned data, of course).
Run some timing checks while your next load goes. In fact, do the basic utlbstat/estat thing, and I'll be glad to look at it. Just run it only for the 45 minutes it takes to run a test.
Get your Windows SA ( Oxymoronic? ) to take a pulse on your server and storage while all this is going on. Saying the CPU is busy doesn't quite pinpoint what's going on.
Consider godlike PL/SQL row tricks like bulk-binding, etc. Plenty of folks on the list will tell you how to do that. or to RTFM.

hth

Ross

-----Original Message-----
Sent: Monday, July 30, 2001 2:31 PM
To: Multiple recipients of list ORACLE-L

We are in the process of loading our DW. The raw data is located in the same database as the DW. We wrote a PL/SQL package that goes thru the raw data table and convert it into the fact table. The process is not really complex, but cannot be done in one insert statement.

Now, the first test I did with only 2500 rows to convert (we have 13 millions in the real table) took 45 minutes!!! The CPU on the NT server is averaging 95% and I am alone on the server.

The package looks roughly like this

Package
  procedure process_col1;
  ...
  procedure process_colxxx
  ...
  main procedure
  for each row in raw table
    execute process_col1;
    execute process_colxxx;
    if ok
      insert into fact table
  next row

Nothing fancy here. After searching on metalink, I found out that the call of a procedure is expensive in cpu. I removed all the procedures and move them in the main one. That did not help at all. I used the nocopy for the OUT and IN OUT parameters, but still no noticeable improvement. I have no indexes on my fact table (I will put them later) and all the FK constraints are disable.

Am i asking too much from PL/SQL here? What is the best approach for loading the data in my fact table? Any pitfall I should be aware of that can cause my process to be so slow? I have a bunch of these procedure extracting sub-string for a big one (spliting the URL field into site, port and path fields). Is that something that PL/SQL is not good at?

TIA
Daniel

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Daniel Garant
INET: danielg_at_conceptis.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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mohan, Ross
INET: MohanR_at_STARS-SMI.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------