Pflege der Aurora-PostgreSQL-Ausführungspläne

Die Abfrageplanverwaltung stellt Techniken und Funktionen zum Hinzufügen, Pflegen und Verbessern von Ausführungsplänen bereit.

Auswerten der Performance von Plänen

Nachdem der Optimierer Pläne als „Nicht genehmigt“ erfasst hat, können Sie mit der Funktion apg_plan_mgmt.evolve_plan_baselines Pläne hinsichtlich ihrer tatsächlichen Performance miteinander vergleichen. Je nach Ergebnis dieses Performance-Vergleichs können Sie den Status eines Plans von „Nicht genehmigt“ zu „Genehmigt“ oder „Abgelehnt“ ändern. Alternativ besteht die Möglichkeit, mit der Funktion apg_plan_mgmt.evolve_plan_baselines einen Plan auf bestimmte Zeit zu deaktivieren, falls er Ihre Anforderungen nicht erfüllt.

Genehmigen besserer Pläne

Im folgenden Beispiel wird beschrieben, wie der Status verwalteter Pläne mit der Funktion apg_plan_mgmt.evolve_plan_baselines auf „Genehmigt“ geändert werden kann.

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';

NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)     

In der Ausgabe wird ein Performance-Bericht für dierangequery-Anweisung mit Parameterbindungen von 1 und 10 000 angezeigt. Der neue nicht genehmigte Plan (Baseline+1) ist besser als der beste zuvor genehmigte Plan (Baseline). Rufen Sie die Approved-Ansicht auf, um zu sehen, ob der neue Plan nun den Status „apg_plan_mgmt.dba_plans“ aufweist.

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;

sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

Der verwaltete Plan enthält jetzt zwei genehmigte Pläne, die zusammen die Plan-Baseline der Anweisung bilden. Sie können auch die Funktion „apg_plan_mgmt.set_plan_status“ aufrufen, um das Statusfeld eines Plans direkt auf 'Approved', 'Rejected', 'Unapproved' oder 'Preferred' festzulegen.

Ablehnen oder Deaktivieren langsamerer Pläne

Leiten Sie 'reject' oder 'disable' als Aktionsparameter an die Funktion apg_plan_mgmt.evolve_plan_baselines weiter, um Pläne abzulehnen oder zu deaktivieren. In diesem Beispiel wird jeder erfasste Plan mit dem Status „Unapproved“ deaktiviert, der um mindestens 10 Prozent langsamer als der beste Plan mit dem Status „Approved“ der Anweisung ist.

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

Sie können den Status eines Plans auch direkt auf „Abgelehnt“ oder „Deaktiviert“ setzen. Rufen Sie die Funktion true auf, um das aktivierte Feld eines Plans direkt auf false oder apg_plan_mgmt.set_plan_enabled festzulegen. Mit der Funktion „'Approved'“ legen Sie das Statusfeld eines Plans direkt auf 'Rejected', 'Unapproved', 'Preferred' oder apg_plan_mgmt.set_plan_status fest.

Validieren von Plänen

Verwenden Sie die Funktion apg_plan_mgmt.validate_plans, um ungültige Pläne zu löschen oder zu deaktivieren.

Pläne können ungültig oder veraltet werden, wenn Objekte entfernt werden, von denen sie abhängig sind (z. B. ein Index oder eine Tabelle). Wenn das entfernte Objekt jedoch erneut erstellt wird, kann ein Plan auch nur für eine bestimmte Zeit ungültig sein. Falls ein ungültiger Plan zu einem späteren Zeitpunkt gültig werden kann, ist es möglicherweise sinnvoller, diesen nicht zu löschen, sondern zu deaktivieren oder einfach nichts zu unternehmen.

Über die Funktion apg_plan_mgmt.validate_plans suchen und löschen Sie alle ungültigen und während der vergangenen Woche nicht verwendeten Pläne. Gehen Sie hierzu folgendermaßen vor:

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') 
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');

Verwenden Sie die Funktion apg_plan_mgmt.set_plan_enabled zum direkten Aktivieren oder Deaktivieren eines Plans.

Reparieren von Plänen mit pg_hint_plan

Mit dem Abfrageoptimierer kann für jede Anweisung der bestmögliche Plan gefunden werden. In den meisten Fällen findet der Abfrageoptimierer in der Tat einen guten Plan. Manchmal gibt es jedoch einen weitaus besseren als den vom Optimierer erstellten Plan. Um zu gewährleisten, dass der Optimierer einen passenden Plan erstellt, wird zum einen die Verwendung der Erweiterung pg_hint_plan, zum anderen die Festlegung der Grand Unified Configuration (GUC)-Variablen in PostgreSQL empfohlen:

