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@umajor:~$ sql scott/tiger@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@xxxxxxxxx <mailto:vijaysehgal21@xxxxxxxxx>> 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@xxxxxxxxxxxxxxxx
<mailto:Jay.Miller@xxxxxxxxxxxxxxxx>> <Jay.Miller@xxxxxxxxxxxxxxxx
<mailto:Jay.Miller@xxxxxxxxxxxxxxxx>> 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
