Logo Oracle Deutschland   Datenbank Community  -   Dezember 2015
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. Es 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. 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. Es kann zu nicht optimalen Ausführungsplänen kommen. Limitations of the Oracle Cost Based Optimizer

Schon in 11g wurde mit dem Feature "extended statistics" Abhilfe geschaffen. Extended Statistics können dabei in folgenden Fällen eingesetzt werden:

  • Korrelierte Spalten in einer Tabelle und
  • Spalten mit komplexen Ausdrücken.

  • Zur Anwendung kommen diese Statistiken dabei nur in der WHERE Klausel, bei Abfragen auf korrelierte Spalten nur in Verbindung mit dem IN Operator oder bei Abfragen auf Gleichheit.

    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. Ganz neu in Oracle Database 12c ist die Verwendung von Extended Statistics, wenn "SQL Direktive" eine Anwendung finden. Dies ist immer dann der Fall, wenn der Parameter OPTIMIZER_ADAPTIVE_FEATURES auf TRUE (Default) steht.

    Ausgangsbeispiel

    Um zu verstehen, welche Funktion "Extended Statistics" übernehmen können, wollen wir das erste Beispiel in einer 11g Datenbank demonstrieren bzw. in Oracle Database 12c mit Parametereinstellung FALSE für OPTIMIZER_ADAPTIVE_FEATURES.

    Es wird die CUSTOMER_TEST Tabelle verwendet, die eine Kopie der CUSTOMERS Tabelle aus dem Schema SH darstellt. Einziger Unterschied ist die Benennung der Spalten statt CUST_STATE_PROVINCE einfach nur PROVINCE und statt CUST_CITY nur CITY. In dieser Tabelle sind Informationen über Kunden gespeichert und häufig werden die Spalten PROVINCE (z.B. Bayern) und CITY (z.B. Erding) gemeinsam abgefragt. Um einen guten Ausführungsplan zu garantieren, sind vorher einfache (ohne Histogramme) 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 province = 'Bayern' AND city = 'Erding';
    
        ANZAHL
    ----------
            69
    
    SQL> SELECT plan_table_output
         FROM TABLE(dbms_xplan.display_cursor());
    ...
    Plan hash value: 2834124715
    ----------------------------------------------------
    | Id  | Operation          | Name          | Rows  |
    ----------------------------------------------------
    |   0 | SELECT STATEMENT   |               |       |
    |   1 |  SORT AGGREGATE    |               |     1 |
    |   2 |   TABLE ACCESS FULL| CUSTOMER_TEST |     2 |
    ----------------------------------------------------
    
    Sieht man sich die folgenden Abfragen an, stellt man fest, dass der Optimizer mit seinen Einzelberechnungen/Statistiken grundsätzlich 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 province = 'Bayern';
        
        ANZAHL
    ----------
          1837
    
    SQL> SELECT plan_table_output
         FROM TABLE(dbms_xplan.display_cursor(format=>'Basic Rows'));
    ...
    ----------------------------------------------------
    | Id  | Operation          | Name          | Rows  |
    ----------------------------------------------------
    |   0 | SELECT STATEMENT   |               |       |
    |   1 |  SORT AGGREGATE    |               |     1 |
    |   2 |   TABLE ACCESS FULL| CUSTOMER_TEST |  1837 |
    ----------------------------------------------------
    
    SQL> SELECT count(*) Anzahl
         FROM sh.customer_test
         WHERE 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 |    61 |
    ----------------------------------------------------
    
    Möchte man nun zusätzliche Statistiken zur Verfügung stellen um das Ergebnis zu optimieren, kommen "Extended Statistics" zum Tragen.

    Hinweis: Ich möchte an dieser Stelle nicht verschweigen, dass bei wiederholter Ausführung unseres Ausgangsstatements mit einer besseren Schätzung der Rows zu rechnen ist. Die Begründung liegt im Feature "Cardinality Feedback", das ebenfalls in 11g Release 2 eingeführt worden ist und häufig im "Note" Abschnitt von DBMS_XPLAN zu sehen ist. Cardinality Feedback hilft in unterschiedlichen Situationen (zum Beispiel auch bei korrelierten Spalten) und nach mehrfacher Statement Ausführung eine korrekte Kardinalität zu berechnen, um einen optimalen Plan zu generieren. Allerdings werden diese Statistiken nicht gespeichert und können unter Umständen auch weniger gute Schätzungen als "Extended Statistics" abgeben.

    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 PROVINCE und CITY existiert eine zusätzliche Spalte mit systemdefiniertem Namen "SYS_STUQUWEO00K8K_HUHYBPT0#2Q0" 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','(PROVINCE,CITY)');
    -- Anlegen der Statistiken
    SQL> select dbms_stats.create_extended_stats(null,'customer_test','(province,city)') from dual;
    
    DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'CUSTOMER_TEST','(PROVINCE,CITY)')
    ------------------------------------------------------------------------------------------------
    SYS_STUQUWEO00K8K_HUHYBPT0#2Q0
    
    SQL> SELECT extension, extension_name 
         FROM dba_stat_extensions WHERE table_name='CUSTOMER_TEST';
    
    EXTENSION                           EXTENSION_NAME
    ----------------------------------- ------------------------------
    ("PROVINCE","CITY")                 SYS_STUQUWEO00K8K_HUHYBPT0#2Q0
    
    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.
    -- Statistiken mit Histogrammen 
    SQL> execute dbms_stats.gather_table_stats('SH','CUSTOMER_TEST');
                       
    SQL> SELECT column_name, num_distinct 
         FROM dba_tab_col_statistics WHERE table_name='CUSTOMER_TEST' ;
    
    COLUMN_NAME                    NUM_DISTINCT
    ------------------------------ ------------
    ...
    PROVINCE                                145
    ...
    SYS_STUQUWEO00K8K_HUHYBPT0#2Q0          620
    
    24 rows selected.
    
    Überprüft man nun wieder die Abfrage aus unserem Beispiel, erhält man Berechnungen (Spalte ROWS), die dem tatsächlichen Ergebnis sehr nahe kommen.
    ...
    
    SQL> SELECT plan_table_output
         FROM TABLE(dbms_xplan.display_cursor(format=>'Basic Rows'));  2
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------
    EXPLAINED SQL STATEMENT:
    ------------------------
    select count(*) Anzahl from sh.customer_test where
    province = 'Bayern' and city like 'Erding'
    
    Plan hash value: 2834124715
    
    ----------------------------------------------------
    | Id  | Operation          | Name          | Rows  |
    ----------------------------------------------------
    |   0 | SELECT STATEMENT   |               |       |
    |   1 |  SORT AGGREGATE    |               |     1 |
    |   2 |   TABLE ACCESS FULL| CUSTOMER_TEST |    69 |
    ---------------------------------------------------
    
    15 rows selected.
    
    Wichtiger Hinweis: Das Löschen von Indizes bzw. ein Umbenennen von Spalten funktionieren danach nur noch, wenn vorher die "Extended Statistics" gelöscht worden sind.

    Was macht man, wenn die Spaltenkombination unbekannt ist? Hierzu wird ein Hilfsmittel zur Verfügung gestellt um zum einen die Spaltennutzung zu überwachen und danach die Erzeugung dieser Spaltengruppe zu veranlassen. Die Prozedur SEED_COL_USAGE überwacht dabei die Statementausführungen ü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 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=> 'SH', time_limit=> 180);
    
    REPORT_COL_USAGE gibt danach das Ergebnis als Report aus. Wie zu sehen ist, wird die Spaltengruppe (CITY, PROVINCE) im Filter verwendet.
    SQL> SET LONG 100000 LINES 120 PAGES 0
    SQL> SELECT dbms_stats.report_col_usage(owname=>'SH', tabname=>'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. CITY                                : EQ
    2. PROVINCE                            : EQ
    3. SYS_STUG3EB4#FRSZR_VFBCCZBG53K      : EQ
    4. (CITY, PROVINCE)                    : FILTER
    ###############################################################################
    
    
    Das automatische Erzeugen wird mit der schon bekannten Funktion CREATE_EXTENDED_STATS durchgeführt. Eine Angabe der Spalten ist allerdings nicht notwendig. Die Statistiken müssen, wie oben schon demonstriert, in einem zusätzlichen Schritt erzeugt werden.
    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
    


    Extended Statistics in Oracle Database 12c

    Die gerade eben gezeigten Funktionen existieren natürlich auch in Oracle Database 12c. Wie in der Einleitung schon angedeutet, gibt es im aktuellen Datenbank Release (Stand 12.1.0.2) noch zusätzliche Funktionen, die "SQL Plan Direktive", die auch im Zusammenhang mit "Extended Statistics" stehen. Es würde an dieser Stelle zu weit führen, im Detail auf SQL Plan Direktive einzugehen. Soviel nur in Kürze: SQL Plan Direktive sind Hinweise und Instruktionen für den Optimizer, um einen optimalen Plan zu generieren. Sie werden während der SQL Ausführung erzeugt, falls beispielsweise falsche Schätzungen vorliegen und zusätzliche Statistiken erforderlich sind. Nachfolgende DBMS_STATS Aufrufe können dann "Extended Statistics" - übrigens nur für Spaltengruppe, nicht für Ausdrücke - erzeugen. SQL Plan Direktive sind übrigens nicht an ein SQL Statement oder SQL ID gebunden. Immer dann wenn ein ähnliches SQL Statement kompiliert wird, untersucht der Optimizer das Statement auf SQL Plan Direktive. Die Datenbank verwaltet SQL Plan Direktive selbst. Zuerst werden sie im Shared Pool erzeugt und danach periodisch in den SYSAUX Tablespace geschrieben.

    Wer mehr darüber erfahren möchten, kann die aktuellen Handbücher und exzellenten SQL Tuning Blogeinträge im Internet heranziehen.

    Betrachten wir nun das Verhalten in Oracle Database 12c. Wichtige Voraussetzung ist, das Einschalten der Funktion über OPTIMIZER_ADAPTIVE_FEATURES.
    SQL> show parameter optimizer_adaptive
    optimizer_adaptive_features          boolean     TRUE
    
    Um das Default Verhalten zu zeigen, sind alle "Extended Statistics" für die Tabelle CUSTOMER_TEST vorab gelöscht worden. Untersuchen wir allerdings die anderen Tabellen, wird man feststellen, dass schon einige "extended statistics" existieren.
    SQL> SELECT extension, extension_name
         FROM dba_stat_extensions WHERE table_name='CUSTOMER_TEST'; 
    no rows selected
    
    SQL> r
         select owner, table_name, extension_name, extension
         from dba_stat_extensions where droppable='YES'
    
    OWNER                TABLE_NAME                EXTENSION_NAME
    -------------------- ------------------------- --------------------------------------------------
    EXTENSION
    --------------------------------------------------------------------------------
    ...
    
    DWH_DATA             LU_PERIOD_701             SYS_STSY#AOJ4X#0UJM8R9E0_TT6PW
    ("PERIOD_ID","PERIODICITY_ID")
    
    DWH_DATA             LU_ITEM_701               SYS_STSJ7F#NPTY6T4JV9DEL49XQAV
    ("ITEM_ID","PRODUCTGROUP_ID","PG_FEATUREVALUE_27_ID")
    
    DWH_DATA             LU_ITEM_701               SYS_STSI9SCCUHSF6CYKV_LZNGJI4Q
    ("ITEM_ID","PRODUCTGROUP_ID","PG_FEATUREVALUE_07_ID")
    
    DWH_DATA             LU_ITEM_701               SYS_STSUQXM$X7OC$ZDDHBQD86LYP4
    ("ITEM_ID","PRODUCTGROUP_ID","PG_FEATUREVALUE_02_ID")
    
    DWH_DATA             LU_ITEM_701               SYS_STS_RHTA#Q79RPXG7$PD#H62O3
    ("ITEM_ID","PRODUCTGROUP_ID")
    
    DWH_DATA             LU_ITEM_701               SYS_STSZ_CJ4X8A7E6W#DJCI_H6H_K
    ("PG_FEATUREVALUE_02_ID","PG_FEATUREVALUE_20_ID")
    ...
    
    Führen wir im nächsten Schritt unsere Beispielabfrage mehrfach aus.
    SQL> SELECT count(*) Anzahl FROM sh.customer_test
         WHERE province = 'Bayern' AND city = 'Erding';
    
    SQL> SELECT plan_table_output
         FROM TABLE(dbms_xplan.display_cursor();  
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------
    SQL_ID  fd33dt653j6fd, child number 0
    -------------------------------------
    SELECT count(*) Anzahl FROM sh.customer_test      
    WHERE province = 'Bayern' AND city = 'Erding'
    
    Plan hash value: 2834124715
    
    ------------------------------------------------------------------------------------
    | Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |               |       |       |   534 (100)|          |
    |   1 |  SORT AGGREGATE    |               |     1 |    21 |            |          |
    |*  2 |   TABLE ACCESS FULL| CUSTOMER_TEST |    73 |  1533 |   534   (3)| 00:00:01 |
    ------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(("CITY"='Erding' AND "PROVINCE"='Bayern'))
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
       - 1 Sql Plan Directive used for this statement
    
    Wie unser Beispiel zeigt, wird eine gute Schätzung vom Optimizer angenommen, zudem kann man in der Note erkennen, dass offensichtlich eine SQL Plan Direktive angelegt worden ist. Um mehr über SQL Plan Direktive zu erfahren, fragen wir die Data Dictionary Views DBA_SQL_PLAN_DIRECTIVES und DBA_SQL_PLAN_DIR_OBJECTS ab.
    SQL> set linesize 80
    SQL> r
         SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OBJECT_NAME,
               o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
        FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
        WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
        AND    o.object_name='CUSTOMER_TEST'
        AND    o.OWNER IN ('SH');
    
    DIR_ID                                   OBJECT_NAME
    ---------------------------------------- -------------------------
    COL_NAME             OBJECT TYPE             STATE
    -------------------- ------ ---------------- ----------
    REASON
    ------------------------------------
    3096962358398079395                      CUSTOMER_TEST
    CITY                 COLUMN DYNAMIC_SAMPLING USABLE
    SINGLE TABLE CARDINALITY MISESTIMATE
    
    3096962358398079395                      CUSTOMER_TEST
    PROVINCE             COLUMN DYNAMIC_SAMPLING USABLE
    SINGLE TABLE CARDINALITY MISESTIMATE
    
    3096962358398079395                      CUSTOMER_TEST
                         TABLE  DYNAMIC_SAMPLING USABLE
    SINGLE TABLE CARDINALITY MISESTIMATE
    
    Offensichtlich sind SQL Plan Direktive in Verwendung (siehe STATE USABLE), um auf die nicht korrekte Schätzung der Kardinalität hinzuweisen.

    Legt man nun wie oben gezeigt, die entsprechenden "Extended Statistics" manuell an, wird man nach einer gewissen Zeit feststellen, dass der Status der SQL Plan Direktive auf SUPERSEDED umschaltet. Es wurde das Vorhandensein von Extended Statistics entdeckt und bei weiteren Ausführungen genutzt.
    SQL> r
        SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OBJECT_NAME,
                   o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
            FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
            WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
            AND    o.object_name='CUSTOMER_TEST' AND o.OWNER IN ('SH');
        
    
    DIR_ID                                   OBJECT_NAME
    ---------------------------------------- --------------------
    COL_NAME             OBJECT TYPE             STATE
    -------------------- ------ ---------------- ----------
    REASON
    ------------------------------------
    3096962358398079395                      CUSTOMER_TEST
    CITY                 COLUMN DYNAMIC_SAMPLING SUPERSEDED
    SINGLE TABLE CARDINALITY MISESTIMATE
    
    3096962358398079395                      CUSTOMER_TEST
    PROVINCE             COLUMN DYNAMIC_SAMPLING SUPERSEDED
    SINGLE TABLE CARDINALITY MISESTIMATE
    
    3096962358398079395                      CUSTOMER_TEST
                         TABLE  DYNAMIC_SAMPLING SUPERSEDED
    SINGLE TABLE CARDINALITY MISESTIMATE
    
    Eine andere Vorgehensweise würde darin bestehen, den automatischen DBMS_STATS Lauf abzuwarten oder selbst manuell DBMS_STATS auf die Tabelle durchzuführen. Beides würde automatisch dazu führen, dass die entsprechenden Extended Statistics angelegt werden.
    SQL> SELECT extension, extension_name
         FROM dba_stat_extensions WHERE table_name='CUSTOMER_TEST'; 
    
    no rows selected
    SQL> execute dbms_stats.gather_table_stats(ownname=> 'SH',- 
                                               tabname=>'CUSTOMER_TEST',-
                                               method_opt =>'for columns(PROVINCE,CITY)');
    
    PL/SQL procedure successfully completed.
    SQL> SELECT extension, extension_name
         FROM dba_stat_extensions WHERE table_name='CUSTOMER_TEST';
    
    EXTENSION
    --------------------------------------------------------------------------------
    EXTENSION_NAME
    --------------------------------------------------------------------------------
    ("PROVINCE","CITY")
    SYS_STUQUWEO00K8K_HUHYBPT0#2Q0
    
    Das Verhalten ist automatisch über den Parameter OPTIMIZER_ADAPTIVE_FEATURES verfügbar. Es gibt im Moment (noch) keine speziellen Parameter oder "Statistic Preference" (siehe DBMS_STATS.SET_GLOBAL_PREFS), um dieses Feature im Einzelnen zu beeinflussen.

    Zurück zur Community-Seite