Of course, my thoughts exactly!
Whatever that said.
"Walking on water and developing software from a specification are easy if
both are frozen."
Christopher R. Spence
Oracle DBA
Fuelspot
-----Original Message-----
Sent: Monday, July 30, 2001 3:05 PM
To: Multiple recipients of list ORACLE-L
Salut Daniel,
Alors tu n'es plus chez Covansys ...
Je reviens au Qu�bec en aout. On va avoir un 3e (et
dernier) b�b� en novembre.
Tu n'en demandes pas trop � PL/SQL. Chez Molson on
chargeait plusieur millions par jour en pl/sql
dynamique.
Si tu regardes les stats, en plus de la cpu � 95%,
est-ce que tes disques sont �galement � 100% ?
Quel genre de plan d'acc�s tu as ?
Avez-vous un mod�le de donn�es en �toile (star schema)
ass ez pur ? Chez mon client actuel, il est un peu
hybride et �videmment ca cause des problemes.
Normalement, le principal travail de l'alimentation
des faits est de faire un lookup sur les tables de
dimensions pour aller chercher la cl� g�n�r�e.
Tu es peut-etre mieux de faire un gros join entre la
table de chargement et tes dimensions, comme cela
Oracle va faire du hash-join. Assure-toi de "booster"
le hash_area_size et de mettre le hint append sur
l'insert et de cr�er la table en nologging.
Tiens moi au courant.
St�phane
P.S. Le march� de l'emploi a pas l'air fort de ce
temps la a Montreal.
--- Daniel Garant <[EMAIL PROTECTED]> a �crit�: >
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).
=====
St�phane Paquette
DBA Oracle, consultant entrep�t de donn�es
Oracle DBA, datawarehouse consultant [EMAIL PROTECTED]
___________________________________________________________
Do You Yahoo!? -- Vos albums photos en ligne,
Yahoo! Photos : http://fr.photos.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
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: Christopher Spence
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).
