SQL & PL/SQL

Hi All -

Siebel sales application that I support generates its own sql query and runs the following alter session statements:

alter session set optimizer_mode = first_rows_10;
alter session set hash_join_enabled = false;
alter session set "_optimizer_sortmerge_join_enabled"=false;
alter session set "_optimizer_join_sel_sanity_check"=true;

When I run the explain plan for the sql query, I find that there are numerous tables involved for a single view (say My account) and that the explain plan has lots of nested loops (outer and inner). Almost all the join columns either have a unique or non-unique index - and there is no full table scan in any of the execution path. And the query produces results in 10secs first time and the second time I execute the same query, the results are out in 600msecs. But the users are complaining that its taking a long time. What is the best option. Since the explain plan is huge, I dont know if I could post it here. Cost of the outermost nested loop is 44. Wherever the index unique row id or index range scan is performed that step has a cost of either 1 or 2. # of rows is 10/11 (owing to using First_Rows_10???).

Note: Statistics are collected on a weekly basis.

Apologize if you cant proceed with the minimum info I have given here. Appreciate your suggestions.