Optimize Oracle SQL which has multiple sub-queries

Question

I have an Oracle SQL query which has multiple sub-queries in it. The query gives correct data but it takes 15 - 20 minutes to execute. After reading some answers from links below:

1. https://dba.stackexchange.com/questions/19605/forcing-oracle-to-use-hash-join-for-a-subquery
2. Optimize performance of sub-queries

I tried to optimize the query using GLOBAL TEMPORARY TABLE but it seems I do not have privilege to CREATE command.

I would like to ask if anyone of you knows any tool or technique to optimize the performance of the query below:

This query is used to generated admission numbers for each campus and programmes.

SELECT DISTINCT STVCAMP_DESC "CAMPUS",
STVDEGC_CODE || ' - ' ||STVDEGC_DESC "PROGRAMME",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
) "REC",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'AD'
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'AD'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "TOTAL ADMITTED",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'AD'
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'AD'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE 
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND SARADAP_ADMT_CODE = '01'

AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "NEWLY ADMITTED",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'AD'
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'AD'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND SARADAP_ADMT_CODE = '03'

AND TO_DATE(TO_CHAR(SARAPPD_APDC_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "ADMITTED RESUMING",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'H'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "HOLD",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'I'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "INCOMPLETE",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'P'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "PENDING USP RESULTS",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'W'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "COUNSELLING REQUIRED",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'C'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "AWAITING ASSESSMENT",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'D'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "DECISION MADE-NOT AD",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'U3'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'U3'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "REJECTED",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'L2'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'L2'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "OL GENERATED"

FROM STVCAMP,STVDEGC,
(SELECT DISTINCT SARADAP_DEGC_CODE_1 FROM SARADAP A WHERE A.SARADAP_TERM_CODE_ENTRY = 201403 ) PROG
WHERE STVCAMP_CODE NOT IN ('X','F','P','W')

AND STVDEGC_CODE = PROG.SARADAP_DEGC_CODE_1

ORDER BY 1,2

Answer 1

what immediately caught my eye are these date conversions:

TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY')

if there are indexes on these date columns, the database will not be able to use them because of these conversions. Why are they needed at all? Get rid of them.