Re: Physics of the FILTER operation within SQL

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 28 Jun 2004 21:48:52 +0100
Message-ID: <011201c45d51$55b620f0$7102a8c0@Primary>

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_at_alise.lv> To: <oracle-l_at_freelists.org> 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.

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.
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_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 Mon Jun 28 2004 - 15:45:44 CDT

Re: Physics of the FILTER operation within SQL_PLANE.