Indexdesign mit SQL Access Advisor analysieren

DBA Community - Mai 2016

von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

In der letzten Zeit erhalte ich wieder häufiger Anfragen zu der Überprüfung der Indexstrukturen in der Datenbank. Speziell geht es darum, ob man zu viele oder zu wenige Indizes für die möglichen Zugriffe in der Datenbank angelegt hat. Beide Fragestellungen sind wesentlich bei der Ausführung von Abfragen. Ohne ausreichende Zugriffsstrukturen, die natürlich noch über die Existenz von Indizes hinausgehen können, und ohne adäquate Statistiken kann der Optimizer im Normalfall keine optimalen Ausführungspläne generieren. Randbemerkung: Die Nutzung der Oracle Database In-Memory Option bildet hier eine Ausnahme, was die Existenz und Verwendung der Indizes angeht. Wer mehr über die Verwendung von Database In-Memory wissen möchte, kann das White Paper "When to use Oracle Database In-Memory" zu rate ziehen.

Zu viel des Guten also zu viele Indizes sind natürlich auch nicht von Vorteil, da sie beispielsweise die Ausführung von DML Operation verlangsamt. Für diesen Fall könnte man ein Indexmonitoring einschalten, das zumindest dokumentiert, ob der Index im Monitoring Zeitraum überhaupt verwendet wurde. Mehr zum Indexmonitoring findet sich auch in einem älteren Blog wie zum Beispiel im Blog "Indizes in der Datenbank: Monitoring und Komprimierung (Index Key Compression)".

Eine weitere Möglichkeit diese Problematik anzugehen, wäre die manuelle Methode: Man könnte sich einzelne Ausführungspläne ansehen und sich für oder gegen die entsprechenden Indizes entscheiden. Das Ganze ist natürlich sehr aufwändig und zeitintensiv.

Einfach und schnell geht es mit dem SQL Access Advisor, der im Linemode über das Package DBMS_ADVISOR oder sehr intuitiv und einfach graphisch im Enterprise Manager nutzbar ist. Der SQL Access Advisor steht wie (fast) alle Advisories ohne Installation in der Datenbank zur Verfügung. Bitte beachten Sie dabei die Hinweise zur Lizenzierung (siehe unten). Wie der Name schon andeutet, kommt dieser Advisor bei der Analyse der Zugriffsstrukturen zum Einsatz. Dabei werden im Wesentlichen Indizes, Materialisierte Views und View Logs und die Partitionierung von Tabellen analysiert. So kann ein Analyseergebnis des SQL Access Advisors eine Basis Partitionierung von Tabellen, ein CREATE/DROP oder auch RETAIN von materialisierten Views, View Logs und Indizes enthalten. In den folgenden Abschnitten zeige ich an Beispielen wie die Analyse von Indexstrukturen funktioniert.

Das Konzept

Die Anwendung des SQL Access Advisors funktioniert ganz einfach. Man benötigt nur einen Workload, den man über ein SQL Tuning Set (STS) zur Verfügung stellt. Falls Sie mehr über STS erfahren möchten, können folgende Blogeinträge weiterhelfen:

Hinweis: Bevor Sie mit dem SQL Access Advisor starten, überprüfen Sie den Parameter CONTROL_MANAGEMENT_PACK_ACCESS. Er muss den Wert DIAGNOSTIC+TUNING haben (siehe Abschnitt Lizenzierung).
Danach konfiguriert und parametriert man eine "SQL Access Advisor Task", führt sie aus und generiert sich ein SQL Skript. Folgende Grafik gibt eine gute Zusammenfassung über die Linemode Verwendung.

Die Verwendung

Bevor wir starten, gehen wir davon aus, dass es ein SQL Tuning Set gibt. Hier verwenden wir das SQL Tuning Set SQL_ACCESS2, das vorab aus einem Data Warehouse Workload generiert wurde. Um einen kleinen Überblick über die Inhalte des STS zu bekommen, überprüfen wir das Data Dictionary. Wir haben offensichtlich 34 Statements im SQL Tuning Set gespeichert.

SELECT count(*)
FROM dba_sqlset_statements
WHERE sqlset_name like upper('SQL_ACCESS2');
  2    3
  COUNT(*)
