Parallel Query With Virtual Private Database Returns Wrong Result or Fails With ORA-600[kzrtgpp - kxfxslavesql] (Doc ID 755975.1)

Last updated on NOVEMBER 10, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
This problem can occur on any platform.

Symptoms

 

When using both
1. parallel query execution
2. VPD (FGAC) predicates attached to a query.

 

a query can either
1. fail with
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kzrtgpp - kxfxSlaveSQL], [], [], [], [], [], [], []
Errorstack:
kzrtgpp <- kzrtpci <- kglsscn <- kqlsscn <- kkmfcblo
<- kkmpfcbk <- qcsprfro <- qcsprfro_tree <- qcsprfro_tree <- qcspafq
<- qcspqbDescendents <- 3a0 <- qcspqb <- kkmdrv <- opiSem
<- opiDeferredSem <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild
<- kxsGetRuntimeLock <- 810 <- kksfbc <- opiexe <- kpoal8
<- opiodr <- kpoodr <- upirtrc <- kpurcsc <- kpuexecv8
<- kpuexec <- OCIStmtExecute <- kxfxsStmtExecute <- kxfxsExecute <- kxfxsp
<- kxfxmai <- kxfprdp <- opirip <- opidrv <- sou2o
<- opimai_real <- main <- start

or
2. yield wrong results.
The issue does not reproduce in 11g.

Example of wrong results situation:

-- The parallel query returns 0 rows with FGAC applied.


   SQL> SELECT /*+ PARALLEL(MAP,32) */ COUNT(*)
      2  FROM   lg_rollups_mapping_global_bak MAP,
      3         lg_se_account_dim se_ac,
      4         lg_advertiser_dim ad,
      5         lg_company_dim co
      6  WHERE  se_accnt_sid IN (7286)
      7  AND    se_ac.rollup_sid = MAP.se_accnt_sid
      8  AND    ad.rollup_sid = MAP.advertiser_sid
      9  AND    co.rollup_sid = MAP.company_sid;
    
      COUNT(*)
    ----------
             0
    
    1 row selected.


-- The same parallel query returns expected results if FGAC removed.

    SQL> SELECT /*+ PARALLEL(MAP,32) */ COUNT(*)
      2  FROM   lg_rollups_mapping_global_bak MAP,
      3         lg_se_account_dim se_ac,
      4         lg_advertiser_dim ad,
      5         lg_company_dim co
      6  WHERE  se_accnt_sid IN (7286)
      7  AND    se_ac.rollup_sid = MAP.se_accnt_sid
      8  AND    ad.rollup_sid = MAP.advertiser_sid
      9  AND    co.rollup_sid = MAP.company_sid;
    
      COUNT(*)
    ----------
            11
    
    1 row selected.


-- By keeping FGAC applied, the query works fine serially without parallel hint
    SQL> SELECT COUNT(*)
      2  FROM   lg_rollups_mapping_global_bak MAP,
      3         lg_se_account_dim se_ac,
      4         lg_advertiser_dim ad,
      5         lg_company_dim co
      6  WHERE  se_accnt_sid IN (7286)
      7  AND    se_ac.rollup_sid = MAP.se_accnt_sid
      8  AND    ad.rollup_sid = MAP.advertiser_sid
      9  AND    co.rollup_sid = MAP.company_sid;
    
      COUNT(*)
    ----------
            11
    
    1 row selected.

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