Memory usage for OracleDataReader

[jizhu233]

Hi,
I have an issue about memory usage for Devart.Data.Oracle.OracleDataReader.
I have a select sql which returns about 100 columns and 5000 rows. I use OracleDataReader to read this query result and put it into a set of object[]. The query result contains few data and most of columns are empty. But during the process, by creating memory dump, I found there are some byte[] in OracleDataReader which take about 800MB memory in total.
So I wonder is it necessary for OracleDataReader to spent such lot of memory to read such few data? Can the memory usage be reduced?
I’m using dotConnect for Oracle 8.4.254.0.

[Shalex]

I found there are some byte[] in OracleDataReader which take about 800MB memory in total

1. Please clarify which properties of OracleDataReader consume 800MB memory in total.
2. Does the issue persist with the newest (9.0.12) build of dotConnect for Oracle?
3. Send us a small test project with the corresponding DDL/DML script for reproducing.

[jizhu233]

I found there are some byte[] in OracleDataReader which take about 800MB memory in total

1. Please clarify which properties of OracleDataReader consume 800MB memory in total.
2. Does the issue persist with the newest (9.0.12) build of dotConnect for Oracle?
3. Send us a small test project with the corresponding DDL/DML script for reproducing.

Hi,
I have created a demo and sent this demo to contact form.

[Shalex]

Sorry but we did not receive your test project.

1. Was there the following message on the form after submitting your request?
"Thank you for your feedback. You will receive a reply shortly."

2. Please uploade a test project to our FTP server (a link is sent to the email specified in your forum profile).

[jizhu233]

Sorry but we did not receive your test project.

1. Was there the following message on the form after submitting your request?
"Thank you for your feedback. You will receive a reply shortly."

2. Please uploade a test project to our FTP server (a link is sent to the email specified in your forum profile).

1. When I submit the request, I select the option 'send a copy to my email', and I have received that copy email.
2. I have uploaded the demo to FTP server root path as a .zip file.

[Shalex]

I cannot restore the table data from your dump. Could you please send us the DML script?

[ericliu]

Hi Shalex,

Could you please show me the error when importing the dump.

The command to do the import is something like below:
imp user/pwd@db file=folder_path/demodata.dmp tables=version

[Shalex]

The first error was

IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'MIG_DATA' does not exist
Import terminated successfully with warnings.

Created MIG_DATA tablespace. The second try:

IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'MIG_LOB' does not exist
Import terminated successfully with warnings.

Created MIG_LOB tablespace. The third try:

[...]
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "VERSION" ENABLE CONSTRAINT "FK_VERSION_VALCTXT""
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "VERSION" ENABLE CONSTRAINT "FK_VERSION_RULETYPE""
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "VERSION" ENABLE CONSTRAINT "FK_VERSION_RULES""
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "VERSION" ENABLE CONSTRAINT "FK_VERSION_RULEPRIORITYTYPE""
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "VERSION" ENABLE CONSTRAINT "FK_VERSION_LAWCARD""
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "VERSION" ENABLE CONSTRAINT "FK_VERSION_ATTRIBUTE""
Import terminated successfully with warnings.

The "VERSION" table is now created with 36 rows.

I have just sent two screenshots to your email specified in the forum profile. There is no byte[] object type in the top of memory consumption with both (FetchSize=100 and FetchSize=0) scenarios using your project.

1. For the test purpose, please switch to the Direct mode. Is there any difference with memory usage?

2. All 36 records have NULLs in the XMLDEF field (CLOB) in the restored VERSION table in our environment. Is this correct?

[jizhu233]

Hi,
I have upload a sql in ftp root path named OracleDataReaderDemoData.sql. It can create table and records instead of previous dump.
In the table version, there are 36 records and each record's XMLDEF field is NULL.

[jizhu233]

1. Our product need many effort to change to direct mode. So we want to keep using OCI mode.

2. When I run the demo with fetchsize=0, it will take more than 170 MB (private working set) when read data. I have created a memory dump. Then I analyze this dump with Windbg:
----------------
0:000> !dumpheap -stat -min 10000
Statistics:
MT Count TotalSize Class Name
000007fe80cd58a0 1 65560 System.Object[][]
000007fedf60e7d8 7 142052208 System.Byte[]
Total 8 objects

It means most of memory are taken by Byte[] and there are 7 Byte[] instances which take 142 MB in total. Other class types take little memory.
-----------
0:000> !dumpheap -type System.Byte[] -min 10000
Address MT Size
0000000012801920 000007fedf60e7d8 1118544
0000000012912a90 000007fedf60e7d8 2237064
0000000012b34d38 000007fedf60e7d8 4474104
0000000012f79250 000007fedf60e7d8 8948184
0000000013801c48 000007fedf60e7d8 17896344
0000000014913000 000007fedf60e7d8 35792664
00000000233f1020 000007fedf60e7d8 71585304

Statistics:
MT Count TotalSize Class Name
000007fedf60e7d8 7 142052208 System.Byte[]
Total 7 objects

By this command we can see the detail of these 7 Byte[] instances. Their memory usage is a geometric sequence, each byte[] is twice larger than next byte[]. So I think they have some relationship.
------------
0:000> !gcroot 00000000233f1020
Thread 1aa4:
*** WARNING: Unable to verify checksum for ConsoleApplication1.exe
000000000015ee60 000007fe80df06ab DevartTest.DevartMemoryTest.LoadTable() [C:\OracleDataReaderDemo\Devart Test\Program.cs @ 41]
rbp-60: 000000000015eea0
-> 0000000002826c48 Devart.Data.Oracle.OracleDataReader
-> 00000000233f1020 System.Byte[]

