Executing SQL extremely slow

[OnCallDuty]

Hello,

we have upgraded to version 10.0.5.1710 (20 user license) and our developers now have a problem executing SQL scripts, because the execution takes a long time. If an SQL script contains only 500 simple INSERT statements, then after pressing for executing the script, the UI displays "Initializing" in the status bar of the SQL Window, the CPU load of a single CPU core goes up to around 100% and after a couple of minutes the script is actually executed (I measured 6.5 minutes for the initialization and then less than 20 seconds for the actual execution).

Please help, this is not just annoying; it's really a problem in case we have to support our business in emergency situations.

Regards,
Fabian

 

[Marco Kalter]

Batch scripts with hundreds of statements should preferably be executed in the Command Window, which was designed for this task. I assume you are using the SQL Window instead, which is more of an interactive tool. To execute 500 inserts in a SQL Window, change the script to a single PL/SQL Block:

begin
  insert into ...;
  insert into ...;
  ...
end;
/

 

[OnCallDuty]

I wrote the script containing hundreds of INSERTs just to give you an example with which you can reproduce the problem. We also have other scripts where an anonymous PL/SQL block doesn't help. In an earlier version the script could be executed in a very short amount of time. Why is an entire CPU core busy at 100% for 6.5 minutes? To me it sounds like a bug.

 

[Marco Kalter]

Can you send me such a script where a single PL/SQL Block takes 6.5 minutes?

 

[OnCallDuty]

It took 6.5 minutes to execute the SQL script containing 500 INSERTs. The INSERTs are not wrapped in an PL/SQL block. You are right that wrapping them in a block solves the problem but the 500 INSERTS were just an example that should help you to reproduce the problem. We have written a number of other scripts where it would take time to rewrite them, because we can't just wrap them in a PL/SQL block. In the version of application we used before, the scripts would execute very quickly. I have two questions: Can you reproduce the poor performance? And do you intend to do anything about it?

 

[Marco Kalter]

Yes, we can reproduce it. As stated before, the SQL Window is an interactive tool and batch scripts should preferable be executed in the Command Window.