----------
        34

Die Überprüfung kann auch im Enterprise Manager erfolgen. Unter "Performance => SQL => SQL TUNING SET" findet man einen Überblick über alle generierten SQL Tuning Sets und kann sogar ihre Inhalte einsehen.

Nun nutzen wir den SQL Access Advisor im Linemode. Wie oben in der Graphik schon gezeigt, müssen 3 Schritte durchgeführt werden, die wir im folgenden Code anzeigen.

var workload_name varchar2(100)
var task_name varchar2(100)
var task_id number

execute :workload_name := 'SQL_ACCESS2';
-- Schritt 1
execute dbms_advisor.create_task( advisor_name =>'SQL Access Advisor',- 
                                  task_id      =>:task_id,-
                                  task_name    =>:task_name);

-- Parametrierung: nur Index Analyse
execute dbms_advisor.set_task_parameter( task_name => :task_name,- 
                                         parameter => 'ANALYSIS_SCOPE',-
                                         value     => 'INDEX');
-- Parametrierung: Wahl des Modus (LIMITED oder COMPREHENSIVE)
execute dbms_advisor.set_task_parameter( task_name => :task_name,- 
                                         parameter => 'MODE',-
                                         value     => 'LIMITED');

-- Parametrierung: Zeitbegrenzung auf 10 Minuten ansonsten bis zu 720 Minuten
execute dbms_advisor.set_task_parameter( :task_name,'TIME_LIMIT','10');
execute dbms_advisor.set_task_parameter( task_name => :task_name,- 
                                         parameter => 'TIME_LIMIT',-
                                         value     => '10');
-- SQL Tuning Set mit Tuning Set verbinden
execute dbms_advisor.add_sts_ref( task_name     => :task_name,- 
                                  sql_owner     => 'SYS',- 
                                  workload_name => :workload_name);

-- Schritt 2
execute dbms_advisor.execute_task( task_name => :task_name);
-- Schritt 3
execute dbms_advisor.create_file( buffer    => DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
                                  directory => 'HOME',-
                                  filename  => 'advscript1.sql');

Möchte man einen Überblick über die mögliche Parametrierung erhalten, eignet sich folgende Stelle im Database PL/SQL Packages and Types Reference Handbuch.

Ist es erforderlich die Task abzubrechen oder gar zu löschen, kann man die Prozeduren cancel_task bzw. delete_task verwenden. Einen groben Überblick über den Stand der Überprüfung erhält man in der Data Dictionary View USER_ADVISOR_LOG.

SELECT task_id, task_name, status, status_message, pct_completion_time 
FROM user_advisor_log
WHERE status_message!='COMPLETED';

   TASK_ID TASK_NAME            STATUS
---------- -------------------- -----------
STATUS_MESSAGE
--------------------------------------------------------------------------------
PCT_COMPLETION_TIME
-------------------
     70372 TASK_70372           EXECUTING
Generating intial recommendations for each query (0%)                  0

Nach erfolgreicher Ausführung, erhalten wir ein Skript, das Informationen über das Erzeugen neuer Indizes (CREATE) bzw. den Erhalt existierender Indizes (RETAIN) ausgibt. Folgender Code Ausschnitt zeigt ein Beispiel für advscript1.sql.

Rem  SQL Access Advisor: Version 12.1.0.2.0 - Production
Rem
Rem  Username:        DWH_DATA
Rem  Task:            TASK_70321
Rem  Execution date:
Rem

/* RETAIN INDEX "DWH_DATA"."FACT_701_CC_IDX" */

/* RETAIN INDEX "DWH_DATA"."FACT_701_PER_IDX" */

/* RETAIN INDEX "DWH_DATA"."FACT_701_ITEM_IDX" */

CREATE INDEX "DWH_DATA"."LU_ELEMENTGROU_IDX$$_112B10000"
    ON "DWH_DATA"."LU_ELEMENTGROUP_REL"
    ("ELEMENTGROUP_ID","VALUE_ID")
    COMPUTE STATISTICS;

CREATE BITMAP INDEX "DWH_DATA"."FACT_PD_OUT_IT_IDX$$_112B10001"
    ON "DWH_DATA"."FACT_PD_OUT_ITM_701"
    ("PROJECT_TYPE_ID")
    COMPUTE STATISTICS;
