Der Autor des Artikels ist
Victor Varlamov (
varlamovVp18 ), OCP.
Der Originalartikel wurde am 07/07/2017 veröffentlicht.
Besonderer Dank geht an den Autor der Übersetzung -
brutaltag .
Unser Berichtssystem führt normalerweise Hunderte von langen Anforderungen aus, die durch verschiedene Ereignisse ausgelöst werden. Die Abfrageparameter sind eine Liste der Clients und ein Zeitintervall (täglich, wöchentlich, monatlich). Aufgrund der ungleichmäßigen Daten in den Tabellen kann eine Abfrage abhängig von den Berichtsparametern entweder eine Zeile oder eine Million Zeilen erzeugen (verschiedene Clients haben unterschiedliche Anzahlen von Zeilen in den Faktentabellen). Jeder Bericht wird in Form eines Pakets mit einer Hauptfunktion erstellt, die Eingabeparameter verwendet, zusätzliche Transformationen ausführt, dann einen statischen Cursor mit
zugehörigen Variablen öffnet und schließlich diesen geöffneten Cursor zurückgibt. Der DB-Parameter CURSOR_SHARING wird auf FORCE gesetzt.
In einer solchen Situation muss man mit einer schlechten Leistung umgehen, sowohl bei der Wiederverwendung des Abfrageplans durch den Optimierer als auch wenn
die Abfrage
vollständig mit Parametern in Form von
Literalen analysiert wird . Gebundene Variablen können einen suboptimalen Abfrageplan verursachen.
In seinem Buch „Oracle Expert Practices“ gibt Alex Gorbatschow eine interessante Geschichte, die von Tom Kite erzählt wird. Jeden regnerischen Montag mussten sich Benutzer mit einem geänderten Abfrageplan befassen. Es ist schwer zu glauben, aber es war:
„Laut Beobachtungen der Endbenutzer war die Datenbankleistung schrecklich, als es am Montag stark regnete. An jedem anderen Wochentag oder Montag gab es keine Probleme ohne Regen. Aus einem Gespräch mit dem DBA erfuhr Tom Kite, dass die Schwierigkeiten so lange anhielten, bis die Datenbank neu gestartet werden musste. Danach wurde die Leistung normal. Das war so eine Problemumgehung: regnerischer Montag - Neustart. “
Dies ist ein realer Fall, und das Problem wurde vollständig ohne Magie gelöst, nur dank hervorragender Kenntnisse über die Funktionsweise von Oracle. Ich werde die Lösung am Ende des Artikels zeigen.
Hier ist ein kleines Beispiel dafür, wie verwandte Variablen funktionieren.
Erstellen Sie eine Tabelle mit ungleichmäßigen Daten.
SQL> CREATE TABLE VVP_HARD_PARSE_TEST(C1 NUMBER, C2 NUMBER, C3 VARCHAR2(300)); TABLE created. SQL> INSERT INTO VVP_HARD_PARSE_TEST SELECT ROWNUM C1, CASE WHEN LEVEL < 9 THEN 1 WHEN MOD(ROWNUM, 100)=99 THEN 99 ELSE 1000000 END C2, RPAD('A', 300, 'A') C3 FROM DUAL CONNECT BY LEVEL CREATE INDEX IND_VVP_HARD_PARSE_TEST_C2 ON VVP_HARD_PARSE_TEST(C2); INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'VVP_HARD_PARSE_TEST', CASCADE => TRUE, METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254'); PL/SQL PROCEDURE successfully completed. SQL> SELECT histogram FROM user_tab_columns WHERE table_name = 'VVP_HARD_PARSE_TEST' AND column_name = 'C2'; HISTOGRAM
Mit anderen Worten, wir haben eine Tabelle VVP_HARD_PARSE_TEST mit einer Million Zeilen, wobei in 10.000 Fällen das Feld C2 = 99, 8 Datensätze mit C2 = 1 und der Rest mit C2 = 1.000.000 ist. Das Histogramm über das Feld C2 zeigt den Oracle-Optimierer über diese Datenverteilung an. Diese Situation wird als
ungleichmäßige Datenverteilung bezeichnet. Mithilfe eines Histogramms können Sie anhand der angeforderten Daten den richtigen Abfrageplan auswählen.
Wir beobachten einfache Abfragen zu dieser Tabelle. Offensichtlich für die Anfrage
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :pWenn p = 1 ist, ist INDEX RANGE SCAN die beste Wahl. Für den Fall p = 1000000 ist es besser, FULL TABLE SCAN zu verwenden. Query1- und Query1000000-Abfragen sind identisch, mit Ausnahme des Textes in den Kommentaren, um unterschiedliche Abfrageplan-IDs zu erhalten.
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v : =0; p := 1; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
Schauen wir uns nun die Abfragepläne an:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST WHERE C2%'; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
Wie Sie sehen, wird zum Zeitpunkt der ersten Ausführung nur einmal ein Plan für verschiedene Anforderungen erstellt (für jede Anforderung ist nur ein untergeordneter Cursor mit CHILD_NUMBER = 0 vorhanden). Jede Anfrage wird zweimal ausgeführt (EXECUTION = 2). Während des harten Parsens ruft Oracle die Werte der zugeordneten Variablen ab und wählt einen Plan gemäß diesen Werten aus. Er verwendet jedoch denselben Plan für den nächsten Lauf, obwohl sich die zugehörigen Variablen im zweiten Lauf geändert haben. Es werden nicht optimale Pläne verwendet - Query1000000 mit der Variablen C2 = 1 verwendet FULL TABLE SCAN anstelle von INDEX RANGE SCAN und umgekehrt.
Es ist klar, dass das Beheben der Anwendung und das Verwenden von Parametern als Literale in der Abfrage der am besten geeignete Weg ist, um das Problem zu lösen, aber es führt zu dynamischem SQL mit seinen bekannten Mängeln. Eine andere Möglichkeit besteht darin, die Abfrage für verwandte Variablen zu deaktivieren (
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE ) oder Histogramme zu löschen (
Link ).
Eine der möglichen Lösungen ist eine alternative Verwendung von Datenzugriffsrichtlinien, die auch als
Virtual Private Database bezeichnet werden (detaillierte Zugriffskontrolle,
feinkörnige Zugriffskontrolle, Kontrolle auf Zeilenebene ). Auf diese Weise können Sie Anforderungen im laufenden Betrieb ändern und daher jedes Mal, wenn die Anforderung eine detaillierte Zugriffskontrolle verwendet, eine vollständige Analyse des Anforderungsplans durchführen. Diese Technik wird in
einem Artikel von Randalph Geist ausführlich
beschrieben . Der Nachteil dieser Methode ist die zunehmende Anzahl vollständiger Analysen und die Unfähigkeit, Abfragepläne zu manipulieren.
Sehen Sie, was wir jetzt tun werden. Nach der Analyse unserer Daten beschließen wir, Kunden entsprechend der Anzahl der Transaktionen oder Transaktionen im Laufe des Jahres in drei Kategorien zu unterteilen: Groß, Mittel und Klein (LMS oder 9-5-1). Außerdem hängt die Anzahl der Zeilen im Bericht stark vom Zeitraum ab: Monatlich - Groß, Wöchentlich - Mittel, Täglich - Klein oder 9-5-1. Darüber hinaus ist die Lösung einfach: Wir werden das Prädikat für Sicherheitsrichtlinien von jeder Kategorie und jedem Zeitraum abhängig machen. Für jede Anfrage erhalten wir also 9 mögliche Kindercursor. Darüber hinaus führen Abfragen mit unterschiedlichen Richtlinien zu denselben Abfragekennungen. Dies ermöglicht die Implementierung von SQL PLAN MANAGEMENT (SQL Plan Baseline).
SQL> CREATE TABLE HARD_PARSE_TABLE AS SELECT * FROM dual; TABLE created. SQL> CREATE TABLE CLIENTS_HP_STATISTICS (client_seqno NUMBER, client_id VARCHAR2(255), cnt_year NUMBER); TABLE created. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1, 'SMALL CLIENT', 8); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (99, 'MIDDLE CLIENT', 50001); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1000000,'LARGE CLIENT', 989992); 1 ROW inserted. SQL> CREATE OR REPLACE PACKAGE FORCE_HARD_PARSE_PKG IS gc_small CONSTANT NUMBER := 1; gc_middle CONSTANT NUMBER := 5; gc_large CONSTANT NUMBER := 9; gc_client_middle CONSTANT NUMBER := 50000; gc_client_large CONSTANT NUMBER := 500000; gc_daterange_middle CONSTANT NUMBER := 10; gc_daterange_large CONSTANT NUMBER := 50; FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2) RETURN VARCHAR2; PROCEDURE SET_PREDICATE (n NUMBER); PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL, p_clientrange NUMBER DEFAULT NULL); PROCEDURE CALC_PREDICATE; PROCEDURE CALC_PREDICATES(p_date_interval NUMBER DEFAULT 1, p_client_seqno NUMBER DEFAULT NULL, p_client_id VARCHAR2 DEFAULT NULL, p_client_seqno_list VARCHAR2 DEFAULT NULL ); END FORCE_HARD_PARSE_PKG; PACKAGE created. SQL> CREATE OR REPLACE PACKAGE BODY FORCE_HARD_PARSE_PKG IS g_predicate NUMBER;
Wenn wir diese Technologie in den Bericht einbetten möchten, müssen wir der Abfrage HARD_PARSE_TABLE hinzufügen (dies wird sie nicht ein wenig ruinieren) und CALC_PREDICATES aufrufen, bevor die Hauptabfrage ausgeführt wird.
Mal sehen, wie diese Technik das vorherige Beispiel transformieren kann:
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
Schauen wir uns die Ausführungspläne an:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text, s.* FROM v$sql s WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2%' ORDER BY 1,2; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
Sieht gut aus! Jede Abfrage wird zweimal mit unterschiedlichen untergeordneten Cursorn und unterschiedlichen Plänen ausgeführt. Für Parameter C2 = 1.000.000 sehen wir in beiden Abfragen FULL TABLE SCAN, und für Parameter C1 = 1 sehen wir immer INDEX RANGE SCAN.
Am Ende gebe ich eine Lösung für den Fall von regnerischen Montagen:
„Es stellt sich heraus, dass jedes Wochenende am Sonntag ein kaltes Backup durchgeführt wurde , sodass alle Abfragepläne bei der ersten Ausführung am Montagmorgen neu generiert wurden. Einer der Mitarbeiter begann seine Arbeit normalerweise früher als die anderen, und sein Anforderungsplan wurde während der Woche für andere Benutzer gut ausgeführt. Wenn es jedoch regnete, verspätete sich dieser Benutzer zu Beginn des Arbeitstages aufgrund von Problemen mit seiner Morgenroute. Dann wurde als erstes mit der Stapelberechnung von Berichten begonnen, aber der Abfrageplan war für die verbleibenden Fälle aufgrund unangemessener Werte der zugehörigen Variablen völlig schlecht. “
Und ein paar nützliche Systemansichten:
•
dba_tab_histograms, all_tab_histograms, user_tab_histograms•
v$vpd_policy•
v$sql_bind_capture•
dba_hist_sqlbind