Partitionierung mit dem Partition Advisor
von Frank Schneede, ORACLE Deutschland B.V. & Co. KG

Für die Steigerung der Performance von Abfragen ist es von entscheidender Bedeutung, die Menge der Daten, die in einer Abfrage bewegt werden müssen, zu reduzieren. Ein sehr gut geeignetes Mittel hierzu ist der Einsatz von Partitionierung. Mit dieser Funktionalität, die bereits seit Oracle 8i zur Verfügung steht und kontinuierlich erweitert wurde, ist es möglich, große Datenmengen aufzuteilen. Dadurch können Abfragen, die nur eine Teilmenge der Gesamtdatenmenge auswerten, performant ausgeführt werden. Ein Teil der in Oracle 11g erweiterten Partitionierungsmethoden wurde bereits in einem Community-Artikel vorgestellt.

Anwender, die noch nie mit der Technik der Partitionierung in Berührung gekommen sind, tun sich erfahrungsgemäß schwer, geeignete Partitionierungsstrategien festzulegen. In der aktuellen Datenbankversion Oracle 11g wurde der Partition Advisor als Zusatzfunktion des SQL Access Advisors eingeführt. Der SQL Access Advisor gibt dem Datenbankadministrator hilfreiche Empfehlungen, mit deren Hilfe ein bestehendes Datenmodell auf Partitionierung umgestellt werden kann. Dieser Artikel stellt diese spezielle Funktion des SQL Access Advisors an einem kleinen Beispiel vor.

Um den SQL Access Advisor demonstrieren zu können, müssen ein paar Vorbereitungen getroffen werden. In dem hier gezeigten Ablauf besteht das Beispielschema (Skript zum Aufbau des Beispielschemas) aus einem Datenmodell mit einer Faktentabelle (UMSAETZE) und vier Dimensionstabellen (KUNDEN, ORTE, PRODUKTE, TAGE), die nach dem Star-Schema aufgebaut sind. Damit eine sinnvolle Partitionierung vorgenommen werden kann, muß im Datenmodell eine relevante Datenmenge enthalten sein.

SQL> select table_name
     ,      num_rows
     from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
PRODUKTE                            10000
KUNDEN                               1000
TAGE                                 9496
ORTE                                   52
UMSAETZE                         64000000

SQL>

Auf den genannten Tabellen wird eine Last erzeugt, die später durch den SQL Access Advisor ausgewertet werden soll. Die verwendeten Statements stellen typische Abfragen dar, wie sie in einem Datawarehouse vorkommen.

Im ersten Schritt wird nun ein Tuning Set erstellt, das der SQL Access Advisor auswerten soll. Weitergehende Informationen zum Arbeiten mit Tuning Sets finden Sie in dem Artikel SQL Tuning Sets im Einsatz - Teil2. Der SQL Access Advisor wird über den entsprechenden Button gestartet.

Für eine größere Ansicht auf das Bild klicken, zurück zum Artikel mit dem Back-Button des Browser


Der Advisor kann mit drei unterschiedlichen Workloads arbeiten und diese auswerten:
  • Auswertung der aktuellen Last auf der Datenbank
  • Auswertung eines bestehenden Tuning Sets. Wenn man aus der Liste der Tuning Sets den Advisor startet, so wird das ausgewählte Tuning Set hier vorbelegt. Diese Auswahl macht insbesondere dann Sinn, wenn man nächtliche Batch- oder Ladeläufe auswerten und optimieren möchte.
  • Auswertung einer generierten Last auf Tabellen bzw. Schematas, die festgelegt werden können. Diese Auswahl setzt voraus, dass die Tabellen über referentielle Integritäten verbunden sind. Eine generierte Last ist insbesondere dann sinnvoll, wenn noch keine Erfahrungswerte aus dem Produktionsbetrieb vorliegen, sondern nur die Daten bereits in den Tabellen enthalten sind. Das könnte zum Beispiel im Zuge einer Systemmigration sein.

Nach der Festlegung der auszuwertenden Last können noch Filter gesetzt werden, die die Kriterien für die auszuwertenden Statements einschränkt. Auf diese Weise kann die Zeit, die für die Berechnung von Empfehlungen benötigt wird, eingeschränkt werden.

Für eine größere Ansicht auf das Bild klicken, zurück zum Artikel mit dem Back-Button des Browser


Im nächsten Schritt wird festgelegt, welche Strukturen empfohlen werden sollen. Hier kann der DBA wählen, ob er Indizes, Materialized Views oder Partitionierung nutzen möchte, um die Performance zu verbessern. Die Tiefe der durchzuführenden Analyse kann ebenfalls gewählt werden, das wird in der Regel abhängig von der Größe des Tuning Sets und der zur Verfügung stehenden Analysezeit erfolgen. In diesem eng umgrenzten Beispiel wird eine ausführliche Analyse durchgeführt.

