Re: Why this query is sooo slow?!

Just a soft follow-up for now. The statistics that the CBO is using are accurate right? Oracle's CBO is quite good at figuring out how best to execute a query but you need to give it the right information first.

It will also definitely help if you'd post the explain plan results here. The old/slow one and maybe if ever the new one after you run dbms_stats.

Cheers,

Romeo

ibm_97_at_yahoo.com (JZ) wrote in message news:<10bc841c.0405191029.28f4cb29_at_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 - 20:47:05 CDT