query tuning tips

Database version - 9.2.0.8 EE

OS - AIX 5.3

SQL Text -

select    a14.PROD_SEASN_KEY PROD_SEASN_KEY,

    a15.HALF_IDNT HALF_IDNT,

    a12.DEPT_KEY DEPT_KEY,

    sum(a11.F_IVL_END_SOH_RTL_AMT) WJXBFS1

from    INV_VAL_SBC_LW_DM    a11,

    PROD_SBC_DM    a12,

    ORG_LOC_DM    a13,

    V_PROD_SBCLASS_SEASN_MTX_DM    a14,

    TIME_WK_DM    a15

where    a11.SBCLASS_KEY = a12.SBCLASS_KEY and

    ROWNUM = ROWNUM and

    a11.LOC_KEY = a13.LOC_KEY and

    a11.SBCLASS_KEY = a14.SBCLASS_KEY and

    a11.WK_IDNT = a15.WK_IDNT

and    (a11.WK_IDNT between (SELECT MIN(WK_IDNT) FROM TIME_DAY_DM WHERE HALF_IDNT IN (SELECT HALF_IDNT FROM TIME_DAY_DM WHERE DAY_DT = TRUNC(SYSDATE-7))) and (SELECT WK_IDNT FROM TIME_DAY_DM WHERE DAY_DT = TRUNC(SYSDATE-7))