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
Hi
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
