Re: Physics of the FILTER operation within SQL_PLANE.

Good experiment.

The number of rows (in the rowsource) for the filter lines 
actually tell you how many times the filter operation took
place - and you can confirm this (in 9.2.0.4, at any rate)
by checking the last_starts column in view v$sql_plan_statistics.

I re-ran and modified you test, particularly the one with the 519
executions.  Note that 519 'suggests' 19 values being cached
and one value being re-queried repeatedly.

Results:
    The break point in this test was 15.

    If F2 in the main table cycled from 1 to 15, then
    the execution count of the filter was 15.

    If F2 in the main table cycled from 1 to 16, then
    the execution count of the filter was 515 - i.e.
    15 cached, and one re-cycled 500 times.

Results 2:
    I increased the number of rows in the sub-table
    in stages, and increased the number of values in
    the main-table to keep up.
   
    At 64 rows in the sub-table, and the value cycling
    from 1 to 64 in the main table, the executions of 
    the filter was 3058.  Which is 500 * 6 + (64 - 6).
    In other words - 58 cached, and 6 non-cached !

It may be possible to figure out exactly what's going
on by varying the number of values - and perhaps the
number of repetitions of the cycle.  But it looks as if
the algorithm is aimed at handling a small number of
possible returns from the subquery.


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: Monday, June 28, 2004 12:00 AM
Subject: Re: Physics of the FILTER operation within SQL_PLANE.


FILTER operation effectiveness depends on how (in which order) rows are 
inserted into driving table. Looks similar to clustering factor in index 
range scans ;)
Take a look on simple TESTCASE I have made on 9.2.0.4 Win2000.
As you can see LIO count defers by 17 times (First case 63 LIO, second 
1061) depending on order how rows have been inserted.
One more effect, if we reduce row count in filter table (third test), then 
Oracle execute filter operation more effective (LIO=43) independing of 
inserting order, due to "remembering results of previous probes" probably.

1. Any comments?
>> [Jonathan Lewis] However, FILTER can be much more efficient than nested 
loop, because it can remember results of previous probes into the second 
table - effectively making the probe an in-memory lookup.
2. Any ideas how many "results of previous probes" Oracle can "remember" 
for next comparison?

Best regards,
Jurijs

TESTCASE
========================================
Preparation part (common for all tests)
-------------------------------
drop table main_tab; 
drop table filter_tab;
create table main_tab (n number, v varchar2(100));
create table filter_tab (n number, v varchar2(100));
begin for f in 1..20 loop
insert into filter_tab values (f,'a');
end loop; commit; end;
/
create unique index filter_tab_i1 on filter_tab (n);
------------------------------
--------  First test  --------
------------------------------
truncate table main_tab;
begin for f2 in 1..20 loop for f1 in 1..500 loop
insert into main_tab values (f2,'a');
end loop; end loop; commit; end;
/
------------------------------
SQL Trace 10046 (tkprof output)
------------------------------
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      1      0.00       0.00          0          0          0     0
Fetch        2      0.10       0.11          0         63          0     1
------- ------  -------- ---------- ---------- ---------- ---------- 
----------
total        4      0.10       0.11          0         63          0     1

Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=63 r=0 w=0 time=111286 us)
  10000   FILTER  (cr=63 r=0 w=0 time=86030 us)
  10000    TABLE ACCESS FULL MAIN_TAB (cr=23 r=0 w=0 time=28758 us)
     20    TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=40 r=0 w=0 time=425 
us)
     20     INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=20 r=0 w=0 time=194 
us)(object id 9669)


------------------------------
--------  Second test  -------
------------------------------
truncate table main_tab;
begin for f1 in 1..500 loop for f2 in 1..20 loop
insert into main_tab values (f2,'a');
end loop; end loop; commit; end;
/
------------------------------
SQL Trace 10046 (tkprof output)
------------------------------
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      1      0.00       0.00          0          0          0     0
Fetch        2      0.12       0.12          0       1061          0     1
------- ------  -------- ---------- ---------- ---------- ---------- 
----------
total        4      0.12       0.13          0       1061          0     1

Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1061 r=0 w=0 time=129048 us)
  10000   FILTER  (cr=1061 r=0 w=0 time=103463 us)
  10000    TABLE ACCESS FULL MAIN_TAB (cr=23 r=0 w=0 time=28637 us)
    519    TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=1038 r=0 w=0 
time=8436 us)
    519     INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=519 r=0 w=0 time=3711 
us)(object id 9666)



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