Physical SQLs of OBIEE are automatically generated by OBIEE. Though there are a few ways we could interfere with it, the process is complex, and may requires service down.

Using SQL Booster, we can tune an OBIEE SQL in short time, without restrarting any service.

This is a SQL Tuning case of tuning a OBIEE SQL in five minutes.

Problem

 We noticed there is an OBIEE SQL has been running for more than ten hours in our production. To identifiey an OBIEE SQL, we can check the SQL structure. It looks like this,

Troubleshooting

We actually tuned quick a few OBIEE SQLs already. The major problem is that Oracle optimizer did not generate a good execution plan, it might involed unnessary SQL transformation.

We can normally use SQL hint "materialize" to materialize a WITH subfactoring view. The benefit of doing this is that the Optimizer will treat WITH subfactoring view as physical table, it reduced much work of calculating the execution plan cost, consequently, will generate a more relatively optimized execution plan.

Tune the OBIEE SQL

As we mentioned, it's not easy to add SQL hints into the OBIEE physical SQL. While using SQLBooster, we can generate a SQL profile for the OBIEE SQL quickly.

We first run the hinted SQL, it was finished in 5 minutes (yes, 5 minutes V.S. 10+ hours...). The click "Edit" of the catched Outline data. Choose the original OBIEE SQL, apply the SQL Profile to the OBIEE SQL.

If your variables need different values, just click Cancel to input individual values for those variables.

Then we re-run the OBIEE report, it's finished in 5 minutes!