I'm having a hard time wrapping my head around this query. it is taking almost 200+ seconds to execute. I've pasted the execution plan as well.

SELECT       
      user_id    ,
             ROLE_ID              ,
             effective_from_date  ,
             effective_to_date    ,
             participant_code     ,
             ACTIVE
FROM    
      CMP_USER_ROLE E
WHERE   
      ACTIVE = 0
 AND  (SYSDATE BETWEEN effective_from_date AND effective_to_date 
  OR TO_CHAR(effective_to_date,'YYYY-Q') = '2010-2')
      AND  participant_code = 'NY005'
      AND  NOT EXISTS ( SELECT 1 FROM CMP_USER_ROLE r 
          WHERE r.USER_ID= E.USER_ID AND r.role_id = E.role_id 
           AND r.ACTIVE = 4
                                AND E.effective_to_date <= (SELECT MAX(last_update_date)
                                FROM CMP_USER_ROLE S
                                WHERE S.role_id = r.role_id
                                AND S.role_id = r.role_id
                                AND S.ACTIVE = 4 ))

Explain plan

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |     1 |    37 |   154   (2)| 00:00:02 |
|*  1 |  FILTER                          |                  |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID    | USER_ROLE        |     1 |    37 |    30   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN              | N_USER_ROLE_IDX6 |    27 |       |     3   (0)| 00:00:01 |
|*  4 |   FILTER                         |                  |       |       |            |          |
|   5 |    HASH GROUP BY                 |                  |     1 |    47 |   124   (2)| 00:00:02 |
|*  6 |     TABLE ACCESS BY INDEX ROWID  | USER_ROLE        |   159 |  3339 |   119   (1)| 00:00:02 |
|   7 |      NESTED LOOPS                |                  |    11 |   517 |   123   (1)| 00:00:02 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| USER_ROLE        |     1 |    26 |     4   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | N_USER_ROLE_IDX5 |     1 |       |     3   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN           | N_USER_ROLE_IDX2 |   957 |       |    74   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------------