Can anyone optimize the following query, am stuck with this.

select /*+ first_rows */
files.APP_SHORT_NAME ,
cact.action_code,
dver.VERSION,
over.VERSION,
pver.TRANSLATION_LEVEL
prba.EXECUTED_FLAG,
bugs.BUG_NUMBER ,
from ad_files files,
ad_bugs bugs,
ad_patch_common_actions cact,
ad_file_versions pver,
ad_file_versions over,
ad_file_versions dver,
ad_patch_run_bug_actions prba,
ad_patch_run_bugs pbug
where pbug.PATCH_RUN_ID = 597
and pbug.PATCH_RUN_BUG_ID = prba.patch_run_bug_id
and bugs.bug_id = pbug.bug_id
and cact.COMMON_ACTION_ID = prba.COMMON_ACTION_ID
and files.FILE_ID = prba.FILE_ID
and pver.FILE_VERSION_ID(+) =prba.PATCH_FILE_VERSION_ID
and over.FILE_VERSION_ID(+) = prba.ONSITE_FILE_VERSION_ID
and dver.FILE_VERSION_ID(+) = prba.ONSITE_PKG_VERSION_IN_DB_ID
and rownum < 1001
order by bugs.BUG_NUMBER,
prba.EXECUTED_FLAG desc,
decode(cact.ACTION_CODE, 'libout', 1, 'copy', 2, 'forcecopy', 3, 'libin', 4,'makedir', 5, 'link', 6, 'jcopy', 7, 'sql', 8, 'exec', 8, 'exectier', 8, 'genfpll', 9, 'genmenu', 10, 'genform', 11, 'genrpll', 12, 'genrep', 13, 'gengpll', 14, 'genogd', 15, 'genmesg', 16, 'genwfmsg', 17, 100),
cact.NUMERIC_PHASE,
cact.NUMERIC_SUB_PHASE,
files.APP_SHORT_NAME,
files.SUBDIR,
files.FILENAME

All the tables are big (100k to 500k rows each).

I guess the ORDER BY clause is taking much time since the tables are big, but i don't have control over the db parameters (SORT_AREA_SIZE, ...).

The explain plan shows the following -

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=HINT: FIRST_ROWS 1 K 3251754
SORT ORDER BY 1 K 306 K 3251754
COUNT STOPKEY
NESTED LOOPS OUTER 378 K 113 M 3233963
NESTED LOOPS OUTER 378 K 99 M 2476719
NESTED LOOPS OUTER 378 K 85 M 1719475
NESTED LOOPS 378 K 71 M 962231
NESTED LOOPS 378 K 55 M 583609
NESTED LOOPS 378 K 21 M 204987
NESTED LOOPS 806 21 K 1069
VIEW index$_join$_008 806 11 K 263
HASH JOIN 806 11 K
INDEX RANGE SCAN AD_PATCH_RUN_BUGS_U2 806 11 K 25
INDEX FAST FULL SCAN AD_PATCH_RUN_BUGS_U1 806 11 K 25
TABLE ACCESS BY INDEX ROWID AD_BUGS 1 13 1
INDEX UNIQUE SCAN AD_BUGS_U1 1
TABLE ACCESS BY INDEX ROWID AD_PATCH_RUN_BUG_ACTIONS 470 15 K 253
INDEX RANGE SCAN AD_PATCH_RUN_BUG_ACTIONS_U2 978 6
TABLE ACCESS BY INDEX ROWID AD_PATCH_COMMON_ACTIONS 1 95 1
INDEX UNIQUE SCAN AD_PATCH_COMMON_ACTIONS_U1 1
TABLE ACCESS BY INDEX ROWID AD_FILES 1 42 1
INDEX UNIQUE SCAN AD_FILES_U1 1
TABLE ACCESS BY INDEX ROWID AD_FILE_VERSIONS 1 39 2
INDEX UNIQUE SCAN AD_FILE_VERSIONS_U1 1 1
TABLE ACCESS BY INDEX ROWID AD_FILE_VERSIONS 1 39 2
INDEX UNIQUE SCAN AD_FILE_VERSIONS_U1 1 1
TABLE ACCESS BY INDEX ROWID AD_FILE_VERSIONS 1 39 2
INDEX UNIQUE SCAN AD_FILE_VERSIONS_U1 1 1

Please help, am stuck with this for quite a while now !

thanks.