Für eine größere Ansicht auf das Bild klicken, zurück zum Artikel mit dem Back-Button des Browser


Über den Database Scheduler wird die SQL Advisor Task gestartet. Es empfiehlt sich, sehr umfassende Tasks in Zeiten geringer Systembelastung zu legen. In diesem Beispiel wurde die Task sofort ("immediately") gestartet. Zum Abschluß werden die eingestellten Optionen zur Kontrolle angezeigt, bevor der SQL Access Advisor Job gestartet wird.

Für eine größere Ansicht auf das Bild klicken, zurück zum Artikel mit dem Back-Button des Browser


Anwender, denen die Arbeit auf Befehlszeilenebene lieber ist, können all diese Schritte natürlich auch in einer SQL*Plus Oberfläche durchführen. Das Skript, das hierfür erstellt werden müsste, enthält die explizite Belegung aller möglichen Parameter zur Steuerung des Advisors. Da das Skript dadurch ziemlich lang wird, sind die Zeilen, die ohnehin nur den Standardwert des Parameters verwenden, im folgenden Listing entfernt worden.
DECLARE
  taskname varchar2(30) := 'SQLACCESS98947';
  task_desc varchar2(256) := 'SQL Access Advisor';
  task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
  task_id number := 0;
  num_found number;
  sts_name varchar2(256) := 'AWR_1277193487109';
  sts_owner varchar2(30) := 'SYSTEM';

