Slow displaying rows when one columns is a collection

[fidelfs]

Hi,

I am looking a simple query SELECT * FROM TABLE_COLLECTION;. It runs very slow compared at the same query using all columns but the collection one.

I ran the SELECT * FROM TABLE_COLLECTION; in sqlplus and runs fast as normal.

What can we do to improve the execution?

Slow with all fields including collection: 30.995 seconds. 400 rows.
All columns but the collection: 0.806 seconds 400 rows.

A user type used to define the collection field: Create or Replace noneditionable type tms_email as table of varchar2(150);

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 14\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 14 directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer 14).

 

[fidelfs]

Debug.txt file attached.
View attachment Debug.txt

 

[Marco Kalter]

Thanks. We can see where the delay is occurring and will try to enhance this.