Onesqlperformancetuning

Original sql:

select *
  from ((select count(c.customer_id) day30
           from customer c
          where c.is_active = '0'
            and c.birth IN (SELECT to_char(SYSDATE - 1 + rownum, 'mm-dd')
                              FROM dual
                            CONNECT BY rownum <= 30)
            AND exists (SELECT 1
                   FROM sid_view v
                  WHERE v.object_id_identity = c.customer_id
                    AND v.class =
                        '##########################'    /* sensitivity information */
                    and v.ACE_SID in (:1, :2))) a left join
        (select count(c.customer_id) tomorrow
           from customer c
          where c.is_active = '0'
            and c.birth = to_char(sysdate + 1, 'mm-dd')
            AND exists (SELECT 1
                   FROM sid_view v
                  WHERE v.object_id_identity = c.customer_id
                    AND v.class =
                        '##########################'
                    and v.ACE_SID in (:3, :4))) b on 1 = 1 left join
        (select count(c.customer_id) today
           from customer c
          where c.is_active = '0'
            and c.birth = to_char(sysdate, 'mm-dd')
            AND exists (SELECT 1
                   FROM sid_view v
                  WHERE v.object_id_identity = c.customer_id
                    AND v.class =
                        '##########################'
                    and v.ACE_SID in (:5, :6))) on 1 = 1)

I known that v.ace_sid in ( value, value) being same by communicating with programer, therefore i extracted the global temp table as the sql_tmp

eg:

WITH sql_tmp AS
 (SELECT c.customer_id, C.BIRTH 
    FROM customer c
   WHERE c.is_active = '0'
     AND c.birth IN (SELECT TO_CHAR(sysdate - 1 + rownum, 'mm-dd')
                       FROM dual
                     CONNECT BY rownum <= 30)
     AND EXISTS
   (SELECT 1
            FROM sid_view v
           WHERE v.object_id_identity = c.customer_id
             AND v.class = '#######################'
             AND V.ACE_SID IN (:1, :2)))
SELECT *
  FROM (SELECT COUNT(T.CUSTOMER_ID) DAY30 FROM SQL_TMP t)
  LEFT JOIN (SELECT COUNT(T.CUSTOMER_ID) TOMORROW
               FROM SQL_TMP T
              WHERE t.BIRTH = TO_CHAR(SYSDATE + 1, 'mm-dd' ) )
    ON 1 = 1
  LEFT JOIN (SELECT COUNT(t.customer_id) today FROM sql_tmp t 
       WHERE t.birth = to_char(sysdate, 'mm-dd'))
    ON 1 = 1;