in our application we are fetching User-Defined Types through Cursors like this:
Code: Select all
Select Test_Type(empno, hiredate) from emp
Code: Select all
Select empno, hiredate from emp
This is the result of our tests with different fetch sizes in Oci mode and in Direct mode:
FetchSize: 50000
Oci UDT: 0.75 s
Oci No UDT: 0.11 s
Direct UDT: 0.51 s
Direct No UDT: 0.12 s
FetchSize: 250000
Oci UDT: 3.57 s
Oci No UDT: 0.51 s
Direct UDT: 2.51 s
Direct No UDT: 0.66 s
FetchSize: 1250000
Oci UDT: 16.74 s
Oci No UDT: 3.06 s
Direct UDT: 12.21 s
Direct No UDT: 3.23 s
As you can see, the performance differs by factor 4 (Direct mode) to 6 (Oci mode).
Do you have any suggestions? We are using Oracle 11.2.0.3.0 and dotConnect for Oracle 8.3.115
Thanks,
Christoph Löffler