oracle前30秒,此sql查询时间大概需要30秒，求优化

select t3.name,t1.ip,t2.ifdesc,to_date(t1.TIME_ID,'yyyymmddhh24')as time_id,t2.ifspeed,t1.Packet_loss_rate,t1.jitter,t1.delay,t2.ifin,t2.ifout from

(select p.ip,p.TIME_ID,avg(p.loss) as Packet_loss_rate,avg(p.jitter) as jitter,avg(p.Delay) as delay

from (select l.remote_ip as  ip,

to_char(get_date_from_millisecond(dctime), 'yyyymmddhh24')as TIME_ID,

(CASE WHEN pm.kpi_no = 10103016010 THEN pm.value ELSE NULL END) loss,

(CASE WHEN pm.kpi_no = 10103016008 THEN pm.value ELSE NULL END) jitter,

(CASE WHEN pm.kpi_no = 10103016009 THEN pm.value ELSE NULL END) Delay

from pm_raw_z_reslink pm, res_link l

where

pm.kbp=l.res_id

and pm.dctime>get_millisecond(sysdate-7)) p

group by p.ip ,p.TIME_ID) t1,

(select p.ip,p.ifdesc,p.TIME_ID,p.ifspeed,avg(p.ifin)as ifin,avg(p.ifout)as ifout from

(select REGEXP_SUBSTR(inf.res_name,

'\d+\.\d+\.\d+\.\d+',

1,

1,

'i') as ip,inf.ifdesc as ifdesc,to_char(get_date_from_millisecond(pm.dctime),'yyyymmddhh24') as time_id,inf.ifspeed as ifspeed,

(CASE WHEN pm.kpi_no = 10103013001 THEN pm.value ELSE NULL END) ifin,

(CASE WHEN pm.kpi_no = 10103013005 THEN pm.value ELSE NULL END) ifout

from pm_raw_z_resinterface pm,res_interface inf

where  pm.kbp=inf.res_id

and pm.dctime>get_millisecond(sysdate-7)) p

group by p.ip,p.ifdesc,p.TIME_ID,p.ifspeed) t2,

(select  REGEXP_SUBSTR(r.mo_name,

'\d+\.\d+\.\d+\.\d+',

1,

1,

'i')as res_ip,r.res_descr as name from res_object r where r.classname in('ResSwitch','ResRouter'))t3

where t1.ip=t2.ip(+)

and t1.TIME_ID=t2.TIME_ID(+)

and t1.ip=t3.res_ip

order by t1.time_id desc;