• pg_hint_plan-Erweiterung – Geben Sie mithilfe der pg_hint_plan-Erweiterung von PostgreSQL einen „Hinweis“, um die Arbeitsweise des Planers zu ändern. Informationen zur Installation und Verwendung der pg_hint_plan-Erweiterung finden Sie in der pg_hint_plan-Dokumentation.

• GUC-Variablen: Überschreiben Sie einen oder mehrere Kostenmodellparameter oder Parameter des Optimierers, wie z. B. from_collapse_limit oder GEQO_threshold.

Wenn Sie den Abfrageoptimierer mit einer dieser Methoden zur Verwendung eines Plans zwingen, können Sie auch die Abfrageplanverwaltung nutzen, um den neuen Plan zu erfassen und dessen Anwendung durchzusetzen.

Mit der Erweiterung pg_hint_plan können Sie die Join-Reihenfolge, die Join-Methoden oder die Zugriffspfade für eine SQL-Anweisung ändern. Sie nutzen einen SQL-Kommentar mit einer besonderen pg_hint_plan-Syntax, um die üblicherweise vom Optimierer angewendete Planerstellungsmethode zu ändern. Angenommen, die entsprechende SQL-Anweisung verfügt über eine Zwei-Wege-Verknüpfung.

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;            

Der Optimierer wählt nun die Join-Reihenfolge (t1, t2) aus. Wir wissen jedoch, dass die Reihenfolge (t2, t1) schneller ist. Der folgende Hinweis zwingt den Optimierer dazu, die schnellere Join-Reihenfolge (t2, t1) zu wählen. Schließen Sie EXPLAIN ein, damit der Optimierer einen Plan für die SQL-Anweisung generiert, ohne jedoch die Anweisung auszuführen. (Ausgabe wird nicht angezeigt.)

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id; 

Im Folgenden wird beschrieben, wie Sie verwende pg_hint_plan.

So ändern und erfassen Sie mit pg_hint_plan den vom Optimierer erstellten Plan:

1. Aktivieren Sie den manuellen Erfassungsmodus.

   SET apg_plan_mgmt.capture_plan_baselines = manual;

