delay selecting CLOB and BLOB values

[Worker]

Is there a way to make SQL Windows smarter about BLOB and CLOB columns? Currently selecting them is very slow. I frequently have to rewrite sql to select TO_CHAR(clob_col) instead because it's so much faster.

Is it possible to delay fetching the value until the "..." is clicked?

 

[Marco Kalter]

This is currently not possible. All LOB data is fetched immediately when the row is fetched.

 

[Worker]

But isn't the LOB data fetched in a separate call? debugsql output for a simple CLOB-column query shows the query, a describe, LOB.Length(), then LOB.Read(). Could the LOB.Read be done later when the user presses "..."?

Also (and sorry, I'm snooping into your code a bit -- I just want this to work as well as possible because it impacts me), comparing debugsql output with a WireShark packet dump I noticed something odd:

debugsql:
TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
WireShark:
Looks right. I see the query go out and a response come back.

debugsql:
TOracleQuery $033CFD00 Start Query.Execute
SQL = select user_id,notes from app_user where user_id=1000023
WireShark:
Looks right. I see the query go out and a response come back. However, the response appears to include the contents of the CLOB column ("notes") which I didn't expect.

debugsql:
TOracleQuery SQLForm.TestQuery Start Query.Describe
SQL = SELECT * FROM app_user
WireShark:
Looks right. I see the query go out and a response come back with column names.

debugsql:
TLOBLocator Start LOB.Length
WireShark:
This looks odd. What goes out appears to include the contents of the CLOB, as apparently received above. Then the response also contains that content again, which is unexpected for a "length" call.

debugsql:
TLOBLocator Start LOB.Read(546)
WireShark:
This looks odd too. What goes out again includes the contents of the CLOB. The response now contains that same content twice.

debugsql:
TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
WireShark:
This looks right again.

Is there a good reason why the CLOB content appears to be sent back and forth multiple times, or is this a bug? Because if it's a bug, it would explain a lot of the slowness and fixing it might make me happy

 

[Marco Kalter]

The data is not sent back and forth multiple times. It is fetched exactly once:

1. The record is fetched (no CLOB data is transferred).
2. The length of the CLOB is determined (no CLOB data is transferred).
3. The CLOB data is retrieved.

 

[T-Gergely]

You've found a bug in the OCI client implementation. Mine (11.2 instant) does the same. Good luck having that fixed by Oracle!

 

[Worker]

Are you positive that during step 1 only a LOB locator is returned and not the value of the CLOB? Because this doesn't seem to happen with BLOBs, nor with the *cough*competing*cough*product from *cough*oracle.

 

[T-Gergely]

Are you positive that during step 1 only a LOB locator is returned and not the value of the CLOB? Because this doesn't seem to happen with BLOBs, nor with the *cough*competing*cough*product from *cough*oracle.

Yes, this is what PSD does: first it fetches 100 rows (less if there's not as much left, obviously), no matter how few rows are actually displayed. Only the locators are fetched for CLOB and BLOB columns so far. Then it reads LOBs for the displayed rows.

BTW, I just checked: your reported problem disappeared for me. But I have a newer machine, newer OS (Win7 x64), different OCI client (not instant), and we have applied PSUs, too.

 

[Worker]

Thanks for checking. If it's not a PL/SQL Developer bug then I won't worry about it.

The other program I looked at appeared to get all the data in a single fetch (fetching LOB data instead of locators) which speeds things up due to reduced latency. If PL/SQL Developer insists on getting the data anyway, getting it in multiple trips via the locator seems inefficient.

Although getting a locator and holding off on getting the data until "..." is clicked seems even better.

 

[T-Gergely]

Using LOB locators may increase latency, but should not matter much unless the length of the LOB columns are very small. As PSD fetches 100 rows at a time, those packets aren't "fragmented". And then when OCILobReads are executed, one LOB usually takes more packets anyway. Still, PSD should either show the (partial) contents of the LOBs or should not read them until clicked. I'd rather it let us configure this behavior.

 

[sky_lt]

Hi,

I can not figure out why my pl/sql developer performs different
on my several computers.

PL/SQL developer version on both computers is 10.0.5.1710

But on one computer it runs queries with clobs quickly while on the
other it lags:

First computer(fast):
* Oracle enterprice 11.2.0.1.0
* OCI VERSION 11.1

Second computer(slow):
* Oracle enterprice 11.2.0.4.0
* OCI VERSION 11.1

Is this behaviour can be configured on developer?

Any suggestions?

Thanks

Skylt

 

[Hilarion]

Are you connecting to the same DB and with the same user? It looks a bit like the "wm_concat" function in first case is returning VARCHAR2 and CLOB in the second case (the function WAS changed from VARCHAR2 to CLOB somewhere between Oracle version - that's one of the reasons one should not use "wm_concat" - try "listagg" if it's available on your version of DB).

 

[sky_lt]

Yes the databases are different.
Oracle enterprise 11.2.0.4.0 and Oracle enterprise 11.2.0.1.0

For me it seems like PL/SQL developer is lagging on displaying
results with clob/blob datatypes, as i have never had such issues with earlier PL/SQL developer releases.

BTW the other tool "oracle sql developer" shows wm_concat like varchar2 on both databases.

Sky_lt

 

[Worker]

I believe PL/SQL Developer should be enhanced to, for CLOB columns, select all of:

* the lob locator as normal
* substr(clob, 1, 4000)
* length(clob)

then if and only if the length is greater than 4000, use OCILobRead to get the rest of the lob data.

(I'm assuming PL/SQL Developer already knows the column datatypes before it does the select. Maybe that's a bad assumption.)