OBIEE 12.2.1.3 - BI administration tool performance problem -- on 12CR1 database

 Here is a quick tip for a quick win. Especially for OBIEE users!

You may encounter performance problems while using BI administration tool , especially while importing metadata, on that wizard while selecting Metadata Types and all that..

We have seen that problem on an OBIEE 12.2.1.3 environment. BI admin tool version was also 12.2.1.3 and the database version was 12CR1.  This tool-specific performance problem started to be seen after the database upgrade. (in our case, after the DWH upgrade - 11gR2 to12CR1)

Client side was analyzed and there wasn't any problems there.

Traced the db session and saw that, it was active all the time, different queries one after another. Those queries were reading data from dictionary views like all_tab_columns and all_nested_tables..

Considered collecting fixed object stats and dictionary stats, but didn't do any of those, as the system was a very mission critical one, and performing that type of a statistics collection was not allowed. (especially at that point where we cannot predict whether collecting those stats will be our solution or will bring some new problems to the environment) ..

It was obvious that, import medata wizard wasn't producing very optimized SQL, or let's say the wizard wasn't producing the SQLs by considering the optimizer fixes and features in newer Oracle releases. Actually, this may also be a database related problem because we have the following note already in place in Oracle Support;

Query to Dictionary ALL_CONSTRAINTS Slow after Upgrade to 12.1.0.2 (Doc ID 2266016.1)

Using the wizard for this task is actually an optional way.. I mean, we can always do that metadata import manually, but in this case it was hard to do it in manual way, because there were several tables to be processed..

We didn't have the motivation to open a support ticket for this. That wizard was already optional and the problem was something in the middle between the tool and the database.. Besides, we were after a quick win..

Recently we dealt with a similar problem in a Oracle Discoverer environment.. There, the database was upgraded to 19C and the customer was facing dramatic performance problems almost in all discoverer reports. 

If you want to read that story, here is the link - > 

https://ermanarslan.blogspot.com/2020/12/ebs-122-rdbms-optimizerfeaturesenable.html 

In this case, too we did something similar.. We created an after logon trigger and by the help of that trigger, we made some optimizer related parameters automatically set for the BI admin tool sessions during the database login.  (note that, there wasn't any performance problems in ETL or OBIEE reports.)

This fixed the issue!

Here is the setting we've done inside our custom after logon trigger -> 

IF LOWER (v_program) LIKE ('%admintool.exe%')
THEN
EXECUTE IMMEDIATE 'alter session set optimizer_features_enable="11.2.0.4"';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_push_pred_cost_based" = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_squ_bottomup" = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_cost_based_transformation" = OFF';
END IF;

Ofcourse, this is not a supported solution, but currently (according to my research), it is the best thing we have :)

That is it. I hope you find this useful.