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 18.104.22.168 to 22.214.171.124 [Release 11.1 to 11.2]
Information in this document applies to any platform.
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 126.96.36.199.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
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