Connection To Sqlplus Slower In 19c, than in version 11g, On AIX (Doc ID 2891054.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

Connection to DBs on SQL*PLUS 19c version is slower than on version 11g.

Since customer upgraded a DB from 11g to 19c, customer jobs take more time and we have identified the latency is not in the queries but in the connection itself to the DB.

Also it appears to be platform specific as well, as we`ve seen noticeable differences between 19c on Linux and 19c on AIX.

Considering the two scripts:

cat sqlnolog.sh

------------------------

time sqlplus /nolog << EOF

exit;

EOF

cat sqlsys.sh

-------------------

time sqlplus / as sysdba << EOF

exit;

EOF

Results of tests:

AIX 7.1 Oracle 11g

sh sqlnolog.sh

real 0m0.06s

user 0m0.02s

sys 0m0.01s

sh sqlsys.sh

real 0m0.30s

user 0m0.03s

sys 0m0.01s

--

AIX 7.1 Oracle 18.4

sh sqlnolog.sh

real 0m0.30s

user 0m0.23s

sys 0m0.02s

sh sqlsys.sh

real 0m0.70s

user 0m0.26s

sys 0m0.03s

--

AIX 7.1 Oracle 19.3

sh sqlnolog.sh

real 0m0.28s

user 0m0.22s

sys 0m0.02s

sh sqlsys.sh

real 0m1.60s <------ replicated issue

user 0m0.24s

sys 0m0.03s

-----------------

Linux 19.13 <---- seems to have less impact on Linux platform

sh sqlnolog.sh

real 0m0.039s

user 0m0.008s

sys 0m0.011s

sh sqlsys.sh

real 0m0.130s

user 0m0.020s

sys 0m0.017s

----------------

----------------

This might be related to Bug 30571037  - 19C SQL*PLUS TAKES 1 SECOND WHEN EXITING AN APPLICATION

We applied the patch and it improved considerably, one second less.
However, it is still slower using 19c than 11g to connect to the 19c database, and the customer batches still taking long for the application.


Using 11g to connect to the 19c database:

real    0m0.45s
real    0m0.44s
real    0m0.45s
real    0m0.45s
real    0m0.42s
real    0m0.45s
real    0m0.44s
real    0m0.44s
real    0m0.45s
real    0m0.44s


Using 19c to connect to the 19c database:

real    0m0.62s
real    0m0.57s
real    0m0.64s
real    0m0.62s
real    0m0.63s
real    0m0.67s
real    0m0.69s
real    0m0.65s
real    0m0.68s
real    0m0.67s

Changes

 Upgrade from 11g to 19c

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.