RE: performance pl/sql for DW

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Mon, 30 Jul 2001 12:46:51 -0700
Message-ID: <F001.00359354.20010730115145@fatcity.com>

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 <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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
  INET: cspence_at_FuelSpot.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:46:51 CDT