Pipelined I/O thread did not timely terminate

[bdbsas]

Getting this message intermittently on queries in SAS 9.4.  SAS support was only able to say the oracle connection was lost, but we don't see anything in the Oracle logs and I'm getting it against two different Oracle databases, one 11g another 12c.  Both are 12c oracle clients.  Sometimes the program will hang, other times it completes and the data looks ok.  Anyone ever seen the message:

ORACLE: Pipelined I/O thread did not timely terminate

ORACLE: Pipelined I/O thread terminated on 2nd attempt

[SASKiwi]

What sort of SAS queries are you running on Oracle? SAS SQL, SQL passthru, DATA step, SAS procedure?

Have you tried running queries with SAS diagnostics switched on:

options sastrace = ',,,d' SASTRACELOC = SASLOG;

One possible explanation might be intermittent network issues. I suggest you get your IT network folks to run a network trace on your database connections to see if they can spot anything. 

[bdbsas]

It's happening on a variety of proc sql statements, some are very simple.  It's only happening during our heavy processing window overnight.  The database server is under a heavy load at the time.  But it's the same database server our 9.2 environment uses and doesn't produce the error.

[SASKiwi]

I suggest you try the diagnostics and also provide SAS Tech Support with this. I think a network trace might also be helpful as it may pick up the dropped connections and provide more evidence to SAS Tech Support as to where the problem lies. 

[bdbsas]

Thanks.  I forgot to mention I did the trace and it didn't add anything useful, we know it's not the particular queries and there was not new information on the oracle connection.  I've asked our DBA to set up logging on the oracle client side.  And he's going to look at network.  I will also open a new Track.

[samgautam007]

Hey Guyz,

is there a way to produce error if such a message occur in logs?

Thanks in advance.

regards,

sandeep

[osmelbrito]

I'm have got this message recently

I used the option trace referred in this post and I got this 

ORACLE_1: Prepared: on connection 11 1 1562878422 no_name 0 SQL (2) 
SELECT * FROM LDC.CLIENTE_TERCERO 2 1562878422 no_name 0 SQL (2) 
  3 1562878422 no_name 0 SQL (2) 
  4 1562878422 no_name 0 SQL (2) 
ORACLE_2: Prepared: on connection 11 5 1562878422 no_name 0 SQL (2) 
SELECT * FROM LDC.CT_SEGMENTACION 6 1562878422 no_name 0 SQL (2) 
  7 1562878422 no_name 0 SQL (2) 
  8 1562878422 no_name 0 SQL (2) 
ORACLE_3: Prepared: on connection 11 9 1562878422 no_name 0 SQL (2) 
SELECT  NULL as TABLE_QUALIFIER,  t.owner as TABLE_OWNER,  t.table_name as TABLE_NAME,  t.num_rows as ROW_COUNT  FROM  all_tables t 
WHERE  t.table_name='CLIENTE_TERCERO' AND  t.owner='LDC'  10 1562878422 no_name 0 SQL (2) 
  11 1562878422 no_name 0 SQL (2) 
  12 1562878422 no_name 0 SQL (2) 
8                                                          The SAS System                              16:50 Thursday, July 11, 2019

ORACLE_4: Executed: on connection 11 13 1562878422 no_name 0 SQL (2) 
SELECT statement  ORACLE_3 14 1562878422 no_name 0 SQL (2) 
  15 1562878422 no_name 0 SQL (2) 
  16 1562878422 no_name 0 SQL (2) 
ORACLE_5: Prepared: on connection 11 17 1562878422 no_name 0 SQL (2) 
SELECT  NULL as TABLE_QUALIFIER,  t.owner as TABLE_OWNER,  t.table_name as TABLE_NAME,  t.num_rows as ROW_COUNT  FROM  all_tables t 
WHERE  t.table_name='CT_SEGMENTACION' AND  t.owner='LDC'  18 1562878422 no_name 0 SQL (2) 
  19 1562878422 no_name 0 SQL (2) 
  20 1562878422 no_name 0 SQL (2) 
