query advice

HI All,

Please give an idea  in tuning this query,im looking for suggestion like how to construct this query than  recommendations such as using sql tuning advisor or sqltxplain

The query takes 32 mins to execute for fetching the total count value,

Version:11.2.0.1

O/s:linux

Thanks for your help!

select count(*)FROM (

WITH src

       AS (SELECT   sbl_row_id,

                    name,

                    CASE

                       WHEN name = 'Sponsor Content'

                       THEN

                          (SELECT   sbl_row_id

                             FROM   dfstaging.ctms_s_ds_lst_of_val

                            WHERE   name = 'Core' AND TYPE = 'QNT_CATEGORY')

                       WHEN name = 'Transfer of Regulatory Respon'

                       THEN

                          (SELECT   sbl_row_id

                             FROM   dfstaging.ctms_s_ds_lst_of_val