BEGIN
  dbms_advisor.reset_task(taskname);
  select count(*) into num_found from user_advisor_sqla_wk_map where task_name = taskname and workload_name = sts_name;
  IF num_found > 0 THEN
    dbms_advisor.delete_sts_ref(taskname, sts_owner, sts_name);
  END IF;
  dbms_advisor.add_sts_ref(taskname,sts_owner, sts_name);
  dbms_advisor.set_task_parameter(taskname,'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);
  dbms_advisor.set_task_parameter(taskname,'INVALID_SQLSTRING_LIST','"@!"');
  dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','INDEX,TABLE,PARTITION');
  dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
  dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
  dbms_advisor.set_task_parameter(taskname,'MODE','COMPREHENSIVE');
  dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
  dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
  dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
  dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
  dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
  dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');
  dbms_advisor.execute_task(taskname);
END;
Die gestartete Advisor Task kann in der Anzeige aller Scheduler Jobs überwacht werden, bei Bedarf ist es in dieser Oberfläche möglich, die Task auch erneut zu starten.

Für eine größere Ansicht auf das Bild klicken, zurück zum Artikel mit dem Back-Button des Browser


Sobald der Job Status auf SUCCEEDED steht, ist die Advisor Task beendet. Dann können über das sogenannte Advisor Central die abgearbeiteten Advisor Tasks angesehen und ausgewertet werden. Die empfohlenen Aktionen und deren vermutliche Auswirkungen auf die Statements werden grafisch und in Tabellenform dargestellt. Wichtig ist es in diesem Zusammenhang, die Auswirkungen in einer geeigneten Testumgebung zu verifizieren, bevor eine Umstellung in der Produktion erfolgt.

Für eine größere Ansicht auf das Bild klicken, zurück zum Artikel mit dem Back-Button des Browser


Die Umstellung kann ebenfalls über einen Scheduler Job gestartet werden, jedoch gibt es an dieser Stelle noch eine Einschränkung in Bezug auf eine Umstellung der Tabellenstruktur mit Partitionierung. So erscheint die Meldung



Daher muss der DBA den SQL Code aus der Anwendung kopieren, ggf. modifizieren und manuell laufenlassen. Das folgende Skript zeigt die vorgeschlagenen Maßnahmen, aus denen sofort klar wird, aus welchem Grunde dieses nicht über den Scheduler der Datenbank automatisch laufengelassen wird. Das Skript nutzt nicht die Methode mittels DBMS_REDEFINITION, sondern es wird eine simple Kopie der Tabelle erzeugt und umbenannt. In der Praxis sollte man daher lieber die Methode DBMS_REDEFINITION verwenden, mit der eine Umstellung der Tabellenstruktur im laufenden Betrieb problemlos möglich ist. Ein sehr hilfreicher Community Tipp zeigt, wie das geht. Trotzdem soll das erzeugte Skript der Vollständigkeit halber an dieser Stelle gezeigt werden. In diesem Beispiel wurde lediglich die Partitionierung umgestellt, der empfohlene Index nicht angelegt.
Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: SYSTEM
Rem Task: SQLACCESS98947
Rem Execution date:
Rem

Rem
Rem Repartitioning table "DEMO"."UMSAETZE"
Rem

SET SERVEROUTPUT ON
SET ECHO ON

Rem
Rem Creating new partitioned table
Rem
CREATE TABLE "DEMO"."UMSAETZE1"
(    "ORTNR" NUMBER(4,0),
    "KUNDENNR" NUMBER(10,0),
    "DATUM" DATE,
    "PRODUKTNR" NUMBER(5,0),
    "SUMME" NUMBER(10,0),
    "MENGE" NUMBER(10,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
PARALLEL
PARTITION BY RANGE ("KUNDENNR") INTERVAL( 30) ( PARTITION VALUES LESS THAN (30)
);

Rem
Rem Copying constraints to new partitioned table
Rem
ALTER TABLE "DEMO"."UMSAETZE1" MODIFY ("ORTNR" NOT NULL ENABLE);

ALTER TABLE "DEMO"."UMSAETZE1" MODIFY ("KUNDENNR" NOT NULL ENABLE);

ALTER TABLE "DEMO"."UMSAETZE1" MODIFY ("DATUM" NOT NULL ENABLE);

ALTER TABLE "DEMO"."UMSAETZE1" MODIFY ("PRODUKTNR" NOT NULL ENABLE);

ALTER TABLE "DEMO"."UMSAETZE1" MODIFY ("SUMME" NOT NULL ENABLE);

ALTER TABLE "DEMO"."UMSAETZE1" MODIFY ("MENGE" NOT NULL ENABLE);

Rem
Rem Populating new partitioned table with data from original table
Rem
INSERT /*+ APPEND */ INTO "DEMO"."UMSAETZE1"
SELECT * FROM "DEMO"."UMSAETZE";
COMMIT;

begin
dbms_stats.gather_table_stats('"DEMO"', '"UMSAETZE1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem
Rem Renaming tables to give new partitioned table the original table name
Rem
ALTER TABLE "DEMO"."UMSAETZE" RENAME TO "UMSAETZE11";
ALTER TABLE "DEMO"."UMSAETZE1" RENAME TO "UMSAETZE";


CREATE INDEX "DEMO"."UMSAETZE_IDX$$_00310000"
ON "DEMO"."UMSAETZE"
("KUNDENNR")
COMPUTE STATISTICS
LOCAL;


Die Messung der Antwortzeiten (Skript mit Statements) nach Durchführung der Optimierungsmaßnahmen ergibt folgendes Bild:

 Unpartitionierte TabellePartitionierte TabelleZeitersparnis
1. Anzahl Sätze (Durchsatz prüfen) 30.99 32.99-2.00
2. Umsatz je Produktsparte und Jahr 7:32.822:37.944:54.88
3. Top 10 Produkte 2:05.961:09.9855.98
4. Top 3 Produkte je Produktgruppe 3:00.931:16.971:43.96
5. Land mit dem stärksten Umsatz je Produktgruppe 2:16.942:02.9513.99
6. die umsatzstärksten Produkte innerhalb einer
Produktgruppe sortiert nach Rangfolge
1:18.981:15.973.01
7. Anwendung CUBE 1:02.9751.98 10.99
8. über Quartale kumulierte Umsätze pro Kunde 4:43.892:41.942:01.95
9. 1/4 der Kunden tragen zu ? % des Umsatzes bei? 3:47.921:43.962:03.96
10. Durchschnittliche Bestellquote eines Kunden ueber 3 Monate2:16.952:27.94-10.99
11. Vergleiche Umsätze mit Vorjahreszeitraum 2:23.942:41.94-18.00


Man sieht deutlich, dass allein die Partitionierung der Faktentabelle UMSAETZE zu einer wesentlichen Verbesserung der Performance geführt hat. Die geringfügig schlechtere Antwortzeit für die Abfragen 1, 10 und 11 rührt daher, dass bei der Ausführung die Partitionen keinen Vorteil (z. B. durch Partition-wise-Joins) bringen, sondern der geringe Mehraufwand an I/O Operationen zu Buche schlägt. Natürlich basiert dieses Ergebnis auf einer Laborumgebung, denn das vorliegende Datenmodell ist ohne Constraints angelegt worden, was in der Praxis sicher nicht vorkommt. Daher ist es umso wichtiger, die vom SQL Access Advisor ermittelten Optimierungsmaßnahmen sorgfältig zu prüfen.

Zum Abschluß noch eine Bemerkung zu den Möglichkeiten, die der SQL Access Advisor in der vorliegenden Version bietet. Momentan werden durch den SQL Access Advisor nur einfache Partitionierungsverfahren und die zusammengesetzte Partitionierung RANGE/HASH oder RANGE/LIST unterstützt. Die neuen Möglichkeiten der zusammengesetzten Partitionierung wie RANGE/RANGE, LIST/RANGE oder auch Referenzpartitionierung werden zur Zeit noch nicht berücksichtigt. Trotzdem bietet der SQL Access Advisor dem Anwender einen guten Einstieg in die Technik der Partitionierung.

Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite