| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: OT Re: performance pl/sql for DW: Sorry wrong language
oh but it was fun trying to remember my college French (and no, I didn't get all, or even most of it, but then I took Spanish for 3 years in high school and French only for a semester)
Rachel
>From: paquette stephane <stephane_paquette_at_yahoo.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: OT Re: performance pl/sql for DW: Sorry wrong language
>Date: Mon, 30 Jul 2001 12:07:14 -0800
>
>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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.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 - 20:19:10 CDT
 |
 |