Performance

Kann ein Select eine Tabelle sperren?

Vor kurzem untersuchte ich ein “enq: TM contention” wait event. Ich fand über eine Abfrage auf die active session history (ASH) die blocking session heraus.

Als ich die blocking session über eine ASH query untersuchte, war zur selben Zeit wie der TM lock in der blockierten session auftrat,  gerade ein lang laufendes Select aktiv.

Das ist doch merkwürdig, nicht wahr? Kann eine Select einen Lock erzeugen? Wo ist hier der Denkfehler? Und würde es helfen, das Select Statement zu beschleunigen?

Wer sich nicht sicher ist, kann sich bei Arup Nanda über den TM Lock informieren.

enq: TX row lock contention and enq:TM contention

Lösung: Index Rebuild: Magic or Voodoo?

Meine Leser haben das Problem natürlich richtig erkannt. Ich danke Jure Bratina, Andrew Sayer und Martin Berger für Ihre Beiträge.
Im folgenden sehen Sie jetzt den ganzen Testcase in kommentieer Form. Das meiste ist, denke ich, selbsterklärend.
Am Anfang wird das Szenario aufgebaut. Das Schema ist, wie gesagt, das SH Beispielschema.

alter table sales add  (sparse varchar2(300)); 
update sales set sparse = rpad('sometext',300, '*');
commit;
create index sparse_idx on sales (sparse);
select blocks from user_segments where segment_name ='SPARSE_IDX';

Lassen Sie uns jetzt die Grösse des Indexsegements prüfen:

select blocks from user_segments where segment_name ='SPARSE_IDX';

   BLOCKS
---------
    44032

select leaf_blocks from user_indexes where index_name ='SPARSE_IDX';

LEAF_BLOCKS
-----------
      41766

Jetzt kommt ein Update, den ich in ähnlicher Form auch in der Original Datenbank gefunden habe:

update sales set sparse=NULL;

918843 Zeilen aktualisiert.

exec dbms_stats.gather_table_stats(user,'SALES');

Und wie wirkt sich das auf die Statistiken und die Segemente aus?

select leaf_blocks from user_indexes where index_name ='SPARSE_IDX';

LEAF_BLOCKS
-----------
          0
select blocks from user_segments where segment_name ='SPARSE_IDX';

    BLOCKS
----------
     44032

Da der Optimizer lediglich die Index Statistiken prüft, nicht aber die Segementstatistiken, hält der Optimizer den Index für winzig klein.
Das also alle nicht null Werte der Spalte sparse im Index sein müssen, ist es aus Sicht des Optimizers am besten, den angeblich kleinen Index zu scanen.
Das Indexsegment hat aber immer noch die volle Grösse. Erst ein Index rebuld schafft hier Abhilfe.

Index Rebuild: Magic or Voodoo?

Den heutigen Blog schreibe ich nicht gerne. Aber ich fühle mich der Wahrheit verpflichtet und das Thema ist zu interessant um es zu verschweigen.

Als in den Freelists gefragt wurde, ob der Index rebuild auch manchmal nützlich sein kann, habe ich das verneint, unter Hinweis auf entsprechende Einträge bei Mr. Index, Richard Foote.

Ich wusste schon, dass ich mich auf dünnen Eis bewege, aber ich  konnte der Chance oberschlau zu sein und einer gängigen Meinung zu widersprechen, einfach nicht widerstehen. Natürlich trat das unvermeidliche ein und Jonathan Lewis korrigierte mich mit den Hinweis, dass ein Index rebuild in manchgen Grenzfällen eben doch nützlich sein kann. Wie hatte ich bloss glauben können, unbemerkt von Jonathan’s Radar durch zu schlüpfen.

Kurz darauf, als wolle das Schicksal mich auch noch mahnen, fiehl mir bei einem Kunden eine Abfrage auf, die offensichtlich ineffizient war. Ich habe die Abfrage auf das SH  schema umgeschrieben und einen kleinen Testcase erzeugt. Wie ich diesen Testcase gemacht habe, werden Sie in der Lösung sehen. Genau das sollen Sie ja erraten.

select time_id from sales where sparse is not null;

Der Exection Plan mit Runtime Statitiken sieht aus wie folgt:


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |      1 |        |      0 |00:00:00.04 |   41770 |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES      |      1 |      1 |      0 |00:00:00.04 |   41770 |
|*  2 |   INDEX FULL SCAN                          | SPARSE_IDX |      1 |      1 |      0 |00:00:00.04 |   41770 |
-------------------------------------------------------------------------------------------------------------------

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

   2 - filter("SPARSE" IS NOT NULL)

