Oracle performance tuning for ENOVIA – V6

While we are dealing with cutting-edge and hyped things like OpenCV, Microsoft LUIS and even MongoDB, it is almost tempting to forget about our bread and butter: Oracle.

I am not speaking about all those different products that Oracle Corp. as a company has created or acquired over the years, but their flagship product: fast, powerful and stable database, with dazzling array of built-in features.

Oracle database is famous for helping to survive in the most complicated circumstances. There is always a trick to make things work. Our team interacts with Oracle daily while supporting our ENOVIA – SMARTEAM and ENOVIA – V6 customers. Sometimes, when we get in trouble with really problematic performance problem and nothing conventional seems to work, instead of despairing, we turn to documentation, specialized internet forums and several Oracle gurus we are fortunate to know – and suddenly there is a sign of hope: a hidden system parameter change, or a hint for a very specific scenario.

Cartesian Nightmare

Now we shall speak about the case we had a few months ago while dealing with a certain ENOVIA – V6 setup. V6 has a very peculiar data model – it is very flexible, but at the cost of extremely heavy load on the database, for example, by using Cartesian join – too much for our personal taste.

Here is a very concise summary of what we had to do to address the Cartesian joins and some other related application design inefficiencies.

• alter system set optimizer_index_caching =99 scope=spfile;
• alter system set optimizer_index_cost_adj=1 scope=spfile;
• alter system set “_b_tree_bitmap_plans”=FALSE scope=spfile;
• alter system set “_optimizer_cartesian_enabled”=FALSE scope=spfile;

While we still had to deal with a lot of troubles in that project, including Java server setup,  static content etc etc, at least from the database perspective, we could sleep well.

Hope this small piece will help to someone else struggling with the V6 performance. And special thanks to Burleson Consulting articles one, two, three and Ask Tom for their great explanations on the subject.