SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Parallel query + direct path reads = better cardinality estimates?

Bob BrylaAug 6 2016 — edited Aug 8 2016

Same query run during the same hour -- buffer cache is "warm" but that doesn't help much at all in the serial plan. Serial execution (actually doesn't finish for 10 hours), SQL Monitoring report:

https://www.dropbox.com/s/dgvifjt1n28nx6q/noparallel_conv_path_SQL_Monitoring.html?dl=0

Parallel plan -- lots of direct path reads, runs in less than two minutes:

https://www.dropbox.com/s/6u84jnnra0fjhhs/parallel16_direct_path_SQL_Monitoring.html?dl=0

PGA usage is not much higher in the parallel plan, and considering extremely short execution time, customer can tolerate having 32 extra CPU threads allocated. Sounds too good to be true -- what am I missing here? Is the optimizer picking a different plan in the serial version based on bad cardinalities (line 37 of serial plan)? Thank you!!!

This post has been answered by Jonathan Lewis on Aug 7 2016

Jump to Answer

Aketi Jyuuzou

I used sense of Tabibitosan B-)
870646

In this case,I imagine four traveler ;-)

create table t(prod,order_date,order_nbr) as
select 'ABC', to_date('6/12/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/13/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/14/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/15/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'ABC', to_date('6/16/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'ABC', to_date('6/17/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/18/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/19/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/20/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/21/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/22/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/18/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/19/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/20/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'DEF', to_date('6/21/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'DEF', to_date('6/22/2009','fmmm/dd/yyyy'), 0 from dual;

select prod,min(order_date),count(*)
from (select prod,order_date,order_nbr,
       Row_Number() over(partition by prod           order by order_date)
      -Row_Number() over(partition by prod,order_nbr order by order_date)
      as distance
        from T)
 where order_nbr = 1
group by prod,distance
order by min(order_date);

PRO  MIN(ORDE   COUNT(*)
---  --------  ---------
ABC  09-06-12          3
ABC  09-06-17          6
DEF  09-06-18          2

1 person found this helpful

Frank Kulash

Hi,

Thanks for posting the CREATE TABLE and INSERT statements: that really helps!

Aketi seems to be assuming that order_nbr is always 0 or 1, and that the order_dates in your table are consecutive days (which happens to be true in your sample data).
If that's not the case, you can modify Aketi's solution like this:

WITH	got_grp	    AS
(
	SELECT	product_name
	,	order_date
	,	order_nbr
	,	ROW_NUMBER () OVER ( PARTITION BY  product_name
			      	     ORDER BY  	   order_date
				   ) + ( TRUNC (SYSDATE) 
				       - TRUNC (order_date)
				       )      		AS d_grp
	FROM	tt1
	WHERE	order_nbr	> 0
)
SELECT	  product_name
,	  MIN (order_date)	AS start_date
,	  COUNT (*)		AS consecutive_days
FROM	  got_grp
GROUP BY  product_name
,	  d_grp
ORDER BY  product_name
,     	  start_date
;

As written, this assumes that the combination (product_name, TRUNC (start_date)) is unique. If that's no the case, it can be adapted.

1 person found this helpful

Aketi Jyuuzou

Oh I did not realize that I can use "where clause" in first select :-)
Likewise my previous solution,I imagine four traveler

select prod,min(order_date),count(*)
from (select prod,order_date,order_nbr,
      order_date-Row_Number() over(partition by prod order by order_date)
      as distance
        from T
       where order_nbr > 0)
group by prod,distance
order by min(order_date);

PRO  MIN(ORDE   COUNT(*)
---  --------  ---------
ABC  09-06-12          3
ABC  09-06-17          6
DEF  09-06-18          2

1 person found this helpful

85972

Thank you. I worked :)

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 5 2016

Added on Aug 6 2016

8 comments

2,443 views

SQL & PL/SQL

Parallel query + direct path reads = better cardinality estimates?

Comments

Post Details