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;
Unfortunately, this does vary on a case-by-case basis.
For your given examples, there is probably no measurable difference. But for a more complicated example, you'd probably want to test your own code.
The link @DumbCoder gave in the comments has some excellent rules of thumb which also apply to Oracle for the most part. You can use something like this to assist you in deciding, but there is no simple rule like "dynamic is faster than static".
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.