1 2 3 Previous Next 36 Replies Latest reply: Jun 2, 2010 10:33 AM by 6363 Go to original post RSS
      • 30. Re: global temporary table row order
        bluefrog
        I agree with you to an extent. The "extent" is that for somebody with a 3GL bacground getting the job done using PL/SQL constructs is quick, simple and, best of all, easy to maintain, especially given modern day highlighting debuggers.

        However, the long term consequence of such an approach is that you end with an enormous amount of code, which eventually becomes unmanageable. Attempting to work your way through layers and layers of packages coded in PL/SQL with minimal SQL is time-consuming indeed and needless to say, over time, will guarantee to deliver poor performance.

        The best approach is to wrap succinct SQL statements in procedures/functions and use PL/SQL merely as a facilitator. Your number of lines of code will be far less and maintenance will remain consistent as you’ll know where everything is. Your SQL skills will improve over time and as it does so your code will become even less.
        • 31. Re: global temporary table row order
          6363
          CharlesRoos wrote:
          >
          Even with properly commented code?
          >

          The complex sql which is well documented is anyway bad thing if one needs to change it.
          Completely disagree.

          Again it depends on the skills of the developer.

          Well commented and formatted SQL code is no harder to modify than code written in any other language.
          This is because sql gramamtics is "weaker" than plsql grammatics.
          This is nonsense that you have just made up.
          It is "weaker" because have strict rules, what one can use, so oftenlty one should redo the whole query design to implement additional logic to the code. But plsql gramamtics offers lot of freedome, one can fastly change the code without fear that something must be rethinked redone in a big part.
          It appears to mean -

          With PL/SQL you can just apply patches to existing code without fully understanding the whole program.

          Hm, I bet that leads to super maintainable code.
          • 32. Re: global temporary table row order
            sybrand_b
            No, SQL is not 'weaker'. You prefer PL/SQL because it allows you to write unstructured code and hack yourself out, resulting in code which doesn't scale and which is fully out of control.

            -------
            Sybrand Bakker
            Senior Oracle DBA
            • 33. Re: global temporary table row order
              sybrand_b
              I fully disagree.
              Non procedural is always preferred over procedural.

              --------
              Sybrand Bakker
              Senior Oracle DBA
              • 34. Re: global temporary table row order
                sybrand_b
                Temporay table solution is not 'clean'. It is an inherently weak solution, hacking yourself our.

                But is the perfect 'solution' for people who don't want to hear and believe in fairy tales.

                ----------
                Sybrand Bakker
                Senior Oracle DBA
                • 35. Re: global temporary table row order
                  Sven W.
                  3360 wrote:
                  Centinul wrote:
                  Sven W. wrote:
                  I fully agree! This is one of the reasons when you should stop developing highly sophisticated SQL statements. Even if the performance is slightly better.
                  I often wonder where this line should be drawn. I'd be interested to hear the thoughts of other members here.
                  In my opinion never.
                  Never say never (or always). ;)
                  >
                  The problem is with the developer not the code.
                  Best statement in this whole thread!
                  Developers who do not spend time learning SQL and reading the SQL reference manual, which unfortunately is the majority, will always find moderately advanced SQL hard to understand and resort to PL/SQL loops, which personally I do not find easier to understand and maintain, and I think you will find the person who wrote the code is about the only one who does think it is easy to understand and maintain.

                  The performance impact is not as trivial as those that do this claim.

                  Re: using cursor to sum  numbers, gives error

                  Simple insert made 15 times slower, rolling back 20 times slower.

                  This is not slightly slower, this is massively, wastefully slower, because it is tens of times more inefficient and consumes vastly more resources.
                  In general I agree, although your example in the linked thread is a bittle biased. E.g. change the number from 10.000 rows to 100 rows and you will see almost no difference at all. Also the difference is less if the select itself needs a large chuck of the total running time. Which is often the case in real world examples.
                  But the ressource consumption needs to be proved. i doubt that statement. The memory footprint of the little Pl/Sql block is not very large at all. Also remember that even a simple SQL might be inserted into a PL/SQL Api to be executed there.
                  Learn basic SQL, and hire developers who know SQL to work on the database.
                  Yes. Additionally SQL is the more universal language. You can apply most of the Oracle SQL constructs to many other major databases. There are differences, but the same SQL mindset applies to all.
                  Unfortunately the majority of the posts on these forums show almost no one does this.
                  This is intrinsic to almost any "expert" forum. Those who know SQL will post less questions.
                  • 36. Re: global temporary table row order
                    6363
                    Sven W. wrote:
                    3360 wrote:

                    The performance impact is not as trivial as those that do this claim.

                    Re: using cursor to sum  numbers, gives error

                    Simple insert made 15 times slower, rolling back 20 times slower.

                    This is not slightly slower, this is massively, wastefully slower, because it is tens of times more inefficient and consumes vastly more resources.
                    In general I agree, although your example in the linked thread is a bittle biased. E.g. change the number from 10.000 rows to 100 rows and you will see almost no difference at all.
                    The difference would still be there, it will just be that you will be comparing smaller and smaller numbers and so other environmental factors will skew the result. The higher number of inserts the better the accuracy of the percentage differences.
                    Also the difference is less if the select itself needs a large chuck of the total running time.
                    It isn't.
                    But the ressource consumption needs to be proved. i doubt that statement.
                    Here is the test run through Tom Kyte's runstats test

                    http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701
                    SQL> exec runstats_pkg.rs_start
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> insert into t
                      2  select level from dual connect by level <= 100000;
                    
                    100000 rows created.
                    
                    SQL> exec runstats_pkg.rs_middle
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> begin
                      2    for c in (
                      3      select level x from dual connect by level <= 100000
                      4      ) loop
                      5    insert into t values (c.x);
                      6    end loop;
                      7  end;
                      8  /
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> exec runstats_pkg.rs_stop(500)
                    Run1 ran in 15 hsecs
                    Run2 ran in 348 hsecs
                    run 1 ran in 4.31% of the time
                    
                    Name                                  Run1        Run2        Diff
                    STAT...Heap Segment Array Inse         554          13        -541
                    STAT...free buffer requested           222         909         687
                    STAT...redo subscn max counts          221         990         769
                    STAT...redo ordering marks              44         871         827
                    STAT...calls to kcmgas                  46         873         827
                    STAT...free buffer inspected           133         982         849
                    LATCH.object queue header oper         625       2,624       1,999
                    LATCH.simulator hash latch             223       6,005       5,782
                    STAT...redo entries                  1,258     100,643      99,385
                    STAT...HSC Heap Segment Block          555     100,014      99,459
                    STAT...session cursor cache hi           5     100,010     100,005
                    STAT...opened cursors cumulati           7     100,014     100,007
                    STAT...execute count                     7     100,014     100,007
                    STAT...session logical reads         2,162     102,988     100,826
                    STAT...db block gets                 1,853     102,780     100,927
                    STAT...db block gets from cach       1,853     102,780     100,927
                    STAT...recursive calls                  33     101,092     101,059
                    STAT...db block changes              1,873     201,552     199,679
                    LATCH.cache buffers chains           7,176     507,892     500,716
                    STAT...undo change vector size     240,500   6,802,736   6,562,236
                    STAT...redo size                 1,566,136  24,504,020  22,937,884
                    
                    Run1 latches total versus runs -- difference and pct
                    Run1        Run2        Diff       Pct
                    9,069     519,238     510,169      1.75%
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL>
                    Every measurable resource from logical reads, to redo and undo and latches was used in much higher quantities for the PL/SQL block.
                    1 2 3 Previous Next