...

Ist man an überflüssigen Indizes interessiert, muss man den Parameter WORKLOAD_SCOPE noch zusätzlich auf FULL setzen. (Der Default ist PARTIAL).

-- im Bereich Parametrierung
execute dbms_advisor.set_task_parameter( task_name => :task_name,-
                                         parameter => 'WORKLOAD_SCOPE',-
                                         value     => 'FULL');

Das Ergebnis sieht dann folgendermaßen aus:

...

CREATE INDEX "DWH_DATA"."LU_CATEGORY_70_IDX$$_1131F000E"
    ON "DWH_DATA"."LU_CATEGORY_701"
    ("CATEGORY_ID")
    COMPUTE STATISTICS;

CREATE INDEX "DWH_DATA"."LU_COUNTRY_CHA_IDX$$_1131F000F"
    ON "DWH_DATA"."LU_COUNTRY_CHANNEL_701"
    ("COUNTRY_CHANNEL_ID")
    COMPUTE STATISTICS;

DROP INDEX "DWH_DATA"."FACT_701_CHANNEL_IDX";

DROP INDEX "DWH_DATA"."FACT_701_COUNTRY_IDX";
...

Wichtig zu wissen ist auf jeden Fall noch das Folgende: Da der Advisor die Statements zur Analyse auch ausführt, bedeutet dies eine gewisse Last auf dem System. Sie sollten also das SQL Tuning Set nicht zu groß wählen oder den SQL Access Advisor zeitlich begrenzen - zum Beispiel mit TIME_LIMIT. Um gute Resultate erzielen zu können, sollten Sie auf jeden Fall aktuelle Statistiken haben.

Ist Ihnen der Linemode zu umständlich, können Sie auch Analysen über den Enterprise Manager mit ein paar Klicks bewerkstelligen. Nutzen Sie dafür folgenden Menübereich "Performance=>SQL=> SQL Access Advisor". Im ersten Schritt können Sie dann zwischen der reinen Überprüfung von existierenden Strukturen oder der Überprüfung von neuen Strukturen wählen. Danach geben Sie ein existierendes SQL Tuning Set an oder Sie erzeugen ein neues SQL Tuning Set in einem Schritt. Im weiteren Verlauf gibt es auch hier die Möglichkeit eine umfangreiche Parametrierungen des SQL Access Advisors durchzuführen. Das Ergebnis der Analyse ist auf "Adviors Home" in mehreren Ausführungen zu finden.

Folgende Screenshots zeigen ausschnittsweise ein paar Schritte im Enterprise Manager Cloud Control 12c.

Wir starten mit einer Auswahl der Analyse.

Im nächsten Schritt generiert man entweder ein SQL Tuning Set oder nutzt ein Vorhandenes.

Im dritten Schritt wird die Parametrierung eingestellt.

Zum Schluss lässt sich das Ergebnis über den Menüpunkt "Performance=> Advisors Home" einsehen. Wählen Sie den Menüpunkt "SQL Statements" um die entsprechenden SQL Statements einzusehen.

Das Fazit

Der SQL Access Advisor bietet eine unkomplizierte Methode um das Indexdesign zu überprüfen. Es ist nur ein Workload erforderlich, um einen Test durchzuführen. Die einfachste Verwendung erfolgt dabei über den Enterprise Manager. Hier ist es in einem Prozess möglich, ein SQL Tuning Set zu generieren und danach den SQL Access Advisor zu parametrieren und zu starten. Wie bei allen Advisors sollten Sie das Endergebnis noch einmal inhaltlich überprüfen, bevor Sie am Indexdesign etwas ändern. Bitte beachten Sie auch, dass eine vollständige Analyse Zeit und gewissen Overhead erfordern kann. Falls Sie kein Testsystem zur Ausführung zur Verfügung haben, nutzen Sie die Parametrierung des SQL Access Advisors oder schränken Sie das SQL Tuning Set ein.

Lizenzierungshinweis

Der SQL Access Advisor erfordert wie der SQL Tuning Advisor die Lizensierung des Oracle Database Tuning Packs.

Weitere Informationen

Zurück zur Community-Seite