Aus welchem Grund wählt der Optimizer einen Full Index Scan? Eine kurze Überprüfung zeigt, dass Full Table scan sehr viel effizienter ist.Zudem ist die Schärtzung (E-Rows) auch noch richtig. Die Statitiken sind also aktuell.

Was ist hier geschehen? Hinweis: Ich habe nach dem DML auf der Tabelle gesucht und ich fand ein update Statement.

Lösung: Eine unerwartete Bedingung in der Where Clausel

Die merkwürdige Bedingung wird von der Datenbank automatisch generiert.
Die Ursache ist die DDL Optimization, die es in der Form seit Version 11G gibt.
Wenn man bei einer Tabelle eine zusätzliche Spalte einfügt, muss diese Spalte nicht zwingend physisch erzeugt werden.
Es kann auch eine “DDL optimized” Spalte erzeugt werden und wenn man einen Default angibt, kann diese Spalte auch not null sein.
Damit erspart die Datenbank sich den Aufwand, jeden Datensatz um eine Spalte zu erweitern.
Satt dessen wird nur ein Eintrag ins Dictionary gemacht, was natürlich viel schneller geht.
Jede Datenzeile kann einen Wert für die “DDL optimized” Spalte enthalten, wenn der Wert über einen insert eingefügt wurde.
Wenn kein Wert eingefügt wird, wird der Default Wert verwendet.
Da es möglich ist, dass kein erfasster Wert existiert, muss die Datenbank den spaltennamen durch die Formel ersetzen.
Hier eine einfaches Beispiel :

create table x (y number);
insert into x select rownum from dual connect by rownum < 1000000;
commit;
alter table x add ( z number default 1 not null);
select 1 from x where z=1;

Wenn wir uns den Execution Plan der Abfrage ansehen, bemerken wir, dass der Spaltenname Z durch die Formel


(NVL("Z",1)=1)

ersetzt wurde.

Hier noch der Link zu Carlos Blog: Interesting case where a full table scan is chosen instead of an index

Lösung: Die parallele Aktivität bricht ein

Wie Sie sich erinnern werden, hatten wir einen Hash Outer Join mit dominanten Nullwerten. In Folge bekam der parallelen Prozess, der die Nullwerten behandelte, bei weitem die meiste Arbeit zu tun.
Wie sollen wir dies lösen? lassen Sie uns einige grundsätzliche Überlegungen anstellen.
Ein Null Wert ist ein Sonderfall . Wir vergleichen einen Null Wert im Foreign Key mit einem Primärschlüssel , der not null definiert ist. Wir können sicherlich keinen Join Treffer für die Null Werte zu finden. Daher kann jeder der parallelen Sklaven den Vergleich durchführen, solange gewährleistet ist, dass dadurch nie ein Treffer erzeugt wird.
Es lohnt sich also die Null Werte in beliebige andere Werte um zu wandeln, solange nur zwei Voraussetzungen erfüllt sind:

  1. wir müssen die Nullwerte in einer solchen Art und Weise umwandeln, dass die gleichmäßig über alle parallel Slaves verteilt werden
  2. wir müssen sicherstellen, dass im Vergleich zu vorher kein zusätzlicher Satz ins Resultat kommt

Die Lösung zu finden hat mich viel Zeit gekostet. Zuerst habe ich versucht mit negativen Werten zum Ziel zu kommen. Aber auch sie wurden auch an nur einen Parallel Prozess gesandt, genau wie die Null Werte.
Das Problem ist, dass unsere neu generierten Schlüssel etwa im gleichen Bereich wie der Primary Key der Build-Tabelle sein müssen, um über alle Slaves verteilt zu werden.
Ich hätte versuchen können über eine with Klausel den Bereich heraus zu finden, aber das hielt ich für uncool und konnte mich nicht dazu überwinden.
Stattdessen habe ich etwas anderes versucht, was ehrlich gesagt ein bisschen wie ein Hack wirkt, aber viel besser aussieht. Ich benutzte den Primary Key der äußeren Tabelle einen nicht null wertigen breit streuenden Schlüssel zu generieren.
Ich habe auch die Tatsache zunutze, dass die IDs integer waren und veränderte die Join-Bedingung zu:

FROM T_TAB1 T1
LEFT OUTER JOIN T_TAB2 T2
ON ( NVL(T2.X_ID, T2_id +0.5)    = T1.X_ID)

Das ist sicherlich ein bischen getrickst. Das einzig Positive daran ist, dass es funktioniert.
Die Aktivity Tab im Sql Monitor sah nachher so aus:

