Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-6421

Optimize usage of Oracle cursors

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Database
    • Labels:

      Description

      Execution of Oracle sequences to generate ids can be improved. This benchmark shows an improvement up to 50% : http://oracle-base.com/articles/11g/plsql-new-features-and-enhancements-11gr1.php#sequences_in_plsql_expressions

      Moreover it reduces the number of open cursors. Quoting this contribution :

      One of the reason I'm proposing this fix is that our DBA impose tight control over the number of cursors a session can have opened and it just happens that the way the triggers are coded could double that trigger usage. In Java, whenever you create a Statement or PreparedStatement, it creates a Cursor in Oracle and, if I'm not mistaken, you are using DBCP to do some parallel SQL processing which can open MANY such statements; for each inserted record, because of the trigger, Oracle will open a 2nd cursor for the SELECT to fetch the sequence number thus possibly doubling the number of opened cursor in parallel processing.

      The optimization is a change in the trigger. It does not support Oracle 10g, so there are 2 options :

      1. drop support of Oracle 10g (R2 included)
      2. detect version of Oracle in order to create the correct trigger code

      Code of trigger for Oracle 11g must be :

      %{CREATE OR REPLACE TRIGGER #{table}_idt
                 BEFORE INSERT ON #{table}
                 FOR EACH ROW
               BEGIN
                  IF :new.id IS null THEN
                   :new.id := #{table}_seq.nextval;
                  END IF;
               END;})
      

      As a reminder on Oracle 10g it is:

      %{CREATE OR REPLACE TRIGGER #{table}_idt
                 BEFORE INSERT ON #{table}
                 FOR EACH ROW
               BEGIN
                  IF :new.id IS null THEN
                   SELECT #{table}_seq.nextval INTO :new.id FROM dual;
                  END IF;
               END;})
      

      12/02/2016 DUQE

      Indeed , queries agains the dual table is not good for the number of cursors opened in the session. To control and to drop the number of opened cursors , I advice this method.

      But I strongly advice you to use the sequence directly in the code (without trigger usage).

      The following tables will allow us to compare the performance of the identity column against direct use of a sequence and a trigger-based solution.

      -- Create a table with an old-style identity column populated using a trigger.
      CREATE TABLE trigger_identity (
        id           NUMBER  NOT NULL,
        description  VARCHAR2(30)
      );
      
      CREATE SEQUENCE trigger_identity_seq;
      
      CREATE OR REPLACE TRIGGER trigger_identity_bir 
      BEFORE INSERT ON trigger_identity 
      FOR EACH ROW
      WHEN (new.id IS NULL)
      BEGIN
        :new.id := trigger_identity_seq.NEXTVAL;
      END;
      /
      
      -- Populate the column directly using a sequence.
      CREATE TABLE sequence_identity (
        id           NUMBER  NOT NULL,
        description  VARCHAR2(30)
      );
      
      CREATE SEQUENCE sequence_identity_seq;
      
      The following script compares the insert performance of the three tables. The first test uses the trigger to populate the ID column. The second test references a sequence directly, rather than relying on a trigger. 
      
      SET SERVEROUTPUT ON
      DECLARE
        l_time    PLS_INTEGER;
        l_cpu     PLS_INTEGER;
      
        TYPE t_data IS TABLE OF trigger_identity.description%TYPE;
        l_data t_data;
      BEGIN
        -- Populate a collection with some dummy data.
        SELECT 'DUMMY DATA'
        BULK COLLECT INTO l_data
        FROM dual
        CONNECT BY level <= 10000;
      
        -- Trigger-based solution.
        EXECUTE IMMEDIATE 'TRUNCATE TABLE trigger_identity';
      
        l_time := DBMS_UTILITY.get_time;
        l_cpu  := DBMS_UTILITY.get_cpu_time;
      
        FORALL i IN l_data.first .. l_data.last
          INSERT INTO trigger_identity (description) VALUES (l_data(i));
        
        DBMS_OUTPUT.put_line('TRIGGER_IDENTITY : ' ||
                             'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                             'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
      
      
        -- Direct use of a sequence.
        EXECUTE IMMEDIATE 'TRUNCATE TABLE sequence_identity';
      
        l_time := DBMS_UTILITY.get_time;
        l_cpu  := DBMS_UTILITY.get_cpu_time;
      
        FORALL i IN l_data.first .. l_data.last
          INSERT INTO sequence_identity (id, description) VALUES (sequence_identity_seq.NEXTVAL, l_data(i));
        
        DBMS_OUTPUT.put_line('SEQUENCE_IDENTITY: ' ||
                             'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                             'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
      
      TRIGGER_IDENTITY : Time=217 hsecs CPU Time=204 hsecs
      SEQUENCE_IDENTITY: Time=26 hsecs CPU Time=22 hsecs
      
      PL/SQL procedure successfully completed.
      
      SQL>
      

      Not surprisingly, trigger-based test performs much worse than the direct use of a sequence.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              simon.brandhof Simon Brandhof (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: