News
DBA-Tipp: SQL Tuning Health Check - Das Oracle Skript zum Performance Tuning von SQL-Statements

Die Information ist ein schnelllebiges Gut. Jeden Tag werden wir mit hunderten Informationen zugemüllt. Deshalb sind wir bestrebt uns auf das Wesentliche zu konzentrieren und nur substantiell nachhaltige Informationen bereitzustellen.

Icon Unternehmen

Heute möchten wir das SQL Tuning Health Check Skript (SQLHC) aus dem Oracle Support vorstellen. Das Skript kann verwendet werden, um eine Analyse eines SQL Statements durchzuführen. Es werden unter anderem nachfolgende Prüfungen durchgeführt:

  • Prüfung der Umgebung
  • Prüfung der Cost-based Optimizer Statistiken
  • Schema Objekt Metadaten
  • Konfigurationsparameter
  • Ausführungspläne
  • andere Elemente, welche die Performance des geprüften SQL Statement beeinflussen.

Das SQLHC Skript benötigt keine Lizenz und ist frei im Oracle Support verfügbar. Es kann Informationen aus dem AWR Repository verwenden, falls Oracle Diagnostics Pack und/oder Oracle Tuning Pack lizenziert sind. Die Verwendung findet aber nur statt, wenn dies extra vom Anwender bestätigt wird. 

Für die Ausführung des SQLHC Skriptes wird die SQL-ID des SQL-Statements benötigt, welches überprüft werden soll. Diese ID kann zum Beispiel in der View v$session oder v$sql herausgelesen werden. 

Nachfolgend wird eine kleine Test-Stellung aufgebaut, um die Funktionsweise des Skriptes zu erläutern. Als erstes erstellen wir uns einen User aspicon und zwei Tabellen, welche eine Kopie der View dba_objects sein sollen. Danach führen wir ein Statement aus, welches beide Tabellen verwendet und unser Performance-Problem darstellt. 

SQL> CREATE TABLE perf_test_table_1 AS SELECT * FROM dba_objects;

Table created.

SQL> INSERT INTO perf_test_table_1 SELECT * FROM perf_test_table_1; (5-10 Wiederholungen)

SQL> CREATE TABLE perf_test_table_2 AS SELECT * FROM perf_test_table_1;

Table created.

Nachdem wir beide Tabellen angelegt haben, führen wir ein Statement mit diesen Objekten aus. Dabei kommt es zu einer Ausführungszeit des SQL Statements von zehn Sekunden.

SQL> set timing on;

SQL> SELECT COUNT(*) FROM perf_test_table_1 a WHERE object_id IN (SELECT object_id FROM perf_test_table_2 WHERE created>TO_DATE('10.04.2016','DD.MM.YYYY'));  

COUNT(*)
----------
620608

Elapsed: 00:00:10.29

Da die Ausführungszeit zu lang erscheint, lesen wir anschließend die SQL_ID aus der v$sql aus. In einem produktiven Fall wird das Statement eher aus der v$session oder einem Statspack- bzw. AWR-Report ausgelesen.

SQL>  select sql_id from v$sql where sql_text like 'SELECT COUNT(*) FROM perf_test_table_1 a%';

SQL_ID
-------------
615sxn65vs11p

Die Ausführung des SQL Health Check Skriptes erfolgt jetzt über sqlplus. Dazu kann entweder der User SYS, ein User mit DBA Rechten oder ein User mit Zugriff auf die Data Dictionary Views verwendet werden. Bitte beachten Sie hier, dass als erstes ein "N" eingegeben werden muss, falls kein Oracle Diagnostics Pack und/oder Oracle Tuning Pack lizenziert ist.  

SQL> @sqlhc

Parameter 1:

Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)

Enter value for 1: N

PL/SQL procedure successfully completed.


Parameter 2:

SQL_ID of the SQL to be analyzed (required)

Enter value for 2: 615sxn65vs11p

Abb. SQLHC - Health Check

Die Ausführungszeit des Skriptes variiert in Abhängigkeit zu den betroffenen Objekten. In dem verwendeten Beispiel betrug die Ausführungszeit circa eine Minute. Danach stehen die generierten Dateien in einem ZIP File zur Verfügung, welches mehrere html Dateien enthält.

In unserem Beispiel sehen wir in der Datei "health_check.html", dass es für die Tabellen keine Indizes und nur unzureichende Statistiken gibt. Im File "execution_plan.html" werden die im Sharedpool befindlichen Ausführungspläne des SQL Statements gezeigt. Hier ist zu erkennen, dass beide Tabellen mit einem Full Table Scan gelesen werden. 

Abb. SQLHC - Execution Plan

Zusammenfassend müssen wir für die Behebung unseres Performance Problems aktuelle Tabellenstatistiken generieren und Indizes für die Felder OBJECT_ID und CREATED der Tabellen PERF_TEST_TABLE_1 und PERF_TEST_TABLE_2 erstellen.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ASPICON',TABNAME=>
'PERF_TEST_TABLE_1',METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',NO_INVALIDATE=>FALSE,CASCADE=>TRUE,ESTIMATE_PERCENT => 100); 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ASPICON',TABNAME=>
'PERF_TEST_TABLE_2',METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',NO_INVALIDATE=>FALSE,CASCADE=>TRUE,ESTIMATE_PERCENT => 100);

SQL> CREATE INDEX PERF_TEST_1_CREATED_IDX ON PERF_TEST_TABLE_1(CREATED); 
SQL> CREATE INDEX PERF_TEST_2_CREATED_IDX ON PERF_TEST_TABLE_2(CREATED); 
SQL> CREATE INDEX PERF_TEST_1_OBJECT_ID_IDX ON PERF_TEST_TABLE_1(OBJECT_ID); 
SQL> CREATE INDEX PERF_TEST_2_OBJECT_ID_IDX ON PERF_TEST_TABLE_2(OBJECT_ID); 

SQL> set timing on;
SQL> SELECT COUNT(*) FROM perf_test_table_1 a WHERE object_id IN (SELECT object_id FROM perf_test_table_2 WHERE created>TO_DATE '10.04.2016','DD.MM.YYYY'));
 

COUNT(*)
----------
620608

Elapsed: 00:00:00.97


Eine erneute Ausführung des Statements zeigt, dass die durchgeführten Anpassungen die Ausführungszeit auf unter eine Sekunde reduziert haben. Nach einer erneuten Erstellung des Health Check Report ist zu sehen, dass jetzt die erstellten Indizes verwendet werden und die Tabellenstatistiken aktuell sind. Somit konnte das Performance Problem gelöst werden.

Hinweis

Da das Skript keine Objekte in der Oracle Datenbank erzeugt oder DDL Statements ausführt, kann es auch in Oracle Data Guard Umgebungen mit im Read-Only Modus geöffneten Standby Datenbanken verwendet werden. 

Fazit

Das SQLHC Skript führt alle notwendigen Checks in der Oracle Datenbank aus, um einem SQL Performance Problem schnell auf die Schliche zu kommen. Natürlich müssen die gelieferten Ergebnisse von einem erfahrenen DBA ausgewertet und die richtigen Schlüsse gezogen werden. Im vorliegenden Beispiel wurde auf die Verwendung des Oracle Diagnostics Packs bzw. Oracle Tuning Packs verzichtet. Falls diese in Kombination mit der Enterprise Edition lizenziert sein sollten, ist der Einsatz von Oracle Cloud Control zu empfehlen.

Oracle Dokumente
Document 1366133.1 SQL Tuning Health-Check Script (SQLHC)
Document 1417774.1 SQLHC HealthCheck Frequently Asked Questions