to slow or not to slow
Hi All,
My Query explains to some execution-plan with cost: 129. It takes minutes to return the first row. When i add a hint the execution plan says the cost is 151 and the query returns with all rows within a few milliseconds.
Statistics are gathered each morning between 05:00 and 06:00 with gather_stats package for 10% of the rows.
Here are the numbers (look at the consitent gets):
WITH HINT:
response-time average 35 milliseconds
Consistent Gets 1806
Physical Reads 499 (Drops to 0 on repeated executions.)
rows-returned: 29 (Varies a bit because of live-data)
WITHOUT HINT:
response-time 8min:51 (times between 7 to 10 minutes)
Consistent Gets 651051
Physical Reads 1381
rows-returned 30
Why does the optimizer calculate a plan which costs less but upon execution appears to be not right? And more important: where should i start looking to get the optimizer choose the better plan..
Regards,
hcHoep