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@xxxxxxxx> 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

Other related posts: