|
12.2: Optimizer Statistics Advisor
Aussagekräftige Statistiken auf Datenbankobjekte sind eine wichtige Voraussetzung, damit Statements mit optimaler Performance ausgeführt werden können. Optimizer Statistiken sind beispielsweise dazu da, Informationen über die Datenverteilung und Speicherung der Objekte in der Datenbank zu geben und helfen damit dem Optimizer den besten Ausführungspfad zu finden. Statistiken lassen sich manuell oder auch automatisch über die sogenannten Automated Maintenance Tasks erstellen, die jeden Tag in einem festgelegten änderbaren Fenster (auch Maintenance Window) ausgeführt werden. Ab 12.2 stellt Oracle zusätzlich einen Optimizer Statistics Advisor zur Verfügung.
Wozu benötigt man überhaupt einen Optimizer Statistics Advisor? Wann und wie wird dieser ausgeführt und welche Ergebnisse kann man erwarten?
Optimizer Statistics Advisor: Generelles
Wie man aus dem Namen leicht ableiten kann, ist der Optimizer Statistics Advisor ein Werkzeug, das die gesammelten Statistiken analysiert und gegebenenfalls Empfehlungen und Verbesserungsvorschläge gibt. Der Advisorlauf (auch Task) wird automatisch im Maintenance Window im Zuge der "auto optimizer stats collection" Task ausgeführt. Zur Erinnerung werden in folgendem Listing noch einmal alle Tasks aufgeführt, die im Maintenance Window zum Beispiel im Montag Fenster (MONDAY_WINDOW) gelaufen sind.
SQL> select client_name, to_char(window_start_time, 'dd.mm.yy hh24:mi') startzeit,
to_char(window_end_time, 'dd.mm.yy hh24:mi') endezeit,jobs_completed
FROM dba_autotask_client_history
WHERE window_name='MONDAY_WINDOW' ORDER BY window_start_time;
CLIENT_NAME STARTZEIT ENDEZEIT JOBS_COMPLETED
----------------------------------- -------------- -------------- --------------
auto optimizer stats collection 26.06.17 22:00 27.06.17 02:00 17
auto space advisor 26.06.17 22:00 27.06.17 02:00 17
sql tuning advisor 26.06.17 22:00 27.06.17 02:00 1
auto optimizer stats collection 03.07.17 22:00 04.07.17 02:00 17
auto space advisor 03.07.17 22:00 04.07.17 02:00 17
sql tuning advisor 03.07.17 22:00 04.07.17 02:00 1
auto optimizer stats collection 10.07.17 22:00 11.07.17 02:00 17
auto space advisor 10.07.17 22:00 11.07.17 02:00 16
sql tuning advisor 10.07.17 22:00 11.07.17 02:00 1
auto optimizer stats collection 17.07.17 22:00 18.07.17 02:00 17
auto space advisor 17.07.17 22:00 18.07.17 02:00 17
sql tuning advisor 17.07.17 22:00 18.07.17 02:00 1
auto optimizer stats collection 24.07.17 22:00 25.07.17 02:00 18
auto space advisor 24.07.17 22:00 25.07.17 02:00 18
sql tuning advisor 24.07.17 22:00 25.07.17 02:00 1
15 rows selected.
Selektiert man die Advisor View DBA_ADVISOR_TASKS, kann man nach der neuen Task AUTO_STATS_ADVISOR_TASK für den Optimizer Statistics Advisor filtern. Offensichtlich ist der Optimizer Statistics Advisor zwischen 22:01 und 22:07 am 24.Juli ausgeführt worden.
SQL> select advisor_name, last_execution, execution_start, execution_end
from dba_advisor_tasks
where task_name='AUTO_STATS_ADVISOR_TASK';
ADVISOR_NAME LAST_EXECUTION EXECUTION_STAR EXECUTION_END
-------------------- -------------------- -------------- --desc ------------
Statistics Advisor EXEC_3528 24.07.17 22:01 24.07.17 22:07
Wie erhält man nun die Empfehlungen des Optimizer Statistics Advisors? Eine Möglichkeit besteht darin, im Data Dictionary in der Advisor View DBA_ADVISOR_RECOMMENDATIONS nachzusehen. Eine gut lesbare Alternative liefert die neue Funktion DBMS_STATS.REPORT_STATS_ADVISOR_TASK. Das Resultat ist dabei abhängig von den Rechten des auszuführenden Users. Hat der User ANALYZE ANY und ANALYZE ANY DICTIONARY Privilegien kann er Empfehlungen bis auf Systemebene erhalten. Darf er nur seine eigenen Objekte analysieren, erhält er auch nur die Empfehlungen für die eigenen Objekte. Mehr dazu auch im Handbuch zum Package DBMS_STATS. In unserem Fall verbinden wir uns mit einem User, der Empfehlungen auf allen Ebenen erhalten kann wie zum Beispiel SYS.
SET LONG 1000000 LONGCHUNKSIZE 100000 PAGESIZE 0
SPOOL rep.html
SELECT DBMS_STATS.REPORT_ADVISOR_TASK(
task_name => 'AUTO_STATS_ADVISOR_TASK'
, execution_name => NULL
, type => 'HTML' -- TEXT, XML
, section => 'ALL' ) AS report
FROM DUAL;
SPOOL OFF
Folgender Screenshot zeigt einen kleinen Ausschnitt des Ergebnisses.
Möchte man einen "On-Demand" Lauf initiieren, kann man dies mit den Funktionen CREATE_ADVISOR_TASK und EXECUTE_ADVISOR_TASK des Packages DBMS_STATS tun. Verbinden wir uns im nächsten Beispiel mit dem User DWH_DATA, der nur seine eigenen Objekte analysieren darf.
Bitte beachten Sie: Eine notwendige Voraussetzung zur Durchführung von Advisor Tasks ist das Privileg ADVISOR.
DECLARE v_tname VARCHAR2(128) := 'ADV_TASK_STAT'; v_execname VARCHAR2(128) := NULL; BEGIN v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname); v_execname := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname); END; /
Monitoren lässt sich die Tasks wie immer über die View USER_ADVISOR_TASKS.
SQL> select status, advisor_name, execution_start, how_created
from user_advisor_tasks
where task_name='ADV_TASK_STAT';
STATUS ADVISOR_NAME EXECUTION_STAR HOW_CREATE
----------- ------------------------- -------------- ----------
EXECUTING Statistics Advisor 25.07.17 10:41 CMD
Das Ergebnis lässt sich dann wie oben mit der Funktion REPORT_ADVISOR_TASK ausgeben.
SQL> connect dwh_data
Enter password:
Connected.
set pagesize 0 long 1000000 LONGCHUNKSIZE 100000
spool manual_report.html
SELECT DBMS_STATS.REPORT_ADVISOR_TASK(
task_name => 'ADV_TASK_STAT'
, execution_name => NULL
, type => 'HTML' -- TEXT oder XML
, section => 'ALL' ) AS report
FROM DUAL;
spool off
Weiterhin ist es auch möglich, mit der Funktion SCRIPT_ADVISOR_TASK ein Skript zu generieren, das die Empfehlungen des Optimizer Statistics Advisors implementiert. Es enthält PL/SQL Aufrufe, die man einfach ausführen/ablaufen lassen kann.
spool script_stat
select DBMS_STATS.SCRIPT_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK') from dual;
spool off
Der folgende Screenshot zeigt einen Ausschnitt:
Diese Skripte können eine gute Hilfestellung bei der Impelmentierung sein, sollten allerdings vor der Ausführung immer genau geprüft und gegebenenfalls angepasst werden.
Die Regeln
Welche Regeln werden bei der Analyse eigentlich berücksichtigt? Um diese Frage zu beantworten kann man die V$View V$STATS_ADVISOR_RULES zu Rate ziehen.
SQL> select * from V$STATS_ADVISOR_RULES;
NAME RULE_TYPE
---------------------------------------------------------------- ---------
SYSTEM
UseAutoJob SYSTEM
CompleteAutoJob SYSTEM
MaintainStatsHistory SYSTEM
UseConcurrent SYSTEM
UseDefaultPreference SYSTEM
TurnOnSQLPlanDirective SYSTEM
AvoidSetProcedures OPERATION
UseDefaultParams OPERATION
UseGatherSchemaStats OPERATION
AvoidInefficientStatsOprSeq OPERATION
AvoidUnnecessaryStatsCollection OBJECT
AvoidStaleStats OBJECT
GatherStatsAfterBulkDML OBJECT
LockVolatileTable OBJECT
UnlockNonVolatileTable OBJECT
MaintainStatsConsistency OBJECT
AvoidDropRecreate OBJECT
UseIncremental OBJECT
NotUseIncremental OBJECT
AvoidOutOfRange OBJECT
UseAutoDegree OBJECT
UseDefaultObjectPreference OBJECT
AvoidAnalyzeTable OBJECT
24 rows selected.
Je nach Privilgien des Users kommen dabei entweder alle Regeln (bei ANALYZE ANY und ANALYZE ANY DICTIONARY) oder nur ein Teil der Regeln zum Tragen. Darf der User beispielsweise nur die eigenen Objekte analysieren (siehe DWH_DATA), kommen nur die Regeln mir RULE_TYPE OBJECT in Betracht. Eine genaue Erklärung der Regeln erhält man, in dem man zusätzlich die Spalte DESCRIPTION ausgibt. Im folgenden Listing ist ein Auszug zu lesen.
SQL> select con_id, name, description from V$STATS_ADVISOR_RULES; NAME ------------------------------- DESCRIPTION -------------------------------------------------------------------------------- UseAutoJob Use Auto Job for Statistics Collection CompleteAutoJob Auto Statistics Gather Job should complete successfully MaintainStatsHistory Maintain Statistics History UseConcurrent Use Concurrent preference for Statistics Collection UseDefaultPreference Use Default Preference for Stats Collection TurnOnSQLPlanDirective SQL Plan Directives should not be disabled AvoidSetProcedures Avoid Set Statistics Procedures UseDefaultParams Use Default Parameters in Statistics Collection Procedures UseGatherSchemaStats Use gather_schema_stats procedure AvoidInefficientStatsOprSeq Avoid inefficient statistics operation sequences AvoidUnnecessaryStatsCollection Avoid unnecessary statistics collection ...
Zu Beginn eines Reports werden übrigens die Regeln mit ausgegeben, zu denen der Advisor Empfehlungen liefern kann. Der HTML Report von oben hat demnach Empfehlungen zu folgenden Regeln gefunden.
Ein vollständiger kleiner Beispielreport befindet sich hier.
Der Optimizer Statistics Advisor in einer Multitenant Umgebung
Ist man mit einer PDB verbunden ändert sich am vorangegangenen Vorgehen nichts. Die Skripte und Views können wie oben demonstriert verwendet werden. Man erzeugt damit die PDB spezifischen Tasks und Sichtweisen.Ist man mit der CDB$ROOT als Common User verbunden, hat man laut Definition die Möglichkeit Sichtweisen über die CDB Root, PDBs, Application Root und Application PDBs zu erhalten. Dazu stehen die speziellen "CDB_" Views zur Verfügung, die wie üblich mit der zusätzlichen Spalte CON_ID ausgestattet sind. Betrachten wir beispielsweise die Automated Maintenance Tasks als Common User in der CDB$ROOT.
SQL> select con_id, client_name, to_char(window_start_time, 'dd.mm.yy hh24:mi') startzeit
from cdb_AUTOTASK_client_history
WHERE window_name='MONDAY_WINDOW' ORDER BY window_start_time;
CON_ID CLIENT_NAME STARTZEIT
---------- ---------------------------------------------------------------- --------------
1 auto optimizer stats collection 26.06.17 22:00
1 sql tuning advisor 26.06.17 22:00
1 auto space advisor 26.06.17 22:00
3 auto optimizer stats collection 27.06.17 00:00
3 sql tuning advisor 27.06.17 00:00
3 auto space advisor 27.06.17 00:00
1 auto optimizer stats collection 03.07.17 22:00
1 sql tuning advisor 03.07.17 22:00
1 auto space advisor 03.07.17 22:00
3 auto optimizer stats collection 04.07.17 00:00
3 sql tuning advisor 04.07.17 00:00
...
SQL> select con_id, ADVISOR_NAME, LAST_EXECUTION, execution_start
2 from cdb_advisor_tasks
3 where task_name='AUTO_STATS_ADVISOR_TASK'
4 /
CON_ID ADVISOR_NAME LAST_EXECU EXECUTION_STAR
---------- -------------------- ---------- --------------
3 Statistics Advisor EXEC_682 26.07.17 00:00
Wie man erkennen kann, wird die Optimizer Statistics Task automatisch im Maintenance Window in der PDB mit CON_ID 3 ausgeführt. (Zur Erklärung: Unsere Umgebung besitzt eine einzige PDB (mit CON_ID 3), die die Userdaten enthält.) Um den zugehörigen Report zu erhalten, ist eine Verbindung mit der PDB notwendig. Syntax und Ausführung der Funktion REPORT_ADVISOR_TASK erfolgt dann wie im oberen Abschnitt schon beschrieben. Befindet man sich in der PDB (mit CON_ID 3), kann man den Optimizer Statistic Advisor auch manuell verwenden. Auch hier folgt die Syntax und das Vorgehen dem Prozedere wie demonstriert und beschrieben.
Fazit
Nutzt man zur Berechnung der Statistiken die Automated Maintenance Tasks (auto optimizer stats collection), wird ab 12.2 automatisch der Optimizer Statistics Advisor zusätzlich ausgeführt. Die Empfehlungen (Findings/Recommendations) lassen sich dabei einfach im Data Dictionary anzeigen oder als Report ausgeben. Diese Möglichkeit sollte man sich nicht entgehen lassen, da die Anwendung dieser Empfehlungen dazu beitragen kann die Güte der Statistiken zu verbessern und damit die Gesamtperformance zu erhöhen.
Da auch ein manueller Start des Advisors einfach durchzuführen ist, sollte man von dieser Möglichkeit von Zeit zu Zeit Gebrauch machen. Speziell bei Änderungen an den Objekten und der Umgebung ist eine manuelles Anstarten des Optimizer Statistic Advisors anzuraten.
Weitere Informationen
- Database SQL Tuning Guide Kapitel 18
- Optimizer Statistics Advisor In 12.2 (Doc ID 2259398.1)
- DBMS_STATS im Handbuch
- White Paper: Understanding Optimizer Statistics With Oracle Database 12c Release 2
Zurück zum Anfang des Artikels
Zurück zur Community-Seite