Suboptimal query plan with connect by

[Radoulov, Dimitre]

Hello all,

env:  single instance EE 12.2.0.1 PSU 201807 on RHEL 7.5

we have the a query similar to this one:

SELECT A.col1,
       A.col2,
       ... other columns ...
       LEVEL
FROM   t A
WHERE  A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
OR     A.other_date_col IS NULL
OR     A.other_date_col >= TO_DATE('20180901', 'YYYYMMDD')
CONNECT BY (
  (A.new_id = PRIOR A.id AND A.other_id = PRIOR A.other_id)
  OR (A.id = PRIOR A.id AND A.new_other_id = PRIOR A.other_id))
START WITH (
  A.other_date_col IS NULL
  OR A.other_date_col >= ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
  )

1. Parallel execution can only be achieved if we remove the "OR"
operator in the "CONNECT BY" clause (with parallel hint or object level
degree > 1).
2. The query performs a full table scan of A even when we add a
selective where clause on indexed column:

...
WHERE  A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
AND id in (val1, val2) -- there is an existing index with id as a first
column

It uses the correct index if we transform the query like this:

SELECT B.col1,
       B.col2,
       ... other columns ...
       LEVEL
FROM (
SELECT A.col1,
       A.col2,
       ... other columns ...
FROM   t A
WHERE  A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
AND id in (val1, val2)
OR     A.other_date_col IS NULL
OR     A.other_date_col >= TO_DATE('20180901', 'YYYYMMDD')
  ) B
CONNECT BY (
  (B.new_id = PRIOR B.id AND B.other_id = PRIOR B.other_id)
  OR (B.id = PRIOR B.id AND B.new_other_id = PRIOR B.other_id))
START WITH (
  B.other_date_col IS NULL
  OR B.other_date_col >= ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
  )

Oracle support engineer suggested to test with the following parameters:

ALTER SESSION SET "_unnest_subquery" = FALSE;
ALTER SESSION SET "_connect_by_use_union_all" = 'OLD_PLAN_MODE';

Nothing changed.
Oracle support says also that this is not a bug and that we need to
rewrite the query as in the second example.

Any insight would be appreciated!


Regards
Dimitre





--
http://www.freelists.org/webpage/oracle-l

[Sayan Malakshinov]

Hi Dimitre,

Since your predicates in where clause are not join predicates, they are
executed AFTER "connect by" and they work just filters for the tree, so
your first and second queries are not equal to each other.

Post by Radoulov, Dimitre
Hello all,
env: single instance EE 12.2.0.1 PSU 201807 on RHEL 7.5
SELECT A.col1,
A.col2,
... other columns ...
LEVEL
FROM t A
WHERE A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
OR A.other_date_col IS NULL
OR A.other_date_col >= TO_DATE('20180901', 'YYYYMMDD')
CONNECT BY (
(A.new_id = PRIOR A.id AND A.other_id = PRIOR A.other_id)
OR (A.id = PRIOR A.id AND A.new_other_id = PRIOR A.other_id))
START WITH (
A.other_date_col IS NULL
OR A.other_date_col >= ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
)
1. Parallel execution can only be achieved if we remove the "OR"
operator in the "CONNECT BY" clause (with parallel hint or object level
degree > 1).
2. The query performs a full table scan of A even when we add a
...
WHERE A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
AND id in (val1, val2) -- there is an existing index with id as a first
column
SELECT B.col1,
B.col2,
... other columns ...
LEVEL
FROM (
SELECT A.col1,
A.col2,
... other columns ...
FROM t A
WHERE A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
AND id in (val1, val2)
OR A.other_date_col IS NULL
OR A.other_date_col >= TO_DATE('20180901', 'YYYYMMDD')
) B
CONNECT BY (
(B.new_id = PRIOR B.id AND B.other_id = PRIOR B.other_id)
OR (B.id = PRIOR B.id AND B.new_other_id = PRIOR B.other_id))
START WITH (
B.other_date_col IS NULL
OR B.other_date_col >= ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
)
ALTER SESSION SET "_unnest_subquery" = FALSE;
ALTER SESSION SET "_connect_by_use_union_all" = 'OLD_PLAN_MODE';
Nothing changed.
Oracle support says also that this is not a bug and that we need to
rewrite the query as in the second example.
Any insight would be appreciated!
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l

