Erweiterte Statistiken (extended statistics)
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG
Aussagekräftige Statistiken auf Tabellen und Spalten sind eine wichtige Voraussetzung, damit Statements mit optimaler Performance ausgeführt werden. Häufig erledigen diese Aufgaben automatische oder eigene Jobs. Generell stellt sich allerdings die Frage, wie werden die Statistiken interpretiert, falls die Spalten in Beziehung (auch Korrelation) zueinander stehen oder durch Ausdrücke verändert werden? Beispiele dafür wären: Ein Fahrzeug wird immer gemeinsam mit einer Farbe ausgewählt; der Nachname wird immer "case sensitiv" abgefragt. Die "normalen" Statistiken auf die einzelnen Spalten können diesen Sachverhalt leider nicht widerspiegeln. Es kommt zu nicht optimalen Ausführungsplänen. Im Falle von korrelierten Spalten werden dabei nur die Einzelstatistiken berücksichtigt und keine zusammengesetzten Statistiken berechnet. Dokumentiert ist die jetzige Limitierung des Cost Based Optimizers auch in der My Oracle Support Note 212809.1 Limitations of the Oracle Cost Based Optimizer
Mit 11g wurde mit dem Feature "extended statistics" endlich Abhilfe geschaffen. Extended Statistics können dabei in folgenden Fällen eingesetzt werden:
Korrelierte Spalten in einer Tabelle und
Spalten mit komplexen Ausdrücken.
Die Verwendung erfolgt dabei in der WHERE Klausel.
Extended Statistics können manuell oder im Falle von korrelierten Spalten sogar automatisch erzeugt werden. Die Statistiken werden dann wie
bei den "normalen" Statistikspalten automatisch bei der Ausführung des Maintenance Jobs mitgepflegt.
Ausgangsbeispiel
Es wird die CUSTOMERS Tabelle aus dem Schema SH verwendet werden. Dort sind Informationen über Kunden gespeichert. Häufig werden die Spalten CUST_STATE_PROVINCE (z.B. Bayern) und CUST_CITY (z.B. Erding) gemeinsam abgefragt. Um einen guten Ausführungsplan zu garantieren, sind vorher aktuelle Statistiken auf der Tabelle erzeugt worden. Die Optimizer Kalkulation geht allerdings von der Reduktion der Ergebnismenge auf einen Satz aus - siehe Spalte ROWS. Tatsächlich handelt es sich aber um 69 Kundensätze, wie das folgende Beispiel zeigt.
Sieht man sich die folgenden Abfragen an, stellt man fest, dass der Optimizer mit seinen Einzelberechnungen/Statistiken von der richtigen Größenordnung ausgeht. In Bayern gibt es ca 1810 Kunden und in Erding ungefähr 70.
Möchte man nun zusätzliche Statistiken zur Verfügung stellen um das Ergebnis zu optimieren, können Erweiterungen im Package DBMS_STATS nützlich sein.
Extended Statistiken erzeugen, berechnen und monitoren
Wie legt man nun zusammengesetzte Statistiken an? Die Funktion CREATE_EXTENDED_STATS aus dem Package DBMS_STATS liefert die Grundlage dazu. Nach der Ausführung für die Spaltenkombination CUST_STATE_PROVINCE und CUST_CITY existiert eine zusätzliche Spalte mit systemdefiniertem Namen "SYS_STUZ8ID7$CBLZWS#6BT8H7W19H" zum Speichern der Statistiken. Monitoren lässt sich das Ergebnis dann in DBA_STAT_EXTENSIONS.
Nachdem die Statistiken wieder neu berechnet worden sind, findet sich die Spalte auch in der Data Dictionary View für Spalten Statistiken (DBA_TAB_COL_STATISTICS). Wie im Skript zu sehen ist, lassen sich auch Histgoramme mit der Option METHOD_OPT für Spaltengruppen berechnen.
Überprüft man nun wieder die Abfrage aus unserem Beispiel, erhält man ROW Kalkulationen, die dem tatsächlichen Ergebnis sehr nahe kommen.
Hinweise: Extended Statistiken finden nur Verwendung bei Abfragen mit dem IN Operator oder Abfragen auf Gleichheit.
Automatisches Erzeugen von Spaltengruppen
Nun stellt sich die Frage, ob diese Spaltengruppen von der Datenbank automatisch angelegt werden können. Dazu ist ein Monitoring der Spaltennutzung erforderlich und ein Kommando zur automatischen Erzeugung von Spaltengruppen. Beides ist möglich mit der Prozedur SEED_COL_USAGE und der Funktion CREATE_EXTENDED_STATS aus DBMS_STATS. Die Prozedur SEED_COL_USAGE überwacht dabei Statements über einen entsprechenden Zeitraum und dokumentiert die Spaltennutzung.
Folgendes Beispiel demonstriert die Handhabung. (Statistiken und Spaltengruppen aus dem vorangegangenen Beispiel sind vorher gelöscht worden.) In einer separaten Session (als User SH) wird SEED_COL_USAGE gestartet; die Zeitangabe ist in Sekunden (also hier 3 Minuten). Parallel dazu werden unter anderem die Statements von oben abgesetzt.
REPORT_COL_USAGE gibt danach das Ergebnis als Report aus. Wie zu sehen ist, wird die Spaltengruppe (CUST_CITY, CUST_STATE_PROVINCE) im Filter verwendet.
Das automatische Erzeugen wird mit der schon bekannten Funktion CREATE_EXTENDED_STATS durchgeführt. Eine Angabe der Spalten ist hier allerdings nicht notwendig. Die Statistiken müssen, wie oben schon demonstriert, in einem zusätzlichen Schritt erzeugt werden. Eine Beschreibung zur automatischen Erzeugung von Spaltengruppen findet sich auch in My Oracle Support Note 1430317.1 .
Informationen und hilfreiche Links
Folgende Handbucheinträge und My Oracle Support (MOS) Notes können nützlich sein:
Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...
Zurück zur Community-Seite
|