Re: 10gR2 Performance sux!

Guys,

Well, I could not user opt_param.. as I can't touch SQLs, they come from 3rd party tool. So, I have created an after login trigger to set this parameter at session level.

I guess I have identified the problem. T_PLAN_XYZ is the huge table, size ~3Gig and 5.5 Millions of rows. Oracle 9i was joining two indexes with AND-EQUAL and its NOT happening in 10g. If I set optimizer_features_enable to 9.0.1 at session level in 10g, it uses AND-EQUAL and query rocks. Im not even happy with this OFE setting too. Some of the other queries DO NOT use AND-EQUAL even if I set optimizer_features_enable to 9.0.1. I found that AND-EQUAL is no more in 10g as a hint.. but we dont use hints either in 9i or 10g..optimizer to pick AND-EQUAL. There are so many reports with the same conditions.. I cannot touch the SQL. I tried creating various combination of indexes, but none of them could be as good as AND-EQUAL of two indexes..

Best plan:

|* 13 |             TABLE ACCESS BY INDEX ROWID      | T_PLAN_XYZ
|  14 |              AND-EQUAL                       |
         |       |       |
|* 15 |               INDEX RANGE SCAN               |
NU_T_PLAN_X_DR_ORG_ID  |       |
|* 16 |               INDEX RANGE SCAN               | XT_PLAN_X_PLAN_ID      |

Worst plan:
|* 17 |             TABLE ACCESS FULL              | T_PLAN_XYZ

Sounds like this is very common problem after upgrading to 10g. Has anyone got insights ?

Thank you so much!

On 6/14/07, amit poddar <amit.poddar_at_yale.edu> wrote:
>
> Can you set any parameter the way you mentioned ?
> If yes than does thatn mean opt_param is not required at all ?
>
> amit
>
> Christian Antognini wrote:
> Amit
>
>
>
> don't you mean /*+ opt_param('optimizer_features_enabled',
> '9.0.1') */
>
> ?
>
> No. I meant what I wrote, i.e.: /*+ optimizer_features_enable('9.0.1')
> */
>
> Also notice that the name of the parameter in your example is wrong.
> There's no "d"...
>
>
> HTH
> Chris
>
>
>
>
>

-- 
"Happy people plan actions, they don't plan results."
--
http://www.freelists.org/webpage/oracle-l