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

With Oracle 12.2, there is another client, known as SQLcli. It doesn't support rowprefetch and maxdata is obsolete. Here is what I use for SQLcli:

• Added by M.Gogala set linesize 80 set serveroutput off set timing on set trimout on set trimspool on set pagesize 999 set arraysize 64 set timing on define _editor="vi" column table_name format a30 wrap column owner format a15 column object_name format a45 wrap set termout off alter session set NLS_DATE_FORMAT='YYYYMMDD'; set termout on set sqlformat ansiconsole

The last option ("sqlformat ansiconsole") will throw an error with sqlplus. The last version of SQLcli (18.1.1) messes up output, when spooled, so I'm considering removing it altogether. Funny thing is that whatever number. SQLCli also doesn't support statement cache, it always sets it to 100. This is what you get when you set statement cache in the login.sql:

mgogala_at_umajor:~$ sql scott/tiger_at_test122

SQLcl: Release 18.1.1 Production on Sat May 12 17:10:00 2018]\

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat May 12 2018 17:10:00 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

_*Statement Caching set to 100*_
SQL> I tried with all round numbers (32,64 and 128) and the result is always the same: 100. Other than that, I find SQLcli behaving better than sqlplus. I don't need to set anything except the array size and it performs as well as sqlplus or even better.

On 05/10/2018 12:21 PM, Andy Sayer wrote:
> Assuming by 12c you mean 12.2.
> This would be an ideal time to try out sqlplus fast mode
> https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/release-changes.html#GUID-476F5AFD-231A-47BD-8A75-5E5EA27896E3
>
> Sqlplus -f
>
> Hope that helps,
> Andrew
>
> On Thu, 10 May 2018 at 16:37, Vijay Sehgal <vijaysehgal21_at_gmail.com
> <mailto:vijaysehgal21_at_gmail.com>> wrote:
>
> Jay,
>
> not sure if you already checked arraysize settings between old and new
>
> Regards,
> Vijay Sehgal
>
> On May 10, 2018, at 11:28 AM, <Jay.Miller_at_tdameritrade.com
> <mailto:Jay.Miller_at_tdameritrade.com>> <Jay.Miller_at_tdameritrade.com
> <mailto:Jay.Miller_at_tdameritrade.com>> wrote:
>
>> 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
>>

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


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