Re: 10gR2 Performance sux!
- From: MVR <yoursraju007@xxxxxxxxx>
- To: amit.poddar@xxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 14 Jun 2007 10:43:50 -0400
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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
