Dynamic SQL performance tied to standard SQL within a package
In Oracle 10g environment, I have a statement that needs to be executed several million times based on the results of a cursor. For flexibility, the current code has an operator as a constant in the package body. Here's a simplified version of the code. Keep in mind that it's all in one package:
c_Stmt CONSTANT VARCHAR2(128) := 'DELETE FROM t WHERE fldA = :b1';
...
PROCEDURE p1(vSomeNumber NUMBER(10)) IS
BEGIN
EXECUTE IMMEDIATE c_Stmt USING vSomeNumber;
END;
...
FOR i IN 1 .. 9999999
LOOP
p1(i);
END LOOP;
Since dynamic SQL already uses bind variables, is there a performance advantage to rewrite this code to replace dynamic SQL with a regular DML statement like this:
PROCEDURE p1(vSomeNumber NUMBER(10)) IS
BEGIN
/* EXECUTE IMMEDIATE c_Stmt USING vSomeNumber; */
DELETE FROM t WHERE fldA = vSomeNumber;
END;
I think there might be an impact on the number of analyzes, but it is unclear if this is still a concern at 10g.
Thank you for your responses.
source to share
It is generally more efficient to prepare a statement before your loop and execute the statement handle during the loop (while also specifying a parameter). This way you avoid the overhead of parsing and preparing the statement every time through the loop.
Using static SQL would avoid the parsing step as it needs to be done when you define the procedure. But this does not avoid the overhead during the preparation phase.
In any case, it almost always happens that people on the Internet cannot know exactly which method will work best. It depends in part on your database and your data. You should try both ways and measure the performance yourself.
source to share
One of the reasons the second method (using the Delete statement) will be faster than the first (using the Execute Immediate) is because of context switching. If you execute Execute Immediate, the PL / SQL process must stop, Oracle needs to load the parsing process, parse your statement (or get it from the statement cache), and then switch back to the execution process. In the latter case, there are fewer context switches.
These context switches (such as thread switching in multithreaded programs) are time consuming. It is for this reason that Oracle has spent a lot of time getting the Bulk Processing (Bulk Insert, Bulk Select) statement to work correctly in PL / SQL.
As suggested above, try and run PL / SQL for all the deletes in one go, for example creating a list of identifiers to delete in a PL / SQL table and performing the delete in a FORALL loop. Less context switching means more work.
source to share
Depending on your specific needs, you have several alternatives to improve your removal speed.
First, store a bunch of IDs in a PL / SQL array and periodically (and at the end) execute either a FORALL DELETE array or a DELETE FROM table WHERE ID in (: arr (1) ,: arr (2) ...)
Second, store the IDs in GLOBAL TEMPORARY TABLE and periodically / at the end, make the table DELETE FROM WHERE id IN (SELECT id FROM global_temp_tbl)
These two parameters obviously delay the delete step, so they can affect the SQL inside the loop using these tables.
Third, switch to static SQL. I don't see the benefit of dynamic SQL here (but you might have simplified the example to keep the benefit hidden). Going static will reduce the number of soft parses (Oracle hashes the operator and finds a match in the operator cache). These savings may or may not be significant in your case. This will mostly be CPU time, but possibly some waiting time if you have other processes running at the same time.
Fourth, if the overhead of soft parsing is significant, but there is a reason you don't want to switch to static SQL, you can use DBMS_SQL. There DBMS_SQL.PARSE will be outside the loop, and only the BIND_VARIABLE and EXECUTE components will be inside the loop.
source to share