Cursors Are Not Being Shared Due to 'PX_MISMATCH' in Standby Environment (Doc ID 1467417.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.7 to 11.2.0.2 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms


Environment:
 2-node RAC Active Data Guard (physical standby in open READ-ONLY mode and also running managed recovery)
 Includes 2-node RAC Primary

Some SQL run on the ADG standby are not being shared. Customer sees in v$sql_shared_cursor that reason is PX_MISMATCH.

From CURSORDUMP that was captured we see:

PARSING SQLTEXT=SELECT * FROM (SELECT guid, content_type_name, payload, payload_size, stage_id, delete_flag, comp_payload_size, comp_payload_code, MAX(stage_id) OVER (PARTITION BY guid) max_stage_num FROM wwlmetadoc23.CMS_CONTENT WHERE guid IN (:1,:2,:3,:4,:5,:6,:7 < snip >

STACK= .... Checking for already pinned child. fbcflg 108
STACK= .... No valid child pinned
STACK= .... Parent 0x11bf5ed7e0(0x11beeb9058) ready for search
STACK= .... kksSearchChildList outside while loop
STACK= .... kksSearchChildList: no suitable child found (hash_match=0)
kksfbc: child #0 is broken
STACK= .... Creating new child object #0
STACK= .... Compilation environment difference Failed sharing : 2000000000000
parallel_execution_enabled = true false
active_instance_count = 1 0
SQL pgadep:0 pgapls:0 user

This appeared to be similar to issue documented in BUG 7352775 'Many child cursors when PARALLEL_INSTANCE_GROUP set wrong' except:

 a) Ct has applied patch 7352775 on top of 11.1.0.7.2 in their environment
 b) Service has been confirmed exists in service$ (mdp08z.westlan.com for this particular env)
 c) Ct is not using PARALLEL_INSTANCE_GROUP parameter

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