Sorry, wrong language
I replay all instead of replying only to the original
poster.
- paquette stephane <stephane_paquette_at_yahoo.com> a
écrit : > 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 <danielg_at_conceptis.com> 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: 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).
>
> =====
> Stéphane Paquette
> DBA Oracle, consultant entrepôt de données
> Oracle DBA, datawarehouse consultant
> stephane_paquette_at_yahoo.com
>
>
> 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: stephane_paquette_at_yahoo.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).
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
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: stephane_paquette_at_yahoo.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).
Received on Mon Jul 30 2001 - 14:03:31 CDT
