Langsamer Zugriff auf v$sql

Ein ganz wohlfeiler Trick, um in diesem Jahr noch auf 94 Blog-Einträge zu kommen - aber keine Angst: 100 werden es diesmal nicht mehr. Kurz vor Weihnachten habe ich im OTN-Forum General Database einen Fall vorgestellt, in dem der Zugriff auf eine eigentlich sehr übersichtliche dynamische Performance-View v$sql (mit kaum mehr als 2000 Einträgen) stabil 25 Sekunden dauert. Dabei sind anscheinend keine Waits im Spiel, sondern es wird fast ausschließlich CPU verbrannt. Trotz zahlreicher sachdienlicher Hinweise von Jonathan Lewis, Stefan Koehler und anderen Beiträgern ist bisher noch keine endgültige Erklärung in Sicht, aber großartig war es, mal wieder zu sehen, wie viele nützliche Antworten man auf OTN bekommt, wenn man eine höfliche Frage stellt. Und wenn ich schon dabei bin, mich bei den OTN-Kommentatoren zu bedanken, dann kann ich gleich auch noch einen Dank an meine treuen Leser und gute Wünsche fürs Folgejahr ergänzen.

Table Expansion Bug mit Interval Partitioning

Als das Interval Partitioning in 11g eingeführt wurde, schien mir das eine der besten Ideen gewesen zu sein, die Oracle seit Einführung der Partitionierung eingefallen waren. Leider hat sich im Verlauf der Zeit herausgestellt, dass die Implementierung eine ziemliche große Zahl von Problemen hervorgerufen hat, von denen mir erstaunlich viele im Rahmen meiner eigenen Arbeit begegnet sind. Einen weiteren bizarren Bug, der in diesem Zusammenhang auftreten kann, hat Jonathan Lewis vor kurzem beschrieben: in 12c ergeben sich unter Umständen falsche Ergebnisse, wenn man Table Expansion - also die Möglichkeit, verschiedene Partitionen einer Tabelle mit unterschiedlichen Zugriffsstrategien abzufragen - in Verbindung mit Interval Partitioning verwendet. Im Beispiel wird - via Hint expand_table - ein Index-Zugriff auf eine Partition hervorgerufen, während die übrigen Partitionen per Full Table Scan gelesen werden. Der daraus resultierende Plan enthält zunächst die erwarteten Elemente: das UNION ALL, einen step PARTITION RANGE SINGLE für den Index-Zugriff und einen step PARTITION RANGE ITERATOR mit dem FULL TABLE SCAN für die Partitionen 2 bis 4. Seltsamerweise folgt dann aber noch ein step PARTITION RANGE INLIST ohne Partitionsangaben. Das könnte noch ein Darstellungsfehler im Plan sein, aber die rowsource Statistiken zeigen, dass tatsächlich die doppelte Anzahl von Datensätzen zurückgegeben wird. Zur Eingrenzung des Problems wurden noch folgende Prüfungen durchgeführt:

• in 11.2.0.4 wird die Table Expansion beim Zugriff auf eine intervallpartitionierte Tabelle auch beim Einsatz des Hints expand_table nicht verwendet.
• in 11g und 12c ergibt sich das Problem nicht, wenn keine Intervallpartitionierung im Spiel ist.

Klar ist an dieser Stelle zunächst nur, dass hier ein Bug im Spiel ist und dass 12c offenbar Table Expansion für intervallpartitionierte Tabellen in Fällen zulässt, in denen das in 11g noch nicht vorgesehen war. Doppelt schade, denn Table Expansion halte ich im Grunde für ein ähnlich interessantes Feature wie Interval Partitioning.

AWR Snapshots vergleichen

Wieder nur ein Link: diesmal auf einen Artikel von Kyle Hailey, der erläutert, wie zwei AWR-Snapshots miteinander vergleicht. Wenn beide Snapshots im im gleichen AWR-Repository liegen, ist dafür nur ein Aufruf der table function dbms_workload_repository.awr_diff_report_text erforderlich (bzw. von dbms_workload_repository.awr_diff_report_html, wenn man die HTML-Version vorzieht). Darüber hinaus hat der Herr Hailey aber noch ein Shell-Skript awrdiff.sh definiert, das es erlaubt textuelle AWR-Reports aus unterschiedlichen Quellen zu vergleichen.

Views für die Postgres Performance Analyse

Nur ein Link auf eine sehr nützliche Grafik, in der Alexey Lesovsky zeigt, welche interne View jeweils verwendet werden kann um bestimmte Performance-Fragestellungen zu beantworten. Sollte ich mir irgendwo an die Wand hängen.

Fünf wichtige Optimizer Hints

Jonathan Lewis hat in seinem Blog eine Zusammenfassung der wichtigsten Punkte seiner DOAG-Präsentation Five Hints for Optimising SQL veröffentlicht (was ich sehr erfreulich finde, da ich den Vortrag natürlich nicht gehört habe, so wie alle Vorträge, die in den letzten zehn Jahren bei nationalen und internationalen Konferenzen gehalten wurden). Dabei beginnt er mit dem üblichen Hinweis, dass Hints normalerweise im Produktivsystem zu vermeiden und als Ultima Ratio zu betrachten sind. Und dass von den 332 Hints, die in 12.1.0.2 zur Verfügung stehen, nur eine Handvoll tatsächlich eingesetzt werden sollten, wenn es sich nicht vermeiden lässt. Aus dieser Menge nimmt er dann noch einmal fünf Hints heraus, die er als relativ nützliche Hilfsmittel ansieht, um den Optimizer bei seiner Entscheidungsfindung zu unterstützen. Zu diesen fünf Hints liefert er dann jeweils eine längere Erklärung, die im Rahmen eines komplexeren Beispiels erscheint. Ich versuche, hier hier die initiale knappe Definition mit den folgenden Erklärungen zusammenzuführen. Wie übliche werde ich dabei zweifellos zentrale Punkte unterschlagen...