2. Geben Sie für die entsprechende SQL-Anweisung einen Hinweis an.

   /*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
   FROM t1, t2 
   WHERE t1.id = t2.id; 

   Nach der Ausführung erfasst der Optimierer den Plan in der apg_plan_mgmt.dba_plans-Ansicht. Der erfasste Plan berücksichtigt nicht die Syntax des besonderen Kommentars pg_hint_plan, weil die Abfrageplanverwaltung die Anweisung durch das Entfernen führender Kommentare normalisiert.

3. Sie können die verwalteten Pläne über die apg_plan_mgmt.dba_plans-Ansicht anzeigen.

   SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
   FROM apg_plan_mgmt.dba_plans;

4. Legen Sie den Status des Plans auf fes Preferred. Dadurch wird sichergestellt, dass der Optimierer den Plan ausführt, anstatt ihn aus dem Satz an genehmigten Plänen auszuwählen, wenn der Minimalkostenplan nicht bereits Approved oder Preferred ist.

   SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' ); 

5. Deaktivieren Sie die manuelle Planerfassung und erzwingen Sie die Verwendung verwalteter Pläne.

   SET apg_plan_mgmt.capture_plan_baselines = false;
   SET apg_plan_mgmt.use_plan_baselines = true;

   Während der Ausführung der ursprünglichen SQL-Anweisung wählt der Optimierer einen Plan aus, der entweder Approved oder Preferred ist. Wenn der Minimalkostenplan weder den Status „Approved“ noch „Preferred“ aufweist, wählt der Optimierer den Plan mit Status „Preferred“ aus.

Löschen von Plänen

Pläne werden automatisch gelöscht, wenn sie seit über einem Monat, genauer gesagt 32 Tagen, nicht mehr verwendet wurden. Dies ist die Standardeinstellung des Parameters apg_plan_mgmt.plan_retention_period. Sie können den Aufbewahrungszeitraum des Plans zu einem längeren oder kürzeren Zeitraum ändern, beginnend mit dem Wert 1. Die Bestimmung der Anzahl von Tagen, seit ein Plan zuletzt verwendet wurde, wird berechnet, indem das last_used-Datum vom aktuellen Datum abgezogen wird. Das last_used-Datum ist das letzte Datum, an dem der Optimierer den Plan als Minimalkostenplan ausgewählt hat oder der Plan ausgeführt wurde. Das Datum wird in der apg_plan_mgmt.dba_plans-Ansicht für den Plan gespeichert.

Wir empfehlen Ihnen, Pläne zu löschen, die lange nicht mehr verwendet wurden oder nicht nützlich sind. Jeder Plan verfügt über ein last_used-Datum, das vom Optimierer aktualisiert wird, wenn er einen Plan ausführt oder den Plan als Minimalkostenplan für eine Anweisung auswählt. Überprüfen Sie die letzten last_used-Daten, um die Pläne zu identifizieren, die Sie ohne Bedenken löschen können.

Die folgende Abfrage gibt eine dreispaltige Tabelle mit der Gesamtanzahl der Pläne, der Anzahl der Pläne, die nicht gelöscht wurden, und der Anzahl der Pläne, die erfolgreich gelöscht wurden, zurück. Sie enthält eine verschachtelte Abfrage, die ein Beispiel dafür ist, wie Sie die apg_plan_mgmt.delete_plan-Funktion verwenden, um alle Pläne zu löschen, die in den letzten 31 Tagen nicht als Minimalkostenplan ausgewählt wurden und deren Status nicht Rejected lautet.

SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;   

 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

Weitere Informationen finden Sie unter apg_plan_mgmt.delete_plan.

Verwenden Sie die apg_plan_mgmt.validate_plans-Funktion, um Pläne zu löschen, die nicht gültig sind und voraussichtlich ungültig bleiben werden. Mit dieser Funktion können Sie ungültige Pläne löschen oder deaktivieren. Weitere Informationen finden Sie unter Validieren von Plänen.

Wichtig

Wenn Sie Ihre irrelevanten Pläne nicht löschen, verfügen Sie möglicherweise über keinen gemeinsam genutzten Speicher mehr, der für die Abfrageplanverwaltung reserviert wird. Mit dem Parameter apg_plan_mgmt.max_plans legen Sie fest, wie viel Speicher für verwaltete Pläne zur Verfügung steht. Legen Sie diesen Parameter in der benutzerdefinierten Parametergruppe fest und starten Sie Ihre DB-Instance neu, damit die Änderungen wirksam werden. Informieren Sie sich über den Parameter apg_plan_mgmt.max_plans, um weitere Informationen hierzu zu erhalten.

Exportieren und Importieren von Plänen

Sie können Ihre verwalteten Pläne exportieren und in eine andere DB-Instance importieren.

So exportieren Sie verwaltete Pläne:

Autorisierte Benutzer können ein beliebiges Subset der apg_plan_mgmt.plans-Tabelle in eine andere Tabelle kopieren und dann mit dem Befehl pg_dump speichern. Im Folgenden wird ein Beispiel gezeigt.

CREATE TABLE plans_copy AS SELECT * 
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;

% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar

DROP TABLE apg_plan_mgmt.plans_copy;

So importieren Sie verwaltete Pläne:

1. Kopieren Sie die .tar-Datei der exportierten verwalteten Pläne in das System, in dem die Pläne wiederhergestellt werden sollen.

2. Verwenden Sie den Befehl pg_restore, um die .tar-Datei in eine neue Tabelle zu kopieren.

   % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar

3. Führen Sie die Tabellen plans_copy und apg_plan_mgmt.plans entsprechend dem folgenden Beispiel zusammen.

   Anmerkung

   In einigen Fällen verwenden Sie möglicherweise für die Sicherung eine bestimmte Version der Erweiterung apg_plan_mgmt und für die Wiederherstellung eine andere Version der Erweiterung. In diesem Fällen fallen die Spalten in der Plantabelle möglicherweise unterschiedlich aus. Geben Sie den Spalten in diesem Fall einen bestimmten Namen, anstatt SELECT* zu verwenden.

   INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
    ON CONFLICT ON CONSTRAINT plans_pkey
    DO UPDATE SET
    status = EXCLUDED.status,
    enabled = EXCLUDED.enabled,
    -- Save the most recent last_used date 
    --
    last_used = CASE WHEN EXCLUDED.last_used > plans.last_used 
    THEN EXCLUDED.last_used ELSE plans.last_used END, 
    -- Save statistics gathered by evolve_plan_baselines, if it ran:
    --
    estimated_startup_cost = EXCLUDED.estimated_startup_cost,
    estimated_total_cost = EXCLUDED.estimated_total_cost,
    planning_time_ms = EXCLUDED.planning_time_ms,
    execution_time_ms = EXCLUDED.execution_time_ms,
    total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, 
    execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;

4. Laden Sie die verwalteten Pläne erneut in den gemeinsam genutzten Speicher und entfernen Sie die Tabelle für die temporären Pläne.

   SELECT apg_plan_mgmt.reload(); -- refresh shared memory
   DROP TABLE plans_copy;