OutOfMemoryException when retrieving rows from a large table

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
kiki
Posts: 7
Joined: Wed 23 Mar 2011 21:05

OutOfMemoryException when retrieving rows from a large table

Post by kiki » Wed 23 Mar 2011 21:50

Hello,

We are retrieving data from a large table by using DevArt.Oracle connector.

We got the OutOfMemoryException when retrieving more than 1000 rows from the table. We are wondering if there's a buffer limit to check to avoid such issue. Do you have any suggestion?

System.OutOfMemoryException
Exception of type 'System.OutOfMemoryException' was thrown.
Stack Trace:
at Devart.Data.Oracle.OracleDataReader.a()
at Devart.Data.Oracle.OracleDataReader.Read()


Here's the table DDL:

CREATE TABLE "SCALABILTY"."COLUMNS_113"
(
"WBIMP_ID" NUMBER(*,0) NOT NULL ENABLE,
"WBITRAN_ID" NUMBER(*,0) NOT NULL ENABLE,
"WBIMP_STATUS" VARCHAR2(40),
"WBIMP_MSG" VARCHAR2(4000),
"WBIMP_PROC_DATE" DATE,
"WBITRAN_FILENAME" VARCHAR2(256),
"WBITYP_NAME" VARCHAR2(40) NOT NULL ENABLE,
"WBIMP_RECNUM" NUMBER(*,0) NOT NULL ENABLE,
"A" VARCHAR2(1000),
"B" VARCHAR2(1000),
"C" VARCHAR2(1000),
"D" VARCHAR2(1000),
"E" VARCHAR2(1000),
"F" VARCHAR2(1000),
"G" VARCHAR2(1000),
"H" VARCHAR2(1000),
"I" VARCHAR2(1000),
"J" VARCHAR2(1000),
"K" VARCHAR2(1000),
"L" VARCHAR2(1000),
"M" VARCHAR2(1000),
"N" VARCHAR2(1000),
"O" VARCHAR2(1000),
"P" VARCHAR2(1000),
"Q" VARCHAR2(1000),
"R" VARCHAR2(1000),
"S" VARCHAR2(1000),
"T" VARCHAR2(1000),
"U" VARCHAR2(1000),
"V" VARCHAR2(1000),
"W" VARCHAR2(1000),
"X" VARCHAR2(1000),
"Y" VARCHAR2(1000),
"Z" VARCHAR2(1000),
"AA" VARCHAR2(1000),
"AB" VARCHAR2(1000),
"AC" VARCHAR2(1000),
"AD" VARCHAR2(1000),
"AE" VARCHAR2(1000),
"AF" VARCHAR2(1000),
"AG" VARCHAR2(1000),
"AH" VARCHAR2(1000),
"AI" VARCHAR2(1000),
"AJ" VARCHAR2(1000),
"AK" VARCHAR2(1000),
"AL" VARCHAR2(1000),
"AM" VARCHAR2(1000),
"AN" VARCHAR2(1000),
"AO" VARCHAR2(1000),
"AP" VARCHAR2(1000),
"AQ" VARCHAR2(1000),
"AR" VARCHAR2(1000),
"as" VARCHAR2(1000),
"AT" VARCHAR2(1000),
"AU" VARCHAR2(1000),
"AV" VARCHAR2(1000),
"AW" VARCHAR2(1000),
"AX" VARCHAR2(1000),
"AY" VARCHAR2(1000),
"AZ" VARCHAR2(1000),
"BA" VARCHAR2(1000),
"BB" VARCHAR2(1000),
"BC" VARCHAR2(1000),
"BD" VARCHAR2(1000),
"BE" VARCHAR2(1000),
"BF" VARCHAR2(1000),
"BG" VARCHAR2(1000),
"BH" VARCHAR2(1000),
"BI" VARCHAR2(1000),
"BJ" VARCHAR2(1000),
"BK" VARCHAR2(1000),
"BL" VARCHAR2(1000),
"BM" VARCHAR2(1000),
"BN" VARCHAR2(1000),
"BO" VARCHAR2(1000),
"BP" VARCHAR2(1000),
"BQ" VARCHAR2(1000),
"BR" VARCHAR2(1000),
"BS" VARCHAR2(1000),
"BT" VARCHAR2(1000),
"BU" VARCHAR2(1000),
"BV" VARCHAR2(1000),
"BW" VARCHAR2(1000),
"BX" VARCHAR2(1000),
"by" VARCHAR2(1000),
"BZ" VARCHAR2(1000),
"CA" VARCHAR2(1000),
"CB" VARCHAR2(1000),
"CC" VARCHAR2(1000),
"CD" VARCHAR2(1000),
"CE" VARCHAR2(1000),
"CF" VARCHAR2(1000),
"CG" VARCHAR2(1000),
"CH" VARCHAR2(1000),
"CI" VARCHAR2(1000),
"CJ" VARCHAR2(1000),
"CK" VARCHAR2(1000),
"CL" VARCHAR2(1000),
"CM" VARCHAR2(1000),
"CN" VARCHAR2(1000),
"CO" VARCHAR2(1000),
"CP" VARCHAR2(1000),
"CQ" VARCHAR2(1000),
"CR" VARCHAR2(1000),
"CS" VARCHAR2(1000),
"CT" VARCHAR2(1000),
"CU" VARCHAR2(1000),
"CV" VARCHAR2(1000),
"CW" VARCHAR2(1000),
"CX" VARCHAR2(1000),
"CY" VARCHAR2(1000),
"CZ" VARCHAR2(1000),
"CLIENT_ID" NUMBER(*,0)
DEFAULT -1 NOT NULL ENABLE,
PRIMARY KEY ("WBIMP_ID"))

Thanks!

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 25 Mar 2011 15:12

1. Are you using the OCI (via Oracle client) or Direct mode? If OCI, try Direct Mode. Does the problem persist in Direct Mode as well? If not, it can be the OCI problem - try another version of Oracle client.
2. Could you please describe your scenario? Are you using ORM (LinqConnect or Entity Framework)?
a) if you are working with OracleDataReader, try setting the FetchSize property of your OracleCommand explicitly;
b) if you are filling the OracleDataTable object, try the FillPage() method of the OracleDataAdapter object to control the amount of data you want to receive, and to call the Clear() method of the OracleDataTable object if you don't need its current content any more.
3. A small test project with the test data (DDL + DML) will be appreciated. You can send it to us via our contact form. Also please specify the versions of your dotConnect for Oracle (x.xx.xxx), Oracle server, and Oracle client.

kiki
Posts: 7
Joined: Wed 23 Mar 2011 21:05

Post by kiki » Mon 04 Apr 2011 20:07

We are using OCI mode and OracleDataReader, I tried set FetchSize = 100 and it works.

Thanks very much.

Post Reply