Why this query is sooo slow?!

From: JZ <ibm_97_at_yahoo.com>
Date: 19 May 2004 11:29:50 -0700
Message-ID: <10bc841c.0405191029.28f4cb29@posting.google.com>

Oracle 9.2.0.5 for Linux on a relatively powerful server (4GB RAM and 4 CPU)
Of course as always, I didn't get any help at all from Oracle tech support.

This is the query:

select
TO_CHAR(n.apptimestamp,'') period,
c.countryname,
sum(n.eventcount)
from
high n
,geocountry_m c
,geoipaddress_m ip
,geoinformation_m info

where
n.apptimestamp >= '05/02/2004 05:00:00'

and  n.apptimestamp < '05/02/2004 06:00:00'
and  n.nfseverity in (4,5)
and  n.deviceid in (24,3,13,22,7,5,21,20,26,12,27,23,9,1,15,18,6,2,11,19,17,10,16,14,25,4,8)
and ip.blockid = info.blockid
and c.countrycode = info.countrycode

and n.sourceint >= ip.startip
and n.sourceint <= ip.endip
group by
TO_CHAR(n.apptimestamp,'')
,c.countryname

;

HIGH table has 3.7 millions rows, it only has 10500 rows which meet the following conditions in the query:

apptimestamp >= '05/02/2004 05:00:00'
and apptimestamp < '05/02/2004 06:00:00' and n.nfseverity in (4,5)
and n.deviceid in (24,3,13,22,7,5,21,20,26,12,27,23,9,1,15,18,6,2,11,19,17,10,16,14,25,4,8)

And we have a composite index on HIGH
(apptimestamp,nfseverity,deviceid,eventcount)

geoipaddress_m table has 1.4 millions rows. It has the following columns:

STARTIP                                   NOT NULL NUMBER(38)
 ENDIP                                     NOT NULL NUMBER(38)
 BLOCKID                                   NOT NULL NUMBER(38)

geoinformation_m table has 81000 rows, and geocountry_m table has 200 rows.

Now for the above query, it took almost 1 hour to get the result wich includes 230 rows. From the explain plan, Oracle did use the all 'necessary' index.

I just don't understand why it's soooo slow for this query? Anything I miss?

Any help?

Thanks a lot!! Received on Wed May 19 2004 - 13:29:50 CDT