[ODAC]: Very bad CLOB performance! (oracle-traces included)

[waeberd]

Dear ODAC-Team,

Using ODAC, I am currently hitting a serious performance problem with CLOBs in clientless NET-mode.

(I am using Delphi7 with the latest ODAC version)

Here are the details and I really hope that this is helpful.
Please have a look at these files:

http://waeberd.home.solnet.ch/daniel/od ... roblem.zip

* setup\"table_element.sql":
the sql script that sets up the table ELEMENT (containing 10 CLOB fields)

* setup\"table_element.pas":
the delphi part that reads this table
The select statement is:
"select * from element where element_id = :element_id"


For testing, the oracle connection is done over a (lousy) ISDN 115.2 kBit/s.
This is of course a very bad connection that accentuates the performance problem!

I am doing 10 select statements, which fetch 100 clob fields in total. The clob fields contain very little or no data ( normal behaviour

* folder "20050330_isdn_netmode": oracle trace using an NET connection over ISDN
=> very bad performance!


and maybe also useful:
* folder "20050330_server_netmode": oracle trace using a NET connection on the server directly
=> excellent performance


The problem is described in the "20050330_isdn_netmode" folder.
My questions:
* Why does the dbms_lob calls last that long?
* The EXECUTE times are very bad, the FETCH times are excellent. Why?
* Why is there such a big difference between the OCI and the NET version?

Is that a problem in ODAC?
Or can I optimize my delphi-access routine?

Any hint is VERY welcome, as I am quite stuck here!

Thank you very much & please ask if you need more information!

Best regards,

Daniel

[Paul]

Yes, working with LOBs in ODAC with Net option is slower then in OCI.
ODAC Net option and OCI use the different methods of working with LOBs. And we cannot increase its performance to OCI. In any case we will review this algorithm in the next build.

ODAC reads LOBs from Oracle server immediately after executing if
OraSession.Options.DeferredLobRead=False and
OraSession.Options.CacheLobs=True. You can set
OraSession.Options.DeferredLobRead=True to read LOB at the time you need its
value.

[waeberd]

Thanks Paul!

ODAC reads LOBs from Oracle server immediately after executing if
OraSession.Options.DeferredLobRead=False and
OraSession.Options.CacheLobs=True. You can set
OraSession.Options.DeferredLobRead=True to read LOB at the time you need its
value.

in my odac version, these properties are defined on the TOraDataSet.
Will this be set globally in TOraSession?

Regards,

Daniel

[Alex]

Sorry, it's our mistake, You need to replace OraSession in quoted code to any descendant of TOraDataSet, e.g.

ODAC reads LOBs from Oracle server immediately after executing if
OraQuery.Options.DeferredLobRead=False and
OraQuery.Options.CacheLobs=True. You can set
OraQuery.Options.DeferredLobRead=True to read LOB at the time you need its
value.