This content has been marked as final. Show 7 replies
Thanks. I have read from various journals and forums that dynamic sql has over head and i use them very sparingly. But my main question was why is there no difference in the trace files. My dynamic SQL's should have got parsed more number of times. But that is not shown in the trace file. Why is it due to. I have even checked after flushing the shared_pool.
You can use this code fragment to generate dynamic SQL with excessive parsing:
Please note that the time needed to perform 1000 simple parses on most modern machines would be minimal.
declare begin for i in 1..1000 loop execute immediate 'insert into xyz values(‘||to_char(i)||’)'; end loop; end; /
No, even this doesnt produces any difference in parsing when compared to a Static SQL of the same.
Please note that the time needed to perform 1000 simple parses on most modern machines would be minimal.That's not the point. Parsing takes out latches. Latches inhibit scalability. If there is more than one user on the system, parsing should be reduced as much as possible.
ramarun - since 10g, PL/SQL has an optimisation that will only parse the first time and can re-use the parsed statement. I cannot find the doc link, but AskTom is good enough for me: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1395225000346824700
ramarun wrote:Do not underestimate the power of The Good Side of The Force? (in this case, PL/SQL compiler's ability to optimise code and reuse cursor handles without having to reopen a cursor and cause a soft parse)
If so why there is no difference in the trace file.
ramarun wrote:I may be missing something too :)
I have a code like this, will the dynamic SQL Result in excessive parsing than Static Sql. If so why there is no difference in the trace file.
This is our code
When traced there is no change in the elapsed time or the number of times the SQL is parsed. Am i missing any thing here.
--Session 1 declare begin for i in 1..1000 loop execute immediate 'insert into xyz values(:1)' using i; end loop; end; --Session 2 declare begin for i in 1..1000 loop insert into xyz values(i); end loop; end; /
Besides Billy's mention of built-in optimizaton won't PL/SQL use a bind variable in the SQL to represent the variable i in your insert? What happens if you generate the SQL dynamically (just to see what happens) with the 'i' variable replaced by the actual value as hard-coded in the SQL text? It is possible that even then the automatic PL/SQL optimizations can occur but IMHO should not with cursor sharing set to EXACT