just look for biggest costs at the execution plan. in your case for example, line 19.
|* 19 | TABLE ACCESS FULL | XXADM_APPLICANT_COURSPREFS_TBL | 1 | 212 | 5724 | 241 (2)| 210 |00:00:00.01 | 879 | | | |
there is a full table access. you should check that table and access methods. why it is accessed by full? does it really be a full table or is there a missing indexes for example.
This was just a place-holder for a zip file with a run-time execution plan that the OP was having trouble attaching to the original thread: tkprof Analysis
The tablescan is probably not the significant problem, by the way - look at where the time goes.
thanks Jonathan, I didn't know about the original thread. I've just looked this one and all A-Time was 1sec. so I just aimed the highest cost