Vergleichen Sie dies mit dem vorherigen Bild. Es ist ziemlich beeindruckend.
Nun, in der Version 12 die Datenbank sollte in der Lage sein, mit schräg verteilten join Schlüsseln um zu gehen.
Aber es funktioniert nicht in jedem Fall, wie Randolf Geist feststellte.
Wenn jemand meine Lösung nicht gut findet, sollte er vielleicht er einen Blick auf_fix_control 6808773 werfen, aber ich gebe für keinen Fall Garantien ab. 😉

 

Die parallele Aktivität bricht ein

In einem meiner DWH POCs für Oracle’s Real World Performance Group bemerkten wir einen plötzlichen Einbruch in der Datenbank Aktivität. Unser Kunde verlangte eine Erklärung dafür.
Im SQL Monitor Activity Reiter sah das so aus:

Nach einer sorgfältigen Untersuchung erkannte ich, dass ein outer join die Ursache war. Formal sah dieser join so aus:

FROM T_TAB1 T1
LEFT OUTER JOIN T_TAB2 T2
ON T2.X_ID    = T1.X_ID

Das Problem war, dass 90% aller Werte in T1.X_ID Null Werte waren. Für die Aufteilung der Arbeit auf die Parallel Prozesse wurde der Hash Schlüssel benutzt. Als Folge bekam ein Parallelpozess 90% der ganzen Arbeit zugeteilt. Eine andere Aufteilung der Arbeit, etwa über einen broadcast war nicht möglich, da T1 zu gross war.
(Randolg Geist schildert das Problem ausführlich: Parallel Execution Skew – Demonstrating Skew).

Können Sie die join Bedingung so umschreiben, dass die Arbeit gleichmässig auf alle paralleln Prozesse verteilt wird? (Die Version war 11G. In version 12 sollte sich der optimizer automatisch um die Fragestellung kümmern.) Noch eine Annahme: die tabelle T2 hat einen Primary Key in Form der Spalte T2_id.

 

Lösung: Ist die Migrationsdatenbank langsamer?

In OTN wurde einmal gefragt, ob der Optimizer beim Erstellen des Planes die Grösse des Buffercaches berücksichtigt. Unter den üblichen Vorbehalten (es könnte in die Kostenbewertung mit einfliessen) würde ich sagen: Nein. Wie sollte sich die Grösse des Caches auch auswirken? Wenn ich einen grösseren Buffer cache habe, ist wahrscheinlicher dass ein X Segement im Cache ist, daher mache ich einen Indexzugriff ? (Ich argumentiere hier analog zum Parameter OPTIMZER_INDEX_CACHING.) Das ist keine gute Logik.  Die Grösse es Buffer caches ist kein sicherer Indikator dafür, ob ein spezifisches Segment auch tatsächlich im Cache ist. Wie man später sehen wird, kann  die Grösse des Caches auch ein Argument für den Full Table Scan sein.

Für die Erstellung des Execution Plans ist die Selektivität der entscheidende Faktor.

Wie jedoch ein bestehender Execution Plan umgesetzt wird, steht auf einem anderen Blatt. Hier hat die  Execution Enigine unter Umständen noch ein Wort mit zu sprechen.

Zum Beispiel bei einem Full Table Scan (FTS). Oft wird dieser als „direct path read“ umgesetzt. Das heisst, dass zwingend ein physischer I/O gemacht werden muss. (Für die Datenbank ist es auch dann noch ein physischer I/O wenn das Resultat aus dem File System Cache kommt. )

Wenn die Execution Engine aber erkennt, dass eine Tabelle sich fast vollständing im Buffer Cache befindet, kann von einem „direct path read“ auf einen „scattered read“ umgeschalten werden, Der „scattered read“ kann im Gegensatz zum „direct path read“ Nutzen aus dem Buffer Cache ziehen.

Ein ältere, aber gute Beschreibung stammt aus der Feder von Tanel Poder: Optimizer statistics-driven direct path read decision for full table scans .

Kurz gesagt gibt es zwei Voraussetzungen ob ein Umschalten über die Execution Engine stattfinden kann

  1. Der Buffer Cache muss gross genug sein, dass das Segment, das Gegenstand des FTS ist im Buffer Cache Platz hat
  2. Das Segment muss auch tatsächlich fast vollständig im Buffer Cache sein

