ORA-4030[65560] [kxs-heap-w] [qesblFilter_seg] for parallel execution with bloom filter enabled. (Doc ID 2224802.1)

Last updated on JANUARY 19, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

1.Parallel execution of a query fails with ORA-4030[65560] [kxs-heap-w] [qesblFilter_seg] when bloom filter is enabled.

2.High allocations are noticed for "qesblFilter_seg":

=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------

*** 2016-09-29 14:26:30.899
50% 16 GB, 261728 chunks: "qesblFilter_seg " SQL --> 16gb
kxs-heap-w ds=0x2b653f471008 dsprt=0x2b653f44d740
50% 16 GB, 261165 chunks: "free memory "
top uga heap ds=0x2b653ef69340 dsprt=(nil)

3.Also the explain plan indicates that bloom filter are used:

============
Plan Table
============
--------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib | Pstart| Pstop |
--------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 3086K | | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 25M | 2453M | 3086K | 12:17:26 |:Q1000| P->S |QC (RANDOM)| | |
| 3 | PX PARTITION LIST ALL | | 25M | 2453M | 3086K | 12:17:26 |:Q1000| PCWC | | 1 | 2987 |
| 4 | HASH JOIN | | 25M | 2453M | 3086K | 12:17:26 |:Q1000| PCWP | | | |
| 5 | JOIN FILTER CREATE | :BF0000 | 81M | 5287M | 2344K | 09:20:09 |:Q1000| PCWP | | | |
| 6 | HASH JOIN | | 81M | 5287M | 2344K | 09:20:09 |:Q1000| PCWP | | | |
| 7 | TABLE ACCESS FULL | TEST | 578M | 14G | 814K | 03:14:23 |:Q1000| PCWP | | 1 | 2987 |
| 8 | TABLE ACCESS FULL | TEST | 81M | 3335M | 1003K | 04:60:41 |:Q1000| PCWP | | 1 | 2987 |
| 9 | JOIN FILTER USE | :BF0000 | 180M | 5749M | 445K | 02:46:16 |:Q1000| PCWP | | | |
| 10 | TABLE ACCESS FULL | TEST| 180M | 5749M | 445K | 02:46:16 |:Q1000| PCWP | | 1 | 2987 |
--------------------------------------------------------+-----------------------------------+-------------------------+---------------+

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms