Persistent Pool TT table not used in all subqueries (Doc ID 2281910.1)

Last updated on JULY 06, 2017

Applies to:

Siebel Marketing - Version 8.1.1.14.11 [IP2014] and later
Information in this document applies to any platform.

Symptoms

On : OBIEE 11.1.1.7.x and 11.1.1.9.x

Even after enabling Persistent Pool that takes advantage of TT (temp) Tables, when attempting to run a complex Segment, the following error occurs.

ERROR
-----------------------
4h 42m 22s 358ms

Job request of type "WriteListFiles" failed.Error executing the list generation SQL. Error in executing cursor for WorkNode (Id:0)Odbc driver returned an error (SQLExecDirectW).State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43119] Query Failed:
[nQSError: 17001] Oracle Error code: 8103, message: ORA-08103: object no longer exists
at OCI call OCIStmtFetch.
[nQSError: 17012] Bulk fetch failed. (HY000)


In addition to the above, it was found that when running a Segment, there is a query generated with a huge IN clause. This was also generating the error ORA-03113: end-of-file on communication channel


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Using customer's RPD, create the Segment with the following Filters

"Marketing Segmentation Assets "."Consumer"."First Name" is not null
"Marketing Segmentation Assets"."Consumer"."Consumer Row ID" IN (SELECT "Contact"."Contact Row ID" from "Marketing Segmentation Opportunities" where "Opportunity"."Opened Date" >= TIMESTAMPADD(SQL_TSI_DAY, -200, CURRENT_DATE))
"Marketing Segmentation Assets"."Consumer"."Consumer Row ID" IN (SELECT "Siebel Campaign History"."Contact Id" from "Marketing Segmentation Targets" where "Siebel Campaign History"."Campaign Id" = '1-O2Q9'')

2. Run Update Counts

3. Check Physical Query:

....

minus
select D1.c1 as c1
from
  OBICOMMON1 D1
where ( D1.c1 in ('101521', '101523', '101525', '101527', '101529', '102520', '102524', '102531', '102537', '10275', '10278', '10281', '10691', '10693', '10702', '10710', '10711', '10715', '10747', '107545', '107547', '107549', '107551' .........................................
.........................................
.........................................
........................................

Error: ORA-03113: end-of-file on communication channel

 

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