Yeah, yeah... uhh... yeah......now what did I do with that French
dictionary...?
-----Original Message-----
Sent: Monday, July 30, 2001 2:52 PM
To: Multiple recipients of list ORACLE-L
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).
The information contained in this e-mail is strictly confidential and for the intended
use of the addressee only; it may also be legally privileged and/or price sensitive.
Notice is hereby given that any disclosure, use or copying of the information by
anyone other than the intended recipient is prohibited and may be illegal. If you
have received this message in error, please notify the sender immediately by return
e-mail.
Corporate Systems, Inc. has taken every reasonable precaution to ensure that any
attachment to this e-mail has been swept for viruses. We accept no liability for any
damage sustained as a result of software viruses and advise you carry out your own
virus checks before opening any attachment.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: April Wells
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).
