Mohamed Houri’s Oracle Notes

December 23, 2019

PUSH SUBQUERY

Filed under: Tuning — hourim @ 8:27 am

In the previous article we examined the performance benefit brought by the scalar subquery caching effect when a call to a PL/SQL function is wrapped into a select from dual. Scalar subquery caching, almost always, reduces the number of PL/SQL function recursive calls. In this article I wanted to share with you the observations I have made in the same real-life query when I replaced the call to the PL/SQL function by its content.

The real-life query running under a 12cR1 Exadata machine and using a PL/SQL function in the where clause looks like the following:

SQL> SELECT /*+ gather_plan_statistics */
2     a.xy_etp_dat_deb,
3     a.xy_etp_num,
4     a.xy_bat_id
5  FROM
6     ps_xy_exp_etp_exe a
7  WHERE
8       a.xy_bat_id = f_get_id('BJOBD176')
9   AND a.xy_etp_dat_deb = (select
10                               max(b.xy_etp_dat_deb)
11                           from ps_xy_exp_etp_exe b
12                           where a.xy_bat_id = b.xy_bat_id
13                           )
14   AND a.xy_etp_num     = (select
15                              max(c.xy_etp_num)
16                           from ps_xy_exp_etp_exe c
17                           where c.xy_bat_id    = a.xy_bat_id
18                           and c.xy_etp_dat_deb = a.xy_etp_dat_deb
19                           );

Elapsed: 00:00:01.80

Statistics
---------------------------------------------------------
18605  recursive calls
0  db block gets
297806  consistent gets
0  physical reads
0  redo size
482  bytes sent via SQL*Net to client
372  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
1  rows processed

Instead of surrounding the PL/SQL function at line 8 by a “select from dual” as explained in the previous article, I decided to use its content directly into the where clause as shown below at line 8:

SQL> SELECT /*+ gather_plan_statistics */
2     a.xy_etp_dat_deb,
3     a.xy_etp_num,
4     a.xy_bat_id
5  FROM
6     ps_xy_exp_etp_exe a
7  WHERE
8       a.xy_bat_id = (select xy_bat_id from ps_xy_exp_bat where xy_bat_nom = 'BJOBD176')
9  AND a.xy_etp_dat_deb = (select
10                               max(b.xy_etp_dat_deb)
11                           from ps_xy_exp_etp_exe b
12                           where a.xy_bat_id = b.xy_bat_id
13                          )
14   AND a.xy_etp_num     = (select
15                              max(c.xy_etp_num)
16                           from ps_xy_exp_etp_exe c
17                           where c.xy_bat_id    = a.xy_bat_id
18                           and c.xy_etp_dat_deb = a.xy_etp_dat_deb
19                           );
Elapsed: 00:00:00.04

Statistics
-------------------------------------------------------
0  recursive calls
0  db block gets
142  consistent gets
0  physical reads
0  redo size
482  bytes sent via SQL*Net to client
372  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
1  rows processed

As you can see, replacing the PL/SQL function call by its content has eliminated the number of recursive calls as we went from 18506 recursive to 0 call and from 2 seconds to 4 milliseconds.

Let’s try to understand what happened so that we got such a performant improvement. First the row source execution plan (for the sake of simplicity I am using the predicate part taken from the explain plan for command):

Plan hash value: 408154845
------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |      1 |        |      1 |
|*  1 |  FILTER                                 |                   |      1 |        |      1 |
|*  2 |   HASH JOIN                             |                   |      1 |      1 |      1 |
|*  3 |    TABLE ACCESS STORAGE FULL            | PS_XY_EXP_ETP_EXE |      1 |     63 |     76 |
|*  4 |     TABLE ACCESS STORAGE FULL FIRST ROWS| PS_XY_EXP_BAT     |      1 |      1 |      1 |
|   5 |    VIEW                                 | VW_SQ_1           |      1 |    296 |    296 |
|   6 |     SORT GROUP BY                       |                   |      1 |    296 |    296 |
|   7 |      TABLE ACCESS STORAGE FULL          | PS_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |
|   8 |   SORT AGGREGATE                        |                   |      1 |      1 |      1 |
|*  9 |    TABLE ACCESS STORAGE FULL            | PS_XY_EXP_ETP_EXE |      1 |      1 |      1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."XY_ETP_NUM"= (SELECT MAX("C"."XY_ETP_NUM") FROM "PS_XY_EXP_ETP_EXE" "C" WHERE
"C"."XY_ETP_DAT_DEB"=:B1 AND "C"."XY_BAT_ID"=:B2))
2 - access("A"."XY_ETP_DAT_DEB"="MAX(B.XY_ETP_DAT_DEB)" AND "A"."XY_BAT_ID"="ITEM_1")
3 - storage("A"."XY_BAT_ID"= (SELECT "XY_BAT_ID" FROM "PS_XY_EXP_BAT" "PS_XY_EXP_BAT" WHERE
"XY_BAT_NOM"='BJOBD176'))
filter("A"."XY_BAT_ID"= (SELECT "XY_BAT_ID" FROM "PS_XY_EXP_BAT" "PS_XY_EXP_BAT" WHERE
"XY_BAT_NOM"='BJOBD176'))
4 - storage("XY_BAT_NOM"='BJOBD176')
filter("XY_BAT_NOM"='BJOBD176')
9 - filter("C"."XY_ETP_DAT_DEB"=:B1 AND "C"."XY_BAT_ID"=:B2)

That’s a weird non-common execution plan, right?

How could it be differently when we see these two unusual operations at lines Id n°3 and n°4?

Usually, a PARENT-CHILD operation is, for example, an index feeding its parent table with ROWIDS which, then, get filtered by the parent table access by index ROWID. But, here, we have a parent TABLE ACCESS STORAGE FULL of PS_XY_EXP_ETP_EXE at line Id n°3 and an unrelated child operation TABLE ACCESS STORAGE FULL FIRST ROWS of table PS_XY_EXP_BAT at line Id n°4.

What I, very often do, when trying to understand what cunning transformation has been done by Oracle behind the scenes is to check the outline section of the execution plan:

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
../..
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$BD9E0841")
OUTLINE(@"SEL$1")
FULL(@"SEL$B9151BA2" "A"@"SEL$1")
NO_ACCESS(@"SEL$B9151BA2" "VW_SQ_1"@"SEL$BD9E0841")
LEADING(@"SEL$B9151BA2" "A"@"SEL$1" "VW_SQ_1"@"SEL$BD9E0841")
USE_HASH(@"SEL$B9151BA2" "VW_SQ_1"@"SEL$BD9E0841")
PUSH_SUBQ(@"SEL$2")                              --> this is where the subuqery is pushed
PQ_FILTER(@"SEL$B9151BA2" SERIAL)
FULL(@"SEL$291F8F59" "B"@"SEL$3")
FULL(@"SEL$4" "C"@"SEL$4")
END_OUTLINE_DATA
*/

Indeed, there is that hint PUSH_SUBQ(@”SEL$2″) which indicates that he scalar subquery against PS_XY_EXP_BAT table has been first evaluated once (Starts =1, Id operation n°4) and then, the result of this evaluation, has been pushed as a predicate against the PS_XY_EXP_ETP_EXE table (Id operation n°3). And this is exactly what made PS_XY_EXP_BAT table a child subordinate action to PS_XY_EXP_ETP_EXE parent table.

If Oracle had not pushed the scalar subquery down the execution plan tree, it would have produced the following execution plan:

