SQL & PL/SQL

hey am I doing anything wrong with this query because it is taking hours and hours to run:

with dp as
(select distinct pid
	from problem
	where 	    
	    lower(code) like 'icd-250%' AND
	    (QUALIFIER = 'S/P' OR
	    QUALIFIER  = 'MDxof' OR
	    QUALIFIER  = 'Hx of' OR
	    QUALIFIER  = 'H/F' OR
	    QUALIFIER  = 'Dx of' OR
	    QUALIFIER  = '? of') AND
	    CHANGE  = 2),
sm as
(select distinct ll.pid,CAST('1' AS NUMBER) riskval
	from 	lastlabs ll,
		obs  o
	where
		ll.pid = o.pid and
		ll.hdid = o.hdid and
		ll.maxlabdate = o.obsdate and
		ll.hdid = 300015 and
		(lower(o.obsvalue) not like '%quit%' or
		 lower(o.obsvalue) not like '%negative%' or
		 lower(o.obsvalue) not like '%never%' or
		 lower(o.obsvalue) not like '%no%' or
		 lower(o.obsvalue) not like '%none%') 
		and ll.pid IN(select pid from dp)),
ht as
(select distinct pid,CAST('1' AS NUMBER) riskval
	from problem
	where 	    
	    (lower(code) like 'icd-401%'
	    or lower(code) like 'icd-402%'
	    or lower(code) like 'icd-403%'
	    or lower(code) like 'icd-404%'
	    or lower(code) like 'icd-405%') AND
	    (QUALIFIER = 'S/P' OR
	    QUALIFIER  = 'MDxof' OR
	    QUALIFIER  = 'Hx of' OR
	    QUALIFIER  = 'H/F' OR
	    QUALIFIER  = 'Dx of' OR
	    QUALIFIER  = '? of') AND
	    CHANGE  = 2
	    and pid IN(select pid from dp)),
dl as
(select distinct pid,CAST('1' AS NUMBER) riskval
	from problem
	where 	    
	    lower(code) like 'icd-272%' AND
	    (QUALIFIER = 'S/P' OR
	    QUALIFIER  = 'MDxof' OR
	    QUALIFIER  = 'Hx of' OR
	    QUALIFIER  = 'H/F' OR
	    QUALIFIER  = 'Dx of' OR
	    QUALIFIER  = '? of') AND
	    CHANGE  = 2
	    and pid IN(select pid from dp)),
cvd as
	(select pid,riskval from sm
	union all
	select pid,riskval from ht
	union all
	select pid,riskval from dl),
cvdsum as
	(select pid, sum(riskval) cmfactor from cvd
	 group by pid
	 order by pid),
cm as
(select pid,cmfactor from cvdsum where cmfactor >=2),
st as
(select distinct ll.pid,ll.maxlabdate
          from 	lastlabs ll,
		obs  o
          where
		ll.pid = o.pid and
		ll.hdid = o.hdid and
		ll.maxlabdate = o.obsdate and
		ll.hdid in (54388,2909) and
                ll.pid in(select pid from cm))
select cm.pid, st.maxlabdate
        from cm
        left join st on cm.pid=st.pid