Der Erste Punkt lässt sich ja leicht klären.  Die Analyse ergab folgendes: Die Tabelle war in der Produktion und in der Migration ca. 25GB gross.
Der Buffercache betrug in der Produktion aktuell 55 GB. In der Migration waren es lediglich 5 GB.
Damit lässt sich mit Sicherheit sagen: In der Produktion kann die besagte Tabelle vollständig gecacht werden. Theoretisch kann die Runtime Engine einen Scan im Memory veranlassen. In der Miragtion ist dies sicher nicht möglich.
War jetzt aber in der Produktion eine ausreichend grosse Anzahl Blöcke gecacht um einen Scan im Memory zu machen?
Zur Klärung dieser Frage dient folgendes Statement, welches die Anzahl der gecachten Blöcke einer Tabelle ermittelt.


SELECT sum(num_buf)
FROM X$KCBOQH x, dba_objects o
WHERE x.obj#=o.object_id
AND object_name='my table'
;

Wenn die so ermittelte Anzahl fast identisch zur Anzahl der Blöcke der Tabelle ist (nach meinen Tests > 90%, aber ohne Gewähr) wird auf „scattered read“ umgestellt.
Die Analyse der produktiven Datenbank ergab, dass die besagte Tabelle wirklich zum grössten Teil im Buffer Cache war. Ich konnte auch zeigen, dast der schnelle FTS nicht garantiert war, sondern nur dann stattfindet wenn die Tabelle ausreichend gecache. Wir machten noch die Gegenprobe und erhöhten den Buffer Cache der Migrationsumgebung. Sodann luden wir die Tabelle in den Cache.
Danach war der FTS auch in der Migrationsumgebung schnell.

Einige Anmerkungen:

Ein neuere, noch tiefere Analyse des Themas findet sich bei Frits Hoogland:

Investigating the full table direct path / buffered decision

Aufgrund verschiedener Beobachtungen denke ich, dass der Storage Server einer Exadata ähnliche Algorithmen in Bezug auf den Smart Scan anwenden kann, und ein Smart Scan in einen Scan des Flash Cache umgewandelt werden kann. Auch diese Information muss ich aber ohne Gewähr geben.

Lösung: Woher kommen die Downgrades?

Wie Stefan schon korrekt im Englischen Blog bemerkte, sind die downgrades zu einem Teil die Folge von parallelen Sessions die Toad stehen lässt. Dabei kann der auslösende Clientprozess schon lange nicht mehr existieren, die Datenbank sessions bestehen weiter. Dadurch kamen wir immer mehr unter Druck, da die Anzahl dieser Sessions langsam anwuchs. Es konnten immer weniger neue px alloziert werden, da der pool nicht unendlich ist.

Als Massnahme dagegen haben wir ein Profile aktiviert, dass inaktive Session nach einer gewissen Zeit killt.

Der zweite Grund lag daran, dass der Parameter parallel_adaptive_multi_user  gesetzt war. Dieser sorgte dafür, dass etwa die Häfte des parallel session pools für parallel processing verwendet werden konnte. Der Parameter war offenbar in einem DWH sinnwidrig. Die lokalen DBA’s bestanden aber darauf die Default wert nicht zu verändern. Zum ersten Mal war ich mit der absurden Idee konfrontiert, dass ein Default wert so etwas wie ein Tabu darstellt. Stattdessen wurde der Parameter parallel_threads_per_cpu triumphierend auf den Default von 2 gesetzt. Jedoch hatte das OS bereits alle Threads als CPU gemeldet, sodass jetzt doppelt so viele CPUs gemeldet waren als wirklich vorhanden waren, ebenso doppelt so viele Threads.

Für die downgrades haben sich drei sinnlose Werte in den Parametern glücklich neutralisiert. Wozu allerdings der Resource Manager noch lief, ist mir ehrlich gesagt nicht klar. 😉

 

 

Woher kommen die Downgrades?

In einem DWH kam es verstärkt zu downgrades der Parallelität. Es schien mit jedem Tag schlimmer zu werden. Tatsächlich zeigte sich, dass die frei verfügbaren parallel prozesse konstant abnahmen. Eine nähere Untersuchung der verwendeten PX zeigte bei vielen Toad als Quelle.
Wie war es möglich, dass die Anzahl dieser Toad verursachten parallelen sessions ständig wuchs?
Andererseits es waren immer noch ausreichend PX zur Verfügung, dennoch kam es zu downgrade. Welchen Parameter sollte man sich hier näher ansehen?

Solution: Runtimestatistiken lesen

