Re: Why this query is sooo slow?!

Cannot use 'use_hash', 'cause we have '>' and '<' for 'HIGH' and 'GEOIPADDRESS_M' table join.

When I mean 10500 rows from 'HIGH' table will be retrived, this happens before applying the filters with 'GEOIPADDRESS_M' table:

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)

All the SGA parameters look fine. 'workarea_size_policy' is auto, 'pga_aggregate_target' is 500MB.

Any ideas?
Thanks!

srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0405210338.3fa0647a_at_posting.google.com>...
> JZ,
> The CBO is estimating that only 184 rows would be retrieved from the
> HIGH table after applying all the filters. Whereas you say that 10500
> rows will be retrieved. This small estimate is causing the CBO to go
> for a NESTED LOOPS join between the tables HIGH and geoipaddress_m
> (geo_ip_pk).
> You can use the USE_HASH hint to force the HASH join between the 2
> tables.
> Also just look at the PGA_AGGREGATE_TARGET or sort and hash area sized
> that you use.
> Explain Plan doesnt always tell the true story, it is just an estimate
> of the CBO.
> To see the actual rows obtained in each step, do the following.
> Run the statement after setting SQL_TRACE at level 12.
> After the statement is executed, close the session (This is needed for
> getting the STAT entries) and run TKPROF on the statement.
> You can see the actual row counts retrieved in each step in the tkprof
> output under ROW SOURCE.
> Paste it here if you like.
> Also from 9i you can see the wait events too.
> regards
> Srivenu
Received on Mon May 24 2004 - 09:46:07 CDT