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>

• 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.

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;

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;

Zurück zur Community-Seite