Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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
TESTCACE 1
exists (select v from
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1000 0.07 0.03 0 0 0 0 Fetch 1000 66.62 67.31 0 63000 0 1000------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 1)
TESTCACE 2
------- ---------------------------------------------------
1000 SORT AGGREGATE (cr=63000 r=0 w=0 time=67303317 us)
10000000 FILTER (cr=63000 r=0 w=0 time=50248421 us)
10000000 TABLE ACCESS FULL MAIN_TAB (cr=23000 r=0 w=0 time=17710168 us)
20000 TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=40000 r=0 w=0
time=186850 us)
20000 INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=20000 r=0 w=0 time=76637
us)(object id 9913)
SELECT /*+ RULE */ count(m.v) from main_tab m where
exists (select v from
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1000 0.02 0.04 0 0 0 0 Fetch 1000 80.55 81.33 0 1061000 0 1000------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT AGGREGATE (cr=1061000 r=0 w=0 time=81322686 us)
10000000 FILTER (cr=1061000 r=0 w=0 time=64221016 us)
10000000 TABLE ACCESS FULL MAIN_TAB (cr=23000 r=0 w=0 time=19141048 us)
519000 TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=1038000 r=0 w=0
time=4702545 us)
519000 INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=519000 r=0 w=0
time=1974375 us)(object id 9910)
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: oracle-l-bounce_at_freelists.org
30.06.2004 11:28
Please respond to oracle-l
To: <oracle-l_at_freelists.org> cc: Subject: Re: Physics of the FILTER operation withinSQL_PLANE. Jurijs
Good point - I forgot to explain how I had discounted that theory and what I concluded instead.
First: if the 'new probe' replaced existing value, then you would have seen 1,018 rows in the probe lines of the plan where the 20 values were cycling instead of repeating - because the two colliding values would keep replacing each other.
So add in the mechanism:
Oracle also 'remembers', without putting it into the hash table, the most recent key value - and if the next row is the same key value then the last value can be re-used without revisiting the hash table. In fact, it is possible that Oracle only tries to put an item into the hash table if the next key value does not match the current key value.
In passing:
The hash table seems to be 256 entries for 9.2 and 1024 entries for 10.1; and the presence of the cost-based optimizer doesn't seem to make any different to the size of the table.
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
Jonathan,
thank you for your interest in this issue
>> If a new probe key collides with an existing probe key, then the result
is not saved.
I suspect this assumption may be not true.
Take a look on first and second test in my example:
They are different jus by inserted rows order in main_tab table.
First one:
1, 1, 1, . 500 times, 2, 2, 2, . , 500 times .. 20, 20, 20, . 500 times
63 LIO = 23 FullScan + 40 IQS+TA
Second:
1,2,3,.20, 1,2,3.20, 1,2,3.20 . 500 times
1061 LIO = 23 FS + 1038 IS+TA
If mentioned assumption true, then even incase of first testcase we will
not get just 40 LIO for FILTER values.
May be algorithm is:
If a new probe key collides with an existing probe key, then the new probe
is saved in place of existing one.
Jurijs
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org 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 -----------------------------------------------------------------Received on Wed Jun 30 2004 - 04:07:37 CDT