RE: Slow connections with 12.2 client

I had posted a thread where a batch job that does a lot of round trips went from running in 3 minutes to running in 8 hours when we switched to a windows 12.2 client. We got that down to 30 minutes by increasing the arraysize. Despite still being much slower (and much slower than running the same report via jdbc) this was well within the SLA so we stopped looking further.

Jay Miller
Sr. Oracle DBA
201.369.8355

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Sescu Sent: Wednesday, May 30, 2018 12:53 AM
To: ORACLE-L
Subject: Re: Slow connections with 12.2 client

Hello Niall,

I observed quite some time ago, that sqlplus connections in combination with LDAP is getting slower and slower with every release. The reason for it, is that Oracle does more and more LDAP searches with every release.

With 10gR2, 1 LDAP search was sufficient, with 11gR2 you have already 2, and with 12cR1 and higher you got 3. For more information, that a look at:

https://blog<https://urldefense.proofpoint.com/v2/url?u=https-3A__blog&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=trbQSJgfIRv4CyeVq8huR0RpVAE6GN-PcT7tuJ1Jo0w&s=FD9CqiyYy1t_H9M2TiDRG35x1Kh5npF2iJ6AdA44B_A&e=>.dbi-services.com/<https://urldefense.proofpoint.com/v2/url?u=http-3A__services.com_&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=trbQSJgfIRv4CyeVq8huR0RpVAE6GN-PcT7tuJ1Jo0w&s=kS0mSqBqSP_eavymz387ifwOczj2F7oxnjYyX8vu37k&e=>oud-oracle-unified-directory-11-1-2-3-oracle-generates-more-and-more-ldap-lookups-with-every-release/

Unfortunately, there is no Patch or workaround that I am aware of, to fix this issue.

Cheers,
William

On Tue, May 29, 2018 at 6:25 PM, Stefan Knecht <knecht.stefan_at_gmail.com<mailto:knecht.stefan_at_gmail.com>> wrote: Not a windows guy at all, but have you tried tracing it? https://stackoverflow.com/questions/3847745/systrace-for-windows<https://urldefense.proofpoint.com/v2/url?u=https-3A__stackoverflow.com_questions_3847745_systrace-2Dfor-2Dwindows&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=trbQSJgfIRv4CyeVq8huR0RpVAE6GN-PcT7tuJ1Jo0w&s=TSM_QmjmMN1XzaEweAYm0avuFeKPIbJ-HkzI2VzdqG8&e=>

Might show you the syscall it's hanging on which would confirm if it's indeed some sort of lookup that hangs.

Stefan

On Tue, May 29, 2018 at 9:11 PM, Niall Litchfield <niall.litchfield_at_gmail.com<mailto:niall.litchfield_at_gmail.com>> wrote: Hi

Has anybody else observed initial connectivity with the 12.2 windows client to be significantly slower than in 12.1 (or 11.2). My setup has ldap and TNS_ADMIN involved.

12.1
ORACLE_HOME: C:\APP\ORACLE\INSTANTCLIENT_12_1 TNS_ADMIN: C:\APP\TNS 12.2
ORACLE_HOME: C:\APP\ORACLE\INSTANTCLIENT_12_2 TNS_ADMIN: C:\APP\TNS Extract from an admin level sqlnet trace in 12.1

(12180) [29-MAY-2018 14:53:56:171] nnflgetnlpactx: NLPA context successfuly initialized
(12180) [29-MAY-2018 14:53:56:172] nnflgetnlpactx: exit
(12180) [29-MAY-2018 14:53:56:517] nnfloidinfocache: entry
(12180) [29-MAY-2018 14:53:56:517] nnfloidinfocache: Info: Inserted value DIRECTORY_SERVER_TYPE=OID at index 0 into NLPA_CACHE

and from 12.2

(9712) [29-MAY-2018 14:47:01:777] nnflgetnlpactx: NLPA context successfuly initialized
(9712) [29-MAY-2018 14:47:01:777] nnflgetnlpactx: exit
(9712) [29-MAY-2018 14:47:38:195] nnfloidinfocache: entry
(9712) [29-MAY-2018 14:47:38:195] nnfloidinfocache: Info: Inserted value DIRECTORY_SERVER_TYPE=OID at index 0 into NLPA_CACHE

Note the extra 30 secs between exit from the nnflgetnlpactx function and entry to the OID related function nnfloidinfocache

I have a ticket with Oracle Support but wondered if anyone else has seen this. --
Niall Litchfield
Oracle DBA
http://www.orawin.info<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.orawin.info&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=trbQSJgfIRv4CyeVq8huR0RpVAE6GN-PcT7tuJ1Jo0w&s=1vluOx4DyPeaL209PTJlhBwGHRey8gSTektXV4nN0tg&e=>

--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework! Visit us at zztat.net<https://urldefense.proofpoint.com/v2/url?u=http-3A__zztat.net_&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=trbQSJgfIRv4CyeVq8huR0RpVAE6GN-PcT7tuJ1Jo0w&s=XN6sCdD4zc5A0knbeEs8-cjl6ZVwIM-yD9pjMCUSV6s&e=> | _at_zztat_oracle | fb.me/zztat<https://urldefense.proofpoint.com/v2/url?u=http-3A__fb.me_zztat&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=trbQSJgfIRv4CyeVq8huR0RpVAE6GN-PcT7tuJ1Jo0w&s=L3x4kkGW6dHrKwtJYwKRC8Di3WBKPrEbQBLN2u8vT2s&e=> | zztat.net/blog/<https://urldefense.proofpoint.com/v2/url?u=http-3A__zztat.net_blog_&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=trbQSJgfIRv4CyeVq8huR0RpVAE6GN-PcT7tuJ1Jo0w&s=LZ6V_aJMQqdNOQXSIggN5VHT-ZJTQx7mGtVNPUZU6g4&e=>

--
http://www.freelists.org/webpage/oracle-l