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

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


----- Original Message ----- 
From: <J.Velikanovs@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 30, 2004 10:02 AM
Subject: Re: Physics of the FILTER operation within SQL_PLANE.


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


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: