Hi Upendra,
can you please send the raw extended SQL trace file? Once for the remote
execution and once for the local (on database server) execution?
Thank you.
P.S.: By the way you can also enable LOBPREFETCH in 12.2 SQL*Plus client now -
https://docs.oracle.com/database/122/SQPUG/SET-system-variable-summary.htm#SQPUG-GUID-57823713-2285-4A66-8802-B2A7ADF7E196
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Upendra nerilla <nupendra@xxxxxxxxxxx> hat am 15. September 2017 um 09:35--
geschrieben:
It is true that we are using LOBs, but the main question is, why does this
query runs faster when it is run directly on the DB server vs run through a
client?
Directly executing your query completed in 2.65 seconds. However when the
same query was executed through a sqlplus client (through sqlnet) it took 14+
seconds..
Is there a relationship between the LOB data and the latency we are seeing?
Here is the description of the function call:
FUNCTION GET_DESC_LIST RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DESC_ID NUMBER IN
FUNCTION GET_DESC_LIST RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DESC_ID NUMBER IN
P_CLIENT_ID NUMBER IN
@Peter, I put in the sqlnet.ora parameter, i didn't see any difference
between the executions.
-Upendra