RE: performance pl/sql for DW

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 30 Jul 2001 12:01:20 -0700
Message-ID: <F001.003591E6.20010730111619@fatcity.com>

Daniel,

You need to look at basic tuning issues like:

SGA size
Redo Log size and disk placement
Data file disk placement
Control file disk placement
Temp templacement disk placement

What you are describing is not typically a problem with PL/SQL. In my humble opinion, it is the fastest way to cleanse and load data.

It sounds like you have basic database tuning issues. Make sure you have all indexes and even PK's on the new fact table turned off - like you said, you can create them later. Look at the SQL you are running to get the data - is it using existing indexes to access the data? What does your substr statement look like - are you maybe looping too far for each record?

If you could share your code, I'd be happy to look at it.

hope this helps.

Tom Mercadante
Oracle Certified Professional

-----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: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

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