Home » RDBMS Server » Performance Tuning » join is slow (Oracle12g)
| join is slow [message #669949] |
Fri, 25 May 2018 05:20  |
Buchas
Messages: 101
Registered: March 2006
|
Senior 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
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  |
Buchas
Messages: 101
Registered: March 2006
|
Senior 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)
;
----------------------------------------------------------------------------------
| 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 |
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
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) |
|
|
|
Goto Forum:
Current Time: Wed Dec 11 15:33:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
