Oracle Skripts für „Performance und Skalierbarkeit“

Dieser Abschnitt enthält die create und insert Anweisungen sowie den PL/SQL Code um den Skalierbarkeitstest aus Kapitel 3, „Performance und Skalierbarkeit“ in einer Oracle Datenbank durchzuführen.

Der Test muss auf einer Tabelle laufen, die groß genug ist, um Caching-Effekte auszuschließen. Abhängig von der Umgebung, muss die Tabelle eventuell noch größer angelegt werden, um ein vergleichbares Ergebnis zu erhalten.

CREATE TABLE scale_data (
   section NUMBER NOT NULL,
   id1     NUMBER NOT NULL,
   id2     NUMBER NOT NULL
);

Bemerkungen:

• Es gibt keine Primärschlüssel um die Datengenerierung einfach zu halten.

• Die Tabelle wird anfangs ohne Index angelegt (folgt später).

• Die Tabelle hat keine JUNK-Spalte, da auf die Tabelle während des Tests nicht zugegriffen wird.

INSERT INTO scale_data
SELECT sections.n, gen.x, CEIL(DBMS_RANDOM.VALUE(0, 100)) 
  FROM (
         SELECT level - 1 n
           FROM DUAL
        CONNECT BY level < 300) sections
       , (
         SELECT level x
           FROM DUAL
        CONNECT BY level < 900000) gen
 WHERE gen.x <= sections.n * 3000;

Bemerkungen:

• Der Code erzeugt 300 Sektionen. In deiner Umgebung brauchst Du eventuell noch mehr. Wenn Du die Anzahl der Sektionen erhöhst, musst du auch den zweiten Generator erweitern. Er muss mindestens 3000 x <anzahl der Sektionen> Zeilen erzeugen.

• Die Tabelle benötigt einige Gigabytes.

CREATE INDEX scale_slow ON scale_data (section, id1, id2);


BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'SCALE_DATA' 
                                       , CASCADE => true);
END;
/

Bemerkungen:

• Der Index benötigt ebenfalls einige Gigabyte.

CREATE OR REPLACE PACKAGE test_scalability IS
  TYPE piped_output IS RECORD ( section  NUMBER
                              , seconds  NUMBER
                              , cnt_rows NUMBER);
  TYPE piped_output_table IS TABLE OF piped_output;

  FUNCTION run(sql_txt IN varchar2, n IN number)
    RETURN test_scalability.piped_output_table PIPELINED;
END;
/


CREATE OR REPLACE PACKAGE BODY test_scalability
IS
  TYPE tmp IS TABLE OF piped_output INDEX BY PLS_INTEGER;

  FUNCTION run(sql_txt IN VARCHAR2, n IN NUMBER)
    RETURN test_scalability.piped_output_table PIPELINED
  IS
    rec  test_scalability.tmp;
    r    test_scalability.piped_output;
    iter NUMBER;
    sec  NUMBER;
    strt NUMBER;
    exec_txt VARCHAR2(4000);
    cnt  NUMBER;
  BEGIN
    exec_txt := 'select count(*) from (' || sql_txt || ')';
    iter := 0;
    WHILE iter <= n LOOP
      sec := 0;
      WHILE sec < 300 LOOP
        IF iter = 0 THEN
           rec(sec).seconds  := 0;
           rec(sec).section  := sec;
           rec(sec).cnt_rows := 0;
        END IF;
        strt := DBMS_UTILITY.GET_TIME;
        EXECUTE IMMEDIATE exec_txt INTO cnt USING sec;
        rec(sec).seconds := rec(sec).seconds 
                          + (DBMS_UTILITY.GET_TIME - strt)/100;
        rec(sec).cnt_rows:= rec(sec).cnt_rows + cnt;
        IF iter = n THEN
          PIPE ROW(rec(sec));
        END IF;
        sec := sec +1;
      END LOOP;
      iter := iter +1;
    END LOOP;
    RETURN;
  END;
END test_scalability;
/

Bemerkungen:

• Die TEST_SCALABILITY.RUN Funktion liefert eine Tabelle als Ergebnis.

• Die Anzahl der Sektionen ist hard-coded (300, hervorgehoben).

• Die Anzahl der Wiederholungen ist konfigurierbar.

Das folgende select ruft die Funktion auf, und übergibt die Testabfrage als Parameter:

SELECT *
  FROM TABLE(test_scalability.run(
       'SELECT * ' 
      || 'FROM scale_data '
      ||'WHERE section=:1 '
      ||  'AND id2=CEIL(DBMS_RANDOM.value(1,100))', 10));

Die Gegenprobe, mit einem besseren Index:

DROP INDEX scale_slow;
CREATE INDEX scale_fast ON scale_data (section, id2, id1);


BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'SCALE_DATA' 
                                       , CASCADE => true);
END;
/

SELECT *
  FROM TABLE(test_scalability.run(
       'SELECT * ' 
      || 'FROM scale_data '
      ||'WHERE section=:1 '
      ||  'AND id2=CEIL(DBMS_RANDOM.value(1,10))', 10));

Bemerkungen:

• Der SCALE_SLOW Index wird gelöscht um den „ORA-01408: such column list already indexed“ Fehler zu vermeiden.