[Radoulov, Dimitre]

Post by Sayan Malakshinov
Hi Dimitre,
Since your predicates in where clause are not join predicates, they
are executed AFTER "connect by" and they work just filters for the
tree, so your first and second queries are not equal to each other.

Thank you very much Sayan!
So I imagine that in this context an index and a selective filter would
not help.

I know that the parallel execution could be quite complex but have you
ever had problems with hierarchical queries and parallelism?


Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l

[Lothar Flatz]

Hi,

you could use this technique:

https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/
It will also show you an other hidden parameter. Make sure the indexes
are in place!

Regards

Lothar

Post by Radoulov, Dimitre

Post by Sayan Malakshinov
Hi Dimitre,
Since your predicates in where clause are not join predicates, they
are executed AFTER "connect by" and they work just filters for the
tree, so your first and second queries are not equal to each other.

Thank you very much Sayan!
So I imagine that in this context an index and a selective filter
would not help.
I know that the parallel execution could be quite complex but have you
ever had problems with hierarchical queries and parallelism?
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l

[Radoulov, Dimitre]

Post by Lothar Flatz
Hi,
https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/
It will also show you an other hidden parameter. Make sure the indexes
are in place!
Regards
Lothar

Thank you Lothar!
I'll try your solution with the pipelined function.


Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l

[Sayan Malakshinov]

Lothar,

But as I see your example just calculates a sum of children rows, it
doesn't return them. I wander hot it can help return a tree?

Dimitre,

As far as I know "connect by" cannot be parallellized, but of course
optimizer can use parallel execution for children row sources.
And sometimes we can optimize it using Recursive subquery factoring clause,
ie recursive WITH (it was optimized in 12.2)
For example:
create table th as
select nullif(level-1,0) parent_id, level id, mod(level,2) x
from dual connect by level<=1e5;

create index th_idx2 on th(parent_id);

select/*+ parallel */ *
from th
connect by parent_id = prior id
start with parent_id=0
/
with v(id, parent_id, x) as (
select id,parent_id,x from th where parent_id=0
union all
select th.id,th.parent_id,th.x from v,th where th.parent_id=v.id
)
select/*+ parallel */ *
from v
/

Post by Lothar Flatz

Post by Lothar Flatz
Hi,

https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/

Post by Lothar Flatz
It will also show you an other hidden parameter. Make sure the indexes
are in place!
Regards
Lothar

Thank you Lothar!
I'll try your solution with the pipelined function.
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l

[Radoulov, Dimitre]

Sayan,

yes, if I remove the "OR" in the "connect by" clause the query runs in
parallel (modified output):

...
CONNECT BY (
  A.new_id = PRIOR A.id
  AND A.other_id = PRIOR A.other_id
  )
START WITH (
  A.other_date IS NULL
  OR A.other_date >= ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
  )


