Download AC-Toolbar Oracle Schulung und Consulting

Eigenartiges Verhalten des Optimizers

Benutzerbewertung: / 608
SchwachPerfekt 
Geschrieben von: Marek Adar   

Bei Performanceproblemen eines Upgrade-Skripts einer Datenbankanwendung stieß ich auf folgende interessante Problematik von Optimizer-Statistiken.


Ein Upgrade-Skript sollte Datensätze innerhalt einer Tabelle mit mehr als 4 Millionen Zeilen konvertieren. Hierfür wurde auf eine Tabelle TAB1 eine SELECT-Anweisung ausgeführt, die Zeilen über eine Spalte S4 filtern sollte. Die Werte der Spalte S4 hatten allerdings die Eigenart, dass bei allen 4 Millionen Zeilen die ersten 45 Zeichen der Werte dieser Spalte identisch waren. Erst ab diesen Zeichen unterscheiden sie sich. Da diese Anweisung mehrere tausendmal auf die Tabelle ausgeführt wurde, aber in Abhängigkeit des Suchwertes der Spalte S4 nur 10 bis 20 Zeilen zurückkommen sollten, wurde ein Index auf diese Spalte erstellt und Optimizer-Statistiken gesammelt.

Interessanterweise ignorierte der Optimizer diesen Index und führte einen Full-Table-Scan aus, was das Upgrade-Skript sehr stark verlangsamte.

SQL> SELECT  s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

Ausführungsplan
----------------------------------------------------------
Plan hash value: 915916058

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  4374K|   471M|       |   130K  (3)| 00:01:48 |
|   1 |  SORT ORDER BY     |       |  4374K|   471M|   560M|   130K  (3)| 00:01:48 |
|*  2 |   TABLE ACCESS FULL| TAB1  |  4374K|   471M|       | 24299   (5)| 00:00:21 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234'
AND "S5" = '0')

Statistiken
----------------------------------------------------------
    0  recursive calls
    1  db block gets
80499  consistent gets
    0  physical reads
    0  redo size
  487  bytes sent via SQL*Net to client
  509  bytes received via SQL*Net from client
    1  SQL*Net roundtrips to/from client
    1  sorts (memory)
    0  sorts (disk)
   15  rows processed

Auch das erneute Sammeln der Optimizer-Statistiken brachte kein Erfolg.

SQL> exec dbms_stats.gather_table_stats('MEINUSER','TAB1',cascade=>true)

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT  s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

15 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 915916058

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  4416K|   475M|       |   131K  (3)| 00:01:49 |
|   1 |  SORT ORDER BY     |       |  4416K|   475M|   565M|   131K  (3)| 00:01:49 |
|*  2 |   TABLE ACCESS FULL| TAB1  |  4416K|   475M|       | 24304   (5)| 00:00:21 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234'
AND "S5" = '0')

Statistiken
----------------------------------------------------------
    0  recursive calls
    1  db block gets
80499  consistent gets
    0 physical reads
    0  redo size
 1134  bytes sent via SQL*Net to client
  520  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    1  sorts (memory)
    0  sorts (disk)
   15  rows processed

Aus diesem Grund entschloss ich mich dediziert ein Histogramm für die Spalte S4 zu erstellen, welches aber ebenfalls kein Erfolg brachte (Ich experimentierte mit den Histogrammen und erstellte sie auf unterschiedlicher Art und Weise).

SQL> BEGIN
2  DBMS_STATS.GATHER_TABLE_STATS(
3  OWNNAME=> 'MEINUSER',
4  TABNAME=> 'TAB1' ,
5  ESTIMATE_PERCENT=> NULL,
6  CASCADE=> DBMS_STATS.AUTO_CASCADE,
7  DEGREE=> NULL,
8  NO_INVALIDATE=> DBMS_STATS.AUTO_INVALIDATE,
9  GRANULARITY=> 'AUTO',
10  METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS S4 SIZE 120');
11  END;
12  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT  s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

15 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 915916058

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  4374K|   471M|       |   130K  (3)| 00:01:48 |
|   1 |  SORT ORDER BY     |       |  4374K|   471M|   560M|   130K  (3)| 00:01:48 |
|*  2 |   TABLE ACCESS FULL| TAB1  |  4374K|   471M|       | 24299   (5)| 00:00:21 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234'
AND "S5" = '0')

