At one recent engagement we ran into a problem with queries against some views running slow. The CBO didn't use complex view merging because this was turned off due to a 10G bug. This bug is documented in Metalink note 437376.1: “A query involving inline subqueries from mulitple tables whose columns are encrypted using Transparent Data Encryption [TDE] results in ORA-600 [qctVCO:csform]”. The Oracle recommended workaround, turn off complex view merging, had been implemented (_complex_view_merging=false) at this site.
We resolved this by turning back on complex view merging, and targeting the views suffering from this bug one by one. We added the /*+ no_merge */ hint to the view definition. Now most queries ran fast again, and the queries on the views suffering from the bug didn't fail with ora-00600.
Subscribe to:
Post Comments (Atom)
1 comment:
Excellent use of hints! Targeting just the statements that need it instead of turning off the feature for the entire database.
Post a Comment