Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Physics of the FILTER operation within SQL_PLANE.

Re: Physics of the FILTER operation within SQL_PLANE.

From: <J.Velikanovs_at_alise.lv>
Date: Wed, 30 Jun 2004 12:02:17 +0300
Message-ID: <OF837F6F10.1B556C30-ONC2256EC3.003185C3-C2256EC3.003252C1@alise.lv>


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



SELECT /*+ RULE */ count(m.v) from main_tab m where
        exists
        (select v from 

  filter_tab f where f.n=m.n and f.v like 'a%')

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
------- ------ -------- ---------- ---------- ---------- ----------

total 2001 66.69 67.34 0 63000 0 1000

Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 1)

TESTCACE 2



Rows Row Source Operation
-------  ---------------------------------------------------

   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 

  filter_tab f where f.n=m.n and f.v like 'a%')

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
------- ------ -------- ---------- ---------- ---------- ----------

total 2001 80.57 81.37 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 within 
SQL_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

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



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
-----------------------------------------------------------------



----------------------------------------------------------------
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US