7 Replies Latest reply: Oct 5, 2012 9:55 AM by riedelme RSS

    Excessive Parsing

    ramarun
      All,

      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
         
      --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;
      /
      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.

      We are running on 11g Release 2 and have set Cursor_Sharing - Exact. This i ask because in one of the tuning exercises carried out we are changing unwanted dynamic sql's written in the application because its a known data that dynamic sql's have to a extra parsing and syntax check at the run time. So is our approach right on this.

      Please clarify and participate.

      Thanks in advance.
        • 1. Re: Excessive Parsing
          Ora
          • 2. Re: Excessive Parsing
            ramarun
            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.

            Thanks
            • 3. Re: Excessive Parsing
              Iordan Iotzov
              You can use this code fragment to generate dynamic SQL with excessive parsing:
                   declare
                 begin
                  for i in 1..1000 loop
                    execute immediate 'insert into xyz values(‘||to_char(i)||’)';
                   end loop;
                end;
               /
              Please note that the time needed to perform 1000 simple parses on most modern machines would be minimal.

              Iordan Iotzov
              http://iiotzov.wordpress.com/
              • 4. Re: Excessive Parsing
                ramarun
                No, even this doesnt produces any difference in parsing when compared to a Static SQL of the same.
                Thanks
                • 5. Re: Excessive Parsing
                  John Stegeman
                  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
                  • 6. Re: Excessive Parsing
                    Billy~Verreynne
                    ramarun wrote:

                    If so why there is no difference in the trace file.
                    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)

                    :-)
                    • 7. Re: Excessive Parsing
                      riedelme
                      ramarun wrote:
                      All,

                      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
                         
                      --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;
                      /
                      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.
                      I may be missing something too :)

                      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