Runtime statistiken sind für mich das wichtigste Optimierungswerkzeug für nicht parallele Abfragen.
Runtime statistiken lassen sich schnell erzeugen und beinhalten in der Regel schon alle nötigen Informationen für eine Optimierung.
Ich wiederhole zunächst die Runtimestatistiken der Rätselfrage:

 

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:15.42 |    3540 |      3262 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:15.42 |    3540 |      3262 |
|*  2 |   TABLE ACCESS BY INDEX ROWID|   T1 |      1 |  52695 |     10 |00:00:15.42 |    3540 |      3262 |
|*  3 |    INDEX RANGE SCAN          |   I1 |      1 |     17M|   4577 |00:00:00.16 |      11 |         9 |
-----------------------------------------------------------------------------------------------------------

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

   2 - filter(("NVL("X1_STATUS",'A')<>'U' AND NVL("X2_STATUS",'A')<>'D' 
               AND NVL("X3_STATUS",'A')<>'D' AND NVL("X4_STATUS",'A')<>'D' 
               AND NVL("X5_STATUS",'A')<>'U'))
   3 - access("T1_ID"=:B2 AND "T2_ID"=:B4 AND "T3_ID"=:B1)

Bei dem Problem fällt zunächst einmal auf, dass die Schätzung des Optimizers beim Indexzugriff in Operation 3 weit daneben liegt. Hierzu werden die „estimated rows“  (E-rows) mit den „actual rows“ (A-Rows) verglichen. Da es sich um eine sehr einfach zusammengesetzte Bedingung mit lauter „=“ Vergleichen handelt liegt es nahe, hier eine funktionale Abhängigkeit zu vermuten.

Die entsprechende Gegenmassnahme wären extended statistics.
Jedoch wird die meiste Zeit in der Operation 2 verloren. Hier fallen die „actual rows“ von 4577 Datensätzen in Operation 3 auf 10 Datensäte ab. Verantwortlich dafür ist die Filterbedingung in Operation 2:

2 - filter(("NVL("X1_STATUS",'A')<>'U' AND NVL("X2_STATUS",'A')<>'D' 
               AND NVL("X3_STATUS",'A')<>'D' AND NVL("X4_STATUS",'A')<>'D' 
               AND NVL("X5_STATUS",'A')<>'U'))

Gemäss der Tuning Technik „Throw-away“ erfunden vom Oak Table Mitglied Martin Berg müssen wir zusätzliche Spalten in den Index hinzuzufügen um den „throw-away“ in operation 2 zu vermeiden.

Ich habe sehr kluge Empfehlungen in meinem Kommentaren gefunden. Allerdings muss man in diesem Falle nicht sehr kompliziert vorgehen. Wir haben die Status Felder getestet und herausgefunden welche die Selektivität des Indexes erhöhen. Diese haben wir einfach in den Index eingefügt. Fälschlicherweise war der Entwickler der Applikation der Meinung die Statusfelder würden Nichts nützen, weil bei einem ungleich Vergleich kein Index verwendet werden kann.

Das ist nicht korrekt. Die Baumstruktur kann nicht verwendet werden. Jedoch wird bei einem Index Range Scan über die Baumstruktur nur der erste Leaf Block gesucht. Ab dann wird serial in den Leaf Blöcken weiter gesucht.

Bei dieser seriellen Suche kann über beliebige Vergleichsoperatoren gefiltert werden. Wie wir anhand der unten stehenden Runtime Statistiken sehen, reicht das schon um eine gute Performance zu gewährleisten. Der Verbesserungsfaktor liegt bei run 700.

Nicht einmal die Verbesserung der Statistiken ist für diesen Fall letztlich erforderlich, da der Optimizer zwar falsch schätzt, aber die zusätzlichen Spalten dennoch als Vorteil wahrnimmt.

Natürlich wäre die extened statistics unabhängig davon trotzdem sinnvoll.

 

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |      1 |        |      1 |00:00:00.02 |   12 |         4 |
|   1 |  SORT AGGREGATE              |                     |      1 |      1 |      1 |00:00:00.02 |   12 |         4 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| MM_SALES_DELIVERIES |      1 |  52695 |     10 |00:00:00.02 |   12 |         4 |
|*  3 |    INDEX RANGE SCAN          | PR_SALE_DEL_03      |      1 |  52695 |     10 |00:00:00.01 |    6 |         0 |
-----------------------------------------------------------------------------------------------------------------------

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

   2 - filter((NVL("MM_DISTRIBUTION_DEL_STATUS",'H')<>'D' AND NVL("MM_OUTBOUND_DEL_STATUS",'H')<>'D' AND
              NVL("MM_OUTBOUND_STATUS",'H')<>'U'))
   3 - access("MM_WAREHOUSE_ID"=:B4 AND "MM_FIRM_ID"=:B1 AND "MM_ITEM_ID"=:B2 AND "MM_FROM_LOCATION_ID"=:B3)
       filter((NVL("MM_DISTRIBUTION_STATUS",'H')<>'U' AND NVL("MM_DIRECT_DEL_STATUS",'H')<>'D'))