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

Last updated on AUGUST 23, 2022

Applies to:

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 <Table_Name> 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$ (<service_name> for this particular env)
 c) Ct is not using PARALLEL_INSTANCE_GROUP parameter

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.