Oracle SQL
  • LICENSE

Too many function executions in simple query

Posted on June 10, 2013 by Sayan Malakshinov Posted in CBO, oracle, PL/SQL optimization, undocumented 2,932 Page views

Suppose we have a table with 10 rows:

SQL> select id from t10;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

And we have the query:

select *
from (
     select xf(t10.id) a
     from t10 
     )
where a*a >= 25

At first it may seem that the function should be executed as many times as rows in a table T10, i.e. 10 times.
Lets test it:

SQL> create or replace function xf(p int) return int as
  2  begin
  3    dbms_output.put_line('F fired!');
  4    return p;
  5  end;
  6  /

Function created.

SQL> set serverout on;
SQL> select *
  2  from (
  3       select xf(t10.id) a
  4       from t10
  5       )
  6  where a*a >= 25
  7  /

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired! -- 15
F fired!
F fired!
F fired!
F fired!
F fired! -- 20
F fired!
F fired!
F fired!
F fired!
F fired! -- 25
F fired!

As you see, there are more than 10 executions, so lets see the execution plan:

SQL> @xplan +projection

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2919944937

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T10  |     1 |     3 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T10"."ID"[NUMBER,22]

Now you see that inner view was merged, and the function was executed 20 times in the filter and 6 times on the fetch after filtering(6 rows – 6 calls).
I see that often in such cases “no_merge” hint is suggested, but let’s test it:

SQL> select *
  2  from (
  3       select/*+ no_merge */ xf(t10.id) a
  4       from t10
  5       )
  6  where a*a >= 25
  7  /

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!

As you can see, the number of function calls wasn’t changed.
And if we look into the plan, we understood why:

SQL> @xplan +projection

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2027387203

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  VIEW              |      |     1 |    13 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T10  |     1 |     3 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"[NUMBER,22]
   2 - "T10"."ID"[NUMBER,22]

20 rows selected.

Now you see, that main problem is the “filter pushdown” transformation. Previously, if we were to disable the “filter pushdown” operation, we had to use a variety of tricks, such as “materialize” hint or adding the “rownum” in expession, etc. But all these solutions require rewriting the query.
But from 11.2.0.3 we can use “_optimizer_filter_pushdown” parameter, for example:

SQL> begin
  2    dbms_sqltune.import_sql_profile(
  3      sql_text    => 'select * from (select xf(t10.id) a from t10) where a*a >= 25'
  4     ,profile     => sys.sqlprof_attr(
  5                             q'[NO_MERGE(@SEL$2)]'
  6                            ,q'[OPT_PARAM('_OPTIMIZER_FILTER_PUSHDOWN' 'FALSE')]'
  7                          )
  8     ,category    => 'DEFAULT'
  9     ,name        => 'TEST_PROFILE'
 10     ,force_match => true
 11     ,replace     => true
 12    );
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16
SQL> set serverout on
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired! -- extra execution because of sql*plus
Elapsed: 00:00:00.17
-- there are no extra calls when we fetches by 100 rows in pl/sql:
SQL> exec for r in (select * from (select xf(t10.id) a from t10) where a*a >= 25) loop null; end loop;
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22
With changing arraysize

[sourcecode language=”sql”]
— with fetching by 1 row:
SQL> declare
2 cursor c is select * from (select xf(t10.id) a from t10) where a*a >= 25;
3 n number;
4 begin
5 open c;
6 loop
7 fetch c into n;
8 exit when c%notfound;
9 end loop;
10 end;
11 /
F fired!
F fired!
F fired!
F fired!
F fired! — 5
F fired!
F fired!
F fired!
F fired!
F fired! — 10
F fired!
F fired!
F fired!
F fired!
F fired! — 15
F fired!
F fired!
F fired!
F fired!
F fired! — 20
F fired!
F fired! — 22

PL/SQL procedure successfully completed.

— with arraysize = 3
SQL> set arraysi 3
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

A
———-
5
6
7
8
9
10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! — 5
F fired!
F fired!
F fired!
F fired!
F fired! — 10
F fired!
F fired!
F fired!
F fired! — 14
Elapsed: 00:00:00.45
SQL> set arraysi 2
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

A
———-
5
6
7
8
9
10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! — 5
F fired!
F fired!
F fired!
F fired!
F fired! — 10
F fired!
F fired!
F fired!
F fired!
F fired!
F fired! — 16
Elapsed: 00:00:00.72
[/sourcecode]

[collapse]

PS. I found that about “_optimizer_filter_pushdown” parameter already wrote Randolf Geist

« A couple of well-known but often forgotten things for PL/SQL developers
Oracle 12c: Inconsistency of Inline “with” functions »
Page views: 2,932
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro Alumni

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • Oracle Telegram Bot
  • Partition Pruning and Global Indexes
  • Interval Search: Part 4. Dynamic Range Segmentation – interval quantization
  • Interval Search Series: Simplified, Advanced, and Custom Solutions
  • Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Popular posts

Recent Comments

  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Optimizing Date Range Queries – Part 1
  • Oracle SQL | Interval Search Series: Simplified, Advanced, and Custom Solutions on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 2. Dynamic Range Segmentation – Simplified on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin
©Sayan Malakshinov. Oracle SQL