Home » RDBMS Server » Performance Tuning » join is slow (Oracle12g)
join is slow [message #669949] Fri, 25 May 2018 05:20 Go to next message
Buchas
Messages: 98
Registered: March 2006
Member
Hello,
I have a query with the following structure:
select <...>
from
(select ...) intervalo_duomenys,
(select ...) amount_by_reception_time
where amount_by_reception_time.reception_time(+) between intervalo_duomenys.nuo and intervalo_duomenys.iki 
A subquery intervalo_duomenys executes in 0,2sec and returns 288 rows. A subquery amount_by_reception_time executes in 0,6sec and returns 2148 rows. However a join of those two queries takes 14seconds (I mean full query takes ~14sec (and returns 2228 rows), but there is nothing else left to be slow). I would expect it to be faster because number of rows in subqueries is relatively small. Do you have any ideas how to make it faster?

Full query:
 select
        intervalo_duomenys.header_interval_nr,
        intervalo_duomenys.day_nr,
        intervalo_duomenys.header_interval_from,
        intervalo_duomenys.header_interval_to,
        intervalo_duomenys.interval_nr,
        intervalo_duomenys.interval_from,
        intervalo_duomenys.interval_to,
        intervalo_duomenys.reception_time,
        intervalo_duomenys.nuo,
        intervalo_duomenys.iki,
           amount_by_reception_time.reception_time as tikras_laikas,
        case
          when intervalo_duomenys.header_interval_nr = 1 and
               intervalo_duomenys.interval_nr = 1 then/*
            sepa_inst_monitoring_utl.get_pac_balance(
              p_par_id => 30,
              p_reception_date => intervalo_duomenys.reception_time,
              p_liq_control_type1 => 'F',
              p_liq_control_type2 => 'T',
              p_liq_control_type3 => 'N')*/
              2222
          else
            --round(avg(pah.amount),2)
            amount_by_reception_time.amount
        end amount
      from(
      select --intervalo_duomenys begin
        tmp4.* ,
        tmp4.reception_time - /*l_interval_length*/10/2/24/60 nuo,
        tmp4.reception_time + /*l_interval_length*/10/2/24/60 - 1/24/60/60  iki     
      from(
        select--tmp4 begin
          tmp3.*,
          case
            when tmp3.interval_nr/(/*l_interval_segments*/12/2) <= 1 then
              1
            else
              2
          end day_indicator,
          case
            when tmp3.interval_nr/(/*l_interval_segments*/12/2) <= 1 then
              to_date(to_char(decode(tmp3.day_nr,1,/*l_cur_date*/trunc(sysdate)-1,/*l_cur_date*/trunc(sysdate)),'yyyy-mm-dd') || ' ' ||
                lpad(to_char(round((tmp3.header_interval_from + tmp3.header_interval_to)/120)) - 1,2,'0') || ':' ||
                lpad(to_char(floor((tmp3.interval_from + tmp3.interval_to)/2)),2,'0') || ':00','yyyy-mm-dd hh24:mi:ss')
            else
              to_date(to_char(decode(tmp3.day_nr,1,/*l_cur_date*/trunc(sysdate)-1,/*l_cur_date*/trunc(sysdate)),'yyyy-mm-dd') || ' ' ||
                lpad(to_char(round((tmp3.header_interval_from + tmp3.header_interval_to)/120)),2,'0') || ':' ||
                lpad(to_char(floor((tmp3.interval_from + tmp3.interval_to)/2)),2,'0') || ':00','yyyy-mm-dd hh24:mi:ss')
          end reception_time
        from(
          select
            tmp2.header_interval_nr,
            case
              when tmp2.header_interval_nr <= 12 then
                1
              else
                2
            end day_nr,
            tmp2.header_interval_from,
            tmp2.header_interval_to,
            case
              when tmp2.interval_from >= 60 then
                tmp2.interval_from - 60
              else
                tmp2.interval_from
            end interval_from,
            case
              when tmp2.interval_to > 60 then
                tmp2.interval_to - 60
              else
                tmp2.interval_to
            end interval_to,
            tmp2.interval_nr
          from(
            select
              tmp1.header_interval_nr,
              case
                when tmp1.header_interval_from >= 1440 then
                  tmp1.header_interval_from - 1440
                else
                  tmp1.header_interval_from
              end header_interval_from,
              case
                when tmp1.header_interval_to > 1440 then
                  tmp1.header_interval_to - 1440
                else
                  tmp1.header_interval_to
              end header_interval_to,
              (tmp1.interval_nr - 1) * /*l_interval_length*/10 interval_from,
              (tmp1.interval_nr) * /*l_interval_length*/10 interval_to,
              tmp1.interval_nr
            from(
              select
                tmp.*,
                rank() over (partition by tmp.header_interval_nr order by tmp.dummy_level) interval_nr
              from(
                select
                  ceil(level//*l_interval_segments*/12 ) header_interval_nr,
                  (ceil(level//*l_interval_segments*/12) - 1)*60*2 header_interval_from,
                  ceil(level//*l_interval_segments*/12)*60*2 header_interval_to,
                  level dummy_level
                from
                  dual
                connect by level <= 24*/*l_interval_segments*/12) tmp) tmp1) tmp2) tmp3) tmp4  ) intervalo_duomenys,

        (select--begin amount_by_reception_time
          max(pah.balance) amount,
          pah.reception_time
        from
          sepa_pab_history pah
        where
          /*p_par_id*/30 is not null and
          pah.par_id = /*p_par_id*/30 and
          trunc(pah.reception_time) in (
            /*l_cur_date*/trunc(sysdate) - 1,
            /*l_cur_date*/trunc(sysdate))
        group by
          pah.reception_time,
          pah.par_id
      ) amount_by_reception_time
      where      
        amount_by_reception_time.reception_time(+) between intervalo_duomenys.nuo and intervalo_duomenys.iki
      order by
        intervalo_duomenys.header_interval_nr,
        intervalo_duomenys.interval_nr

Execution plan is:
 Plan Hash Value  : 

-----------------------------------------------------------------------------------------------
| Id | Operation                           | Name                | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                     |    5 |   370 |  244 |      |
|  1 |   SORT ORDER BY                     |                     |    5 |   370 |  244 |      |
|  2 |    NESTED LOOPS OUTER               |                     |    5 |   370 |  243 |      |
|  3 |     VIEW                            |                     |    1 |    52 |    3 |      |
|  4 |      WINDOW SORT                    |                     |    1 |    52 |    3 |      |
|  5 |       VIEW                          |                     |    1 |    52 |    2 |      |
|  6 |        CONNECT BY WITHOUT FILTERING |                     |      |       |      |      |
|  7 |         FAST DUAL                   |                     |    1 |       |    2 |      |
|  8 |     VIEW                            |                     |    5 |   110 |  240 |      |
|  9 |      SORT GROUP BY                  |                     | 2176 | 32640 |  240 |      |
| 10 |       TABLE ACCESS FULL             | SEPA_PAB_HISTORY_RT | 2212 | 33180 |  239 |      |
-----------------------------------------------------------------------------------------------

[Updated on: Fri, 25 May 2018 05:30]

Report message to a moderator

Re: join is slow [message #669995 is a reply to message #669949] Tue, 29 May 2018 01:39 Go to previous messageGo to next message
Buchas
Messages: 98
Registered: March 2006
Member
Just in case somebody would like to reproduce the problem, here is the select, that depends only on dual. Subselect intervalo_duomenys returns 2880 rows in 0.5sec. Subselect amount_by_reception_time returns 2000 rows in 0.1sec. The big select returns 2880 rows in 71sec. Why is so drastic increase in time?
Big select:
 select 
      buc.header_interval_nr,
      buc.day_nr,
      buc.header_interval_from,
      buc.header_interval_to,
      buc.interval_nr,
      buc.interval_from,
      buc.interval_to,
      buc.reception_time,
      buc.amount     
  from
  (--buc begin
    select
      pat.header_interval_nr,
      pat.day_nr,
      pat.header_interval_from,
      pat.header_interval_to,
      pat.interval_nr,
      pat.interval_from,
      pat.interval_to,
      pat.reception_time,
      case
        when pat.reception_time <= sysdate then
          nvl(pat.amount,
            last_value(pat.amount ignore nulls) over (order by
              pat.header_interval_nr,
              pat.interval_nr
              rows between unbounded preceding and 1 preceding))
        else
          null
      end amount,
      max(pat.tikras_laikas)over(partition by pat.reception_time) artimiausias_intervalo_galui,
      pat.tikras_laikas
    from(
      select
        intervalo_duomenys.header_interval_nr,
        intervalo_duomenys.day_nr,
        intervalo_duomenys.header_interval_from,
        intervalo_duomenys.header_interval_to,
        intervalo_duomenys.interval_nr,
        intervalo_duomenys.interval_from,
        intervalo_duomenys.interval_to,
        intervalo_duomenys.reception_time,
           amount_by_reception_time.reception_time as tikras_laikas,
        case
          when intervalo_duomenys.header_interval_nr = 1 and
               intervalo_duomenys.interval_nr = 1 then
              2222
          else
            amount_by_reception_time.amount
        end amount
      from(
          select --intervalo_duomenys begin
        tmp4.* ,
        tmp4.reception_time - /*l_interval_length*/1/2/24/60 nuo,
        tmp4.reception_time + /*l_interval_length*/1/2/24/60 - 1/24/60/60  iki     
      from(
        select--tmp4 begin
          tmp3.*,
          case
            when tmp3.interval_nr/(/*l_interval_segments*/120/2) <= 1 then
              1
            else
              2
          end day_indicator,
          case
            when tmp3.interval_nr/(/*l_interval_segments*/120/2) <= 1 then
              to_date(to_char(decode(tmp3.day_nr,1,/*l_cur_date*/trunc(sysdate)-1,/*l_cur_date*/trunc(sysdate)),'yyyy-mm-dd') || ' ' ||
                lpad(to_char(round((tmp3.header_interval_from + tmp3.header_interval_to)/120)) - 1,2,'0') || ':' ||
                lpad(to_char(floor((tmp3.interval_from + tmp3.interval_to)/2)),2,'0') || ':00','yyyy-mm-dd hh24:mi:ss')
            else
              to_date(to_char(decode(tmp3.day_nr,1,/*l_cur_date*/trunc(sysdate)-1,/*l_cur_date*/trunc(sysdate)),'yyyy-mm-dd') || ' ' ||
                lpad(to_char(round((tmp3.header_interval_from + tmp3.header_interval_to)/120)),2,'0') || ':' ||
                lpad(to_char(floor((tmp3.interval_from + tmp3.interval_to)/2)),2,'0') || ':00','yyyy-mm-dd hh24:mi:ss')
          end reception_time
        from(
          select
            tmp2.header_interval_nr,
            case
              when tmp2.header_interval_nr <= 12 then
                1
              else
                2
            end day_nr,
            tmp2.header_interval_from,
            tmp2.header_interval_to,
            case
              when tmp2.interval_from >= 60 then
                tmp2.interval_from - 60
              else
                tmp2.interval_from
            end interval_from,
            case
              when tmp2.interval_to > 60 then
                tmp2.interval_to - 60
              else
                tmp2.interval_to
            end interval_to,
            tmp2.interval_nr
          from(
            select
              tmp1.header_interval_nr,
              case
                when tmp1.header_interval_from >= 1440 then
                  tmp1.header_interval_from - 1440
                else
                  tmp1.header_interval_from
              end header_interval_from,
              case
                when tmp1.header_interval_to > 1440 then
                  tmp1.header_interval_to - 1440
                else
                  tmp1.header_interval_to
              end header_interval_to,
              (tmp1.interval_nr - 1) * /*l_interval_length*/1 interval_from,
              (tmp1.interval_nr) * /*l_interval_length*/1 interval_to,
              tmp1.interval_nr
            from(
              select
                tmp.*,
                rank() over (partition by tmp.header_interval_nr order by tmp.dummy_level) interval_nr
              from(
                select
                  ceil(level//*l_interval_segments*/120 ) header_interval_nr,
                  (ceil(level//*l_interval_segments*/120) - 1)*60*2 header_interval_from,
                  ceil(level//*l_interval_segments*/120)*60*2 header_interval_to,
                  level dummy_level
                from
                  dual
                connect by level <= 24*/*l_interval_segments*/120) tmp) tmp1) tmp2) tmp3) tmp4) intervalo_duomenys,
        (
        select level amount, sysdate-level/24/60/60 reception_time from dual  connect by level <= 2000
          ) amount_by_reception_time
      where
        amount_by_reception_time.reception_time(+) between intervalo_duomenys.nuo and intervalo_duomenys.iki 
        ) pat
    order by
      pat.header_interval_nr,
      pat.interval_nr
      ) buc
 where
        nvl(tikras_laikas, reception_time) = nvl(artimiausias_intervalo_galui, reception_time)
      ;
Execution plan:
----------------------------------------------------------------------------------
| Id | Operation                             | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                      |      |    1 |   118 |    6 |      |
|  1 |   VIEW                                |      |    1 |   118 |    6 |      |
|  2 |    WINDOW SORT                        |      |    1 |    71 |    6 |      |
|  3 |     WINDOW SORT                       |      |    1 |    71 |    6 |      |
|  4 |      NESTED LOOPS OUTER               |      |    1 |    71 |    5 |      |
|  5 |       VIEW                            |      |    1 |    52 |    3 |      |
|  6 |        WINDOW SORT                    |      |    1 |    52 |    3 |      |
|  7 |         VIEW                          |      |    1 |    52 |    2 |      |
|  8 |          CONNECT BY WITHOUT FILTERING |      |      |       |      |      |
|  9 |           FAST DUAL                   |      |    1 |       |    2 |      |
| 10 |       VIEW                            |      |    1 |    19 |    2 |      |
| 11 |        CONNECT BY WITHOUT FILTERING   |      |      |       |      |      |
| 12 |         FAST DUAL                     |      |    1 |       |    2 |      |
----------------------------------------------------------------------------------
Re: join is slow [message #670262 is a reply to message #669995] Wed, 20 June 2018 05:22 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
IMHO the problem is that
(
        select level amount, sysdate-level/24/60/60 reception_time from dual  connect by level <= 2000
      ) amount_by_reception_time
is executed for each output line of the first inline view ( 2800 times ).

Try:
WITH 
    intervalo_duomenys AS  (
                        SELECT /*+ MATERIALIZE */ 
                            tmp4.*,
                            tmp4.reception_time - /*l_interval_length*/ 1 / 2 / 24 / 60 nuo,
                            tmp4.reception_time + /*l_interval_length*/ 1 / 2 / 24 / 60 - 1 / 24 / 60 / 60 iki, 
                            ROWNUM RNN
                        FROM
                            (
                                SELECT--tmp4 begin
                                    tmp3.*,
                                    CASE
                                            WHEN tmp3.interval_nr / (/*l_interval_segments*/ 120 / 2 ) <= 1 THEN 1
                                            ELSE 2
                                        END
                                    day_indicator,
                                    CASE
                                            WHEN tmp3.interval_nr / (/*l_interval_segments*/ 120 / 2 ) <= 1 THEN TO_DATE(TO_CHAR(DECODE(tmp3.day_nr,1,/*l_cur_date*/trunc(SYSDATE) - 1,/*l_cur_date*/trunc(SYSDATE) ),'yyyy-mm-dd')
                                            || ' '
                                            || lpad(TO_CHAR(round( (tmp3.header_interval_from + tmp3.header_interval_to) / 120) ) - 1,2,'0')
                                            || ':'
                                            || lpad(TO_CHAR(floor( (tmp3.interval_from + tmp3.interval_to) / 2) ),2,'0')
                                            || ':00','yyyy-mm-dd hh24:mi:ss')
                                            ELSE TO_DATE(TO_CHAR(DECODE(tmp3.day_nr,1,/*l_cur_date*/trunc(SYSDATE) - 1,/*l_cur_date*/trunc(SYSDATE) ),'yyyy-mm-dd')
                                            || ' '
                                            || lpad(TO_CHAR(round( (tmp3.header_interval_from + tmp3.header_interval_to) / 120) ),2,'0')
                                            || ':'
                                            || lpad(TO_CHAR(floor( (tmp3.interval_from + tmp3.interval_to) / 2) ),2,'0')
                                            || ':00','yyyy-mm-dd hh24:mi:ss')
                                        END
                                    reception_time
                                FROM
                                    (
                                        SELECT
                                            tmp2.header_interval_nr,
                                            CASE
                                                    WHEN tmp2.header_interval_nr <= 12 THEN 1
                                                    ELSE 2
                                                END
                                            day_nr,
                                            tmp2.header_interval_from,
                                            tmp2.header_interval_to,
                                            CASE
                                                    WHEN tmp2.interval_from >= 60 THEN tmp2.interval_from - 60
                                                    ELSE tmp2.interval_from
                                                END
                                            interval_from,
                                            CASE
                                                    WHEN tmp2.interval_to > 60 THEN tmp2.interval_to - 60
                                                    ELSE tmp2.interval_to
                                                END
                                            interval_to,
                                            tmp2.interval_nr
                                        FROM
                                            (
                                                SELECT
                                                    tmp1.header_interval_nr,
                                                    CASE
                                                            WHEN tmp1.header_interval_from >= 1440 THEN tmp1.header_interval_from - 1440
                                                            ELSE tmp1.header_interval_from
                                                        END
                                                    header_interval_from,
                                                    CASE
                                                            WHEN tmp1.header_interval_to > 1440 THEN tmp1.header_interval_to - 1440
                                                            ELSE tmp1.header_interval_to
                                                        END
                                                    header_interval_to,
                                                    ( tmp1.interval_nr - 1 ) * /*l_interval_length*/ 1 interval_from,
                                                    ( tmp1.interval_nr ) * /*l_interval_length*/ 1 interval_to,
                                                    tmp1.interval_nr
                                                FROM
                                                    (
                                                        SELECT
                                                            tmp.*,
                                                            RANK() OVER(
                                                                PARTITION BY tmp.header_interval_nr
                                                                ORDER BY
                                                                    tmp.dummy_level
                                                            ) interval_nr
                                                        FROM
                                                            (
                                                                SELECT
                                                                    ceil(level //*l_interval_segments*/ 120) header_interval_nr,
                                                                    ( ceil(level //*l_interval_segments*/ 120) - 1 ) * 60 * 2 header_interval_from,
                                                                    ceil(level //*l_interval_segments*/ 120) * 60 * 2 header_interval_to,
                                                                    level dummy_level
                                                                FROM
                                                                    dual
                                                                CONNECT BY
                                                                    level <= 24 */*l_interval_segments*/ 120
                                                            ) tmp
                                                    ) tmp1
                                            ) tmp2
                                    ) tmp3
                            ) tmp4
                    ),
   amount_by_reception_time AS (
                        SELECT /*+ MATERIALIZE */
                            level amount,
                            SYSDATE - level / 24 / 60 / 60 reception_time, ROWNUM RNN
                        FROM
                            dual
                        CONNECT BY
                            level <= 2000
                    ) 
SELECT
    buc.header_interval_nr,
    buc.day_nr,
    buc.header_interval_from,
    buc.header_interval_to,
    buc.interval_nr,
    buc.interval_from,
    buc.interval_to,
    buc.reception_time,
    buc.amount
FROM
    (--buc begin
        SELECT
            pat.header_interval_nr,
            pat.day_nr,
            pat.header_interval_from,
            pat.header_interval_to,
            pat.interval_nr,
            pat.interval_from,
            pat.interval_to,
            pat.reception_time,
            CASE
                    WHEN pat.reception_time <= SYSDATE THEN nvl(pat.amount,LAST_VALUE(pat.amount IGNORE NULLS) OVER(
                        ORDER BY
                            pat.header_interval_nr,
                            pat.interval_nr
                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                    ) )
                    ELSE NULL
                END
            amount,
            MAX(pat.tikras_laikas) OVER(
                PARTITION BY pat.reception_time
            ) artimiausias_intervalo_galui,
            pat.tikras_laikas
        FROM
            (
                SELECT /*+ ORDERED */ 
                    intervalo_duomenys.header_interval_nr,
                    intervalo_duomenys.day_nr,
                    intervalo_duomenys.header_interval_from,
                    intervalo_duomenys.header_interval_to,
                    intervalo_duomenys.interval_nr,
                    intervalo_duomenys.interval_from,
                    intervalo_duomenys.interval_to,
                    intervalo_duomenys.reception_time,
                    amount_by_reception_time.reception_time AS tikras_laikas,
                    CASE
                            WHEN intervalo_duomenys.header_interval_nr = 1
                                 AND intervalo_duomenys.interval_nr = 1 THEN 2222
                            ELSE amount_by_reception_time.amount
                        END
                    amount
                FROM
                    intervalo_duomenys 
                    LEFT JOIN amount_by_reception_time ON amount_by_reception_time.reception_time BETWEEN intervalo_duomenys.nuo AND intervalo_duomenys.iki
            ) pat
        ORDER BY
            pat.header_interval_nr,
            pat.interval_nr
    ) buc
WHERE
    nvl(tikras_laikas,reception_time) = nvl(artimiausias_intervalo_galui,reception_time)
Previous Topic: Used Left Join - Query goes for Full Table scan
Next Topic: Encountering issues with join condition in stored procedure
Goto Forum:
  


Current Time: Fri Dec 13 21:01:24 CST 2019