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 Fri May 21 2004 - 06:38:04 CDT
