oracle - Static vs dynamic sql

Question

In my database at several places developers have used dynamic sql instead of static. And they are saying reason for this is to improve the performance. Can someone tell me can if dynamic sql can really increase the performance in stored procedure or plsql block?

Which will execute faster and why ?
1.

begin  
    execute immediate 'delete from X';  
end;

2.

begin  
    delete from X;  
end;

Answer 1

Your example code is so simple that there will be little difference, but in that case the static version would most likely execute better.

The main reason to use dynamic SQL for performance is when the SQL statement can vary in a significant way - i.e. you might be able to add extra code to the WHERE clause at runtime based on the state of the system (restrict by a sub-query on Address, if Address entered, etc).

Another reason is that sometimes using Bind variables as parameters can be counter-productive.

An example is if you have something like a status field, where data is not evenly distributed (but is indexed).

Consider the following 3 statements, when 95% of the data is 'P'rocessed

   SELECT col FROM table 
   WHERE status = 'U'-- unprocessed
   AND company = :company

   SELECT col FROM table 
   WHERE status = 'P' -- processed
   AND company = :company

   SELECT col FROM table
   WHERE status = :status
   AND company = :company

In the final version, Oracle will choose a generic explain plan. In the first version, it may decide the best plan is to start with the index on status (knowing that 'U'nprocessed entries are a very small part of the total).

You could implement that through different static statements, but where you have more complex statements which only change by a couple of characters, dynamic SQL may be a better option.

Downsides

Each repetition of the same dynamic SQL statement incurs a soft parse, which is a small overhead compared to a static statement, but still an overhead.

Each NEW sql statement (dynamic or static) also incurs a lock on the SGA (shared memory), and can result in pushing 'old' statements out.

A bad, but common, system design is for someone to use dynamic SQL to generate simple selects that only vary by key - i.e.

SELECT col FROM table WHERE id = 5
SELECT col FROM table WHERE id = 20
SELECT col FROM table WHERE id = 7

The individual statements will be quick, but the overall system performance will deteriorate, as it is killing the shared resources.

Also - it is far harder to trap errors at compile time with dynamic SQL. If using PL/SQL this is throwing away a good compilation time check. Even when using something like JDBC (where you move all your database code into strings - good idea!) you can get pre-parsers to validate the JDBC content. Dynamic SQL = runtime testing only.

Overheads

The overhead of execute immediate is small - it is in the thousandths of a second - however, it can add up if this is inside a loop / on a method called once per object / etc. I once got a 10x speed improvement by replacing dynamic SQL with generated static SQL. However, this complicated the code, and was only done because we required the speed.