Oracle Scratchpad

November 5, 2013


Filed under: Oracle,Parallel Execution,Partitioning — Jonathan Lewis @ 6:14 am BST Nov 5,2013

There an interesting example of a deadlock on the OTN database forum:

[Transaction Deadlock]

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
PS-00000001-00000011        92     423     S             33     128     S     X
BF-2ed08c01-00000000        33     128     S             92     423     S     X

One of the responses to the post points out that Oracle error ORA-00060 is an application error and the OP needs to fix his code – and that’s usually a valid comment, especially if the deadlock involves only TX enqueues, TM enquees or a mixture of both; but this deadlock is between a BF and a PS enqueue.

PS enqueue resources represent parallel execution slaves
BF enqueue resources represent Bloom Filters

The OP tells us that he’s getting this deadlock when running a parallel query (i.e. select, not insert, update, or delete). Parallel query is exactly the environment where parallel execution slaves get allocated and freed, and where parallel execution slaves and the query co-ordinator write and read to Bloom Filters.

You may want to read the entire thread to see where we got to – but the end user found an initial workaround to the problem by setting the hidden parameter “_bloom_pruning_enabled” to false. (The query involved a partitioned table, and partition pruning is one of the places where Bloom filtering can be used.)


Identified by Herald ten Dam as bug “12637294 – Deadlock of PS and BF locks during parallel query operations”. Patched in

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by