explain plan for
SELECT
a.xy_etp_dat_deb,
a.xy_etp_num,
a.xy_bat_id
FROM
ps_xy_exp_etp_exe a
WHERE
a.xy_bat_id = (select /*+ NO_PUSH_SUBQ(@"SEL$2")*/ xy_bat_id from ps_xy_exp_bat where xy_bat_nom = 'BJOBD176')
AND a.xy_etp_dat_deb = (select
max(b.xy_etp_dat_deb)
from ps_xy_exp_etp_exe b
where a.xy_bat_id = b.xy_bat_id)
AND a.xy_etp_num     = (select
max(c.xy_etp_num)
from ps_xy_exp_etp_exe c
where c.xy_bat_id    = a.xy_bat_id
and c.xy_etp_dat_deb = a.xy_etp_dat_deb);

Plan hash value: 1136506203
----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |      1 |
|*  1 |  FILTER                               |                   |      1 |        |      1 |
|*  2 |   HASH JOIN                           |                   |      1 |    296 |    296 |
|*  3 |    HASH JOIN                          |                   |      1 |    296 |    318 |
|   4 |     VIEW                              | VW_SQ_1           |      1 |    296 |    296 |
|   5 |      SORT GROUP BY                    |                   |      1 |    296 |    296 |
|   6 |       TABLE ACCESS STORAGE FULL       | PS_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |--1
|   7 |     TABLE ACCESS STORAGE FULL         | PS_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |
|   8 |    VIEW                               | VW_SQ_2           |      1 |  18697 |  17933 |
|   9 |     SORT GROUP BY                     |                   |      1 |  18697 |  17933 |
|  10 |      TABLE ACCESS STORAGE FULL        | PS_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |
|* 11 |   TABLE ACCESS STORAGE FULL FIRST ROWS| PS_XY_EXP_BAT     |      1 |      1 |      1 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."XY_BAT_ID"= (SELECT /*+ NO_PUSH_SUBQ */ "XY_BAT_ID" FROM "PS_XY_EXP_BAT"
"PS_XY_EXP_BAT" WHERE "XY_BAT_NOM"='BJOBD176'))
2 - access("A"."XY_ETP_NUM"="MAX(C.XY_ETP_NUM)" AND "ITEM_2"="A"."XY_BAT_ID" AND
"ITEM_3"="A"."XY_ETP_DAT_DEB")
3 - access("A"."XY_ETP_DAT_DEB"="MAX(B.XY_ETP_DAT_DEB)" AND "A"."XY_BAT_ID"="ITEM_1")
11 - storage("XY_BAT_NOM"='BJOBD176')
filter("XY_BAT_NOM"='BJOBD176')

Notice now how the scalar subquery has been left until all the other predicates have been applied to the parent query block (operation Id n°1). This is the case where the subquery is evaluated as a FILTER predicate for every row in the result set. In the PUSH_SUBQ former case, Oracle runs the subquery for each row in PS_XY_EXP_ETP_EXE table and join the 76 survived rows with the rest of the data. This means that when the subquery has been pushed, the first operation in the execution plan tree has eliminated almost all rows and kept only 76. However, in the NO_PUSH_SUBQ case, the first operation(id n°6 in plan with plan hash value 1136506203) in the execution plan produced 18605 rows. Since Oracle started bigger, more data have then to be carried out through the rest of the plan causing, consequently, more work and more time.

Summary

Through two distinct articles, we’ve examined a performance issue of a real-life query using a PL/SQL function in one of its WHERE clause to FILTER rows from the parent query block. In the first article we outlined how we can take benefit from scalar subquery cache effect simply by surrounding the PL/SQL function call with a SELECT FROM DUAL. In the second article (current one) we examined how sometimes it is better to replace the PL/SQL function by its content in the scalar subquery WHERE clause. Proceeding as such, we can offer the Oracle optimizer engine the possibility to push the result of the scalar subquery where clause deeper in the execution plan path; filtering, as such, non-qualified rows much earlier and carrying less data through the rest of the execution plan.

1 Comment »

  1. Superb

    Comment by Amit Gorain — May 14, 2020 @ 4:38 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: