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
1) You can ditch PL/SQL and load pre-cleaned data with SQLLDR
there's alot of new functionality there, now.
2) 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).
3) 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.
4) 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.
5) 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
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: Mohan, Ross
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).
