This was the issue identified when we upgraded the database from 11.2.0.2 to 11.2.0.3 the performance of entire database was impacted.This was a datawarehouse database and there were lots of queries generated from OBIEE using the "with clause".To workaround the issue quickly we switched back the optimizer_features_enable parameter to 11.2.0.2. Now it came to identifying the issue with 11.2.0.3 optimizer.We ran 10053 trace on the problem query which was not performing well with optimizer_features_enable set to 11.2.0.3 and 11.2.0.2 and compared the two traces.It was found that with 11.2.0.3 the CBQT (cost based query transformation ) was not getting successful causing the issue
.Later it was identified to be an issue with bug fix 11740670.To solve the issue we did following
Alter system set "_fix_control"='11740670:OFF';
Alter system set optimizer_features_enable=11.2.0.3;
.Later it was identified to be an issue with bug fix 11740670.To solve the issue we did following
Alter system set "_fix_control"='11740670:OFF';
Alter system set optimizer_features_enable=11.2.0.3;
11.2.0.3 optimizer
11.2.0.2 optimizer
Query transformations (QT)
|
|||||
**************************
|
|||||
JF: Checking validity of join
factorization for query block SEL$1 (#0)
|
|||||
JF: Bypassed: not a UNION or
UNION-ALL query block.
|
|||||
ST: not valid since new CBQT
star transformation parameter is FALSE
|
|||||
TE: Checking validity of table
expansion for query block SEL$1 (#0)
|
|||||
TE: Bypassed: table expansion
disabled.
|
|||||
JF: Checking validity of join
factorization for query block SEL$4 (#0)
|
|||||
JF: Bypassed: not a UNION or
UNION-ALL query block.
|
|||||
ST: not valid since new CBQT
star transformation parameter is FALSE
|
|||||
TE: Checking validity of table
expansion for query block SEL$4 (#0)
|
|||||
TE: Bypassed: table expansion
disabled.
|
|||||
CBQT: Validity checks passed for 98utkpdkpq0af.
|
No comments:
Post a Comment