Skip to Main Content
  • Questions
  • JPPD not occurring with nested UNION and/or row generation

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Narendra.

Asked: March 03, 2020 - 3:03 pm UTC

Last updated: March 16, 2020 - 10:28 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hello,

I am faced with a performance issue with a sql in our application and am struggling to come up with ways to influence the optimizer to use JPPD.
I have tried to simulate the setup as much as possible and can reproduce the issue on Livesql (which appears to suggest that the issue exists in 19c too) ( https://livesql.oracle.com/apex/livesql/s/joo2vr7zjudklu42aahptpkhx ) as well as on 11.2.0.4 with April 2019 PSU applied.

In the testcase, there are 2 versions of AS_DIM_VIEW.
One version uses CONNECT BY...DUAL method to generate multiple rows but the final sql using this view, is not able to take advantage of JPPD on AS_FACT* tables and hence results in full table scan of those tables and data gets filtered late in the plan and hence the performance is poor.

Another version of AS_DIM_VIEW uses a "normal" table (TEMP_TABLE) which has a single column (NUM_VAL) storing numbers (up to 10000) and when used in the final sql, optimizer is able to use JPPD to push predicates to AS_FACT* tables and hence results in indexed access of those tables and data gets filtered early, which produces better performance.

Can you please help me figure out
a) if this behavior (lack of JPPD) is a known feature or a bug? AND
b) if there is a way to influence optimizer to use JPPD when AS_DIM_VIEW uses CONNECT BY...DUAL

The only closely related known bug that I could find was in JPPD Is Not Performed On 11.2.0.4 And 12.1.0.2 On A Union All View That Contains Table Expressions ( Doc ID 2391659.1 ) but not sure if that is causing this.

with LiveSQL Test Case:

and Chris said...

Eeesh, you've got a lot going on there!

Looking at the Live SQL plans, the query using CONNECT BY DUAL reads the fact tables first. This is probably because the row estimate and cost for AS_DIMENSION is much higher when using dual compared to a real table. 1.7 Billion rows vs 108 million and ~43,500 cost vs. 21.

Thus the optimizer can't push predicates from the dimension view into the fact view because it hasn't read the dimension values yet!

So I suspect this is more a costing/row estimation issue than JPPD.

With a much more simplified example joining CONNECT BY DUAL to a view with UNIONs, the optimizer is able to push the predicates into the view. As you can see from the access predicates at lines 8-10:

create table t1 (
  c1 
) as
with rws as (
  select level x from dual
  connect by level <= 10
)
  select * from rws;
create table t2 (
  c1 
) as
with rws as (
  select level+10 x from dual
  connect by level <= 10
)
  select * from rws;
create table t3 (
  c1 
) as
with rws as (
  select level+19 x from dual
  connect by level <= 10
)
  select * from rws;

create index i1 on t1 ( c1 );
create index i2 on t2 ( c1 );
create index i3 on t3 ( c1 );

create or replace view vw as 
  select * from t1
  union
  select * from t2
  union
  select * from t3;

set serveroutput off
with rws as (
  select level x from dual
  connect by level <= 2
)
  select /*+ gather_plan_statistics */* 
  from   vw
  join   rws
  on     c1 = x;
select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PREDICATE'));

Plan hash value: 3255747573                                                 
                                                                            
-----------------------------------------------                             
| Id  | Operation                      | Name |                             
-----------------------------------------------                             
|   0 | SELECT STATEMENT               |      |                             
|   1 |  NESTED LOOPS                  |      |                             
|   2 |   VIEW                         |      |                             
|   3 |    CONNECT BY WITHOUT FILTERING|      |                             
|   4 |     FAST DUAL                  |      |                             
|   5 |   VIEW                         | VW   |                             
|   6 |    SORT UNIQUE                 |      |                             
|   7 |     UNION-ALL PARTITION        |      |                             
|*  8 |      INDEX RANGE SCAN          | I1   |                             
|*  9 |      INDEX RANGE SCAN          | I2   |                             
|* 10 |      INDEX RANGE SCAN          | I3   |                             
-----------------------------------------------                             
                                                                            
Predicate Information (identified by operation id):                         
---------------------------------------------------                         
                                                                            
   8 - access("C1"="X")                                                     
   9 - access("C1"="X")                                                     
  10 - access("C1"="X")   


I've checked this on both 11.2.0.4 and 19.3.

Of course, there's lots of other stuff going on in your queries. So it's possible the combination means the optimizer isn't pushing predicates.

Which leads to a big question:

Why does this matter?

Using a real table gives you better (good enough?) performance, so why are you trying to use dual?

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks but...

Narendra, March 05, 2020 - 4:11 pm UTC

Hello Chris,

Thank you for your response.
The reason it matters because the original sql uses TABLE with CONNECT BY...DUAL in order to generate rows on-the-fly and uses it to split strings in a column from the main row source.

One of the reasons to make my test case bigger and complicated is that I want to make it as similar to original sql as possible. As you correctly pointed out, my test case causes the view on fact tables to be accessed first, thereby preventing any attempt to use JPPD. In actual application sql, the row source containing TABLE and CONNECT BY...DUAL is always picked up as leading row source, something I could not reproduce in my test case.
I am not sure but the fact that my test case (and original sql) is using multi-level view, appears to be also playing a role here.
the main reason for this question is because the original sql is suffering from poor performance, due to inability of optimizer to use JPPD.
My issue appears to be similar to the one mentioned in http://orasql.org/2019/05/30/workarounds-for-jppd-with-view-and-tablekokbf-xmltable-or-json_table-functions/
I am hoping to find a workaround that would make it work on 11.2.0.4
Chris Saxon
March 06, 2020 - 10:13 am UTC

the original sql uses TABLE with CONNECT BY...DUAL

You could change it to use a real table though, right?

This would also allow you to switch to a regular join, instead of the table nonsense:

select * from as_dimension
join   temp_table
on     num_val <= length ( username );


Which might help the optimizer find a better plan.

I'd also look into adding a virtual column and change the query to use it:

alter table as_dimension
  add ulength int as ( length ( username ) );

select * from as_dimension
join   temp_table
on     num_val <= ulength;


This will enable the optimizer to get stats on the length, leading to better row estimates.

Whether the original issue is a problem with JPPD itself or other transformations are causing this to be overlooked needs some digging. If this is a big problem for you, speak to support.

Thanks

Narendra, March 10, 2020 - 10:40 am UTC

Hello Chris,

Thank you for your time..
While I appear to have a workaround, it requires me to and use a table of N number of rows and prevents me from using the row-generation techniques that have been around for ages.
Anyways, I have raised a SR for the same but like most of the SRs, it appears that Oracle engineers have adopted the standard delaying tactics and the SR does not appear to be getting right focus.
So I thought I would ask here in case you are already aware of any known issue with such behavior.
Chris Saxon
March 10, 2020 - 11:43 am UTC

it requires me to and use a table of N number of rows

What exactly is the problem with this? A real table has advantages, such as you can gather stats on it.

Unless this is causing other problems you've not mentioned, I'm not seeing why you're objecting to using a solution that works.

You could still use CONNECT BY DUAL in other, simpler queries if you insist.

So I thought I would ask here in case you are already aware of any known issue with such behavior.

I've chatted about this with Nigel Bayliss, optimizer PM. We think it's a side-effect of so many things going on this in query, not a bug with JPPD.

When using a real table and preventing view merging with query block hints:

create or replace view as_dim_view as select * from as_dimension asd, table(cast(multiset(select /*+ qb_name(MYQB) */ num_val from temp_table where num_val <= length(asd.username)) as sys.odcinumberlist)) asd_t ; (edited) 

explain plan for
select /*+ NO_MERGE(f) NO_MERGE(d) NO_MERGE(@MYQB) */ *
from   as_fact_sqlview   f
left outer join  as_dim_view d  on d.username = f.owner
where  trunc(f.created) = to_date('17-FEB-2020','DD-MON-YYYY') ;


You should see the same plan as CONNECT BY DUAL. This appears to prevent simple view merging. Meaning JPPD is not an option later on.

Thanks

Narendra, March 11, 2020 - 9:20 am UTC

Thank you Chris for your efforts.
Unfortunately, the test case is only similar but not exactly same as the original application sql that is sufferring from performance. The nature of original application sql means I am struggling to come up with a test case that simulates the behavior of the original application sql. The original sql contains an outer join between 2 views, with one view using join between multiple tables and one of them being outer joined to a view that is similar to AS_FACT_VIEW setup in my testcase. and this view is left outer joined to another view which contains the CONNECT BY...DUAL used to split strings.
The only consistent observation I have seen so far is when I query the view (referring to UNIONised views) on its own but with same filter criteria as original application sql, the JPPD kicks in (with explicit hinting only) and the performance is much better. As soon as I introduce the join to another view (with CONNECT BY...DUAL construct), none of the versions of PUSH_PRED hint works and the performance is poor.
I have not ruled out using a real table if it helps. With original application sql, I have not managed to get the expected performance improvement using real table under every case.
Chris Saxon
March 11, 2020 - 10:24 am UTC

With original application sql, I have not managed to get the expected performance improvement using real table under every case.

Can you share an example where a real table is still not getting the performance you want?

The simpler you can make the example, the easier it is for us to figure out what's going on!

Test Example

Narendra, March 11, 2020 - 10:38 am UTC

Thank you Chris.

Can you share an example where a real table is still not getting the performance you want?

The simpler you can make the example, the easier it is for us to figure out what's going on!


Using real table in my test case is able to perform better. Unfortunately, there are some differences between my test case and original sql, which is (most likely) making it difficult to apply tweaks in test case to original sql and get different/better results.

I understand this makes it difficult for you (or anyone) to help me. I do appreciate all the advice but am not sure how to come up with a more appropriate test case to simulate the issue, at this stage.
Chris Saxon
March 11, 2020 - 4:37 pm UTC

One thing I noticed in the examples - many row estimates were way off.

Are you finding this in your slow queries? What have you tried to get better estimates?

Don't think stats is an issue

Narendra, March 12, 2020 - 10:11 am UTC

Hello Chris,

Don't believe stats or estimates are way off.
If it helps, below is the SQL Monitor report for original application sql (object names changed to protect the innocents...)

The lack of JPPD in VW_MAIN_DETAIL all the way down means, CHLDTABFIVE table is accessed using FTS (which is the most time consuming step in the plan).
The join condition at line 26 is the one that is not getting pushed into VW_CHLDONE. No matter what version of hint PUSH_PRED I use, JPPD does not occur.

However, as soon as I take VW_ADDNDETAIL out of the sql, I am able to force JPPD within VW_MAIN_DETAIL with PUSH_PRED hint.

