I am trying to help a user tune a very intersting query presented by a
user.
Currently runs in about 7 seconds.

SELECT * FROM ((SELECT DISTINCT APPOINTMENT_DTE AS "ApptDate",
AW.START_TIME as "StartTime", AW.END_TIME as "EndTime", A.LEVEL_NBR as
"ApptWindow", A.SEQ_NBR as "ApptSequence" FROM UPI.AR_AVAILABILITY A,
APPOINTMENT_WINDOW AW WHERE APPOINTMENT_DTE BETWEEN '20040129' AND
'20040428' AND A.LEVEL_NBR = AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR
AND AW.LEVEL_NBR IN ('03','06') AND A.MIN_AVAIL >=
SOS_PKG.GETORDERDURATION('3FAA0', 'COFF' ) AND A.GEO_AREA_CD = '3FAA0'
AND A.JOB_GROUP = 'FTECH' UNION (SELECT DISTINCT APPOINTMENT_DTE,
AW.START_TIME, AW.END_TIME, A.LEVEL_NBR, A.SEQ_NBR FROM
UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW WHERE APPOINTMENT_DTE
BETWEEN '20040129' AND '20040428' AND A.LEVEL_NBR = AW.LEVEL_NBR AND
A.SEQ_NBR = AW.SEQ_NBR AND AW.LEVEL_NBR IN ( '03','06' ) AND
A.MIN_AVAIL >= SOS_PKG.GETORDERDURATION('3FAA0', 'COFF') AND
A.GEO_AREA_CD = '3F000' AND A.JOB_GROUP = 'FTECH' MINUS SELECT
DISTINCT APPOINTMENT_DTE, AW.START_TIME, AW.END_TIME, A.LEVEL_NBR,
A.SEQ_NBR FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW WHERE
APPOINTMENT_DTE BETWEEN '20040129' AND '20040428' AND A.LEVEL_NBR =
AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR AND A.GEO_AREA_CD = '3FAA0'
AND AW.LEVEL_NBR IN ('03','06'))) MINUS SELECT DISTINCT
APPOINTMENT_DTE, AW.START_TIME, AW.END_TIME, A.LEVEL_NBR, A.SEQ_NBR
FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW WHERE
APPOINTMENT_DTE = TO_CHAR(SYSDATE,'YYYYMMDD') AND A.LEVEL_NBR =
AW.LEVEL_NBR AND A.SEQ_NBR = AW.SEQ_NBR AND AW.LEVEL_NBR IN
('03','06') AND (CAL_ETIME_MIN -
(TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))*60 +
TO_NUMBER(TO_CHAR(SYSDATE,'MI'))) < '120') AND GEO_AREA_CD = '3F000'
)AGG WHERE ROWNUM <= '10' ORDER BY 1 ASC,2 ASC,3 ASC


ps$oracle@ADD1> @explainer2

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM<=10)
8 - filter("A"."MIN_AVAIL">="AGLCUST"."SOS_PKG"."GETOR DERDURATION"('3FAA0','COFF'))
11 - access("AW"."LEVEL_NBR"=6)
12 - access("A"."APPOINTMENT_DTE">='20040129' AND
"A"."JOB_GROUP"='FTECH' AND
"A"."GEO_AREA_CD"='3FAA0' AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND "A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."APPOINTMENT_DTE"<='20040428')
filter("A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."GEO_AREA_CD"='3FAA0' AND "A"."JOB_GROUP"='FTECH')
13 - filter("A"."MIN_AVAIL">="AGLCUST"."SOS_PKG"."GETOR DERDURATION"('3FAA0','COFF'))
16 - access("AW"."LEVEL_NBR"=3)
17 - access("A"."APPOINTMENT_DTE">='20040129' AND
"A"."JOB_GROUP"='FTECH' AND
"A"."GEO_AREA_CD"='3FAA0' AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND "A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."APPOINTMENT_DTE"<='20040428')
filter("A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."GEO_AREA_CD"='3FAA0' AND "A"."JOB_GROUP"='FTECH')
21 - filter("A"."MIN_AVAIL">="AGLCUST"."SOS_PKG"."GETOR DERDURATION"('3FAA0','COFF'))
24 - access("AW"."LEVEL_NBR"=6)
25 - access("A"."APPOINTMENT_DTE">='20040129' AND
"A"."JOB_GROUP"='FTECH' AND
"A"."GEO_AREA_CD"='3F000' AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND "A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."APPOINTMENT_DTE"<='20040428')
filter("A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."GEO_AREA_CD"='3F000' AND "A"."JOB_GROUP"='FTECH')
26 - filter("A"."MIN_AVAIL">="AGLCUST"."SOS_PKG"."GETOR DERDURATION"('3FAA0','COFF'))
29 - access("AW"."LEVEL_NBR"=3)
30 - access("A"."APPOINTMENT_DTE">='20040129' AND
"A"."JOB_GROUP"='FTECH' AND
"A"."GEO_AREA_CD"='3F000' AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND "A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."APPOINTMENT_DTE"<='20040428')
filter("A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."GEO_AREA_CD"='3F000' AND "A"."JOB_GROUP"='FTECH')
35 - access("AW"."LEVEL_NBR"=6)
36 - access("A"."APPOINTMENT_DTE">='20040129' AND
"A"."GEO_AREA_CD"='3FAA0' AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."APPOINTMENT_DTE"<='20040428')
filter("A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."GEO_AREA_CD"='3FAA0')
39 - access("AW"."LEVEL_NBR"=3)
40 - access("A"."APPOINTMENT_DTE">='20040129' AND
"A"."GEO_AREA_CD"='3FAA0' AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."APPOINTMENT_DTE"<='20040428')
filter("A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."GEO_AREA_CD"='3FAA0')
44 - filter("AW"."CAL_ETIME_MIN"-(TO_NUMBER(TO_CHAR(SYSDATE@!,'HH24'))*60+TO_NUMBER (TO_CHAR(SYSDAT
E@!,'MI')))<120)
45 - access("AW"."LEVEL_NBR"=6)
46 - access("A"."APPOINTMENT_DTE"=TO_CHAR(SYSDATE@!,'YY YYMMDD') AND
"A"."GEO_AREA_CD"='3F000' AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."SEQ_NBR"="AW"."SEQ_NBR")
filter("A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."GEO_AREA_CD"='3F000')
48 - filter("AW"."CAL_ETIME_MIN"-(TO_NUMBER(TO_CHAR(SYSDATE@!,'HH24'))*60+TO_NUMBER (TO_CHAR(SYSDAT
E@!,'MI')))<120)
49 - access("AW"."LEVEL_NBR"=3)
50 - access("A"."APPOINTMENT_DTE"=TO_CHAR(SYSDATE@!,'YY YYMMDD') AND
"A"."GEO_AREA_CD"='3F000' AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."SEQ_NBR"="AW"."SEQ_NBR")
filter("A"."SEQ_NBR"="AW"."SEQ_NBR" AND
"A"."LEVEL_NBR"="AW"."LEVEL_NBR" AND
"A"."GEO_AREA_CD"='3F000')

Note: rule based optimization

117 rows selected.