SQL & PL/SQL

Hi All,

      Posting the entire query which is pretty slow. if I remove the partition and order by clause, relatively fast.

select

          aa.*,

          FIRST_VALUE(aa.doc_status_rank) OVER (PARTITION BY aa.person_id ORDER BY aa.appl_status_rank ASC, aa.doc_status_rank ASC) person_doc_rank,

          FIRST_VALUE(aa.doc_status_rank) OVER (PARTITION BY aa.person_id, aa.application_number ORDER BY aa.appl_status_rank ASC, aa.doc_status_rank ASC ) appln_doc_rank --UAP# 343 Get the person application document status by application and the documents ranks.

        from APPLY_APPLIC_APPL_STATUS_V aa

        where exists ( /* filter application with current agent */

          select 1 from agent_person_appl apa

          where aa.person_id              = apa.person_id

            AND aa.admission_appl_number  = apa.admission_appl_number

            AND CASE WHEN v_query.appl_active_ind = 'Y' and NVL(aa.absolute_val, aa.alias_val) > sysdate

                        THEN 1

                     WHEN v_query.appl_active_ind = 'N'

                        THEN 1

                     ELSE 0

                 END = 1

Please advise how to go about tuning. The environment has no setup for DBMS_PROFILER.