Re: Poor report performance with sqlnet connection only, not jdbc

I have also encountered problems with the database migrations. Since then, I always set SQLPATH to specific directory which doesn't change when the database is migrated. This is my personal setting:

mgogala_at_umajor:~$ echo $SQLPATH
/home/mgogala/misc/SQL
mgogala_at_umajor:~$

Your setting will probably be different, but should not point into the $ORACLE_HOME tree, since the oracle home is changed with migration.

On 05/11/2018 09:20 AM, Mark W. Farnham wrote:
>
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of
> *Jay.Miller_at_tdameritrade.com
> *Sent:* Thursday, May 10, 2018 4:57 PM
> *To:* tkiernan_at_castiarx.com; oracle-l_at_freelists.org
> *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_at_castiarx.com]
> *Sent:* Thursday, May 10, 2018 11:37 AM
> *To:* Miller, Jay; oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of
> *Jay.Miller_at_tdameritrade.com <mailto:Jay.Miller_at_tdameritrade.com>
> *Sent:* Thursday, May 10, 2018 10:28 AM
> *To:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *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.
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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