1 2 Previous Next 24 Replies Latest reply: Sep 5, 2012 11:30 PM by Manik RSS

    Need Good points in all scenarios - Dynamic SQL

    Ashu_Neo
      Qs:- What are the advantages of using dynamic sql based upon coding pattern and performance wise? ( Please mention in points or you can share a better ink for this)

      Thanks!
      Ashutosh

      Edited by: Ashu_Neo on Sep 5, 2012 12:15 PM
        • 1. Re: Need Good points in all scenarios - Dynamic SQL
          Hoek
          Performance wise there's usually no advantage at all. More a performance penalty.

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01101
          • 2. Re: Need Good points in all scenarios - Dynamic SQL
            sybrand_b
            There are no advantages, or it must be your goal to make an unscalable application.

            Dynamic SQL means parsing each statement everytime.
            Parsing latches part of memory.
            When memory is latched no other statements can be parsed.

            This means
            Parsing is a means to serialize statements.

            Your question indicates your knowledge lacks and your brain needs a sanity check.

            -----------
            Sybrand Bakker
            Senior Oracle DBA

            Edited by: sybrand_b on 5-sep-2012 8:50
            • 3. Re: Need Good points in all scenarios - Dynamic SQL
              jeneesh
              Ashu_Neo wrote:
              Qs:- What are the advantages of using dynamic sql based upon coding pattern and performance wise? ( Please mention in points or you can share a better ink for this)

              Thanks!
              Ashutosh

              Edited by: Ashu_Neo on Sep 5, 2012 12:15 PM
              With what are you comparing?

              With Static SQL ? - But what is the reason for you to think to use dynamic SQL, when something can be accomplished using static SQL?

              Or are you trying to compare it with something else?

              Edited by: jeneesh on Sep 5, 2012 12:34 PM
              • 4. Re: Need Good points in all scenarios - Dynamic SQL
                ShankarViji
                Hi Ashu,

                The only advantage of Using Dynamic SQL, is that if the objects are not known Until runtime.

                And, as Hoek and Sybrand said, there is no wise related to Performance.

                And, When you use Dynamic SQL,

                As, the Code becomes large it might be littered with mistakes, but
                they Still Compile.

                Objects Validation are not done until runtime.

                which are great disadvantages using Dynamic SQL.

                Thanks,
                Shankar
                • 5. Re: Need Good points in all scenarios - Dynamic SQL
                  Hoek
                  Some more explanations:
                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:22697017378500
                  • 6. Re: Need Good points in all scenarios - Dynamic SQL
                    Ashu_Neo
                    See, When I can use it and how, that I know, in case of solving business rules.

                    But is there anything else, that happens internally in oracle server side in positive terms of performance rather than static sql statements execution?

                    Let me show you one example and you have to tell me why it's happening ( taking less execution time(0.18 sec in 2nd program) and what is role of dynamic sql here then?

                    I don't know about what is happening in back ground and I need specific, proper answer.

                    NB:- No time pass reply will be entertained and I will report it abuse.

                    ------------------------START--------------------------------------
                    SQL> set serveroutput on;
                    SQL> declare
                    2 type rc is ref cursor;
                    3 l_rc rc;
                    4 l_dummy all_objects.object_name%type;
                    5 l_start number default dbms_utility.get_time;
                    6 begin
                    7 for i in 1 .. 1000
                    8 loop
                    9 open l_rc for
                    10 'select object_name
                    11 from all_objects
                    12 where object_id = ' || i;
                    13 fetch l_rc into l_dummy;
                    14 close l_rc;
                    15 -- dbms_output.put_line(l_dummy);
                    16 end loop;
                    17 dbms_output.put_line
                    18 (round((dbms_utility.get_time-l_start)/100, 2) ||
                    19 ' Seconds...' );
                    20 end;
                    21 /
                    67.86 Seconds...

                    PL/SQL procedure successfully completed.

                    SQL>
                    SQL> declare
                    2 type rc is ref cursor;
                    3 l_rc rc;
                    4 l_dummy all_objects.object_name%type;
                    5 l_start number default dbms_utility.get_time;
                    6 begin
                    7 for i in 1 .. 1000
                    8 loop
                    9 open l_rc for
                    10 'select object_name
                    11 from all_objects
                    12 where object_id = :a '
                    13 using i;
                    14 fetch l_rc into l_dummy;
                    15 close l_rc;
                    16 -- dbms_output.put_line(l_dummy);
                    17 end loop;
                    18 dbms_output.put_line
                    19 (round((dbms_utility.get_time-l_start)/100, 2) ||
                    20 ' Seconds...' );
                    21 end;
                    22 /
                    .18 Seconds...

                    PL/SQL procedure successfully completed.

                    ------------------------END------------------------------------------

                    Thanks!
                    Ashutosh

                    Edited by: Ashu_Neo on Sep 5, 2012 12:43 PM

                    Edited by: Ashu_Neo on Sep 5, 2012 12:51 PM

                    Edited by: Ashu_Neo on Sep 5, 2012 12:52 PM
                    • 7. Re: Need Good points in all scenarios - Dynamic SQL
                      jeneesh
                      Ashu_Neo wrote:
                      See, When I can use it and how, that I know, in case of solving business rules.

                      But is there anything else, that happens internally in oracle server side in positive terms of performance rather than static sql statements execution?
                      So, are you asking is there any advantage if I use dynamic SQL for something which can be done in static SQL?
                      • 8. Re: Need Good points in all scenarios - Dynamic SQL
                        sybrand_b
                        Second time the data is in cache, so it will be faster.
                        Reverse the two scripts and you will see the same behavior, the second script will be faster
                        Your test is seriously flawed.
                        You shouldn't only measure wall clock time.
                        You should measure all resources.
                        Run this through the runstats harness of Tom Kyte (available at http://asktom.oracle.com) and you will see the real difference.

                        -----------
                        Sybrand Bakker
                        Senior Oracle DBA
                        • 9. Re: Need Good points in all scenarios - Dynamic SQL
                          Purvesh K
                          Apology, if you feel we are passing time here. But it actually isn't. At least not to me.

                          Now, if you have actually thought that your Dynamic SQL has executed faster than the Static SQL, It was because none of the Queries required to be Parsed as it was already executed due to the Block with Static Queries. Results were already present in cache.
                          Hence, seemingly a Faster execution.

                          Maybe, if you try
                          ALTER SYSTEM FLUSH BUFFER_CACHE;
                          between the two executions, or rather reverse the execution order after clearing buffer cache; You might (almost certainly) see a change of execution time.

                          I do not have a standalone database to flush buffer cache and try. Hence, please do help yourself and us as well by trying and posting the results.

                          if you wish you can also use TKPROF for the executions, to compare the parsing, reads and executes.
                          • 10. Re: Need Good points in all scenarios - Dynamic SQL
                            Ashu_Neo
                            Purvesh K,

                            Don't defend anybody by making a group (as you said "we"). I just registered here recently by collecting some good feedback from others, that you can learn something more by sharing and gaining from other resources( experts/old users) ! But in recent past, I found some discussions a bit more criticizing rather then sharing knowledge. I am not pointing anybody individually even you. I want to confess, what I felt in those days. I am saying it bit collectively and generalized manner.

                            So please respond well in simple/ specific manner; as there can have different people with different queries of some more and less knowledge! Make it a good clean discussion room.

                            P.S Sorry for posting out of context. But I need to.
                            • 11. Re: Need Good points in all scenarios - Dynamic SQL
                              Purvesh K
                              Ashu_Neo wrote:
                              Purvesh K,

                              Don't defend anybody by making a group (as you said "we"). I just registered here recently by collecting some good feedback from others, that you can learn something more by sharing and gaining from other resources( experts/old users) ! But in recent past, I found some discussions a bit more criticizing rather then sharing knowledge. I am not pointing anybody individually even you. I want to confess, what I felt in those days. I am saying it bit collectively and generalized manner.
                              I am not defending anybody here (and I am not accounted to defend anybody). It was just my Opinion. If there is anybody, strolling here just to pass time, it would be there business, I do not care. I have been associated here for around 6 months and I have not had any bad experience. It has always been a learning experience (as I term it). At times it may be, learning about what and how you should approach and it may also be how not to approach. I term it as Perspective. If you have good perspective, you learn good things else you are always probable to get wound into Negative aspects.

                              >
                              So please respond well in simple/ specific manner; as there can have different people with different queries of some more and less knowledge! Make it a good clean discussion room.
                              Was that regarding my previous post? Yes, I was not specific because I do not have a Standalone database at hand to test those executions (Also I did not wish to stall other by Flushing Buffer Cache and causing unwanted effects).
                              If it was a general comment, I would agree to it but would like to add that People with vast experience do have certain opinions that they share and I find that useful. So, I still see it as a Clean Discussion Room.
                              • 12. Re: Need Good points in all scenarios - Dynamic SQL
                                sybrand_b
                                You just indicated -for the second time- you have very long toes and are easily insulted.
                                Why?
                                Your question is not a serious one. Dynamic SQL is simply evil. PERIOD. Anyone using it would best loose their job asap.

                                -----------
                                Sybrand Bakker
                                Senior Oracle DBA
                                • 13. Re: Need Good points in all scenarios - Dynamic SQL
                                  Vite DBA
                                  Hi Ashutosh,

                                  Whether these are examples of dynamic SQL is debatable and the performance difference has nothing to do with dynamic SQL versus static SQL. The difference is explainable through the fact that the first query has a parameter assigned dynamically and the second example uses a bind variable. This means that the first query has to be parsed for every iteration of the query whereas the second only needs to be parsed once.

                                  In the debate regarding dynamic/static SQL. An individual SQL statement will perform identically regardless of whether it was constructed dynamically or statically. This of course takes no account of how the database will deal with multiple queries running in a database at the same time in a real world application. In this case, the rule of thumb is that an application will perform better with well constructed queries using bind variables where applicable. In terms of supportability, applications with large elements of dynamic SQL can become a nightmare.

                                  Regards
                                  Andre
                                  • 14. Re: Need Good points in all scenarios - Dynamic SQL
                                    padders
                                    I'm not sure what your definition of 'dynamic SQL' is but I think it would be generally true to say that both cases you have tested are dynamic SQL.

                                    What you appeared to have demonstrated is the difference between binding and not binding (concatenating) when using dynamic SQL. Without some tracing it is not 100% possible to say but the former potentially results in a single hard parse (and subsequent sharing of the same cursor) whereas the latter potentially results in a thousand hard parses, incurring significant overhead for parsing and filling the shared SQL area with potentially unshareable SQL.

                                    A very important observation but not a new one and not a comparison of static and dynamic SQL, although it is true that failure to bind is a common reason for poor performance when using dynamic SQL instead of static SQL.
                                    1 2 Previous Next