Question for 10g enthusiasts

I just migrated a production DB from 9i to 10.2.0.3 (Solaris->RHEL Linux) and I am very impressed about the conveniences that 10g offers to DBAs.

After migration I found out that some SQL queries generated by the use of the application are being executed diferently depending on the DB account used. This is an Oracle based commercial application with its own client, no way to tune the text of SQL queries. I would like to force Oracle to use the better execution plan for all users.

Since the DB Control or Enterprise Manager is such a rich tool I thought that I could get it done easily but either I cannot navigate to the right place dealing with outlines, SQL profiles etc. or I am missing the clou.

I tried to invalidate the already existing execution plans by recomputing statistics of the queried tables, but that did not work (perhaps I should have rebuilt some index - flushing the SGA would be a pity, and could cause degradation for some time).

My cursor_sharing parameter is currently set to "similar" (was "exact" for a short period of time), all queries generated by the application use bind variables. I ran ADDM, used the Advisor and agreed to implement all proposed recommendations. The "SQL Tuning" function of the EM did not change the plan of the poor performing queries. My CBO is in all_rows mode (the preset first_rows mode leads to index abuse and never-ending queries).

Could someone give me a hint about how to use the DB Control to achieve this? Or lead me to some document describing the procedure? The crucial difference in the bad execution plan is that it goes for a full table scan of a very large table - but not for all users.

Thanks in advance,

Rick Denoire Received on Thu Nov 01 2007 - 20:31:57 CDT