Without using DISTINCT function , how to achieve the same result.
TAB_C = 2947109424 rows, act = 43460 rows
SELECT tc.email_addr_id
, COUNT(DISTINCT tc.m_act_id) AS num_12mons
, COUNT(DISTINCT CASE WHEN ROUND(tc.outbound_date, 'DD')
> (ROUND(sysdate, 'DD') - 90)
THEN tc.m_act_id ELSE NULL END) AS num_3mons
, COUNT(DISTINCT CASE WHEN ROUND(tc.outbound_date, 'DD')
> (ROUND(sysdate, 'DD') - 180)
THEN tc.m_act_id ELSE NULL END) AS num_6mons
, COUNT(DISTINCT CASE WHEN ROUND(tc.outbound_date, 'DD')
> (ROUND(sysdate, 'DD') - 270)
THEN tc.m_act_id ELSE NULL END) AS num_9mons
FROM Tab_C tc
INNER JOIN act a
ON tc.act_id = a.act_id
where a.channel_code IN ('FM','RM')
AND ROUND(tc.outbound_date, 'DD') > (ROUND(sysdate, 'DD') - 365)
GROUP BY tc.email_addr_id
NULLIF is supposed to be a lot quicker than CASE statements, and you might find that it's the CASE that's causing the slow rather than the distinct ... but it's all due to your indexing. NULLIF obviously only works based on finding the correct value, rather than excluding the "wrong" values, so you have to reverse the search.
this might work better, but you'll need to double-check the results.
select email_addr_id,
count(m_act_id) AS num_12mons,
COUNT(nullif(sign(outbound_date +90 - ROUND(sysdate, 'DD')), -1))) AS num_3mons,
COUNT(nullif(sign(outbound_date +180 - ROUND(sysdate, 'DD')), -1))) AS num_6mons,
COUNT(nullif(sign(outbound_date +270 - ROUND(sysdate, 'DD')), -1))) AS num_9mons
from (
select distinct tc.email_addr_id, tc.m_act_id, round(tc.outbound_date, 'DD') as outbound_date
from Tab_C tc
INNER JOIN act a ON tc.act_id = a.act_id
where a.channel_code IN ('FM','RM')
AND ROUND(tc.outbound_date, 'DD') > (ROUND(sysdate, 'DD') - 365))
group by email_addr_id;