| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Physics of the FILTER operation within SQL_PLANE.
You have to be careful with the word "cost".
In discussions like these, I try to stick with 'resource usage' as a description of how much work is done and 'cost' as the value predicted by the optimizer.
You are quite correct - there are many cases where the optimizer works out a cost by making some assumptions about the data distribution - but the actual resource usage can vary greatly for the same data 'content' if it is arranged in a different order.
I have just built a test case based on your test 1 test 2 (repeating the value vs. cycling the value) where the execution plan is identical, the cost is identical, the actual number of logical I/Os is identical - and the only difference is the CPU usage. Given the exact structure of the test, I think this confirms my hypothesis that Oracle remembers the last join key and value rather then visiting the hash table every time.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
If your hypnotize is true (regerding remembering last value), then I have
one more interesting conclusion:
The cost of the same SQL with the same execution plans can differ
depending on rows ordering in the driving rowset.
Like with index range scan and clustering factor, but with one little
difference - Oracle optimizer in case of FILTER operation can't evaluate
this cost (in case of range scan - clustering factor statistics
available).
Even more, if your hypnotize is true, then cost difference can appears not
from bigger LIO count, but also because in one case Oracle need to manage
hash table in other doesn't (just use last remembered value).
For my point demonstrating purpose, I have a little bit modified my
initial testcase (1,2).
Take a look: the same SQL, the same ExplPlan, time differs by 18% and
Oracle optimizer can't caltulate this difference.
Jurijs
 |
 |