Bloom filters and parallel execution

Today I want to share with you another interesting example of solving performance problem which happened in the database used for Business Intelligence purposes. Users were complaining that the performance in kind of staging database (STGDB) was much better than the production one (PRDDB), even though the dataset was exactly the same.

Also hardware resources and initialisation parameters were quite similar, apart from major difference in Dynamic Sampling setting:

“Slow” database – PRDDB

optimizer_dynamic_sampling = 2 (default)

“Fast” database – STGDB

optimizer_dynamic_sampling = 0

Keeping this in mind, I’ve checked test query execution elapsed times in both databases, which indeed showed quite significant difference:

STGDB – around 30 seconds
PRDDB – around 2 minutes 15 seconds

Another problem was with object statistics – some of them were missing, but this was being followed up already in order to design the best strategy of collecting the statistics after data loading/partition exchange operations. Quick check showed that for this particular query, EXPLAIN PLAN took around 15 seconds on PRDDB and almost nothing in STGDB. After switching off dynamic sampling in PRDDB, the plan wasn’t changed and query execution elapsed time was about 2 minutes, which perfectly matched previously measured parsing/dynamic sampling timing. It was still around 4 times slower, so clearly it was not the root cause of the problem.

Comparison of query plans showed that STGDB used Bloom filter which was not the case in PRDDB. In order to find the reason of this difference, I’ve enabled basic 10053 tracing:

SQL> alter session set events '10053 trace name context forever, level 1';

Searching through generated trace files for “bloom” string, showed interesting results.

PRDDB

*********************************
 Number of join permutations tried: 2
 *********************************
 Consider using bloom filter between ALIAS1[TABLE1] and ALIAS2[TABLE2] with ??
 kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because distribution method
 Enumerating distribution method (advanced)
 --- Distribution method for
 join between ALIAS1[TABLE1](parallel) and ALIAS2[TABLE2](serial); jm = 1; right side access path = TableScan

 [...]

 Consider using bloom filter between ALIAS1[TABLE1] and ALIAS2[TABLE2] with VALUE
 kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because Partially Parallel Plan
 Skew handling disabled due to parameter
 --- final distr:VALUE

STGDB

*********************************
Number of join permutations tried: 2
*********************************
Consider using bloom filter between ALIAS1[TABLE1] and ALIAS2[TABLE2] with ??
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because distribution method
Enumerating distribution method (advanced)
--- Distribution method for
join between ALIAS1[TABLE1](parallel) and ALIAS2[TABLE2](parallel); jm = 1; right side access path = TableScan

[...]

Consider using bloom filter between ALIAS1[TABLE1] and ALIAS2[TABLE2] with BROADCAST-LEFT

[...]

kkoBloomFilter: join ndv:19282 reduction:0.004388 (limit:0.500000) accepted
Skew handling disabled due to parameter
--- final distr:BROADCAST-LEFT

So, the root cause was in different setting of parallel degree for involved tables – it was set to 1 on PRDDB for the second table, that’s why Bloom filter has been rejected with Partially Parallel Plan reason. After setting parallel degree to the same value as on STGDB, I was able to achieve the same timing for this query on both databases.

SQL> alter table OWNER.TABLE2 parallel 4;
 
Table altered.

If you are interested in reading more about Bloom filters and parallel execution, I strongly recommend presentation of Toon Koppelaars, which I had a pleasure to attend during Trivadis TechEvent in September 2017:

http://tkoppela.blogspot.com/2015/12/bloom-filter-presentation.html

Advertisements

2 thoughts on “Bloom filters and parallel execution”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s