HandleTable:
0000000000221798 (pinned handle)
-> 00000000233f1020 System.Byte[]

Found 2 unique roots (run '!GCRoot -all' to see all roots).

By this command we can see gcroot of the largest Byte[]. This Byte[](addr:00000000233f1020) belongs to OracleDataReader(addr:0000000002826c48). So I think OracleDataReader have a memory issue.

[jizhu233]

More information:
0:000> !do 0000000002826c48
Name: Devart.Data.Oracle.OracleDataReader
MethodTable: 000007fed85230f0
EEClass: 000007fed8200ac0
Size: 216(0xd8) bytes
File: C:\Windows\assembly\GAC_MSIL\Devart.Data.Oracle\8.4.254.0__09af7300eec23701\Devart.Data.Oracle.dll
Fields:
MT Field Offset Type VT Attr Value Name
000007fedf608b28 4000577 8 System.Object 0 instance 0000000000000000 __identity
000007fed9cbef88 40001f3 20 System.Int32 1 instance 0 a
000007fedf60d188 40001f4 10 ...ections.Hashtable 0 instance 0000000000000000 b
000007fedf62a2f0 40001f5 24 System.Boolean 1 instance 0 closed
000007fed9cd14f8 40001f6 18 ...em.Data.DataTable 0 instance 0000000000000000 schemaTable
000007fed851dc10 4000477 28 ....OracleConnection 0 instance 00000000027f3e48 b
000007fed8512b50 4000478 30 ....Data.Oracle.al[] 0 instance 0000000002826c28 c
000007fed8525570 4000479 38 ...rt.Data.Oracle.al 0 instance 0000000002826488 d
000007fedf60af70 400047a 98 System.Int32 1 instance 0 e
000007fedf60af70 400047b 9c System.Int32 1 instance -1 f
000007fedf60af70 400047c a0 System.Int32 1 instance 0 g
000007fedf62a2f0 400047d 25 System.Boolean 1 instance 0 h
000007fedf60af70 400047e a4 System.Int32 1 instance 1280 i
000007fedf60af70 400047f a8 System.Int32 1 instance 0 j
000007fed8525570 4000480 40 ...rt.Data.Oracle.al 0 instance 0000000002826488 k
000007fed8512be8 4000481 48 ....Data.Oracle.bt[] 0 instance 0000000002826d48 l
000007fed852b990 4000482 50 Devart.Data.Oracle.n 0 instance 000000000282c320 m
000007fedf60e7d8 4000483 58 System.Byte[] 0 instance 00000000233f1020 n
000007fedf60d188 4000484 60 ...ections.Hashtable 0 instance 0000000002e11df8 o
000007fedf608bc0 4000485 68 System.Object[] 0 instance 0000000003c93fb8 p
000007fedf60af70 4000486 ac System.Int32 1 instance 55926 q
000007fedf60af70 4000487 b0 System.Int32 1 instance 65321568 r
000007fedf60af70 4000488 b4 System.Int32 1 instance 1168 s
000007fedf60af70 4000489 b8 System.Int32 1 instance 1178 t
000007fedf60af70 400048a bc System.Int32 1 instance 4999 u
000007fedf60af70 400048b c0 System.Int32 1 instance 4989 v
000007fedf60af70 400048c c4 System.Int32 1 instance 1000 w
000007fedf62a2f0 400048d 26 System.Boolean 1 instance 1 x
000007fedf62a2f0 400048e 27 System.Boolean 1 instance 0 y
000007fed8512e70 400048f 70 ...t.Data.Oracle.v[] 0 instance 000000000282c340 z
000007fedf627280 4000490 78 ...ections.ArrayList 0 instance 0000000000000000 aa
000007fed8524b58 4000491 80 ...leSelectStatement 0 instance 0000000000000000 ab
000007fedf62a2f0 4000492 cc System.Boolean 1 instance 0 ac
000007fedf62a2f0 4000493 cd System.Boolean 1 instance 0 ad
000007fed85197f0 4000494 c8 System.Int32 1 instance 0 ae
000007fed8521cf8 4000495 88 ...MappingCollection 0 instance 0000000000000000 af
000007fedf608548 4000496 90 System.String 0 instance 0000000000000000 ag
000007fedf62a2f0 4000497 ce System.Boolean 1 instance 1 ah

This line: 000007fedf60e7d8 4000483 58 System.Byte[] 0 instance 00000000233f1020 n
It means this variable n(addr:00000000233f1020 ) is the Byte[] which take so much memory.
--------------------------------------
0:000> !do 00000000233f1020
Name: System.Byte[]
MethodTable: 000007fedf60e7d8
EEClass: 000007fedf013f08
Size: 71585304(0x4444e18) bytes
Array: Rank 1, Number of elements 71585280, Type Byte
Content: ............................DR02......................................................Demo Rule 02..............................
Fields:
None

It means this Byte[] contains 71585280 Bytes. The first several bytes are: ............................DR02......................................................Demo Rule 02..............................

[Shalex]

Thank you for the additional information. Please specify:
1) your current version of dotConnect for Oracle is 8.4.254, isn't it?
2) the version and capacity (x86 or x64) of your Oracle Client
3) the version and capacity of your Oracle Server

[jizhu233]

1) Yes, we are using dotConnect for Oracle 8.4.254
2) My oracle client is 11.2.0.3.0, 32bit
3) My oracle server is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

[Shalex]

I found there are some byte[] in OracleDataReader which take about 800MB memory in total

We have reproduced the issue and are investigating it. We will notify you about the result as soon as possible.

[jizhu233]

Hi, is there any update about this issue?