Oracle 9.2.0.1.0 For Windows performance question

From: Aguyngueran <grzybek_at_mikrus.pw.edu.pl>
Date: 23 Aug 2003 07:11:04 -0700
Message-ID: <cdac6650.0308230611.28874bac@posting.google.com>

My Oracle as above server is running on 4 x 2.8GHz with 1GB RAM, Windows 2000 Server system. I'd like to ask about my server performance:
First I have table "TABLEKA":
create table TABELKA (

  kod_w VARCHAR2(2),
  kod_p VARCHAR2(2),
  kod_g VARCHAR2(2),

  rodzaj_g CHAR,
  uzytecznosc CHAR,
  ...
);

I'd like to create a raport contaning for each distinct kod_w || kod_p || kod_g || rodzaj_g count of records by 'uzytecznosc'. I did it like this:
CREATE OR REPLACE FUNCTION STAT_WPGG(a VARCHAR2, b VARCHAR2, c VARCHAR2, d CHAR, e CHAR) RETURN NUMBER IS   result NUMBER;
BEGIN
   SELECT count(*) INTO result FROM TABELKA WHERE kod_w = a and kod_p = b and kod_g = c and rodzaj_g = d and uzytecznosc = e;

   return result;
END; And now, like this (uzytecznosc field is constrained to 'N', 'B', 'U' and 'W' values):

SELECT
  kod_w || kod_p || kod_g || rodzaj_g,

  STAT_WPGG(kod_w, kod_p, kod_g, rodzaj_g, 'N') AS N,
  STAT_WPGG(kod_w, kod_p, kod_g, rodzaj_g, 'B') AS B,
  STAT_WPGG(kod_w, kod_p, kod_g, rodzaj_g, 'U') AS U,
  STAT_WPGG(kod_w, kod_p, kod_g, rodzaj_g, 'W') AS W

FROM
  tabelka
GROUP BY
  kod_w, kod_p, kod_g, rodzaj_g;
/

And for 2 000 000 records table it takes very long time to generate such stats ?
More that 1 hour. Why ? Is there any better way to do this (I'm sure there is :) ?
I must say that
  SELECT count(*) from tabelka
takes abous 40 seconds !!! Maybe there is sthg wrong with database configuration, I really don't know.

Any suggestions

Thanks

   Aguyngueran Received on Sat Aug 23 2003 - 09:11:04 CDT