sql - Connect By Level In Subquery Slow -
i using connect_by_level subquery in oracle database supply dates join table. connect level appears causing query run slowly. here slow query:
select t.code, d.month_end, count(*) device t, (select add_months(trunc(sysdate, 'mm'), - (level - 1)) - 1 month_end dual connect level <= 1) d (d.month_end between t.date , t.exp_date) group t.code, d.month_end the slow query above took 2 hours run other day. query below should equivalent runs in less 30 seconds:
select t.code, trunc(sysdate, 'mm') - 1 month_end, count(*) device t ((trunc(sysdate, 'mm') - 1) between t.date , t.exp_date) group t.code ultimately, want 1st query return data past 24 months, need figure out why running 1 month criteria. suggestions on causing slow completion time connect level query?
edit add explain plan output slow query:
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop | tq |in-out| pq distrib | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | select statement | | 520 | 65520 | 134k (1)| 00:00:03 | | | | | | | 1 | px coordinator | | | | | | | | | | | | 2 | px send qc (random) | :tq10006 | 520 | 65520 | 134k (1)| 00:00:03 | | | q1,06 | p->s | qc (rand) | | 3 | hash group | | 520 | 65520 | 134k (1)| 00:00:03 | | | q1,06 | pcwp | | | 4 | px receive | | 520 | 65520 | 134k (1)| 00:00:03 | | | q1,06 | pcwp | | | 5 | px send hash | :tq10005 | 520 | 65520 | 134k (1)| 00:00:03 | | | q1,05 | p->p | hash | | 6 | hash group | | 520 | 65520 | 134k (1)| 00:00:03 | | | q1,05 | pcwp | | |* 7 | hash join outer | | 520 | 65520 | 134k (1)| 00:00:03 | | | q1,05 | pcwp | | | 8 | px receive | | 520 | 48880 | 134k (1)| 00:00:03 | | | q1,05 | pcwp | | | 9 | px send hash | :tq10003 | 520 | 48880 | 134k (1)| 00:00:03 | | | q1,03 | p->p | hash | |* 10 | hash join outer buffered | | 520 | 48880 | 134k (1)| 00:00:03 | | | q1,03 | pcwp | | | 11 | px receive | | | | | | | | q1,03 | pcwp | | | 12 | px send hash | :tq10001 | | | | | | | q1,01 | p->p | hash | | 13 | nested loops | | | | | | | | q1,01 | pcwp | | | 14 | nested loops | | 276 | 13800 | 50303 (0)| 00:00:01 | | | q1,01 | pcwp | | | 15 | buffer sort | | | | | | | | q1,01 | pcwc | | | 16 | px receive | | | | | | | | q1,01 | pcwp | | | 17 | px send broadcast | :tq10000 | | | | | | | | s->p | broadcast | | 18 | view | | 1 | 6 | 3 (0)| 00:00:01 | | | | | | |* 19 | connect without filtering| | | | | | | | | | | | 20 | fast dual | | 1 | | 3 (0)| 00:00:01 | | | | | | | 21 | px partition range | | 3898k| | 1656 (0)| 00:00:01 | 1 | 75 | q1,01 | pcwc | | |* 22 | index range scan | ftr_dt_ix1 | 3898k| | 1656 (0)| 00:00:01 | 1 | 75 | q1,01 | pcwp | | |* 23 | table access local index rowid| ftr_dt | 276 | 12144 | 50300 (0)| 00:00:01 | 1 | 1 | q1,01 | pcwp | | | 24 | px receive | | 7344k| 308m| 83875 (1)| 00:00:02 | | | q1,03 | pcwp | | | 25 | px send hash | :tq10002 | 7344k| 308m| 83875 (1)| 00:00:02 | | | q1,02 | p->p | hash | | 26 | px block iterator | | 7344k| 308m| 83875 (1)| 00:00:02 | | | q1,02 | pcwc | | |* 27 | table access storage full | dtl_hist_dt | 7344k| 308m| 83875 (1)| 00:00:02 | | | q1,02 | pcwp | | | 28 | px receive | | 108k| 3376k| 275 (0)| 00:00:01 | | | q1,05 | pcwp | | | 29 | px send hash | :tq10004 | 108k| 3376k| 275 (0)| 00:00:01 | | | q1,04 | p->p | hash | | 30 | px block iterator | | 108k| 3376k| 275 (0)| 00:00:01 | | | q1,04 | pcwc | | |* 31 | table access storage full | point_t | 108k| 3376k| 275 (0)| 00:00:01 | | | q1,04 | pcwp | | predicate information (identified operation id): --------------------------------------------------- 7 - access("c"."code"="ap"."code"(+) , "c"."point_id"="ap"."point_id"(+)) 10 - access("cf"."p_id"="c"."wtn"(+)) 19 - filter(level<=1) 22 - access("cf"."date"<=internal_function("d"."month_end")) 23 - filter(upper("cf"."plan") '%dv%' , ("cf"."ft_code"='7370' or "cf"."ft_code"='7371' or "cf"."ft_code"='7372' or "cf"."ft_code"='7373' or "cf"."ft_code"='7374' or "cf"."ft_code"='7380' or "cf"."ft_code"='7380c' or "cf"."ft_code"='7381' or "cf"."ft_code"='7381c' or "cf"."ft_code"='7382' or "cf"."ft_code"='7382c' or "cf"."ft_code"='7383' or "cf"."ft_code"='7384' or "cf"."ft_code"='7409' or "cf"."ft_code"='7409c' or "cf"."ft_code"='7410' or "cf"."ft_code"='7410c' or "cf"."ft_code"='trkdv') , "cf"."exp_date">=internal_function("d"."month_end")) 27 - storage("c"."exp_date"(+)>=trunc(sysdate@!,'fmmm')-1 , "c"."date"(+)<=trunc(sysdate@!,'fmmm')-1) filter("c"."exp_date"(+)>=trunc(sysdate@!,'fmmm')-1 , "c"."date"(+)<=trunc(sysdate@!,'fmmm')-1) 31 - storage("ap"."exp_date"(+)>=trunc(sysdate@!,'fmmm')-1 , "ap"."date"(+)<=trunc(sysdate@!,'fmmm')-1) filter("ap"."exp_date"(+)>=trunc(sysdate@!,'fmmm')-1 , "ap"."date"(+)<=trunc(sysdate@!,'fmmm')-1) note ----- - dynamic sampling used statement (level=6) - automatic dop: computed degree of parallelism 8 edit add explain plan faster query:
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop | tq |in-out| pq distrib | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | select statement | | 278 | 33360 | 622k (1)| 00:00:11 | | | | | | | 1 | px coordinator | | | | | | | | | | | | 2 | px send qc (random) | :tq10005 | 278 | 33360 | 622k (1)| 00:00:11 | | | q1,05 | p->s | qc (rand) | | 3 | hash group | | 278 | 33360 | 622k (1)| 00:00:11 | | | q1,05 | pcwp | | | 4 | px receive | | 4034 | 472k| 622k (1)| 00:00:11 | | | q1,05 | pcwp | | | 5 | px send hash | :tq10004 | 4034 | 472k| 622k (1)| 00:00:11 | | | q1,04 | p->p | hash | |* 6 | hash join outer buffered | | 4034 | 472k| 622k (1)| 00:00:11 | | | q1,04 | pcwp | | | 7 | px receive | | 4034 | 346k| 621k (1)| 00:00:11 | | | q1,04 | pcwp | | | 8 | px send hash | :tq10002 | 4034 | 346k| 621k (1)| 00:00:11 | | | q1,02 | p->p | hash | |* 9 | hash join outer buffered | | 4034 | 346k| 621k (1)| 00:00:11 | | | q1,02 | pcwp | | | 10 | px receive | | 2140 | 94160 | 538k (1)| 00:00:09 | | | q1,02 | pcwp | | | 11 | px send hash | :tq10000 | 2140 | 94160 | 538k (1)| 00:00:09 | | | q1,00 | p->p | hash | | 12 | px block iterator | | 2140 | 94160 | 538k (1)| 00:00:09 | 1 | 75 | q1,00 | pcwc | | |* 13 | table access storage full| ftr_dt | 2140 | 94160 | 538k (1)| 00:00:09 | 1 | 75 | q1,00 | pcwp | | | 14 | px receive | | 7344k| 308m| 83875 (1)| 00:00:02 | | | q1,02 | pcwp | | | 15 | px send hash | :tq10001 | 7344k| 308m| 83875 (1)| 00:00:02 | | | q1,01 | p->p | hash | | 16 | px block iterator | | 7344k| 308m| 83875 (1)| 00:00:02 | | | q1,01 | pcwc | | |* 17 | table access storage full| dtl_hist_dt | 7344k| 308m| 83875 (1)| 00:00:02 | | | q1,01 | pcwp | | | 18 | px receive | | 108k| 3376k| 275 (0)| 00:00:01 | | | q1,04 | pcwp | | | 19 | px send hash | :tq10003 | 108k| 3376k| 275 (0)| 00:00:01 | | | q1,03 | p->p | hash | | 20 | px block iterator | | 108k| 3376k| 275 (0)| 00:00:01 | | | q1,03 | pcwc | | |* 21 | table access storage full | point_t | 108k| 3376k| 275 (0)| 00:00:01 | | | q1,03 | pcwp | | predicate information (identified operation id): --------------------------------------------------- 6 - access("c"."code"="ap"."code"(+) , "c"."point_id"="ap"."point_id"(+)) 9 - access("cf"."p_id"="c"."wtn"(+)) 13 - storage(upper("cf"."plan") '%dv%' , ("cf"."ft_code"='7370' or "cf"."ft_code"='7371' or "cf"."ft_code"='7372' or "cf"."ft_code"='7373' or "cf"."ft_code"='7374' or "cf"."ft_code"='7380' or "cf"."ft_code"='7380c' or "cf"."ft_code"='7381' or "cf"."ft_code"='7381c' or "cf"."ft_code"='7382' or "cf"."ft_code"='7382c' or "cf"."ft_code"='7383' or "cf"."ft_code"='7384' or "cf"."ft_code"='7409' or "cf"."ft_code"='7409c' or "cf"."ft_code"='7410' or "cf"."ft_code"='7410c' or "cf"."ft_code"='trkdv') , "cf"."exp_date">=trunc(sysdate@!,'fmmm')-1 , "cf"."date"<=trunc(sysdate@!,'fmmm')-1) filter(upper("cf"."plan") '%dv%' , ("cf"."ft_code"='7370' or "cf"."ft_code"='7371' or "cf"."ft_code"='7372' or "cf"."ft_code"='7373' or "cf"."ft_code"='7374' or "cf"."ft_code"='7380' or "cf"."ft_code"='7380c' or "cf"."ft_code"='7381' or "cf"."ft_code"='7381c' or "cf"."ft_code"='7382' or "cf"."ft_code"='7382c' or "cf"."ft_code"='7383' or "cf"."ft_code"='7384' or "cf"."ft_code"='7409' or "cf"."ft_code"='7409c' or "cf"."ft_code"='7410' or "cf"."ft_code"='7410c' or "cf"."ft_code"='trkdv') , "cf"."exp_date">=trunc(sysdate@!,'fmmm')-1 , "cf"."date"<=trunc(sysdate@!,'fmmm')-1) 17 - storage("c"."exp_date"(+)>=trunc(sysdate@!,'fmmm')-1 , "c"."date"(+)<=trunc(sysdate@!,'fmmm')-1) filter("c"."exp_date"(+)>=trunc(sysdate@!,'fmmm')-1 , "c"."date"(+)<=trunc(sysdate@!,'fmmm')-1) 21 - storage("ap"."exp_date"(+)>=trunc(sysdate@!,'fmmm')-1 , "ap"."date"(+)<=trunc(sysdate@!,'fmmm')-1) filter("ap"."exp_date"(+)>=trunc(sysdate@!,'fmmm')-1 , "ap"."date"(+)<=trunc(sysdate@!,'fmmm')-1) note ----- - dynamic sampling used statement (level=6) - automatic dop: computed degree of parallelism 8
in first query oracle doing index range scan on large table , retrieving huge amounts of data on first pass access("cf"."date"<=internal_function("d"."month_end")). can work around rewriting select more fast one. -
select t.code, trunc(t.exp_date, 'mm') - 1 month_end, count(*) device t t.date < :report_period_end , t.exp_date > :report_period_start group t.code, trunc(t.exp_date,'mm') - 1; also note there several potential problems original slow query:
- a row t.date , t.exp_date more 2 months apart counted multiple times.
- rows t.date , t.exp_date has same month not counted @ all
- you comparing dates e.g. 2013.10.30 00:00:00. results may incorrect if dates contain time data.
Comments
Post a Comment