Statistiken
----------------------------------------------------------
    0  recursive calls
    1  db block gets
80499  consistent gets
    0  physical reads
    0  redo size
  487  bytes sent via SQL*Net to client
  509  bytes received via SQL*Net from client
    1  SQL*Net roundtrips to/from client
    1  sorts (memory)
    0  sorts (disk)
   15  rows processed

Im nächsten Schritt entschloss ich mich den Optimizer zur Verwendung des Index zu zwingen und erhielt ein interessantes Ergebnis:
Die Kosten für die Verwendung des Index schienen noch einmal zusätzlich angestiegen zu sein. Aber die Ausführungsstatistiken zeigten, dass anstatt 80499 nur noch 6 Blöcke gelesen wurden. Zudem wurde diese Anweisung mit dem Hint für die Verwendung des Index um ein Vielfaches schneller ausgeführt. Also gab es in der Tat ein Problem mit den Statistiken. Meine Vermutung war, dass Oracle beim Sammeln der Statistiken die Histogramme aufgrund der ersten 45 gleichen Zeichen für jeden Datensatz der Spalte S4 nicht richtig erzeugte.

SQL> SELECT  /*+ INDEX(TAB1 IDX_S4) */ s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

15 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2461020050

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |  4374K|   471M|       |   631K  (1)|00:08:42 |
|   1 |  SORT ORDER BY               |          |  4374K|   471M|   560M|   631K  (1)|00:08:42 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TAB1     |  4374K|   471M|       |   525K  (1)|00:07:15 |
|*  3 |    INDEX RANGE SCAN          | IDX_S4   |  4516K|       |       |  44711  (1)|00:00:37 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S5" ='0')
3 - access("S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234')

Statistiken
----------------------------------------------------------
  0  recursive calls
  0  db block gets
  6  consistent gets
  0  physical reads
  0  redo size
853  bytes sent via SQL*Net to client
520  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
 15  rows processed

Aus diesem Grund entschied ich mich nun die Statistiken der Tabelle TAB1 zu löschen, um auf das Dynamic Sampling der Statistiken zu schwenken. Nach erneuter Ausführung der Anweisung verwendete Oracle nun auf einmal den Index. Ein Vergleich der Kosten zeigte, dass der Optimizer sie anhand der Statistiken nicht richtig berechnete, denn sie sanken nun von 631000 auf 3!!.

SQL> exec dbms_stats.delete_table_stats('MEINUSER','TAB1')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT  s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

15 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3167567956

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   542 |   135K|     (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |          |   542 |   135K|     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1     |   542 |   135K|     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_S4   |    87 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("S5"='0' AND "S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234')

Note
-----
- dynamic sampling used for this statement (level=2)

Statistiken
----------------------------------------------------------
  1  recursive calls
  0  db block gets
  6  consistent gets
  0  physical reads
  0  redo size
853  bytes sent via SQL*Net to client
520  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
 15  rows processed

 

©Adar-Consult, Ihr Partner für Oracle Schulungen, Seminare, Workshops und Consulting

Oracle Training und Schulungen
Tag Cloud
oracle-architekturposter application google404, pl/sql-entwicklung recovery rac-praxisworkshop poster hochverfügbarkeits-bootcamp kleine export plan, google oracle sql-pl/sql-bootcamp pl/sql sql-commander schulungsangebot oracle-check, import cluster packages excel sql-grundlagen sql-tuning oracle-datenbankadministration
AC-Blog