• Merge / no_merge — "Whether to use complex view merging". Der Hint kann aber auch verwendet werden, um einzelne Abschnitte einer komplexen Abfrage voneinander zu isolieren. Dadurch kann man einerseits den Suchraum des Optimizers reduzieren und andererseits manchmal auch verhindern, dass der Optimizer in einem bestimmten Bereich der Query eine ungeeignete Strategie wählt. Im Beispiel wird neben den no_merge Hint noch ein cardinality Hint gestellt, der zwar nicht vollständig dokumentiert ist, aber dazu verwendet werden kann, die cardinality eines Query Blocks anzugeben. Ein Einsatzgebiet des Hint-Paars ist auch der Wechsel zwischen den Möglichkeiten "join then aggregate" und "aggregate then join".
• Push_pred / no_push_pred — "What to do with join predicates to non-merged views". Mit Hilfe dieser Hints kann man festlegen, ob eine Join-Bedingung in eine (nicht im Rahmen der Transformation in einen Join umgewandelte) Subquery propagiert werden soll. Das kann sinnvoll sein, wenn sich dadurch eine effektivere Zugriffsstrategie ergibt (etwa durch einen indizierten Zugriff).
• Unnest / no_unnest — "Whether or not to unnest subqueries". Normalerweise wandelt der Optimizer Subqueries gerne in Joins um (Semi-Join für exists; Anti-Join für not exists). Da eine Subquery in solchen Fällen oft besser zu verstehen ist, kann ein Hint an dieser Stelle dafür sorgen, dass der Optimizer auch dann die Join-Variante wählt, wenn das (fehleranfällige) Costing nicht für diese Strategie sprechen würde. Im Rahmen des Beispiels weist der Autor darauf hin, dass das Costing in seinem Beispiel (ohne Unnest) tatsächlich die Anzahl der Ausführungen einer Subquery berücksichtigt (Anzahl Departments), was eine jüngere Errungenschaft des CBOs ist.
• Push_subq / no_push_subq — "When to handle a subquery that has not been unnested". Über diesen Hint kann man beeinflussen, wann eine Subquery ausgeführt wird - traditionell wurde sie erst gegen Ende der Verarbeitung berücksichtigt. Das Pushing einer Subquery bedeutet, sie im Plan nach unten zu schieben, also früher auszuführen.
• Driving_site — "Where to execute a distributed query". Dieser Hint hat keine NO-Variante, weil er nur angibt, auf welcher Seite einer verteilten Query die Ausführung koordiniert wird und welche Seite als remote fungiert. Der Hint fünktioniert übrigens nicht für CTAS und Insert as Select Queries, was nicht unbedingt nachvollziehbar ist. In solchen Fällen kann die Verwendung einer pipelined table function sinnvoll sein.

Mal wieder eine sehr nützliche Zusammenfassung, die ich in Zukunft vermutlich häufiger als Referenz verwenden werde.

Nutzlose und weniger nutzlose METHOD_OPT-Angaben

Da ich hier zuletzt fast nur noch Links kommentiert habe, zur Abwechslung noch mal ein bisschen was Praktisches. Im OTN-Forum wurde heute die Frage gestellt, wieso dbms_stats.gather_table_stats auf den nicht dokumentierten Parameter-Wert "FOR ALL INDEXES" nicht mit einem Fehler reagiert. Meine Antwort darauf lautet: keine Ahnung, aber er ist noch gefährlicher als "FOR ALL INDEXED COLUMNS":

drop table t;
create table t
as
select rownum id
     , mod(rownum, 2) col1
     , mod(rownum, 5) col2
     , mod(rownum, 10) col3
  from dual
 connect by level <= 10000;  
 
create index t_idx1 on t(id);

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                                 NONE
COL2                                                 NONE
COL3                                                 NONE
ID                                    10000 01.12.15 HEIGHT BALANCED

--> create column statistics (and histograms) just for indexed columns

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL INDEXES')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                                 NONE
COL2                                                 NONE
COL3                                                 NONE
ID                                                   NONE

--> creates no column statistics

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL COLUMNS')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                      2 01.12.15 FREQUENCY
COL2                                      5 01.12.15 FREQUENCY
COL3                                     10 01.12.15 FREQUENCY
ID                                    10000 01.12.15 HEIGHT BALANCED

--> creates column statistics (and histograms) for all columns

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS COL3 SIZE 254')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                      2 01.12.15 NONE
COL2                                      5 01.12.15 NONE
COL3                                     10 01.12.15 FREQUENCY
ID                                    10000 01.12.15 NONE

--> creates column statistics for all columns and a histogram for COL3

Warum gefährlicher als "FOR ALL INDEXED COLUMNS"? Weil man damit tatsächlich gar keine Spalten-Statistiken erhält, so dass der Optimizer bei der Bestimmung der Cardinalities für alle Spalten auf Schätzungen zurückgehen muss. Ganz ohne Statistiken hätte man da noch dynamic sampling (und dadurch brauchbare Cardinalities), aber wenn Tabellen-Statistiken vorliegen, geht der Optimizer davon aus, dass er auch mit den Angaben zu den Spalten etwas anfangen kann:

SQL> select count(*) from t where col2 = 1;

  COUNT(*)
----------
      2000

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100 |  1300 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("COL2"=1)

SQL> exec dbms_stats.delete_table_stats(user, 't')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select count(*) from t where col2 = 1;

  COUNT(*)
----------
      2000

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  2000 | 26000 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("COL2"=1)

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