Global Stats
===============================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Cluster  | PL/SQL  |  Other   | Fetch | Buffer | Read  | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs  | Bytes | Reqs  | Bytes |
===============================================================================================================================
|     283 |     110 |       29 |        0.00 |     0.00 |    0.00 |      144 |     2 |   933K | 38646 |  10GB | 12740 |   3GB |
===============================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3937408368)
======================================================================================================================================================================================================================================
| Id |                   Operation                    |            Name             |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read  | Read  | Write | Write |  Mem  | Temp  | Activity |      Activity Detail       |
|    |                                                |                             | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Reqs  | Bytes | (Max) | (Max) |   (%)    |        (# samples)         |
======================================================================================================================================================================================================================================
|  0 | SELECT STATEMENT                               |                             |         |      |       184 |   +100 |     1 |        1 |       |       |       |       |       |       |     0.35 | Cpu (1)                    |
|  1 |   SORT ORDER BY                                |                             |       1 |   24 |         1 |   +283 |     1 |        1 |       |       |       |       |  2048 |       |          |                            |
|  2 |    HASH JOIN RIGHT OUTER                       |                             |       1 |   23 |         1 |   +283 |     1 |        1 |       |       |       |       |  225K |       |          |                            |
|  3 |     VIEW                                       | VW_ADDNDETAIL               |       1 |   20 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
|  4 |      NESTED LOOPS OUTER                        |                             |       1 |   20 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
|  5 |       NESTED LOOPS OUTER                       |                             |       1 |   19 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
|  6 |        NESTED LOOPS OUTER                      |                             |       1 |   18 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
|  7 |         NESTED LOOPS OUTER                     |                             |       1 |   17 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
|  8 |          VIEW                                  |                             |       1 |   16 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
|  9 |           WINDOW NOSORT                        |                             |       1 |   16 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 10 |            NESTED LOOPS                        |                             |       1 |   16 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 11 |             TABLE ACCESS BY INDEX ROWID        | ADDNDETAIL_TAB              |       1 |      |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 12 |              INDEX FULL SCAN                   | IDX_PK_ADDNDETAIL           |       1 |      |           |        |       |          |       |       |       |       |       |       |          |                            |
| 13 |             COLLECTION ITERATOR SUBQUERY FETCH |                             |     408 |   16 |           |        |       |          |       |       |       |       |       |       |          |                            |
| 14 |              CONNECT BY WITHOUT FILTERING      |                             |         |      |           |        |       |          |       |       |       |       |       |       |          |                            |
| 15 |               FAST DUAL                        |                             |       1 |    2 |           |        |       |          |       |       |       |       |       |       |          |                            |
| 16 |          TABLE ACCESS BY INDEX ROWID           | REFDATA                     |       1 |    1 |           |        |       |          |       |       |       |       |       |       |          |                            |
| 17 |           INDEX UNIQUE SCAN                    | PK_REFDATA                  |       1 |      |           |        |       |          |       |       |       |       |       |       |          |                            |
| 18 |         TABLE ACCESS BY INDEX ROWID            | REFDATA                     |       1 |    1 |           |        |       |          |       |       |       |       |       |       |          |                            |
| 19 |          INDEX UNIQUE SCAN                     | PK_REFDATA                  |       1 |      |           |        |       |          |       |       |       |       |       |       |          |                            |
| 20 |        TABLE ACCESS BY INDEX ROWID             | REFDATA                     |       1 |    1 |           |        |       |          |       |       |       |       |       |       |          |                            |
| 21 |         INDEX UNIQUE SCAN                      | PK_REFDATA                  |       1 |      |           |        |       |          |       |       |       |       |       |       |          |                            |
| 22 |       TABLE ACCESS BY INDEX ROWID              | REFDATA                     |       1 |    1 |           |        |       |          |       |       |       |       |       |       |          |                            |
| 23 |        INDEX UNIQUE SCAN                       | PK_REFDATA                  |       1 |      |           |        |       |          |       |       |       |       |       |       |          |                            |
| 24 |     VIEW                                       | VW_MAIN_DETAIL              |       1 |    3 |         1 |   +283 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 25 |      UNION-ALL                                 |                             |         |      |         1 |   +283 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 26 |       HASH JOIN OUTER                          |                             |       4 |   2M |       276 |     +8 |     1 |        1 |       |       |       |       |  426K |       |     2.12 | Cpu (6)                    |
| 27 |        MERGE JOIN CARTESIAN                    |                             |       4 |   46 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 28 |         NESTED LOOPS SEMI                      |                             |       1 |   41 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 29 |          NESTED LOOPS OUTER                    |                             |       1 |    5 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 30 |           NESTED LOOPS                         |                             |       1 |    4 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 31 |            NESTED LOOPS                        |                             |       1 |    2 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 32 |             INDEX UNIQUE SCAN                  | PK_STATES                   |       1 |      |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 33 |             TABLE ACCESS BY INDEX ROWID        | MAINTABONE                  |       1 |    2 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 34 |              INDEX UNIQUE SCAN                 | IDX_PK_MAINTABONE           |       1 |    1 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 35 |            TABLE ACCESS BY INDEX ROWID         | CHLDTABONE                  |       1 |    2 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 36 |             INDEX UNIQUE SCAN                  | PK_CHLDTABONE               |       1 |    1 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 37 |           TABLE ACCESS BY INDEX ROWID          | MOREREFDATA                 |       1 |    1 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 38 |            INDEX UNIQUE SCAN                   | PK_MOREREFDATA              |       1 |      |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 39 |          INDEX FAST FULL SCAN                  | PK_REFDATA                  |   17706 |   36 |       276 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 40 |         BUFFER SORT                            |                             |       4 |   10 |         1 |     +8 |     1 |        1 |       |       |       |       |  2048 |       |          |                            |
| 41 |          TABLE ACCESS BY INDEX ROWID           | CHLDTABTWO                  |       4 |    5 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 42 |           INDEX RANGE SCAN                     | IDX_CHLDTABTWO_BATCHTNUM    |       5 |    3 |         1 |     +8 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 43 |           TABLE ACCESS BY INDEX ROWID          | CONFIGPARAMS                |       1 |    1 |           |        |       |          |       |       |       |       |       |       |          |                            |
| 44 |            INDEX UNIQUE SCAN                   | PK_CONFIGPARAMS             |       1 |      |           |        |       |          |       |       |       |       |       |       |          |                            |
| 45 |        VIEW                                    | VW_CHLDONE                  |     77M |   2M |        41 |   +243 |     1 |      85M |       |       |       |       |       |       |     1.06 | Cpu (3)                    |
| 46 |         SORT UNIQUE                            |                             |     77M |   2M |       146 |   +139 |     1 |      85M | 24760 |   1GB |  6417 |   1GB |  102M |    1G |    23.67 | Cpu (61)                   |
|    |                                                |                             |         |      |           |        |       |          |       |       |       |       |       |       |          | direct path read temp (3)  |
|    |                                                |                             |         |      |           |        |       |          |       |       |       |       |       |       |          | direct path write temp (3) |
| 47 |          UNION-ALL                             |                             |         |      |       105 |   +139 |     1 |      85M |       |       |       |       |       |       |     1.77 | Cpu (5)                    |
| 48 |           VIEW                                 | VW_CHLDTWO                  |     77M | 899K |       105 |   +139 |     1 |      85M |       |       |       |       |       |       |     1.06 | Cpu (3)                    |
| 49 |            SORT UNIQUE                         |                             |     77M | 899K |       240 |     +4 |     1 |      85M |  6542 |   1GB |  6083 |   1GB |  102M |    1G |    63.25 | Cpu (167)                  |
|    |                                                |                             |         |      |           |        |       |          |       |       |       |       |       |       |          | direct path read temp (5)  |
|    |                                                |                             |         |      |           |        |       |          |       |       |       |       |       |       |          | direct path write temp (7) |
| 50 |             UNION-ALL                          |                             |         |      |       132 |     +8 |     1 |      85M |       |       |       |       |       |       |     0.35 | Cpu (1)                    |
| 51 |              TABLE ACCESS FULL                 | CHLDTABTHREE                |       1 |    2 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 52 |              TABLE ACCESS FULL                 | CHLDTABFOUR                 |       1 |    2 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 53 |              TABLE ACCESS FULL                 | CHLDTABFIVE                 |     77M | 127K |       138 |     +2 |     1 |      85M |  7069 |   7GB |       |       |       |       |     6.01 | Cpu (8)                    |
|    |                                                |                             |         |      |           |        |       |          |       |       |       |       |       |       |          | direct path read (9)       |
| 54 |              TABLE ACCESS FULL                 | CHLDTABSIX                  |       1 |    2 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 55 |              TABLE ACCESS FULL                 | CHLDTABSEVEN                |       1 |    2 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 56 |           TABLE ACCESS FULL                    | CHLDTABEIGHT                |       1 |    2 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 57 |           TABLE ACCESS FULL                    | CHLDTABNINE                 |       1 |    2 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 58 |       NESTED LOOPS SEMI                        |                             |       1 |   41 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 59 |        NESTED LOOPS                            |                             |       1 |    5 |           |        |     1 |          |       |       |       |       |       |       |          |                            |
| 60 |         NESTED LOOPS                           |                             |       1 |    4 |         1 |   +283 |     1 |        0 |       |       |       |       |       |       |          |                            |
| 61 |          NESTED LOOPS                          |                             |       1 |    2 |         1 |   +283 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 62 |           INDEX UNIQUE SCAN                    | PK_STATES                   |       1 |      |         1 |   +283 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 63 |           TABLE ACCESS BY INDEX ROWID          | MAINTABONE                  |       1 |    2 |         1 |   +283 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 64 |            INDEX UNIQUE SCAN                   | IDX_PK_MAINTABONE           |       1 |    1 |         1 |   +283 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 65 |          TABLE ACCESS BY INDEX ROWID           | CHLDTABONE                  |       1 |    2 |         1 |   +283 |     1 |        0 |       |       |       |       |       |       |          |                            |
| 66 |           INDEX UNIQUE SCAN                    | PK_CHLDTABONE               |       1 |    1 |         1 |   +283 |     1 |        1 |       |       |       |       |       |       |          |                            |
| 67 |         TABLE ACCESS BY INDEX ROWID            | MOREREFDATA                 |       1 |    1 |           |        |       |          |       |       |       |       |       |       |          |                            |
| 68 |          INDEX UNIQUE SCAN                     | PK_MOREREFDATA              |       1 |      |           |        |       |          |       |       |       |       |       |       |          |                            |
| 69 |        INDEX FAST FULL SCAN                    | PK_REFDATA                  |   17706 |   36 |           |        |       |          |       |       |       |       |       |       |          |                            |
======================================================================================================================================================================================================================================

Chris Saxon
March 12, 2020 - 10:30 am UTC

Do you really need a distinct UNION in the views, as opposed to a UNION ALL?

The sorting steps at lines 46 & 49 getting these distinct rows consumes over 85% of the runtime.

If possible switching to UNION ALL should save a lot of time.

Also the query seems to be accessing the same set of tables twice (MAINTABONEM, CHLDTABONE, MOREREFDATA, ...).

Can you eliminate the second reference to these?

Good points

Narendra, March 12, 2020 - 11:14 am UTC

Hello Chris,

Guess what....when I first started looking into this issue and this plan, these were my first observations. We have already opened a line of investigation with developers over the same. Waiting for their input, especially as I am still not completely familiar with data model and how it addresses the business requirement (although I am sure at least one of the SORT UNIQUE steps can be safely eliminated in this sql).
However, after some analysis, I thought the main issue is the large amount of data being flown out of views only for most of it to be eventually discarded (when being joined to other row source using HASH JOIN at line 26).
My theory is if we are able to filter data at the earliest opportunity then any of the subsequent operations (like SORT UNIQUE) will not be so expensive. Of course, not trying to justify any overheads that are not needed.

Chris Saxon
March 12, 2020 - 1:33 pm UTC

It really is those sorts that are killing you. The hash join on the output of these is ~2% of the time; relatively trivial.

It's hard to say for sure given such big numbers, but there are 85 million rows returned by each those union steps. So it's possible these sort operations spend a lot of time to "do nothing".

No matter what version of hint PUSH_PRED I use, JPPD does not occur.

We suspect that JPPD is just not an option due to other transformations. Which is why the hints are having no effect.

What plan do you get when you change dual for a real table in that query?

Filtering early is a great idea. I'd focus on the easy things you can change for now though; use a real table instead of dual and switch to (one) union all.

Real table and PUSH_PRED

Narendra, March 12, 2020 - 3:42 pm UTC

Hello Chris,

In my original sql, using real table inside VW_ADDNDETAIL instead of CONNECT BY...DUAL makes no difference to the plan and the performance.

However, when I use the PUSH_PRED hint in main sql to force JPPD, the plan changes to use JPPD and performance is much better.
Below is the plan with both above changes:
Plan hash value: 1564148209
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                             |     3 | 96045 |   596   (4)| 00:00:03 |
|   1 |  SORT ORDER BY                         |                             |     3 | 96045 |   596   (4)| 00:00:03 |
|*  2 |   HASH JOIN RIGHT OUTER                |                             |     3 | 96045 |   595   (4)| 00:00:03 |
|   3 |    VIEW                                | VW_ADDNDETAIL_NP            |     1 | 12209 |    25   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER                 |                             |     1 |  2389 |    25   (0)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER                |                             |     1 |  2302 |    24   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER               |                             |     1 |  2215 |    23   (0)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER              |                             |     1 |  2128 |    22   (0)| 00:00:01 |
|*  8 |         VIEW                           |                             |     1 |  2041 |    21   (0)| 00:00:01 |
|   9 |          WINDOW NOSORT                 |                             |     1 |  2032 |    21   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                 |                             |     1 |  2032 |    21   (0)| 00:00:01 |
|* 11 |            TABLE ACCESS BY INDEX ROWID | ADDNDETAIL_TAB              |     1 |  2028 |     0   (0)| 00:00:01 |
|  12 |             INDEX FULL SCAN            | IDX_PK_ADDNDETAIL_TAB       |     1 |       |     0   (0)| 00:00:01 |
|* 13 |            TABLE ACCESS FULL           | TEMP_TABLE                  |   500 |  2000 |    21   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS BY INDEX ROWID    | REFDATA                     |     1 |    87 |     1   (0)| 00:00:01 |
|* 15 |          INDEX UNIQUE SCAN             | PK_REFDATA                  |     1 |       |     0   (0)| 00:00:01 |
|* 16 |        TABLE ACCESS BY INDEX ROWID     | REFDATA                     |     1 |    87 |     1   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN              | PK_REFDATA                  |     1 |       |     0   (0)| 00:00:01 |
|* 18 |       TABLE ACCESS BY INDEX ROWID      | REFDATA                     |     1 |    87 |     1   (0)| 00:00:01 |
|* 19 |        INDEX UNIQUE SCAN               | PK_REFDATA                  |     1 |       |     0   (0)| 00:00:01 |
|* 20 |      TABLE ACCESS BY INDEX ROWID       | REFDATA                     |     1 |    87 |     1   (0)| 00:00:01 |
|* 21 |       INDEX UNIQUE SCAN                | PK_REFDATA                  |     1 |       |     0   (0)| 00:00:01 |
|  22 |    VIEW                                | VW_MAIN_DETAIL              |     3 | 59418 |   570   (4)| 00:00:03 |
|  23 |     UNION-ALL                          |                             |       |       |            |          |
|  24 |      NESTED LOOPS OUTER                |                             |     2 |  2718 |   299   (7)| 00:00:02 |
|  25 |       MERGE JOIN CARTESIAN             |                             |     2 |  1658 |   275   (1)| 00:00:02 |
|  26 |        NESTED LOOPS SEMI               |                             |     1 |   459 |   272   (1)| 00:00:02 |
|  27 |         NESTED LOOPS OUTER             |                             |     1 |   453 |     5   (0)| 00:00:01 |
|  28 |          NESTED LOOPS                  |                             |     1 |   435 |     4   (0)| 00:00:01 |
|  29 |           NESTED LOOPS                 |                             |     1 |   329 |     2   (0)| 00:00:01 |
|* 30 |            INDEX UNIQUE SCAN           | PK_STATES                   |     1 |     6 |     0   (0)| 00:00:01 |
|* 31 |            TABLE ACCESS BY INDEX ROWID | MAINTABONE                  |     1 |   323 |     2   (0)| 00:00:01 |
|* 32 |             INDEX UNIQUE SCAN          | IDX_PK_MAINTABONE           |     1 |       |     1   (0)| 00:00:01 |
|  33 |           TABLE ACCESS BY INDEX ROWID  | CHLDTABONE                  |     1 |   106 |     2   (0)| 00:00:01 |
|* 34 |            INDEX UNIQUE SCAN           | PK_CHLDTABONE               |     1 |       |     1   (0)| 00:00:01 |
|  35 |          TABLE ACCESS BY INDEX ROWID   | MOREREFDATA                 |     1 |    18 |     1   (0)| 00:00:01 |
|* 36 |           INDEX UNIQUE SCAN            | PK_MOREREFDATA              |     1 |       |     0   (0)| 00:00:01 |
|* 37 |         INDEX FAST FULL SCAN           | PK_REFDATA                  | 17905 |   104K|   267   (1)| 00:00:02 |
|  38 |        BUFFER SORT                     |                             |     2 |   740 |     8   (0)| 00:00:01 |
|* 39 |         TABLE ACCESS BY INDEX ROWID    | CHLDTABTWO                  |     2 |   740 |     3   (0)| 00:00:01 |
|* 40 |          INDEX RANGE SCAN              | IDX_CHLDTABTWO_BATCHTNUM    |     2 |       |     2   (0)| 00:00:01 |
|  41 |          TABLE ACCESS BY INDEX ROWID   | CONFIGPARAMS                |     1 |    37 |     1   (0)| 00:00:01 |
|* 42 |           INDEX UNIQUE SCAN            | PK_CONFIGPARAMS             |     1 |       |     0   (0)| 00:00:01 |
|  43 |       VIEW                             | VW_CHLDONE                  |     1 |   530 |    12  (67)| 00:00:01 |
|  44 |        SORT UNIQUE                     |                             |     7 |  1830 |    12  (67)| 00:00:01 |
|  45 |         UNION ALL PUSHED PREDICATE     |                             |       |       |            |          |
|  46 |          VIEW                          | VW_CHLDTWO                  |     5 |   275 |     8  (63)| 00:00:01 |
|  47 |           SORT UNIQUE                  |                             |     5 |   536 |     8  (63)| 00:00:01 |
|  48 |            UNION-ALL                   |                             |       |       |            |          |
|  49 |             TABLE ACCESS BY INDEX ROWID| CHLDTABTHREE                |     1 |    55 |     0   (0)| 00:00:01 |
|* 50 |              INDEX UNIQUE SCAN         | IDX_PK_CHLDTABTHREE         |     1 |       |     0   (0)| 00:00:01 |
|  51 |             TABLE ACCESS BY INDEX ROWID| CHLDTABFOUR                 |     1 |    55 |     0   (0)| 00:00:01 |
|* 52 |              INDEX UNIQUE SCAN         | IDX_PK_CHLDTABFOUR          |     1 |       |     0   (0)| 00:00:01 |
|  53 |             TABLE ACCESS BY INDEX ROWID| CHLDTABFIVE                 |     1 |    48 |     3   (0)| 00:00:01 |
|* 54 |              INDEX UNIQUE SCAN         | IDX_PK_CHLDTABFIVE          |     1 |       |     2   (0)| 00:00:01 |
|  55 |             TABLE ACCESS BY INDEX ROWID| CHLDTABSIX                  |     1 |    55 |     0   (0)| 00:00:01 |
|* 56 |              INDEX UNIQUE SCAN         | IDX_PK_CHLDTABSIX           |     1 |       |     0   (0)| 00:00:01 |
|  57 |             TABLE ACCESS BY INDEX ROWID| CHLDTABSEVEN                |     1 |    55 |     0   (0)| 00:00:01 |
|* 58 |              INDEX UNIQUE SCAN         | IDX_PK_CHLDTABSEVEN         |     1 |       |     0   (0)| 00:00:01 |
|  59 |          TABLE ACCESS BY INDEX ROWID   | CHLDTABEIGHT                |     1 |    68 |     1   (0)| 00:00:01 |
|* 60 |           INDEX SKIP SCAN              | IDX_PK_CHLDTABEIGHT         |     1 |       |     1   (0)| 00:00:01 |
|  61 |          TABLE ACCESS BY INDEX ROWID   | CHLDTABNINE                 |     1 |   572 |     0   (0)| 00:00:01 |
|* 62 |           INDEX UNIQUE SCAN            | IDX_PK_CHLDTABNINE          |     1 |       |     0   (0)| 00:00:01 |
|  63 |      NESTED LOOPS SEMI                 |                             |     1 |   463 |   272   (1)| 00:00:02 |
|  64 |       NESTED LOOPS                     |                             |     1 |   457 |     5   (0)| 00:00:01 |
|  65 |        NESTED LOOPS                    |                             |     1 |   439 |     4   (0)| 00:00:01 |
|  66 |         NESTED LOOPS                   |                             |     1 |   329 |     2   (0)| 00:00:01 |
|* 67 |          INDEX UNIQUE SCAN             | PK_STATES                   |     1 |     6 |     0   (0)| 00:00:01 |
|* 68 |          TABLE ACCESS BY INDEX ROWID   | MAINTABONE                  |     1 |   323 |     2   (0)| 00:00:01 |
|* 69 |           INDEX UNIQUE SCAN            | IDX_PK_MAINTABONE           |     1 |       |     1   (0)| 00:00:01 |
|* 70 |         TABLE ACCESS BY INDEX ROWID    | CHLDTABONE                  |     1 |   110 |     2   (0)| 00:00:01 |
|* 71 |          INDEX UNIQUE SCAN             | PK_CHLDTABONE               |     1 |       |     1   (0)| 00:00:01 |
|  72 |        TABLE ACCESS BY INDEX ROWID     | MOREREFDATA                 |     1 |    18 |     1   (0)| 00:00:01 |
|* 73 |         INDEX UNIQUE SCAN              | PK_MOREREFDATA              |     1 |       |     0   (0)| 00:00:01 |
|* 74 |       INDEX FAST FULL SCAN             | PK_REFDATA                  | 17905 |   104K|   267   (1)| 00:00:02 |
----------------------------------------------------------------------------------------------------------------------


Chris Saxon
March 12, 2020 - 3:49 pm UTC

So... problem solved?

And another one...

Narendra, March 12, 2020 - 3:51 pm UTC

Hello Chris,

Not sure if this is relevant but the strange part is, after using real table in place of CONNECT BY...DUAL in one view, the plan (and performance) is no different and the plan cost is reported as 1622K (in my test environment).
However, when I use PUSH_PRED hint on second view, in addition to the real table usage, not only is the performance is better but the plan cost is 596, which is far less than the cost of unhinted version of the sql (so does it mean optimizer is using more expensive plan ?? )

Hmm...

Narendra, March 12, 2020 - 3:57 pm UTC

So... problem solved?
Well...one can say that...But, I have no idea how to explain/reason that behavior...and I prefer to know the logical reason behind the solution rather than "accidentally" stumbling on it.
Chris Saxon
March 13, 2020 - 11:32 am UTC

I believe the reason what I mentioned earlier: CONNECT BY prevents simple view merging.

But, I have no idea how to explain/reason that behavior

I get where you're coming from. But there are edge cases all over the place in the optimizer. You can waste lots of time getting to the bottom of why one method works and other doesn't. Particularly with complex queries.

Sometimes you've just got to accept "this works" and move on...

Table operator blocks JPPD

Andrew sayer, March 14, 2020 - 10:51 am UTC

Since this looks to be using a manipulation of my demo that shows the issue with JPPD to subqueries that union all to dual (With clever workaround) https://livesql.oracle.com/apex/livesql/s/jic53s3esmzk2jh9ns705xqgb I feel obliged to comment.

The existence of the table operator means that JPPD is not possible, it doesn’t matter where it exists, the CBO will just decide that JPPD is no longer allowed. There would be a note in your optimizer trace saying exactly that. Here’s a quick demo: https://livesql.oracle.com/apex/livesql/s/jsc17xg68s30b0eld6v2gelh5

The same restriction exists when a few other features are used, database links comes to mind. I have no rational explanation why the optimizer code has decided that JPPD shouldn’t be done when these are mentioned but that is how it is, usually these shortcomings are workarounds because of bugs with how they were implemented once. Maybe Nigel/Chris can confirm.

The comment about all the time going towards sorting the data was a bit questionable, it was sorting 85 million rows, it wouldn’t have had to sort that many if the predicate had been pushed.

There was another later plan posted without any collection iterator. JPPD in that case could have been not allowed via the cost, or this could have been more like the original demo where you need the same column indexed at all points of the union all. It’s hard to say for sure without DDL and query to represent the data.
Connor McDonald
March 16, 2020 - 10:28 am UTC

thanks for stopping by

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.