likely glogin.sql and/or login.sql didn't get moved over. To prevent
additional surprises, you probably want to hunt those down.
Several past threads show how to trace the client to find all the possible
places it looks and Tim Gorman's paper at #C18LV included a very concise
list for the similar for sqlnet.ora,
which is also a danger point to cause subtle changes in migrations. (At
least I think it was Tim's paper, what a whirlwind of content #C18LV was!)
Of the optional configuration files, those are the top three where Oracle
mostly looks just fine for the "works, doesn't work" test, but where
substantive behavioral changes may lurk due to omission.
I've misplaced my laundry list, since I've only been optimizing performance
for a while now.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Thursday, May 10, 2018 4:57 PM
To: tkiernan@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Poor report performance with sqlnet connection only, not jdbc
Arraysize did it!
Thanks,
Jay Miller
Sr. Oracle DBA
From: TJ Kiernan [mailto:tkiernan@xxxxxxxxxxxx] ;
Sent: Thursday, May 10, 2018 11:37 AM
To: Miller, Jay; oracle-l@xxxxxxxxxxxxx
Cc: TJ Kiernan
Subject: RE: Poor report performance with sqlnet connection only, not jdbc
Arraysize on the client? Did the old client have a login.sql file setting
this sort of thing?
HTH,
T. J.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Thursday, May 10, 2018 10:28 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Poor report performance with sqlnet connection only, not jdbc
Here's an oddity. One of my app owners recently moved to a new reporting
server. The old one had the 11g client on linux, the new one has the 12c
client on windows.
He has a number of reports automated to run through sqlplus and spool to a
file. After the move a report that previously took only minutes to run took
8 hours. On the database side it shows all the extra time spent on sqlnet
message to/from client.
My first thought was that it was caused by network latency from the new
location. traceroute/tracert did show a few more hops and a few more ms.
However, when he tested running the same query through jdbc rather than
sqlnet and spooling the output to the same location the report returned in
minutes again.
My next thought was to increase the sdu setting to the maximum on the client
and database sqlnet.ora but that made no difference.
Before we bite the bullet and rewrite the reporting automation to use jdbc
does anyone have suggestions for anything else to try?
Database version is 11.2.0.3
Thanks!
Jay Miller
Sr. Oracle DBA
CONFIDENTIALITY NOTICE: This message may contain confidential information,
including Protected Health Information as defined under the Health Insurance
Portability and Accountability Act of 1996, intended only for the use of the
individual or entity identified above. If the receiver of this message is
not the intended recipient, you are hereby notified that any dissemination,
distribution, use or copying of this message is strictly prohibited. If you
have received this message in error, please immediately notify the sender by
replying to his/her e-mail address noted above and delete the original
message, including any attachments. Thank you.
