Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Poore performance of SQLcl compared to SQL*Plus

ITineraDec 20 2023

Hi all,

the job is to extract outdated data from a data warehouse and store it for possible later audits. To do this, we start a VM, select the partitions to be archived, convert them to CSV format, compress and move the archives to a long-term archive.

For some reasons we would like to use SQLcl to do the job. But, we faced a problem:
The performance of SQLcl is a factor of 10 worse compared to SQL*Plus.

I was able to reproduce the case locally on my computer using a simple test case:

Script using SQLcl

set array 1000
set sqlformat csv
set termout off
spool sqlcl.out
select * from dba_objects;
spool off
exit

Runtime 8 seconds, CPU usage all time 100%

Script using SQL*Plus

set arraysize 1000
set markup csv on
@termout 
spool sqlplus.out
select * from dba_objects;
spool off
exit

Runtime 0.8 seconds

sample call:

date && sql user/***@jdbc:oracle:oci:@oracle-19c-vagrant:1521/orclpdb1 @script && date

It makes no difference whether we use thin or thick driver or spool to a file or to /dev/null. It’s not related to /dev/random device. We also checked with different JVM settings.

Another strange observation is that tnsping executed in SQLcl shows significantly longer round trip times than tnsping on the command line.

Did we miss something? Why does SQLcl take ten times longer to extract data from the network layer and convert it into printable form?

Needless to say, the real example is about minutes versus hours.

Any hints or tips are welcome,

kind regards,

Steffen

(Database is 19c, checked with different client and SQLcl versions)

Comments
Post Details
Added on Dec 20 2023
8 comments
259 views