OBIEE PERF_PREFER_INTERNAL_STITCH_JOIN: To stitch or not to stitch
Modern BI Reporting tools allow end users to put together reports, ad-hoc queries, dashboards with few mouse clicks, thus hiding the complexity of physical data model, design and architecture in their Implementations. It is quite naive to believe that the masked complexity would not impact performance, scalability or worst, the combination of performance and scalability on large volume databases. The tools haven't reached the state of perfection in generating the efficient and performing database queries. And most of them generate database SQLs, overloaded with redundant SQL sub-queries, unnecessary functions, repeating SQL patterns. It is also true that database engines have been improving, parsing more and more complex SQLs, but so far BI tools produce more and more complex generated SQLs at faster pace, compared to database SQL optimization additions in DB engines. Oracle has attempted to mitigate the impact by implementing more and more database features, enabled in RPD connection pools for each database and its versions. Let's look at one such database feature, PERF_PREFER_INTERNAL_STITCH_JOIN. I haven't been able to find any clear explanation how it works on the web, and Oracle doesn't provide much information either.
OBIEE generates a logical execution plan for each logical query (refer to pic 1 below for an example)
and attempts to break it into several logical sub-requests. Each sub-request is considered a separate unit, which then can be used for generating a separate physical SQL with PERF_PREFER_INTERNAL_STITCH_JOIN = OFF. This approach offers such obvious benefits as generating autonomous requests, which may run against different database sources, with results joined in BI Server, caching sub-request results, and attempting to break large requests into smaller physical SQLs running in the same database. In other words, PERF_PREFER_INTERNAL_STITCH_JOIN=OFF may result in larger number of smaller physical SQLs, produced by a single logical OBIEE request. With PERF_PREFER_INTERNAL_STITCH_JOIN=ON, OBIEE would attempt to merge all possible sub-requests into fewer physical SQLs. It doesn't mean that with STITCH_JOIN = 'ON' you always get just one physical SQL per database. Depending our logical RPD modeling, when sub-requests cannot be merged, you may not be able to reduce a number of physical SQLs with STITCH_JOIN = 'ON'.
What are pros and cons from using PERF_PREFER_INTERNAL_STITCH_JOIN?
- There will be fewer physical queries, created by OBIEE, pushing more processing to database, relying on optimizer to transform queries, and, if possible, generating more efficient plans
- BI Server would incur smaller workload, and scale to support more concurrent requests on its tier
- The biggest concern is that fewer generated physical SQLs would be much bigger to digest by the database. Oracle RDBMS may take significantly longer time to parse such requests, produce sub-optimal execution plans for more complex SQLs, take longer time to run them in database
- Obviously, you would not be able to take full advantage of sub-request caching for all possible sub-requests
- The number of physical SQLs could explode. A single SQL could be broken in 20-50 smaller SQLs, fired by OBIEE in parallel. That would put a lot of stress on heavily loaded environments, and might not scale well in a database
- There will be higher processing costs performed by BI Server, joining sub-request results on OBIEE tier, not database
As you can see, there is no simple solution, and the only way is to measure the impact by closely monitoring your BI environment, benchmarking the reports and analyzing the workload for both serial and concurrent executions. With Troype APE Diagnostics you can quickly generate very handy views of OBIEE concurrency for logical and physical SQLs (refer to Pic 2 for concurrency view example), map the workload to database, compare the runs by set of logical reports, and measure database impact from PERF_PREFER_INTERNAL_STITCH_JOIN setting in the integrated diagnostic suite.