Oracle-DB: Root-cause Analyse nach unable to exend temp segment

Transcript

1. Root-Cause-Analyse nach "unable to extent temp segment" Mai 2017 Peter Ramm, OSP Dresden
2. Seite 2 Gründung: März 1991 Muttergesellschaft: OTTO Group Standorte: Dresden, Hamburg, Burgkunstadt, Taipeh, Bangkok Mitarbeiterzahl: Ca. 250 Geschäftsführer: Dr. Stefan Borsutzky, Alexander Hauser Otto Group Solution Provider Dresden GmbH www.osp.de
3. Seite 3 zur Person Peter Ramm Teamleiter strategisch-technische Beratung bei OSP Dresden > 20 Jahre Historie in IT-Projekten Schwerpunkte: • Entwicklung von OLTP-Systemen auf Basis von Oracle- Datenbanken • Architektur-Beratung bis Trouble-Shooting • Performance-Optimierung bestehender Systeme
4. ORA-01652: unable to extend temp segment Seite 4 Variante 1 Die Session die den ORA-1652 auslöst hat selbst große Bereiche des TEMP-Tablespace allokiert und erhält den ORA-1652 wenn kein weiterer Platz im TEMP-Tablespace allokiert werden kann. Auf welche Ursachen ist das Auftreten des Fehlers zurück zu führen? Variante 2 Andere Sessions haben erfolgreich große Bereiche des TEMP-Tablespace allokiert, jedoch eine Session mit nur einer kleinen nicht mehr erfüllbaren Anforderung erhält letztlich den ORA-1652. Wie kann jetzt im Nachhinein ermittelt werden: • Welche einzelne Session oder welche Sessions haben wirklich große Bereiche des TEMP-Tablespace allokiert und sind somit verantwortlich für das Auftreten des ORA-01652? • Was führte in den identifizierten Sessions zu der kritischen TEMP-Nutzung? Variante 3: Ungenutzter TEMP-TS auf anderer RAC-Instance allokiert, heute nicht weiter betrachtet
5. Zur Verfügung stehende Daten für Auslastung des temporary Tablespace Seite 5 Aktueller Zustand • GV$SORT_SEGMENT: Übergreifend je Instance • GV$TEMPSEG_USAGE: Allokierter TEMP-Tablespace nach Sessions und SQL-IDs Rückwirkende Betrachtung • DBA_Hist_Active_Sess_History, GV$Active_Session_History, Spalte Temp_Space_Allocated : Used TEMP-TS je Session alle 10 Sekunden bzw. je Sekunde • DBA_Hist_SysMetric_Summary / GV$SysMetric_History, Metric_Name=‘Temp Space Used‘: Used TEMP-TS per Instance je AWR-Snapshot bzw. je Minute für die letzte Stunde • DBA_Hist_Sysstat, Stat_Name=‘temp space allocated (bytes)‘: je Instance und AWR-Snapshot Bei Active Session History verbleibt allerdings eine konzeptionelle Lücke in der rückwirkenden Betrachtung, indem nicht alle Sessions mit allokiertem TEMP-Tablespace auch aufgezeichnet werden. Nicht aufgezeichnet in ASH werden Sessions wenn sie inaktiv sind oder mit Wait-Class „Idle“ aktiv sind .
6. Beispiel-SQL für Auslastung TEMP-TS und PGA über Zeit auf Basis der Active Session History Seite 6 WITH samples AS ( SELECT CAST (Sample_Time+INTERVAL '0.5' SECOND AS DATE) Sample_Time, s.Instance_Number, s.Session_ID, s.Session_serial# Session_Serial_No, 10 Sample_Cycle, -- Gewichtete Zeit in der Annahme, dass Wait aktiv für die Dauer des Samples war (und daher vom Snapshot gesehen wurde) s.PGA_Allocated, s.Temp_Space_Allocated -- eigentlich nichtssagend, da Summe über alle Sample-Zeiten hinweg, nur benutzt fuer AVG FROM DBA_Hist_Active_Sess_History s WHERE s.Sample_Time >= TO_TIMESTAMP(:A5, 'YYYY-MM-DD HH24:MI') AND s.Sample_Time < TO_TIMESTAMP(:A6, 'YYYY-MM-DD HH24:MI') ) SELECT /*+ ORDERED Panorama-Tool Ramm */ MIN(s.Sample_Time) Start_Sample_Time, MAX(s.Sample_Time) End_Sample_Time, SUM(Sample_Count) Sample_Count, SUM(Time_Waited_Secs) Time_Waited_Secs, MAX(s.Sum_PGA_Allocated)/(1024*1024) Max_Sum_PGA_Allocated, MAX(s.Sum_PGA_Floating)/(1024*1024 ) Max_Sum_PGA_Floating, MAX(s.Max_PGA_Allocated_per_Session)/(1024*1024) Max_PGA_Alloc_Per_Session, SUM(s.Sum_PGA_Allocated)/SUM(s.Sample_Count)/(1024*1024) Avg_PGA_Alloc_per_Session, MAX(s.Sum_Temp_Space_Allocated)/(1024*1024) Max_Sum_Temp_Space_Allocated, MAX(s.Sum_Temp_Floating)/(1024*1024 ) Max_Sum_Temp_Floating, MAX(s.Max_Temp_Space_Alloc_per_Sess)/(1024*1024) Max_Temp_Space_Alloc_per_Sess, SUM(s.Sum_Temp_Space_Allocated)/SUM(s.Sample_Count)/(1024*1024) Avg_Temp_Space_Alloc_per_Sess FROM (SELECT Sample_Time, SUM(Sample_Count) Sample_Count, -- Summation über die Sessions des Samples SUM(Time_Waited_Secs) Time_Waited_Secs, -- Gewichtete Zeit in der Annahme, dass Wait aktiv für die Dauer des Samples war (und daher vom Snapshot gesehen wurde) SUM(PGA_Exact) Sum_PGA_Allocated, -- Summation über die Sessions des Samples SUM(PGA_Floating) Sum_PGA_Floating, -- Summation über die Sessions des Samples MAX(PGA_Exact) Max_PGA_Allocated_Per_Session, -- Max. Wert einer Session des Samples SUM(Temp_Exact) Sum_Temp_Space_Allocated, -- Summation über die Sessions des Samples SUM(Temp_Floating) Sum_Temp_Floating, -- Summation über die Sessions des Samples MAX(Temp_Exact) Max_Temp_Space_Alloc_per_Sess -- Max. Wert einer Session des Samples FROM (SELECT Sample_Time, MAX(Sample_Count) Sample_Count, MAX(Time_Waited_Secs) Time_Waited_Secs, MAX(PGA_Exact) PGA_Exact, MAX(PGA_Floating) PGA_Floating, MAX(Temp_Exact) Temp_Exact, -- Temp je Session zum Zeitpunkt des Samples MAX(Temp_Floating) Temp_Floating -- Max. Temp je Session zum Zeitpunkt +- x Sekunden FROM (SELECT /*+ NO_MERGE ORDERED */ t.Sample_Time, -- Jede vorkommende Sample_Time verknüpft mit Samples vorher und nachher s.Instance_Number, s.Session_ID, s.Session_Serial_No, -- Attribute der verknüpften Sessions CASE WHEN t.Sample_Time = s.Sample_Time THEN 1 ELSE 0 END Sample_Count, CASE WHEN t.Sample_Time = s.Sample_Time THEN ss.Sample_Cycle ELSE 0 END Time_Waited_Secs, -- Gewichtete Zeit in der Annahme, dass Wait aktiv für die Dauer des Samples war (und daher vom Snapshot gesehen wurde) CASE WHEN t.Sample_Time = s.Sample_Time THEN ss.PGA_Allocated ELSE 0 END PGA_Exact, -- konkreter Wert zu t.sample_Time MAX(NVL(ss.PGA_Allocated, 0)) OVER (PARTITION BY s.Instance_Number, s.Session_ID, s.Session_Serial_No, s.Sample_Time) PGA_Floating, -- Max. Wert je Session zu t.sample_Time +- x Sekunden CASE WHEN t.Sample_Time = s.Sample_Time THEN ss.Temp_Space_Allocated ELSE 0 END Temp_Exact, -- konkreter Wert zu t.sample_Time MAX(NVL(ss.Temp_Space_Allocated, 0)) OVER (PARTITION BY s.Instance_Number, s.Session_ID, s.Session_Serial_No, s.Sample_Time) Temp_Floating -- Max. Wert je Session zu t.sample_Time +- x Sekunden FROM (SELECT /*+ NO_MERGE */ DISTINCT Instance_Number, Sample_Time FROM Samples) t JOIN (SELECT /*+ NO_MERGE */ Sample_Time, Instance_Number, Session_ID, Session_Serial_No FROM Samples) s ON s.Instance_Number = t.Instance_Number AND t.Sample_Time >= s.Sample_Time - INTERVAL '20' SECOND AND t.Sample_Time <= s.Sample_Time + INTERVAL '20' SECOND LEFT OUTER JOIN Samples ss ON ss.Sample_Time = t.Sample_Time AND ss.Instance_Number = s.Instance_Number AND ss.Session_ID = s.Session_ID AND ss.Session_Serial_No = s.Session_Serial_No ) GROUP BY Sample_Time, Instance_Number, Session_ID, Session_Serial_No -- Verdichten des mit +/- x Sekunden ausmultiplizierten Ergebnis zurück auf reale Menge ) GROUP BY Sample_Time -- Auf Ebene eines Samples reduzieren ueber RAC-Instanzen hinweg ) s WHERE s.Sample_Time >= TO_TIMESTAMP(:A7, 'YYYY-MM-DD HH24:MI') -- Nochmal Filtern nach der Rundung auf ganze Sekunden AND s.Sample_Time < TO_TIMESTAMP(:A8, 'YYYY-MM-DD HH24:MI') -- Nochmal Filtern nach der Rundung auf ganze Sekunden GROUP BY TRUNC(s.Sample_Time, 'MI') ORDER BY TRUNC(s.Sample_Time, 'MI') Unscharfe Abgrenzung über mehrere Samples hinweg hilft TEMP-Allokation auch temporär inaktiver Sessions mit zu zählen und somit näherungsweise die Realität zu rekonstruieren.
7. Panorama: Analyse-Tool für Oracle-DB Seite 7 Freies Werkzeug für Performance-Analyse von Oracle-DB Verfügbar unter: http://rammpeter.github.io • „Leichte“ Machbarkeit der Analysen mittels GUI-Workflow, auch für „Laien“ • Senken der Hemmschwelle, Problemen tatsächlich auf den Grund zu gehen • Identifikation der konkrete Ursachen für unzureichend Applikations-Performance Erfordert Enterprise Edition und Diagnostic Pack (bislang noch)
8. TEMP-Auswertung per „Panorama“: Summen über alle Sessions Seite 8 Menü: „Schema / Storage“ / „Temp usage“ / „Historisch“ Auswahl des Zeitraums, zeigt allokierten und genutzten TEMP-TS global und je Instance Allokiert: Snapshot am Ende des AWR- Zyklus Used: Max-Wert innerhalb AWR-Zyklus
9. TEMP-Auswertung per „Panorama“ nach aktiven Sessions je Sekunde / 10 Sekunden per „Panorama“ Seite 9 Menü: „Schema / Storage“ / „Temp usage“ / „Historisch“ Auswahl Zeitraum und Gruppierung nach Zeiteinheit, optional Sekunden für unscharfe Betrachtung
10. Anzeige als Diagramm auf Zeitleiste Seite 10 Ein- und Ausblendung einzelner Spalten in Diagramm per Kontext-Menü (rechte Maustaste) Visualisiert zeitlichen Verlauf der Auslastung TEMP incl. Anschlag am oberen Ende des verfügbaren TEMP-Tablespace
11. Identifikation der Verbraucher per ASH-Drilldown Seite 11 Wahl der passenden zeitlichen Gruppierung (Stunde / Minute / Sekunde ...) Ermitteln des interessierenden Zeitpunktes mit max. Auslastung. z. B. durch absteigendes Sortieren nach Spalte „Max. TEMP allocated MB“ Wechsel in ASH-Analyse durch Link in Spalte „Total time waited“ Drilldown in ASH-Analyse z.B. nach „Session/Sn.“ oder „SQL-ID“ oder ... Absteigend Sortieren nach Spalte „Max. Temp“
12. Identifikation der Verbraucher per ASH-Samples Seite 12 Wahl der passenden zeitlichen Gruppierung (hier am Besten nach Sekunden) Ermitteln des interessierenden Zeitpunktes mit max. Auslastung. z. B. durch absteigendes Sortieren nach Spalte „Max. TEMP allocated MB“ Wechsel in ASH-Samples durch Link in Spalte „Samples (ASH)“ Ermitteln der TEMP-Verbraucher durch absteigendes Sortieren nach Spalte „Max. Temp“
13. Panorama greift nur lesend auf Ihre Datenbank zu und benötigt keine eigenen PL/SQL-Objekte. Sie können die Funktionen also ohne Risiko testen und verstehen. Probieren sie es gern aus. Beschreibung inkl. Download-Link: http://rammpeter.github.io/panorama.html Blog zum Thema: https://rammpeter.blogspot.de/search/label/Panorama%20How-To Letztes Wort
14. Vielen Dank für Ihr Interesse Otto Group Solution Provider (OSP) Dresden GmbH Freiberger Str. 35 | 01067 Dresden T +49 (0)351 49723 0 | F +49 (0)351 49723 119 osp.de