----------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name              |
E-Rows |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |        |       |      
|          |
|*  1 |  FILTER |                   |        |       |       |          |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|                  
|        |    92M|  3298K|   97M (1)|
|   3 |    PX COORDINATOR |                   |        | 73728 | 73728
|          |
|   4 |     PX SEND QC (RANDOM)                  | :TQ10000          |  
1328K|       |       |          |
|   5 |      PX BLOCK ITERATOR |                   |   1328K|      
|       |          |
|*  6 |       TABLE ACCESS FULL                  | tname             |  
1328K|       |       |          |
----------------------------------------------------------------------------------------------------------

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

   1 - filter(("A"."date1"<TO_DATE(' 2018-10-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')
              AND ("A"."date2" IS NULL OR "A"."date2">=TO_DATE('
2018-09-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))))
   2 - access("A"."new_id"=PRIOR NULL AND "A"."other_id"=PRIOR NULL)
       filter(("A"."date2" IS NULL OR "A"."date2">=TO_DATE(' 2018-10-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   6 - access(:Z>=:Z AND :Z<=:Z)


Regards
Dimitre

Post by Sayan Malakshinov
Lothar,
But as I see your example just calculates a sum of children rows, it
doesn't return them. I wander hot it can help return a tree?
Dimitre,
As far as I know "connect by" cannot be parallellized, but of course
optimizer can use parallel execution for children row sources.
And sometimes we can optimize it using Recursive subquery factoring
clause, ie recursive WITH (it was optimized in 12.2)
create table th as
select nullif(level-1,0) parent_id, level id, mod(level,2) x
from dual connect by level<=1e5;
create index th_idx2 on th(parent_id);
select/*+ parallel */ *
from th
connect by parent_id = prior id
start with parent_id=0
/
with v(id, parent_id, x) as (
select id,parent_id,x from th where parent_id=0
union all
select th.id <http://th.id>,th.parent_id,th.x from v,th where
th.parent_id=v.id <http://v.id>
)
select/*+ parallel */ *
from v
/
On Mon, Oct 29, 2018 at 4:24 PM Radoulov, Dimitre

Post by Lothar Flatz
Hi,

https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/

Post by Lothar Flatz
It will also show you an other hidden parameter. Make sure the

indexes

Post by Lothar Flatz
are in place!
Regards
Lothar

Thank you Lothar!
I'll try your solution with the pipelined function.
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

[Lothar Flatz]

Hi Sayan,

I think Dimitre should try the easiest thing first. alter session set
“_old_connect_by_enabled”=true;
It might be possible that the old algorithm can be parallelized without
further work.
The old algorithm is depth first, I guess that could help.
The pipe table function is a bit forced nut I guarantee parallel
processing. It must be adapted to the respective case. The outer query
(ref cursor) retrieves the start points (root rows).
If there is only one root, we need to retrieve the first level under the
root.
Of course it is possible to return every row, you just have to adapt the
inner query.

Regards

Lothar

Post by Sayan Malakshinov
Lothar,
But as I see your example just calculates a sum of children rows, it
doesn't return them. I wander hot it can help return a tree?
Dimitre,
As far as I know "connect by" cannot be parallellized, but of course
optimizer can use parallel execution for children row sources.
And sometimes we can optimize it using Recursive subquery factoring
clause, ie recursive WITH (it was optimized in 12.2)
create table th as
select nullif(level-1,0) parent_id, level id, mod(level,2) x
from dual connect by level<=1e5;
create index th_idx2 on th(parent_id);
select/*+ parallel */ *
from th
connect by parent_id = prior id
start with parent_id=0
/
with v(id, parent_id, x) as (
select id,parent_id,x from th where parent_id=0
union all
select th.id <http://th.id>,th.parent_id,th.x from v,th where
th.parent_id=v.id <http://v.id>
)
select/*+ parallel */ *
from v
/
On Mon, Oct 29, 2018 at 4:24 PM Radoulov, Dimitre

Post by Lothar Flatz
Hi,

https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/

Post by Lothar Flatz
It will also show you an other hidden parameter. Make sure the

indexes

Post by Lothar Flatz
are in place!
Regards
Lothar

Thank you Lothar!
I'll try your solution with the pipelined function.
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--

[Radoulov, Dimitre]

Hi,

with "_old_connect_by_enabled"=true the query still runs in serial mode.


Thank you!

Regards
Dimitre

Post by Lothar Flatz
Hi Sayan,
I think Dimitre should try the easiest thing first. alter session set
“_old_connect_by_enabled”=true;
It might be possible that the old algorithm can be parallelized
without further work.
The old algorithm is depth first, I guess that could help.
The pipe table function is a bit forced nut I guarantee parallel
processing. It must be adapted to the respective case. The outer query
(ref cursor) retrieves the start points (root rows).
If there is only one root, we need to retrieve the first level under
the root.
Of course it is possible to return every row, you just have to adapt
the inner query.
Regards
Lothar

Post by Sayan Malakshinov
Lothar,
But as I see your example just calculates a sum of children rows, it
doesn't return them. I wander hot it can help return a tree?
Dimitre,
As far as I know "connect by" cannot be parallellized, but of course
optimizer can use parallel execution for children row sources.
And sometimes we can optimize it using Recursive subquery factoring
clause, ie recursive WITH (it was optimized in 12.2)
create table th as
select nullif(level-1,0) parent_id, level id, mod(level,2) x
from dual connect by level<=1e5;
create index th_idx2 on th(parent_id);
select/*+ parallel */ *
from th
connect by parent_id = prior id
start with parent_id=0
/
with v(id, parent_id, x) as (
select id,parent_id,x from th where parent_id=0
union all
select th.id <http://th.id>,th.parent_id,th.x from v,th where
th.parent_id=v.id <http://v.id>
)
select/*+ parallel */ *
from v
/
On Mon, Oct 29, 2018 at 4:24 PM Radoulov, Dimitre

Post by Lothar Flatz
Hi,

https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/

Post by Lothar Flatz
It will also show you an other hidden parameter. Make sure the

indexes

Post by Lothar Flatz
are in place!
Regards
Lothar

Thank you Lothar!
I'll try your solution with the pipelined function.
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--

[l***@bluewin.ch]

Hi,
thanks. The pipeline Table function will work in parallel. The efficiency depends a bit on how your hierarchies are constructed.
Regards
Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@gmail.com
Datum : 29/10/2018 - 16:01 (CET)
An : ***@bluewin.ch, ***@gmail.com
Cc : oracle-***@freelists.org
Betreff : Re: Suboptimal query plan with connect by





Hi,

with "_old_connect_by_enabled"=true the query still runs in
serial mode.




Thank you!

Regards
Dimitre



On 29/10/2018 15:30, Lothar Flatz
wrote:




Hi Sayan,

I think Dimitre should try the easiest thing first. alter
session set “_old_connect_by_enabled”=true;
It might be possible that the old algorithm can be parallelized
without further work.
The old algorithm is depth first, I guess that could help.
The pipe table function is a bit forced nut I guarantee parallel
processing. It must be adapted to the respective case. The outer
query (ref cursor) retrieves the start points (root rows).
If there is only one root, we need to retrieve the first level
under the root.
Of course it is possible to return every row, you just have to
adapt the inner query.

Regards

Lothar

Am 29.10.2018 um 14:37 schrieb Sayan Malakshinov:





Lothar,



But as I see your example just calculates a sum of
children rows, it doesn't return them. I wander hot it can
help return a tree?



Dimitre,



As far as I know "connect by" cannot be parallellized,
but of course optimizer can use parallel execution for
children row sources.


And sometimes we can optimize it using Recursive
subquery factoring clause, ie recursive WITH (it was
optimized in 12.2)

For example:


create
table th as

select
nullif(level-1,0) parent_id, level id, mod(level,2) x

from dual
connect by level<=1e5;



create
index th_idx2 on th(parent_id);



select/*+
parallel */ *

from th

connect by
parent_id = prior id

start with
parent_id=0

/

with v(id,
parent_id, x) as (

select
id,parent_id,x from th where parent_id=0

union all

select th.id,th.parent_id,th.x
from v,th where th.parent_id=v.id

)

select/*+
parallel */ *

from v

/








On Mon, Oct 29, 2018 at 4:24 PM Radoulov,

Post by Lothar Flatz
Hi,
https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/
It will also show you an other hidden parameter. Make

sure the indexes

Post by Lothar Flatz
are in place!
Regards
Lothar

Thank you Lothar!
I'll try your solution with the pipelined function.


Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l







--





Best regards,
Sayan Malakshinov
Oracle performance tuning
engineer

Oracle ACE Associate
http://orasql.org








--

[Radoulov, Dimitre]

We'll first try to divide the query in order to avoid the "OR" operator
in the "connect by", run both queries in parallel and add a third
"connect by" to correlate the resultsets after.


Regards
Dimitre

Post by l***@bluewin.ch
Hi,
thanks. The pipeline Table function will work in parallel. The
efficiency depends a bit on how your hierarchies are constructed.
Regards
Lothar
----UrsprÃŒngliche Nachricht----
Datum : 29/10/2018 - 16:01 (CET)
Betreff : Re: Suboptimal query plan with connect by
Hi,
with "_old_connect_by_enabled"=true the query still runs in serial mode.
Thank you!
Regards
Dimitre

Post by Lothar Flatz
Hi Sayan,
I think Dimitre should try the easiest thing first. alter session
set “_old_connect_by_enabled”=true;
It might be possible that the old algorithm can be parallelized
without further work.
The old algorithm is depth first, I guess that could help.
The pipe table function is a bit forced nut I guarantee parallel
processing. It must be adapted to the respective case. The outer
query (ref cursor) retrieves the start points (root rows).
If there is only one root, we need to retrieve the first level
under the root.
Of course it is possible to return every row, you just have to
adapt the inner query.
Regards
Lothar

Post by Sayan Malakshinov
Lothar,
But as I see your example just calculates a sum of children
rows, it doesn't return them. I wander hot it can help return a
tree?
Dimitre,
As far as I know "connect by" cannot be parallellized, but of
course optimizer can use parallel execution for children row
sources.
And sometimes we can optimize it using Recursive subquery
factoring clause, ie recursive WITH (it was optimized in 12.2)
create table th as
select nullif(level-1,0) parent_id, level id, mod(level,2) x
from dual connect by level<=1e5;
create index th_idx2 on th(parent_id);
select/*+ parallel */ *
from th
connect by parent_id = prior id
start with parent_id=0
/
with v(id, parent_id, x) as (
select id,parent_id,x from th where parent_id=0
union all
select th.id <http://th.id>,th.parent_id,th.x from v,th where
th.parent_id=v.id <http://v.id>
)
select/*+ parallel */ *
from v
/
On Mon, Oct 29, 2018 at 4:24 PM Radoulov, Dimitre

Post by Lothar Flatz
Hi,

https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/

Post by Lothar Flatz
It will also show you an other hidden parameter. Make sure

the indexes

Post by Lothar Flatz
are in place!
Regards
Lothar

Thank you Lothar!
I'll try your solution with the pipelined function.
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--

[Lothar Flatz]

Sounds like a good plan.

Regards

Lothar

Post by Radoulov, Dimitre
We'll first try to divide the query in order to avoid the "OR"
operator in the "connect by", run both queries in parallel and add a
third "connect by" to correlate the resultsets after.
Regards
Dimitre

Post by l***@bluewin.ch
Hi,
thanks. The pipeline Table function will work in parallel. The
efficiency depends a bit on how your hierarchies are constructed.
Regards
Lothar
----UrsprÃŒngliche Nachricht----
Datum : 29/10/2018 - 16:01 (CET)
Betreff : Re: Suboptimal query plan with connect by
Hi,
with "_old_connect_by_enabled"=true the query still runs in serial mode.
Thank you!
Regards
Dimitre

Post by Lothar Flatz
Hi Sayan,
I think Dimitre should try the easiest thing first. alter
session set “_old_connect_by_enabled”=true;
It might be possible that the old algorithm can be parallelized
without further work.
The old algorithm is depth first, I guess that could help.
The pipe table function is a bit forced nut I guarantee parallel
processing. It must be adapted to the respective case. The outer
query (ref cursor) retrieves the start points (root rows).
If there is only one root, we need to retrieve the first level
under the root.
Of course it is possible to return every row, you just have to
adapt the inner query.
Regards
Lothar

Post by Sayan Malakshinov
Lothar,
But as I see your example just calculates a sum of children
rows, it doesn't return them. I wander hot it can help return a
tree?
Dimitre,
As far as I know "connect by" cannot be parallellized, but of
course optimizer can use parallel execution for children row
sources.
And sometimes we can optimize it using Recursive subquery
factoring clause, ie recursive WITH (it was optimized in 12.2)
create table th as
select nullif(level-1,0) parent_id, level id, mod(level,2) x
from dual connect by level<=1e5;
create index th_idx2 on th(parent_id);
select/*+ parallel */ *
from th
connect by parent_id = prior id
start with parent_id=0
/
with v(id, parent_id, x) as (
select id,parent_id,x from th where parent_id=0
union all
select th.id <http://th.id>,th.parent_id,th.x from v,th where
th.parent_id=v.id <http://v.id>
)
select/*+ parallel */ *
from v
/
On Mon, Oct 29, 2018 at 4:24 PM Radoulov, Dimitre

Post by Lothar Flatz
Hi,

https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/

Post by Lothar Flatz
It will also show you an other hidden parameter. Make

sure the indexes

Post by Lothar Flatz
are in place!
Regards
Lothar

Thank you Lothar!
I'll try your solution with the pipelined function.
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--

--