Command window performance

[Pazus]

Hello.

I use sqlplus scripts to install patches (compile packages + DDL) which I run through Command Window.
Why compiling packages takes much longer when doing it through @@package_name.pck in command windows than though opening it in Pl/SQL Developer and compiling directly (F8)? Are there any hints to improve performance of Command Window execution?

 

[Marco Kalter]

The Command Window will indeed be slower than the Program Window, because the Command Window needs to parse the script. What exactly is the performance difference?

 

[Pazus]

Up to times... We have one big package (about 25k lines). It opens from file or from the DB in about 7 sec on my PC, compiles in about 11 sec. If you rune it through Command window it take more than a minute (up to several) to compile body.

 

[Pazus]

x64 bit client - same issue.

Actually it seems that Command window silently performs ECHO even though it is disabled.

Also as the output in the Command window grows (more packages compiled, inserts made), scripts execution performance decreases. And it depends on the length of the text posted to the command window, not the number of statements.

 

[fraenkg]

I have the same problem since several versions of plsd. It tooks minutes in command window and show compile time of seconds. Compiling in program window is 10++ times faster.
So, if there is a roll out to production, it is not recommended to use a fine script for all. All package bodys have to compile in program window in order of minimum downtime for applications. I do not understand thins since years.
For information: compile in command window use one cpu 100% the whole time.

 

[Bernhard S]

I'm sure like me a lot of other users as well use in fact SQL*Plus in order to roll out to production or other environments. I would therefore suggest to have an option in command window to execute statements alternatively using SQL*Plus. This would at least also be a workaround for the problem reported here and also make it easier to find out issues regarding different behaviour of command window vs SQL*Plus.

 

[fraenkg]

Could you already consider this behavior ?
Is their any solution or workaround?

Regards frank

 

[Pazus]

Marco, any news on the topic?

Performance difference is minutes vs several seconds so I approce 10++ times difference. It seams like the performance is approximately the same with echo on/off. Could it be that even with echo off the content is still echoes silently?

 

[Tim Scott]

... I would therefore suggest to have an option in command window to execute statements alternatively using SQL*Plus. ...

I would second this idea - especially if the cmd.exe output is shown within PLSD: though capturing both stdout and stderr and quietly handling the file/save and ...
Although I do like PLSD's habit of showing the full command when there's an error, rather than just the error line one gets from SQL*Plus. There's improved error handling from 11.2 in that SQL*Plus records the full statement in a table - maybe that could be taken advantage of?

 

[Pazus]

I've noticed that while the Developer is "stuck" there is no active session so it's pure local stuck occurred on the PC, not in the DB.

 

[Pazus]

Detailed analysis is posted here:https://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=54186#Post54186