Thursday, December 12, 2013

Problem with "with clause" queries after upgrade to 11.2.0.3

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;


11.2.0.3 optimizer

Query transformations (QT)
**************************
CBQT: copy not possible on query block SEL$10 (#0) because linked to with clause
CBQT bypassed for query block SEL$10 (#0): Cannot copy query block.
CBQT: Validity checks failed for 98utkpdkpq0af.


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: