Puja Shaw
Reputation: 145
Oracle query tuning advice
I have a SQL query the produces a result set being inserted into meter_read_alert table on daily basis using a procedure.
This process takes around 3 hrs daily. Explain plan for it is shown below.
How can I optimize the query? And what are the problem areas by seeing explain plan?
The table has range interval partition and local indexes. Every day around 9 million of rows inserts happen.
query:
select * from (
SELECT
'SBILL' Scenario,
mfg_serial_num,
device_id,
channel_id,
read_value,
read_time,
date_val,
reading_hourly,
reading_daywise,
CASE
WHEN read_value IS NULL THEN'04'--(Meter goes stale)'
WHEN event_name = 'BACKFLOW' THEN'01'
WHEN event_name = 'TAMPER' THEN'02'
WHEN event_name = 'Tamper' THEN'02'
WHEN event_name = 'EMPTYPIPE' THEN '03' END AS CodeAlert_HOUR,
CASE WHEN reading_daywise BETWEEN CON.min_value AND CON.max_value THEN CON.alert_code END AS CodeAlert_DAY,
CASE
WHEN read_value IS NULL THEN'4'
WHEN event_name = 'BACKFLOW' THEN'4'
WHEN event_name = 'TAMPER' THEN'4'
WHEN event_name = 'Tamper' THEN'4'
WHEN event_name = 'EMPTYPIPE' THEN'4' END AS Priority_HOUR,
CASE WHEN reading_daywise BETWEEN CON.min_value AND CON.max_value THEN CON.priority END AS Priority_DAY,
CASE WHEN val_status='VAL' THEN 'A' WHEN val_status='EST' THEN 'E' ELSE val_status END Val_status,
desc_text,
CASE WHEN reading_hourly BETWEEN CONH.min_value AND CONH.max_value THEN CONH.alert_code END AS ConAlert_per_hour
FROM
(
SELECT d.mfg_serial_num,
d.id device_id,
mdr.channel_id,
mdr.read_value,
mdr.read_time,
To_date(To_char(mdr.read_time, 'dd/mm/yyyy')) date_val,
mdr.read_value - Lag(mdr.read_value, 1, 0) over ( PARTITION BY mdr.channel_id ORDER BY mdr.read_time) reading_hourly,
Max(mdr.read_value) keep (dense_rank last ORDER BY To_char (mdr.read_time, 'HH24:MI:SS')) over ( PARTITION BY mdr.channel_id,To_date(To_char(mdr.read_time, 'dd/mm/yyyy'))) -
Min(mdr.read_value) keep (dense_rank first ORDER BY To_char (mdr.read_time, 'HH24:MI:SS') ) over ( PARTITION BY mdr.channel_id,To_date(To_char(mdr.read_time, 'dd/mm/yyyy'))) as reading_daywise,
det.event_desc desc_text,
det.event_name,
mdr.val_status
FROM (select channel_id,read_value,read_time,val_status,device_id from mudr.register_read
WHERE read_time between (select TO_TIMESTAMP(sysdate-7) from dual) and (select TO_TIMESTAMP(sysdate-1) - INTERVAL '1' SECOND from dual)
--ORDER BY channel_id,read_time
)Mdr
left join (select channel_id, device_id from EIP.device_channel_rel --where eff_end_time is null
) DCR ON dcr.channel_id = mdr.channel_id
left join (select id, mfg_serial_num from EIP.device) d ON d.id = DCR.device_id
left join (select device_id, device_event_type_id,event_time ,
case when to_char(event_time,'mi') between '01' and '59' then trunc(event_time,'HH24') + interVal '60' minute else event_time end New_Event_Time
from MUDR.device_event
where event_time between (select TO_TIMESTAMP(sysdate-7) from dual) and (select TO_TIMESTAMP(sysdate-1) - INTERVAL '1' SECOND from dual)
) de ON mdr.device_id = de.device_id
and mdr.read_time= de.New_Event_Time
left join (select device_event_type_id,event_desc,event_name from MUDR.device_event_type) det ON det.device_event_type_id= de.device_event_type_id
--ORDER BY channel_id,read_time
)t1
left join (SELECT * FROM condition WHERE scenario = 'SBILL' AND unit_of_period_check='DAY' )CON ON t1.reading_daywise BETWEEN CON.min_value AND CON.max_value
left join (SELECT * FROM condition WHERE scenario = 'SBILL' AND unit_of_period_check='HOUR' )CONH ON t1.reading_hourly BETWEEN CONH.min_value AND CONH.max_value
ORDER BY channel_id,read_time) a
where not exists
(
select 1
from meter_read_alert b
WHERE a.scenario=b.scenario
AND a.mfg_serial_num= b.mfg_serial_num
AND a.device_id = b.device_id
and a.channel_id=b.channel_id
AND a.read_time=b.read_time
and b.read_time between (select TO_TIMESTAMP(sysdate-7) from dual) and (select TO_TIMESTAMP(sysdate-1) - INTERVAL '1' SECOND from dual)
);
Plan hash value: 618507408
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106K| 46M| | 153K (1)| 00:00:07 | | |
| 1 | SORT ORDER BY | | 106K| 46M| 48M| 153K (1)| 00:00:07 | | |
| 2 | MERGE JOIN OUTER | | 106K| 46M| | 153K (1)| 00:00:07 | | |
| 3 | SORT JOIN | | 106K| 44M| 92M| 153K (1)| 00:00:07 | | |
| 4 | MERGE JOIN OUTER | | 106K| 44M| | 153K (1)| 00:00:07 | | |
| 5 | SORT JOIN | | 106K| 41M| 87M| 153K (1)| 00:00:07 | | |
|* 6 | HASH JOIN RIGHT ANTI | | 106K| 41M| 3432K| 145K (1)| 00:00:06 | | |
| 7 | PARTITION RANGE ITERATOR | | 87775 | 2400K| | 20495 (1)| 00:00:01 | KEY | KEY |
| 8 | VIEW | VW_SQ_1 | 87775 | 2400K| | 20495 (1)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | METER_READ_ALERT | 87775 | 2914K| | 20495 (1)| 00:00:01 | KEY | KEY |
|* 10 | INDEX RANGE SCAN | READTIME_ALL_IDX | 87775 | | | 81 (0)| 00:00:01 | KEY | KEY |
| 11 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 12 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 13 | VIEW | | 155K| 56M| | 124K (1)| 00:00:05 | | |
| 14 | WINDOW SORT | | 155K| 24M| 26M| 124K (1)| 00:00:05 | | |
| 15 | WINDOW SORT | | 155K| 24M| 26M| 124K (1)| 00:00:05 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 155K| 24M| | 119K (1)| 00:00:05 | | |
| 17 | TABLE ACCESS FULL | DEVICE_EVENT_TYPE | 1513 | 124K| | 8 (0)| 00:00:01 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 52840 | 4076K| | 119K (1)| 00:00:05 | | |
| 19 | PARTITION RANGE ITERATOR | | 3 | 54 | | 1 (0)| 00:00:01 | KEY | KEY |
| 20 | PARTITION HASH ALL | | 3 | 54 | | 1 (0)| 00:00:01 | 1 | LAST |
| 21 | VIEW | | 3 | 54 | | 1 (0)| 00:00:01 | | |
| 22 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | DEVICE_EVENT | 3 | 48 | | 1 (0)| 00:00:01 | KEY | KEY |
|* 23 | INDEX RANGE SCAN | IX_DEVICE_EVENT_1 | 1 | | | 1 (0)| 00:00:01 | KEY | KEY |
| 24 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 25 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
|* 26 | HASH JOIN OUTER | | 52840 | 3147K| 2944K| 119K (1)| 00:00:05 | | |
|* 27 | HASH JOIN OUTER | | 52840 | 2322K| 2312K| 115K (1)| 00:00:05 | | |
| 28 | VIEW | | 51466 | 1708K| | 113K (1)| 00:00:05 | | |
| 29 | SORT ORDER BY | | 51466 | 1357K| 2240K| 113K (1)| 00:00:05 | | |
| 30 | PARTITION RANGE ITERATOR | | 51466 | 1357K| | 112K (1)| 00:00:05 | KEY | KEY |
| 31 | PARTITION HASH ALL | | 51466 | 1357K| | 112K (1)| 00:00:05 | 1 | LAST |
| 32 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| REGISTER_READ | 51466 | 1357K| | 112K (1)| 00:00:05 | KEY | KEY |
|* 33 | INDEX SKIP SCAN | UK_REGISTER_READ | 1 | | | 112K (1)| 00:00:05 | KEY | KEY |
| 34 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 35 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 36 | INDEX FAST FULL SCAN | UK_DEVICE_CHANNEL_REL | 1482K| 15M| | 1443 (3)| 00:00:01 | | |
| 37 | TABLE ACCESS FULL | DEVICE | 1096K| 16M| | 4063 (2)| 00:00:01 | | |
|* 38 | FILTER | | | | | | | | |
|* 39 | SORT JOIN | | 1 | 26 | | 3 (34)| 00:00:01 | | |
|* 40 | EXTERNAL TABLE ACCESS FULL | CONDITION | 1 | 26 | | 2 (0)| 00:00:01 | | |
|* 41 | FILTER | | | | | | | | |
|* 42 | SORT JOIN | | 2 | 46 | | 3 (34)| 00:00:01 | | |
|* 43 | EXTERNAL TABLE ACCESS FULL | CONDITION | 2 | 46 | | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1"."MFG_SERIAL_NUM"="ITEM_1" AND "T1"."DEVICE_ID"="ITEM_2" AND "T1"."CHANNEL_ID"="ITEM_3" AND "T1"."READ_TIME"="ITEM_4")
9 - filter("B"."SCENARIO"='SBILL')
10 - access("B"."READ_TIME">= (SELECT TO_TIMESTAMP(TO_CHAR(SYSDATE@!-7)) FROM "SYS"."DUAL" "DUAL") AND "B"."READ_TIME"<= (SELECT
TO_TIMESTAMP(TO_CHAR(SYSDATE@!-1))-INTERVAL'+00 00:00:01.000000' DAY(2) TO SECOND(6) FROM "SYS"."DUAL" "DUAL"))
16 - access("DEVICE_EVENT_TYPE_ID"(+)="DE"."DEVICE_EVENT_TYPE_ID")
18 - access("MDR"."DEVICE_ID"="DE"."DEVICE_ID"(+) AND "MDR"."READ_TIME"="DE"."NEW_EVENT_TIME"(+))
23 - access("EVENT_TIME">= (SELECT TO_TIMESTAMP(TO_CHAR(SYSDATE@!-7)) FROM "SYS"."DUAL" "DUAL") AND "EVENT_TIME"<= (SELECT
TO_TIMESTAMP(TO_CHAR(SYSDATE@!-1))-INTERVAL'+00 00:00:01.000000' DAY(2) TO SECOND(6) FROM "SYS"."DUAL" "DUAL"))
26 - access("ID"(+)="DEVICE_ID")
27 - access("CHANNEL_ID"(+)="MDR"."CHANNEL_ID")
33 - access("READ_TIME">= (SELECT TO_TIMESTAMP(TO_CHAR(SYSDATE@!-7)) FROM "SYS"."DUAL" "DUAL") AND "READ_TIME"<= (SELECT
TO_TIMESTAMP(TO_CHAR(SYSDATE@!-1))-INTERVAL'+00 00:00:01.000000' DAY(2) TO SECOND(6) FROM "SYS"."DUAL" "DUAL"))
filter("READ_TIME">= (SELECT TO_TIMESTAMP(TO_CHAR(SYSDATE@!-7)) FROM "SYS"."DUAL" "DUAL") AND "READ_TIME"<= (SELECT
TO_TIMESTAMP(TO_CHAR(SYSDATE@!-1))-INTERVAL'+00 00:00:01.000000' DAY(2) TO SECOND(6) FROM "SYS"."DUAL" "DUAL"))
38 - filter("T1"."READING_DAYWISE"<="CONDITION"."MAX_VALUE"(+))
39 - access(INTERNAL_FUNCTION("T1"."READING_DAYWISE")>=INTERNAL_FUNCTION("CONDITION"."MIN_VALUE"(+)))
filter(INTERNAL_FUNCTION("T1"."READING_DAYWISE")>=INTERNAL_FUNCTION("CONDITION"."MIN_VALUE"(+)))
40 - filter("SCENARIO"(+)='SBILL' AND "UNIT_OF_PERIOD_CHECK"(+)='DAY')
41 - filter("T1"."READING_HOURLY"<="CONDITION"."MAX_VALUE"(+))
42 - access(INTERNAL_FUNCTION("T1"."READING_HOURLY")>=INTERNAL_FUNCTION("CONDITION"."MIN_VALUE"(+)))
filter(INTERNAL_FUNCTION("T1"."READING_HOURLY")>=INTERNAL_FUNCTION("CONDITION"."MIN_VALUE"(+)))
43 - filter("SCENARIO"(+)='SBILL' AND "UNIT_OF_PERIOD_CHECK"(+)='HOUR')
Upvotes: 1
Views: 91
Answers (1)
Thorsten Kettner
Reputation: 95101
There are only two restrictions on the data you want to select. The main one is the time span. The other one is that you want to exclude data already existing in meter_read_alert. You could start your query like this, applying the restrictions as early as possible:
with main as
(
select
s.scenario,
mdr.channel_id,
mdr.read_value,
mdr.read_time,
mdr.val_status,
mdr.device_id as mdr_device_id,
dcr.device_id as dcr_device_id,
d.mfg_serial_num as dcr_mfg_serial_num
from (select 'SBILL' as scenario from dual) s
cross join mudr.register_read mdr
left join from eip.device_channel_rel dcr on dcr.channel_id = mdr.channel_id
left join eip.device d on d.id = dcr.device_id
where mdr.read_time >= systimestamp - interval '7' day and mdr.read_time < systimestamp
and not exists
(
select null
from meter_read_alert mra
where mra.scenario = s.scenario
and mra.mfg_serial_num = d.mfg_serial_num
and mra.device_id = mdr.device_id
and mra.channel_id = mdr.channel_id
and mra.read_time = mdr.read_time
)
)
select ...
(Are you sure you want outer joins here by the way? If inner joins suffice, make these inner joins.)
Your time range condition looked complicated. I hope I got it right in my query. If you want it slightly different, change it. With dates/times we usually don't use BETWEEN, but >= and < to be independent on time precision (e.g. somedate < date '2019-12-01' would get the complete November until the last millisecond, nanosecond or whatever and not include any of December).
Speeding up queries is mostly about providing apropriate indexes. I'd suggest:
create index idx1 on mudr.register_read (read_time, channel_id);
create index idx2 on eip.device_channel_rel (channel_id, device_id);
create index idx3 on eip.device (device_id);
create index idx4 on meter_read_alert (read_time, channel_id, device_id, mfg_serial_num, scenario);
Or even use covering indexes:
create index idx1 on mudr.register_read (read_time, channel_id, read_value, val_status, device_id);
create index idx2 on eip.device_channel_rel (channel_id, device_id);
create index idx3 on eip.device (device_id);
create index idx4 on meter_read_alert (read_time, channel_id, device_id, mfg_serial_num, scenario);
Upvotes: 2
Related Questions
- Looking for performance improvements in the SQL
- need to tune this sql query to improve performance
- Query optimization on Oracle
- Tuning for Oracle query
- Tuning SQL Query in Oracle
- Tuning SQL statement performance
- Oracle:query optimization
- Query Tuning - Advice
- Tuning my SQL query
- Need some advice to optimize an Oracle query