Before I really get started discussing seriously about the subject matter, I would like to shared and echo what
Coskan's had said at his blog.
"Every Oracle version, developers at Oracle add or change some of the
undocumented parameters to do better optimization. Most of the time
these new optimizations
works fine but from time to time they have a negative effect for the
generated plan which causes post upgrade slowness. When you change OFE
(optimizer_features_enable) from 10.2.0.4 to 11.2.0.1 Oracle changes value of 33 parameters !!!
Because they are undocumented, unless an Oracle Scientist reveals what
they are actually doing or note on MOS explaining the parameter, their effects to the plans are not that clear." (although a bit older stuff, still worth reading his blog entry about Plan Stability Through Upgrade).
First and foremost, it is indeed a big disappointment (at least for ourselves) the way Oracle 11gR2 CBO behave/manage things with regards to ENCRYPTED columns in contrast to 10gR2 CBO. We really had enough surprises post 11gR2 upgrade with most of our business critical databases where ENCRYPTED column tables involved in a query. The queries in the context had terrible performance degradation and it was totally unacceptable. Since there was no suitable workaround great help from the Oracle Support, we decided to DECRYPT the columns temporary to fall back to 10gR2 behavior.
Couple of days ago, I have been notified by one of the application support guys about the wired situation of some queries, where the queries run OK at first run and fail in another run to produce the results. When I simulated the test case over non-production database, the session constantly terminating with following errors:
ERROR at line 20:
ORA-03113: end-of-file on communication channel
Process ID: 12560
Session ID: 104 Serial number: 21213
Additionally, an
ORA- 07445 error also written in the alert.log.
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1200000000] [PC:0x4000000006320C80, evaopn3()+384] [flags: 0x0, count: 1]
Errors in file /u00/app/oracle/diag/rdbms/pmsit/DBXX/trace/DBXX_ora_12560.trc (incident=48362):
ORA-07445: exception encountered: core dump [evaopn3()+384] [SIGSEGV] [ADDR:0x1200000000] [PC:0x4000000006320C80] [Address not mapped to object] []
Incident details in: /u00/app/oracle/diag/rdbms/pmsit/DBXX/incident/incdir_48362/DBXX_ora_12560_i48362.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Jun 04 16:37:55 2012
Dumping diagnostic data in directory=[cdmp_20120604163755], requested by (instance=1, osid=12560), summary=[incident=48362].
Mon Jun 04 16:37:59 2012
Sweep [inc][48362]: completed
Sweep [inc2][48362]: completed
After a very quick initial analysis, found MOS Note "ORA-7445 (evaopn3) [ID 860969.1], that describes the cause as one of the BUG due to ENCRYPTED columns. It was confirmed after looking at the table structure, we indeed have ENCRYPTED columns. The MOS Note explained that the fix would be in version 12 and also have the following suggestion:
"_optimizer_join_factorization"=false
I then googled about the parameter in the question and come across several great posts from many people, including Oracle CBO blog. Although the join factorization (JF) appears to be a great improvement for the queries with UNION ALL and with SELF/INNER JOIN conditions, where it could drastically improve the query performance, unfortunately, we had to cut if OFF due to column ENCRYPTION.. The query went well after turning the feature OFF.
I have couple more interesting topics to discuss in my next blog entries.
Till then, happy reading.
