Performance-Fallen in SQL-Statements
Überblick
Mit SQL kann man zwar schnell und komfortabel Daten abfragen, leider ist die Performance trotz korrektem Ergebnis manchmal schlecht.
Deshalb werden in diesem Blog einige Fallen in SELECT-Statements vorgestellt, die zu einer schlechten Performance führen können.
Viele Faktoren haben Einfluss auf die Performance der SQL-Anweisungen. Dazu zählen neben der physischen Speicherung, dem Datenmodell und der Datenbank-Parameter auch das SQL-Statement selbst, die Statistiken und die zur Verfügung stehenden Zugriffsstrukturen.
An drei Beispielen von “falschen" Zugriffen auf die customers Tabelle soll durch Analyse von Ausführungsplänen gezeigt werden, wie kleine Änderungen im SQL zu großen Verbesserungen führen können.
Problem 1 : Ungewollte Datentypkonvertierung
Die Tabelle customers beinhaltet u.a. diese Spalten.
SQLT@localhost/orclpdb>descr customers
Name Null? Type
----------------------------------------------- -------- --------------------------------------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
. . .
Die Spalte cust_postal_code hat den Datentyp VARCHAR2(10) und ist indiziert:
Die folgende Abfrage nutzt nicht wie erwartet einen Indexzugriff, sondern führt einen FULL TABLE SCAN aus.
select cust_street_address from customers where cust_postal_code=33115;
. . .
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 2378 | 422 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 82 | 2378 | 422 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=33115)
Warum?
Da das Vergleichsliteral ein numerischer Datenwert ist, muss für die Auswertung der Bedingung eine Typ-Konvertierung gemacht werden (siehe Predicate Information im Ausführungsplan).
Der Index kann trotz selektiver Ergebnismenge nicht genutzt werden, da die indizierte Spalte durch die implizite Konvertierungsfunktion TO_NUMBER() verändert wird.
Die Lösung des Problem ist die Verwendung des richtigen Datentyps im Vergleichsoperator.
Der Index wird genutzt und die Kosten sind deutlich geringer.
select cust_street_address from customers where cust_postal_code='33115';
. . .
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 2378 | 78 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 82 | 2378 | 78 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_POSTAL_CODE_IDX | 82 | | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_POSTAL_CODE"='33115')
Hinweis:
Die implizite Typkonvertierung führt zu einem Laufzeitfehler, wenn sich die Inhalte der VARCHAR2-Spalte nicht erfolgreich in einen numerischen Wert umwandeln lassen. Darum sollte unbedingt auf die Datentypfamilien, besonders in Bedingungen der SQL-Anweisungen, geachtet werden.
Problem 2: Ineffiziente Suche nach NULL-Werten
Die Spalte cust_valid der Tabelle customers beinhaltet zwei unterschiedliche Datenwerte in großer Anzahl und ein paar wenige NULL-Werte.
SQLT@orclpdb>select cust_valid, count(*)
from customers
group by cust_valid;
CUST_VALID COUNT(*)
---------- ----------
I 44666
A 10598
236
Interessieren uns die Zeilen mit fehlenden Werten in der cust_valid-Spalte, so wird Oracle einen FULL TABLE SCAN für die Abfrage statt eines Indexzugriffs verwenden, da NULL-Werte im Index generell nicht berücksichtigt werden.
SQLT@orclpdb>select count(*) from customers where cust_valid is null;
...
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 423 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 236 | 472 | 423 (1)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_VALID" IS NULL)
Wie kann diese Abfrage effizienter werden?
Ein zusammengesetzter Index für die Spalte verknüpft mit einem konstanten Literal ermöglicht Indexzugriffe auch bei Abfragen auf NULL-Werte. Die Kosten betragen jetzt nur ein Bruchteil.
SQLT@orclpdb>create index cust_cust_valid_kix on customers (cust_valid, 0);Index created.
SQLT@orclpdb>select count(*) from customers where cust_valid is null;
...
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100) | |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2| INDEX RANGE SCAN | CUST_CUST_VALID_KIX | 236 | 472 | 2 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_VALID" IS NULL)Problem 3: Datentyp NUMBER() im BETWEEN-Operator
Die nächste Abfrage verwendet die Spalten country_id, definiert als NUMBER() und die VARCHAR2-Spalte cust_state_province. Es wird ein zusammengesetzter Index für diese Spaltenkombination angelegt. Dennoch wird der Index nicht so genutzt, wie es erwartet wird.
SQLT@orclpdb>create index cust_country_state_ix on customers(country_id, cust_state_province);
SQLT@orclpdb>select cust_id, cust_first_name, cust_last_name, cust_state_province
from customers
where country_id between 52788 and 52790
and cust_state_province like 'W%';
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 338 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 489 | 17604 | 338 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | CUST_COUNTRY_STATE_IX | 489 | | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COUNTRY_ID">=52788 AND "CUST_STATE_PROVINCE" LIKE 'W%' AND "COUNTRY_ID"<=52790)
filter("CUST_STATE_PROVINCE" LIKE 'W%')
Warum?
Wird für eine Spalte der Datentyp NUMBER() ohne explizite Werte für die Genauigkeit und die Skalierung verwendet, erlaubt Oracle numerische Werte mit Nachkommastellen. In diesem Fall vermutet Oracle eine deutlich niedrigere Selektivität, statt nur der drei verschiedenen ganzzahligen country_id-Werte. Der führende Teil des Index wird beim Zugriff nicht genutzt, was zu deutlich mehr Lesevorgängen führt.
Eine schnelle Lösung besteht darin, die einzelnen drei Werte mit einem IN-Operator zu vergleichen.
SQLT@orclpdb>select cust_id, cust_first_name, cust_last_name, cust_state_province
from customers
where country_id in (52788, 52789, 52790)
and cust_state_province like 'W%';
…
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 279 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 409 | 14724 | 279 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CUST_COUNTRY_STATE_IX | 409 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access((("COUNTRY_ID"=52788 OR "COUNTRY_ID"=52789 OR "COUNTRY_ID"=52790)) AND
"CUST_STATE_PROVINCE" LIKE 'W%')
filter("CUST_STATE_PROVINCE" LIKE 'W%')
Die beste Lösung besteht jedoch darin, für ganzzahlige Spaltenwerte den Datentyp NUMBER(p) zu verwenden, wobei p die maximale Anzahl der Ziffern angibt.
Damit sind keine Nachkommastellen zulässig und Oracle kann die Selektivität für die Bedingung mit dem BETWEEN-Operator besser bzw. genauer ermitteln.
Fazit
Ein Indexzugriff ist in der Regel performanter als ein FULL TABLE SCAN, wenn die Ergebnismenge eher klein ist oder sämtliche Daten des Abfrageergebnisses aus Indexzugriffen ermittelt werden können. Darum ist es wichtig, dass der Entwickler die Abfragen so formuliert, dass Indexzugriffe möglich sind. Der Optimizer von Oracle trifft für jede SQL-Anweisung eine kostenbasierte, auf den gespeicherten Statistiken basierende Entscheidung und verwendet diesbezüglich einen effizienten Ausführungsplan.
Noch mehr Beispiele und fundiertes Wissen zum Thema Optimierung und Performance von SQL gibt es im Kurs: Praxisworkshop Oracle Datenbank 19c SQL Tuning
Kommentare
Keine Kommentare