My Oracle Support Banner

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

Last updated on FEBRUARY 19, 2019

Applies to:

Oracle Database - 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

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.


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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


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