ORACLE_6: Executed: on connection 11 21 1562878422 no_name 0 SQL (2) 
SELECT statement  ORACLE_5 22 1562878422 no_name 0 SQL (2) 
  23 1562878422 no_name 0 SQL (2) 
  24 1562878422 no_name 0 SQL (2) 
ORACLE_7: Prepared: on connection 11 25 1562878422 no_name 0 SQL (2) 
SELECT  "TIPOIDENT", "IDENT", "SEGMENTACION", "IDCLIENTE" FROM LDC.CLIENTE_TERCERO  WHERE  ( ("TIPOIDENT" NOT  IN  ( 'I' , 'J' ) ) 
) 26 1562878422 no_name 0 SQL (2) 
  27 1562878422 no_name 0 SQL (2) 
  28 1562878422 no_name 0 SQL (2) 
ORACLE_8: Executed: on connection 11 29 1562878422 no_name 0 SQL (2) 
SELECT statement  ORACLE_7 30 1562878422 no_name 0 SQL (2) 
  31 1562878422 no_name 0 SQL (2) 
  32 1562878422 no_name 0 SQL (2) 
ORACLE_9: Prepared: on connection 11 33 1562878422 no_name 0 SQL (2) 
SELECT  "DESCSEGMENTACION", "CODSEGMENTACION" FROM LDC.CT_SEGMENTACION  34 1562878422 no_name 0 SQL (2) 
  35 1562878422 no_name 0 SQL (2) 
  36 1562878422 no_name 0 SQL (2) 
ORACLE_10: Executed: on connection 11 37 1562878422 no_name 0 SQL (2) 
SELECT statement  ORACLE_9 38 1562878422 no_name 0 SQL (2) 
  39 1562878422 no_name 0 SQL (2) 
ORACLE: Pipelined I/O thread did not timely terminate

ORACLE: Pipelined I/O thread terminated on 2nd attempt

  40 1562878424 no_name 0 SQL (2) 
ORACLE_11: Prepared: on connection 11 41 1562878424 no_name 0 SQL (2) 
SELECT  "TIPOIDENT", "IDENT", "SEGMENTACION", "IDCLIENTE" FROM LDC.CLIENTE_TERCERO  WHERE  ( ( ("TIPOIDENT" NOT  IN  ( 'I' , 'J' ) 
) ) AND  ( ("SEGMENTACION" IN  ( '00' , '01' , '02' , '03' , '04' , '05' , '06' , '11' , '13' , '15' , '17' , '50' , '51' , '52' , 
'53' , '54' , '55' , '56' , '88' , '99' ) ) ) ) 42 1562878424 no_name 0 SQL (2) 
  43 1562878424 no_name 0 SQL (2) 
  44 1562878424 no_name 0 SQL (2) 
ORACLE_12: Executed: on connection 11 45 1562878424 no_name 0 SQL (2) 
SELECT statement  ORACLE_11 46 1562878424 no_name 0 SQL (2) 
  47 1562878424 no_name 0 SQL (2)

[ChrisNZ]

Old thread, but since there was no solution, and since I could fix this when it happened to me, here is my take.

My query looked like this:

proc sql outobs=1 ;
  connect using ORALIB;
  select * from connection to ORALIB ( ... );

The log displayed:

ORACLE_326: Prepared: on connection 0
select * from TABLE 
 
ORACLE_327: Executed: on connection 0
SELECT statement  ORACLE_326
 
WARNING: Statement terminated early due to OUTOBS=1 option.
ORACLE: Pipelined I/O thread did not timely terminate

ORACLE: Pipelined I/O thread terminated on 2nd attempt

Because I used both

- the OUTOBS= option

- SQL pass-through

the connection to oracle seems to get interrupted and Oracle complains.

Only when these 2 features are used does the message appear.

Replacing the OUTOBS= option with a filter on ROWNUM in the Oracle where clause removed the Oracle messages.