튜닝 관련 문의입니다.

505건의 대화방 리스트 출력 쿼리이며 일부분만 짤라서 게시판에 올립니다.

속도는 1초 이하로 괜찮으나 앱 상에서 보면 6~7초 정도 나와서 튜닝 대상으로 선정되었습니다.

실제 실행계획을 뽑아보니 과도한 버퍼를 탐색하는 것이 아닌가 의문이 들었고

기존의 인덱스를 활용하고 싶어도 부정형 비교가 많아서 특히 A.USERID <> 'skytown@korea.kr' 로 비교되는 부분때문에 인덱스를 탈수도 없습니다.

어떻게 튜닝하는 것이 맞을지 고민이여서 실행계획과 쿼리, 테이블 정보를 올리오니

고수님들의 조언 좀 부탁드리겠습니다.

SELECT /*+ gather_plan_statistics 20180427 */ N.GBN,                                            
            N.CHATROOMSEQ,                                            
            N.CHATROOMNAME,                                            
            N.ALARMYN,                                            
            N.REGDT,                                                            
            N.TIMESTAMP,                                                            
            NVL(                                                
                (SELECT TB.USERID                                
                 FROM V2_USERPROFILE TB, V2_CHATROOM CR, V2_CHATROOMUSER CRU        
                 WHERE TB.USERID = N.REGID                        
                   AND CR.REGID = TB.USERID                    
                   AND CR.CHATROOMSEQ = N.CHATROOMSEQ            
                   AND CRU.CHATROOMSEQ = CR.CHATROOMSEQ        
                   AND CRU.USERID = CR.REGID                    
                   AND CRU.DELYN = 'N'                            
                )                                                
                ,(SELECT USERID                                  
                  FROM V2_CHATROOMUSER                            
                  WHERE CHATROOMSEQ = N.CHATROOMSEQ            
                    AND DELYN = 'N'                            
                    AND ROWNUM = 1                                
                 )                                                
              ) AS REGID,                                        
            N.USERID,                                                            
            N.DISPLAYNAME,                                                
            N.PHOTO,                                                            
            N.PLUSPHOTO,                                                            
            N.PLUSROOMNAME                                                            
 FROM (                                                            
 SELECT S.GBN,                                            
            S.CHATROOMSEQ,                                            
            (SELECT CHATROOMNAME FROM V2_CHATROOMNAME WHERE CHATROOMSEQ = S.CHATROOMSEQ AND REGID = S.REGID) AS CHATROOMNAME,                                            
            S.ALARMYN,                                            
            S.REGDT,                                                            
            S.TIMESTAMP,                                                            
            S.REGID,                                                            
            S.USERID,                                                            
            S.DISPLAYNAME,                                                
            S.PHOTO,                                                            
            S.PLUSPHOTO,                                                             
          ( SELECT CHATROOMNAME                        
          FROM   V2_CHATROOMNAME SCRN, V2_CHATROOM SCR                        
          WHERE  1=1                        
          AND    SCRN.CHATROOMSEQ = SCR.CHATROOMSEQ                         
          AND    SCRN.CHATROOMSEQ = S.CHATROOMSEQ                        
          AND    SCR.REGID = SCRN.REGID                         
          ) PLUSROOMNAME                        
  FROM                                                                       
  (                                                                       
  SELECT B.GBN,                                                                      
         A.CHATROOMSEQ,                                                                    
         B.CHATROOMNAME,                                                                    
         B.ALARMYN,                                                                    
         TO_CHAR(B.REGDT, 'yyyy-MM-dd am hh12:mi') AS REGDT,                                
         TO_CHAR(B.REGDT, 'YYYYMMDDHH24MISS') AS TIMESTAMP,                                
         B.REGID,                                                                           
         A.USERID,                                                                          
         C.DISPLAYNAME,                                                                     
         C.PHOTOPATH||C.PHOTONM AS PHOTO,                                                    
         row_number() over (partition BY B.CHATROOMSEQ ORDER BY B.CHATROOMSEQ DESC, B.REGDT DESC ) rnum,                                                    
          DECODE (P.FILEPATH, '', '/upload/Plus/room.png', P.FILEPATH||P.FILENM) PLUSPHOTO                            
    FROM V2_CHATROOMUSER A,                                                                 
         (SELECT D.GBN, D.CHATROOMSEQ,  CN.CHATROOMNAME, CN.ALARMYN, D.REGID, D.REGDT         
                  FROM (SELECT * FROM V2_CHATROOM WHERE GBN <> 'N'                          
                        UNION ALL                                                           
                        SELECT * FROM V2_CHATROOM WHERE GBN = 'N' AND REGID = :1 ) D,        
                       V2_CHATROOMNAME CN,                                                    
                       V2_CHATROOMUSER F                                                    
                 WHERE D.DELYN = 'N'                                                        
                       AND F.USERID = :1                             
                       AND F.DELYN = 'N'                                                    
                       AND D.CHATROOMSEQ = F.CHATROOMSEQ                                    
                       AND D.CHATROOMSEQ = CN.CHATROOMSEQ(+)) B,                             
         V2_USERPROFILE C, V2_PLUSFRIEND P                                                                   
   WHERE A.CHATROOMSEQ = B.CHATROOMSEQ                                                      
         AND A.DELYN = 'N'                                                                  
         AND A.USERID = C.USERID                                                            
         AND A.USERID <> :1                                                                 
         AND A.CHATROOMSEQ = P.CHATROOMSEQ(+)                                                            
  ) S                                                                       
  WHERE S.rnum < 5                                                                       
  UNION ALL                                                                        
   SELECT X.GBN,                            
          X.CHATROOMSEQ CHATROOMSEQ,                            
          X.CHATROOMNAME CHATROOMNAME,                            
          X.ALARMYN ALARMYN,                            
          TO_CHAR(X.REGDT, 'YYYY-MM-DD AM HH12:MI') REGDT,                            
          TO_CHAR(X.REGDT, 'YYYYMMDDHH24MISS') TIMESTAMP,                            
          USERPROFILE.USERID REGID,                            
          DECODE(X.GBN, 'I', USERPROFILE.USERID, 'bababarotalk') AS USERID,        
          DECODE(X.GBN,'I',X.CHATROOMNAME,(SELECT NVL((SELECT V2_USERINFO.DISPLAYNAME        
              FROM V2_SNSUSER,        
              V2_USERINFO,        
              V2_CHATROOM        
              WHERE V2_SNSUSER.CERTID     = V2_USERINFO.CERTID        
              AND V2_SNSUSER.USERID       = V2_CHATROOM.LASTEXITID        
              AND V2_CHATROOM.CHATROOMSEQ = X.CHATROOMSEQ), '대화상대없음') FROM DUAL)) DISPLAYNAME,    
          DECODE(X.GBN, 'I', USERPROFILE.PHOTOPATH||USERPROFILE.PHOTONM, '/upload/Profile/user.png') AS PHOTO,            
          DECODE (P.FILEPATH, '', '/upload/Plus/room.png', P.FILEPATH||P.FILENM) PLUSPHOTO,                            
          ( SELECT CHATROOMNAME                        
          FROM   V2_CHATROOMNAME SCRN, V2_CHATROOM SCR                        
          WHERE  1=1                        
          AND    SCRN.CHATROOMSEQ = SCR.CHATROOMSEQ                         
          AND    SCRN.CHATROOMSEQ = X.CHATROOMSEQ                        
          AND    SCR.REGID = SCRN.REGID                         
          ) PLUSROOMNAME                        
   FROM   (SELECT COUNT(*) CNT,                            
                  MIN(K.GBN) GBN,                            
                  K.CHATROOMSEQ,                            
                  MIN(K.REGDT) REGDT,                                    
                  MIN(CHATROOMNAME) CHATROOMNAME,                            
                  MIN(G.ALARMYN) ALARMYN,                            
                  MIN(Z.USERID) USERID                            
           FROM   (SELECT D.GBN, D.CHATROOMSEQ, D.REGDT            
                   FROM   V2_CHATROOM D,                            
                          V2_CHATROOMUSER F                            
                   WHERE  D.DELYN = 'N'                            
                   AND    F.USERID = 'skytown@korea.kr'                            
                   AND    F.DELYN = 'N'                            
                   AND    D.CHATROOMSEQ = F.CHATROOMSEQ ) K,                            
                  V2_CHATROOMUSER Z,                            
                  (SELECT *                            
                   FROM   V2_CHATROOMNAME                            
                   WHERE  REGID = 'skytown@korea.kr' ) G                            
           WHERE  1=1                            
           AND    K.CHATROOMSEQ = Z.CHATROOMSEQ                            
           AND    K.CHATROOMSEQ = G.CHATROOMSEQ(+)                            
           AND    Z.DELYN = 'N'                            
           GROUP BY K.CHATROOMSEQ                            
           HAVING COUNT(*) = 1 ) X ,                            
          V2_USERPROFILE USERPROFILE,                            
          V2_PLUSFRIEND P                               
   WHERE  1 = 1                            
   AND    X.USERID = USERPROFILE.USERID                             
   AND    X.CHATROOMSEQ = P.CHATROOMSEQ(+)                             
 ) N 
  ORDER BY N.CHATROOMSEQ DESC, N.REGDT DESC;