Re: SQL*Forms Technical Tips (LONG)

In article <1992May17.084855.20943_at_oracle.us.oracle.com>, ddruker_at_us.oracle.com (Daniel Druker) writes:
> 15) Remember that every 'select ... into ... ' does TWO fetches
> (to find out TOO_MANY_ROWS exception), the better way is
> always to declare a cursor and open/fetch.

Just to be a little clearer about the statement:

         every select ..into in a *V3* style (PLSQL) trigger does 2     fetches.

         This behavior is required to enforce the definition of     select...into that says this MUST return A SINGLE unique row. While     using a cursor and doing a fetch from the cursor will eliminate one     fetch, I would advise you to think carefully about doing this.

         If I recall correctly (it's been a while since I've looked at     the 3.0 source code) user opened cursors are EXPLICITY closed at the     end of triggers (see the PLSQL and/or SQL*Forms doc). Internal     SQL*Forms opened cursors are simply HIBERNATED and are be re-used at     some time later for the same SQL*Statement. Of course all bets are     off if your application has reached MAX_OPEN_CURSORS and one has to     be closed anyway to complete the request. The point? We will     possibly change the way this works in the future (thus obviating the     perceived need for the use of the explicit cursor), Making a change     like this this won't drastically improve your performance.

         What WILL improve the performance of SQL statements in V3 style     triggers? SQL*Forms 3.0.16.8 or greater. An optimization has been     made to SQL*Forms 3.0.16.8 (and later) which reduces the number of     interactions required between SQL*Forms and the database (regardless     if you are linked SHARED, or you are two-task or client server     across a network). The details of this are in the READ30.ME file     distributed with the SQL*Forms 3.0.16.8 release. For those who can't     wait for the release notes... I"ve included an extract for your     reading pleasure. NOTE: It is unlikely that you will see a version of     3.0.16.8 on any platform you are more likely to see 3.0.16.9 or .10.     Fear not: the changes are in those versions as well.

     SQL Statement Execution

                  SQL*Forms now uses a different interface to the RDBMS
        that allows the bundling of bind variable addresses and WHERE
        clause conditions. This substantially reduces the number of
        client/server interactions required to execute some SQL
        statements.

                  The table below details the results of the measurement
        of the number of packets exchanged between client and server,
        for a number of different types of SQL statements.

                  Note Statements 1 and 2 demonstrate NO improvement in
        performance as there is only 1 bind variable and/or one WHERE
        clause condition, and thus no bundling of RDBMS calls takes
        place.

          SQL            Number of Packets Exchanged
        Statement                                      Percent
        Number         3.0.16.7          3.0.16.8      Improvement

          1                 14               14            0%
          2                 10               10            0%
          3                 28               14           50%
          4                 40               14           65%

             SQL Statements:

             1) select dname into :dname from dept where deptno = :deptno;

             2) select ename into :ename from emp;

             3) select empno, ename, job, mgr, hiredate, sal, comm, deptno
           into  :empno, :ename, :job, :mgr, :hiredate, :sal, :comm, :deptno
           from emp
           where empno = :empno;

             4) select empno, ename, job, mgr, hiredate, sal, comm, deptno
           into  :empno, :ename, :job, :mgr, :hiredate, :sal, :comm,
                 :deptno
           from emp
           where empno = :empno and ename= :ename and
                 job= :job and MGR= :mgr and hiredate= :hiredate
                 and SAL= :sal and deptno= :deptno;

            When Will SQL*Forms Use UPIALL?

                  A significant amount of discussion has arisen about the
        use of UPIALL in Oracle Tools.  This section describes the
        results of our study of the performance of UPIALL vs. the
        current methods being used.

                  The execution of SQL statements is divided into a 5
        distinct operations: parse, bind, define, execute, and fetch.
        UPIALL is a programmatic interface designed to minimize the
        number of client/server interactions by "bundling" groups of
        these calls into one client/server interaction. That is,
        rather  than one client server interaction for each
        operation, it is possible to bundle them into two or even one
        large client/server interaction, thus reducing the number of
        packets exchanged between client and server.

                  SQL*Forms development has studied in great depth the
        performance of SQL statements in SQL*Forms V3.0.  After
        careful study of the results we decided NOT to implement
        UPIALL support in SQL*Forms for several reasons:

                   1) Prior to RDBMS 6.0.32.4 there were bugs in the
              RDBMS that precluded the use of UPIALL in SQL*Forms.

                   2) A substantial performance improvement (reduction
              in packets) is achieved by using the bundled bind varible
              and where clause define RDBMS interface.

                   3) The minimum performance improvement that UPIALL
              provides over and above (2) above does not warrant a
              strict RDBMS version requirement by SQL*Forms.

                   4) RDBMS V7 provides changes to the UPI interface,
              which further reduces client/server interaction which will
              require no further changes in SQL*Forms code.


                   PL/SQL 1.0.34 Performance Enhancements

                   SQL*Forms 3.0.16.8 include PL/SQL 1.0.34. The
              following fixes in PL/SQL 1.0.34 affect the performance of
              SQL*Forms 3.0.16.8


                   PL/SQL Compiler (SQL*Forms Generator)

                   Changes were made to the PL/SQL compiler to improve
              its performance, which in turn improves the performance of
              the SQL*Forms generator.

                   An example terse INP file of 588 VMS blocks
              containing some 217 triggers, 2 form-level procedures, and
              3,460 lines of PL/SQL text was measured. The changes
              improve the elapsed time performance of generation on this
              form by 25%.

-------------------------------------------------------------------------------

              "QUALITY is never an accident; it is always the result of
              high intention, sincere effort, intelligent direction and
              skillful execution; it represents the wise choice of many
              alternatives." 

     Kevin Mallory                                       SQL*Forms Performance 
                                                         Oracle Corporation 

     The opinions expressed herein are my own and in no way are a
        commitment by Oracle Corporation.

-------------------------------------------------------------------------------