BookmarkSubscribeRSS Feed
bdbsas
Calcite | Level 5

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

 

 

7 REPLIES 7
SASKiwi
PROC Star

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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
Fluorite | Level 6

Hey Guyz,

 

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

 

Thanks in advance.

 

regards,

sandeep

osmelbrito
Obsidian | Level 7

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
Tourmaline | Level 20

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.

 

 

 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4931 views
  • 0 likes
  • 5 in conversation