By Franck Pachot
I’m currently following the session ‘Real-World Performance of Star and Snowflake Schemas’ with Michael Hallas, Andrew Holdsworth, John Clarke. It’s really a good presentation. the Real Performance team tries to spread messages about how to design for performance. But today I’ll not blog about what I’ve seen but about what I’ve not seen. Everybody talks about those nice 12c features that are useful in BI workloads, such as adaptive joins, adaptive parallel distribution, vector ‘in-memory’ aggregation, etc. Nobody talks about Adaptive Bitmap Pruning.
If you google for for it there is only one result (at least before my blog is published) which is the patent about all all those new CBO features that came in 220.127.116.11
And when I assist to a session that shows star transformation and execution plans with and without temporary table, I’m frustrated that I don’t see anything about that great feature that stabilizes our BI reports on star schemas. I’m preparing our tuning workshop (dates here – 10% discount if you book before the end of the year) and it’s something I can’t skip when talking about star transformation and bitmap indexes.
So let’s have a look to it. It you want a refresh about star transformation, please just wait about the next SOUG newsletter. But if you’re already familiar with it, this is for you.
Let’s have a look at an execution plan in 12c after running a query on a star schema. You have the same as in 11g except that we have that grayed ‘STATISTICS COLLECTOR’. Star transformation is good when the predicate is selective enough to filter few rows. Imagine that the cardinality estimation was wrong and most of FACT rows have the required value. This is what happened here, and the optimizer has chosen to stop iterating in that bitmap branch. It just ignores the predicate at that step and the join back to the dimension Cartesian join will filter it anyway.
If you check the execution plan with predicates you will see the predicate on dimension in the two table access.
Look at the end. When the statistics collector has seen that the threshold has been passed over, it has decided to skip that bitmap branch. This is Adaptive Bitmap Pruning. The bitmap branch is good only if it helps to filter a lot of rows. If it’s not the case, then it’s just an overhead and it is skipped, coming back – for that branch only – to the behavior we have when star transformation was disabled.
As with the other adaptive plans, the threshold is calculated at parse time.
See more details about the inflection point in a previous blog about Adaptive Joins