사용중인 쿼리문은

SELECT PT1.*, IS_LOCATION(CARPARTCODE) AS PART_LOCATION FROM (
            SELECT
                ROW_NUMBER() OVER(ORDER BY a.ITEM_SEQ DESC) RN,
                a.ITEM_SEQ, a.ITEM_CODE, a.COM_SEQ, a.ERP_CODE, a.PRODUCTNM,a.CARYYYY, a.GRADE, a.USER_PRICE, a.SUPPLIER_PRICE, a.SALE_PRICE, a.INQUIRY_YN,
                a.FEE_YN, a.BESTYN, a.EVENTYN, a.NEWYN, a.PUBLICYN, a.RECOMMYN, a.SALEYN, a.PLANYN, a.STOCK_NUM,
                a.CARMAKERSEQ, a.CARMODELSEQ, a.CARGRADESEQ, a.PART1, a.PART2, a.PART3, a.COLOR,
                a.REG_DT,a.DISCOUNT_RATE, a.CARPARTCODE, 
                (SELECT MAKERNM FROM V_IS_CARMAKER WHERE a.CARMAKERSEQ=CARMAKERSEQ) AS MAKERNM,
                (SELECT CARMODELNM FROM V_IS_CARMODEL WHERE a.CARMODELSEQ=CARMODELSEQ) AS CARMODELNM,
                (SELECT CARGRADENM FROM V_IS_CARGRADE WHERE a.CARGRADESEQ=CARGRADESEQ) AS CARGRADENM,
                (SELECT CODENM FROM V_IS_CODEMST WHERE a.PART1=CODENO) AS PART1_NM,
                (SELECT CODENM FROM V_IS_CODEMST WHERE a.PART2=CODENO) AS PART2_NM,
                (SELECT DISTINCT PARTNM FROM V_IS_CARPART WHERE DELYN='N' AND a.PART3=CARPARTSEQ) AS PART3_NM,
                (SELECT CODE_NM FROM MC_COMMON_CODE WHERE CODE_GROUP_SEQ = '37' AND a.COLOR=CODE) AS COLOR_NM,
                DECODE(a.APPROVAL, 'Y', '승인', '비승인') as APPROVAL,
                ('/upload/board/'||b.YYYY||'/'||b.MM||'/'||b.UUID) AS THUMB,
                b.ATTACH_NM,
                c.COM_NM, c.STAFF_TEL,
                (SELECT DONG_NM FROM NSH_DONG WHERE c.SIDO_CD=SIDO AND c.SIGUNGU_CD=SIGUNGU AND DONG='000') as SIGUNGU_NM
            FROM NSH_GOODS a LEFT OUTER JOIN (
                SELECT
                    bb.*
                FROM (SELECT max(uuid) as uuid FROM NSH_ATTACH GROUP BY TABLE_NM, TABLE_SEQ) aa LEFT OUTER JOIN NSH_ATTACH bb
                ON aa.UUID = bb.UUID
            ) b
            ON b.TABLE_NM='NSH_GOODS' AND a.ITEM_SEQ = b.TABLE_SEQ
            LEFT OUTER JOIN NSH_COOPERATION c
            ON a.COM_SEQ = c.SEQ
            JOIN V_IS_CARMAKER d
            ON a.CARMAKERSEQ=d.CARMAKERSEQ

WHERE a.DEL_YN='N'
            AND a.GUBUN = nvl(#{gubun}, '1')

            AND (INSTR(a.PRODUCTNM,#{search_all_text}) > 0 

            OR INSTR(a.SEARCH_TAG,#{search_all_text}) > 0 
            OR a.ITEM_CODE = #{search_all_text}
            OR INSTR(e.PARTALIAS,#{search_all_text}) > 0)

        ) PT1
        WHERE CEIL(RN/NVL(10,10))=NVL(1,1)

쿼리 검색시 속도가 너무 느려서 그런데 뭐가 문제일까요..... 초보자라 어떻게 설명할수가 없네요

답변좀 부탁드립니다.