Logo Oracle Deutschland   DBA Community  -  April 2013
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. 
    SQL> SELECT count(*) Anzahl
     FROM sh.customer_test
     WHERE cust_state_province = 'Bayern' AND cust_city = 'Erding';

    ANZAHL
----------
        69

SQL> SELECT plan_table_output
     FROM TABLE(dbms_xplan.display_cursor(format=>'Basic Rows'));
...
Plan hash value: 2834124715
----------------------------------------------------
| Id  | Operation          | Name          | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT   |               |       |
|   1 |  SORT AGGREGATE    |               |     1 |
|   2 |   TABLE ACCESS FULL| CUSTOMER_TEST |     1 |
----------------------------------------------------
    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. 
    SQL> SELECT count(*) Anzahl
     FROM sh.customer_test
     WHERE cust_state_province = 'Bayern';
    
    ANZAHL
----------
      1837

SQL> SELECT plan_table_output
     FROM TABLE(dbms_xplan.display_cursor(format=>'Basic Rows'));
...
Plan hash value: 2834124715
----------------------------------------------------
| Id  | Operation          | Name          | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT   |               |       |
|   1 |  SORT AGGREGATE    |               |     1 |
|   2 |   TABLE ACCESS FULL| CUSTOMER_TEST |  1808 |
----------------------------------------------------

SQL> SELECT count(*) Anzahl
     FROM sh.customer_test
     WHERE cust_city = 'Erding';

    ANZAHL
----------
        69

SQL> SELECT plan_table_output
     FROM TABLE(dbms_xplan.display_cursor(format=>'Basic Rows'));
...
Plan hash value: 2834124715
----------------------------------------------------
| Id  | Operation          | Name          | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT   |               |       |
|   1 |  SORT AGGREGATE    |               |     1 |
|   2 |   TABLE ACCESS FULL| CUSTOMER_TEST |    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. 
    -- Löschen der Statistiken
-- execute dbms_stats.drop_extended_stats('sh','CUSTOMER_TEST','(CUST_STATE_PROVINCE,CUST_CITY)');
-- Anlegen der Statistiken

SQL> variable var1 varchar2(200)
SQL> execute :var1 := dbms_stats.create_extended_stats(null,'customer_test','(cust_state_province,cust_city)');
PL/SQL procedure successfully completed.

SQL> SELECT extension, extension_name 
     FROM dba_stat_extensions WHERE table_name='CUSTOMER_TEST';

EXTENSION                           EXTENSION_NAME
----------------------------------- ------------------------------
("CUST_STATE_PROVINCE","CUST_CITY") SYS_STUZ8ID7$CBLZWS#6BT8H7W19H
    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. 
    SQL> execute dbms_stats.gather_table_stats(user,'CUSTOMER_TEST');
PL/SQL procedure successfully completed.

-- Statistiken mit Histogrammen 
-- SQL> execute dbms_stats.gather_table_stats('SH','CUSTOMER_TEST', -
--      METHOD_OPT =>'FOR COLUMNS (CUST_STATE_PROVINCE,CUST_CITY) SIZE SKEWONLY');

SQL> SELECT column_name, num_distinct 
     FROM dba_tab_col_statistics WHERE table_name='CUSTOMER_TEST' ;

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
SYS_STUZ8ID7$CBLZWS#6BT8H7W19H          620
CUST_VALID                                2
CUST_EFF_TO                               0
...
    Überprüft man nun wieder die Abfrage aus unserem Beispiel, erhält man ROW Kalkulationen, die dem tatsächlichen Ergebnis sehr nahe kommen. 
    ...
select count(*) Anzahl      from sh.customer_test      where
cust_state_province = 'Bayern' and cust_city = 'Erding'

Plan hash value: 2834124715
----------------------------------------------------
| Id  | Operation          | Name          | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT   |               |       |
|   1 |  SORT AGGREGATE    |               |     1 |
|   2 |   TABLE ACCESS FULL| CUSTOMER_TEST |    67 |
    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. 
    execute dbms_stats.seed_col_usage (sqlset_name=>null, owner_name=> null, time_limit=> 180);
    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. 
    SQL> SET LONG 100000 LINES 120 PAGES 0
SQL> SELECT dbms_stats.report_col_usage('SH', 'CUSTOMER_TEST') FROM dual;
LEGEND:
.......
EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.CUSTOMER_TEST
........................................
1. CUST_CITY                           : EQ
2. CUST_STATE_PROVINCE                 : EQ
3. (CUST_CITY, CUST_STATE_PROVINCE)    : FILTER
###############################################################################
    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 . 
    SQL> SET LONG 100000 LINES 120 PAGES 0
SQL> SELECT dbms_stats.create_extended_stats('SH','CUSTOMER_TEST') FROM dual;

###############################################################################
EXTENSIONS FOR SH.CUSTOMER_TEST
...............................
1. (CUST_CITY, CUST_STATE_PROVINCE)    : SYS_STUWMBUN3F$#398R7BS0YVS86R created
###############################################################################
-- Ueberpruefung des Ergebnis
SQL> SET PAGES 14
SQL> COL extension FORMAT a40
SQL> SELECT extension, extension_name
     FROM dba_stat_extensions WHERE table_name='CUSTOMER_TEST';
EXTENSION                                EXTENSION_NAME
---------------------------------------- ------------------------------
("CUST_CITY","CUST_STATE_PROVINCE")      SYS_STUWMBUN3F$#398R7BS0YVS86R
    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