© OPITZ CONSULTING 2017

überraschend mehr Möglichkeiten!

© OPITZ CONSULTING 2017

mit frei verfügbaren Mitteln

#DOAG2017

Uwe KüchlerOPITZ CONSULTING Deutschland GmbH

Oracle Performance-Analyse

© OPITZ CONSULTING 2017

möglich, Hauptinh. l. + Sekundärinhalt r.

Oracle Performance-Analyse mit frei verfügbaren Mitteln

Zur Person

Generation C=64

Seit über 25 Jahren in der IT tätig

1997-2000 bei Oracle Deutschland

Seither durchgehend Oracle-Berater, im DBA-und Entwicklungs-Umfeld, Tutor

Seit 09/2013 bei OPITZ CONSULTING

Buch- und Blogautor (oraculix.de)

Performance als „Steckenpferd“

1975

2016

Cloud!☺

© OPITZ CONSULTING 2017 Seite 3

überzählige blaue Zahlenboxen löschen!

Agenda

1

2

3

Analyse akuter Probleme

Historische Analyse ohne AWR

Fragen

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017 Seite 4

Analyse akuter Probleme

Zugriff nur per SSH? AMON

SQL-Zauberkasten

Zugriff nur per SQL? Tunas360

V$SESSION_LONGOPS

orasrp / Extended SQL Trace

1

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Oracle Performance-Analyse mit frei verfügbaren Mitteln

Keine GUI? Nur SSH-Zugriff?Kein Problem!

© OPITZ CONSULTING 2017

möglich

AMON – ein „top“ für die Oracle-DB

Fortlaufend entwickelt von Andrej Simon (Oracle Support)

Für fast alle Unixe/Linuxe verfügbar

Rein Text-basiert Läuft daher in jedem Terminal

Ideal, wenn nur SSH-Zugriff auf DB-Server möglich ist

Berechnet regelmäßig differentielle Metriken, ähnlich „top“

Leichtgewichtig und schnell aufgerufen

https://sites.google.com/site/freetoolamon/

https://github.com/Rendanic/SQL-Zauberkasten

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Oracle Performance-Analyse mit frei verfügbaren Mitteln

AMON – ein „top“ für die Oracle-DB

LD_LIBRARY_PATH muss (korrekt) gesetzt sein

Empfehlung: „amon“ als Alias in .bashrc anlegen.

$ alias amon="~/zk/amon/11.2/amon64_ol5_11r2 -u system -p oracle"

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$ amon

© OPITZ CONSULTING 2017

missachten!

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

TUNAs360

„Tuning with Active Sessions“ Von Mauro Pagano (Accenture Enkitec, ex-Oracle-Support)

Teil der „*360“-Tools (edb360, eAdam360)

Ca. 10 min Sampling von v$session Messung aktiver Sessions, ähnlich ASH

HTML-Ausgabe DB-Übersicht

Active Session Graph

Top 5 Sessions

Top 5 SQL

Top Objects aus verschiedenen Perspektiven

Top Ausführungspläne als Textdateien

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

TUNAs360

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

TUNAs360

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

Rücksprache mit Mktg.

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

Rücksprache mit Mktg.

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

V$SESSION_LONGOPS

Zeigt lange laufendes SQL und PL/SQL Ab bestimmten Schwellwerten

PL/SQL: bei entsprechender Instrumentierung immer. DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS

Nur Operationen, deren Dauer berechnet werden kann Full Table Scans

Index (Fast) Full + Range Scans

Hash- und Merge Joins

leider keine Nested Loops Nachteil bei OLTP

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

V$SESSION_LONGOPS im SQL Developer

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Historisierte Metriken für 60 Minuten kostenfrei

Es gibt Views auf die Performance-Metriken der letzten 60 Minuten, für die kein Diagnostics Pack gekauft werden muss:

V$WAITCLASSMETRIC_HISTORY Wartezeiten nach Wait Class (Application, Concurrency, Network, …)

Durchschnitt über 15s und 60s, für eine Stunde historisiert

V$SYSMETRIC_HISTORY System-Metriken (CPU, Executes, Parses, Commits, Reads, …)

Durchschnitt über 15s und 60s, für eine Stunde historisiert

Entspricht den Performance-Infos in EM – aber kostenfrei!

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Historisierte Metriken für 60 Minuten kostenfrei (2)select * from (

with WC as(

select distinct e.WAIT_CLASS, e.WAIT_CLASS_ID

from v$event_name e

)

select to_char( END_TIME, 'yyyy-mm-dd hh24:mi' ) time

, wc.WAIT_CLASS

, round( AVERAGE_WAITER_COUNT, 2 ) AVERAGE_WAITER_COUNT

from GV$WAITCLASSMETRIC_HISTORY h

, WC

where h.WAIT_CLASS_ID = wc.WAIT_CLASS_ID

and wc.WAIT_CLASS <> 'Idle'

and h.INST_ID = ( select INST_ID from V$INSTANCE )

union all

select to_char( END_TIME, 'yyyy-mm-dd hh24:mi' ) time

, '_CPU' as WAIT_CLASS

, round( sh.VALUE / 100, 2 ) AVERAGE_WAITER_COUNT

from GV$SYSMETRIC_HISTORY sh

where sh.METRIC_ID = 2075 -- "CPU Usage Per Sec" in csec/s

and sh.INST_ID = ( select INST_ID from V$INSTANCE )

)

where rownum < 1000

order by TIME, WAIT_CLASS;

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

Rücksprache mit Mktg.

Gather Database Stats

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Oracle Performance-Analyse mit frei verfügbaren Mitteln

orasrp / Extended SQL Trace

© OPITZ CONSULTING 2017

möglich

Extended SQL Trace-- An-/Abschalten in der eigenen Session

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

-- An-/Abschalten für eine andere Session

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

-- An-/Abschalten für eine andere Session

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Extended SQL Trace (2)

„Howto“ auf oracle-base.com

Trace File auswerten mit Tkprof

SQL Developer

orasrporasrp --google-charts tracefile.trc parsedfile.html

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Oracle Performance-Analyse mit frei verfügbaren Mitteln

ORASRP

Ein „tkprof“ mit HTML-Ausgabe, Verlinkten Abschnitten und Graph

Kommandozeile fast wie bei tkprof

Kann mit Proxy-Script im Client-/Server-Modus laufen Start im Trace-Verzeichnis auf dem DB-Server

Aufruf orasrp vom Client

© OPITZ CONSULTING 2017

missachten!

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017 Seite 25

Historische Analyse ohne AWR

Log Switch Heat Map

Statspack

SQLT und sqlhc

2

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Log Switch Heat Map

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Log Switch Heat Map

Grundlage: v$log_history

SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24')||'h' t

, COUNT(*)

FROM v$log_history

WHERE trunc(FIRST_TIME) >= trunc(sysdate - 31)

GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24');

…das geht aber schöner! ☺

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Log Switch Heat Map in SQL Developer

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

STATSPACK

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Statspack lebt!

Bordmittel seit Oracle 8

Vorläufer des AWR, basierend auf Schnappschüssen von Wait Events.

Ist auch in Oracle 12c immer noch verfügbar! Anfängliche Startschwierigkeiten in 12c behoben.

Kleiner Bug in 12.1.0.2 bei einem unbekannten, aber nützlichen Feature (s.u.)

Größerer Bug: Idle Events werden in Top Events gelistet.

Kombiniert mit Oracle SQL Developer Reports sogar graphisch und kostenfrei.

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

STATSPACK 12c: Installation

Installation in 12c genauso wie bisher: Tablespace für Schema PERFSTAT einrichten.

Wenn Multi-Tenant und Installation in CDB:SQL> alter session set "_oracle_script"=TRUE;

SQL> @spcreate

Snapshot-Job einrichten;Empfehlung: Um Ausführungspläne zu archivieren, höchsten Level einstellen (statspack.snap(7))

Löschjob einrichten (Aufruf von statspack.purge(<days>)).

Achtung bei RAC: Es wird ein Snapshot-Job pro Instance benötigt. Skripte, um dies alles zu automatisieren, gibt es auf Github:

https://github.com/Rendanic/SQL-Zauberkasten/tree/master/sql/statspack/spcreate

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

STATSPACK 12c: Reports

Wie bei AWR wird ein Bericht aus den Differenzen zweier Snapshots generiert.

$ORACLE_HOME/rdbms/admin/spreport

Idle Events

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

STATSPACK und DBMS_XPLAN

In Statspack historisierte Ausführungspläne können mit dem bekannten DBMS_XPLAN formatiert werden Über den Plan_Hash_Value (phv), nicht über die sql_id

select * from table(DBMS_XPLAN.DISPLAY(

TABLE_NAME => 'perfstat.stats$sql_plan'

, STATEMENT_ID => null

, FORMAT => 'ALL -predicate -note'

, FILTER_PREDS => 'plan_hash_value = '|| &phv

));

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

STATSPACK und DBMS_XPLAN (2)

Bug in 12.1 und 12.2: DBMS_XPLAN erwartet eine Spalte, die in der Statspack-Tabelle derzeit noch fehlt.

Workaround: Spalte (unsichtbar) manuell anlegen.

ALTER TABLE perfstat.stats$sql_plan ADD timestamp

INVISIBLE AS (cast(NULL AS DATE));

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Statspack-Analyse mit SQL Developer Reports

Beispiel: Average Active Sessions (AAS) Wesentlicher Indikator für Belastung des RDBMS.

Vergleichbar mit „Load“ in Unix/Linux top.

Guter Einstieg für ein Drill-Down nach Top Waits und Top SQL

Nicht in SQL Developer mitgeliefert; bei GitHub zu finden http://tinyurl.com/sqldevReports

http://oraculix.com/2015/03/24/visualizing-statspack-average-active-sessions-in-sql-developer/

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Problem: Mehr aktive Sessions als log. CPUs (hier: Hyperthreads) Überlastung des Servers Lange Antwortzeiten

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Worauf haben die Sessions in diesem Zeitraum am längsten gewartet?

Idle Events korrekt sortiert

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

SQLT und sqlhc

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Wozu SQLT / SQLHC?

Beschleunigt den SQL-Tuning-Prozess durch Automatisierung von ansonsten manuell ausgeführten Recherchen:

Plötzliche Änderungen am Ausführungsplan

Änderungen an den Optimizer-Statistiken der vom SQL benutzten Objekte

Grundsätzlich schlechte oder fehlende Optimizer-Statistiken

Nicht mehr benutzte (z.B. weil invalide oder gelöschte) Indizes

Aufruf des SQLs mit außergewöhnlichen Bind-Werten

Hinweise auf versionsspezifische Bugs

© OPITZ CONSULTING 2017

möglich

Funktionsweise von SQLT

© OPITZ CONSULTING 2017

möglich

Funktionsweise von SQLT

Sammlung von SQL-Skripten, PL/SQL-Packages und Repository-Tabellen in eigenem Schema

Sammelt Informationen zum Tuning eines SQL-Statements ein, wahlweise aus historischen Informationen oder durch Ausführung des Statements

Führt über 100 Health Checks rund um das untersuchte Statement aus

Kann aus einem Anwendungs-Schema heraus ausgeführt werden

Läuft auf Single Instance, RAC und Exadata

Läuft unter Oracle 8i ++

© OPITZ CONSULTING 2017

möglich

Funktionsweise von SQLHC

Ein einzelnes Script (sqlhc.sql) Download unter Document ID 1366133.1

Erfordert keine Installation

Kann daher sofort auf eine Datenbank losgelassen werden

Hinterlässt keinen „Fußabdruck“ in der DB

Ausführen als SYS (niedriger privilegierter User möglich)

Entspricht Methode „XTRACT“ von SQLT SQL wird nicht ausgeführt

Gegenüber XTRACT kein STA, ADDM, 10046

Aber, falls verfügbar bzw. lizensiert: Historische Ausführungspläne aus AWR Historische Binds SQL-Monitor-Bericht (Flash-Version)

© OPITZ CONSULTING 2017

möglich

Funktionsweise von SQLHC:Bericht (Hauptseite)

© OPITZ CONSULTING 2017

möglich

Fazit

Eine komfortable Performance-Analyse geht auch Ohne teure Zusatzlizenzen

Ohne Tools von Drittherstellern

Statspack funktioniert auch noch in 12c und erlaubt eine fundierte Top-Down-Analyse.

Die Reports des SQL Developer sind auch für DBAs interessant!

Es gibt eine Menge freier Tools von Oracle und der Community TUNAs360, SQLT, sqld360, orasrp, …

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017 Seite 46Oracle Performance-Analyse mit frei verfügbaren Mitteln

Fragen und Antworten

© OPITZ CONSULTING 2017

überraschend mehr Möglichkeiten!

@OC_WIRE

OPITZCONSULTING

opitzconsulting

opitz-consulting-bcb8-1009116

WWW.OPITZ-CONSULTING.COM

Uwe M. Küchler

Managing Consultant

uwe.kuechler@opitz-consulting.comTelefon +49 6172 66260 – 0Mobil +49 173 727 91 43

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Links + Literatur

Li3

6.941

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Referenzen

SQL Developer Performance Reports: https://github.com/oraculix/sql-developer-tools

ORASRP – Profiler für Extended SQL Trace Files: http://oracledba.ru/orasrp/

Introducing TUNAs360: (Mauro Pagano)

SQLT und SQLHC: Präsentation von der DOAG 2013

SQLd360, SQL diagnostics collection made faster (Mauro Pagano)

SQL_TRACE/Event 10046 Trace File Analyzer (trca): MOS Doc ID 224270.1

Trace File Analyzer (TFA): MOS Doc ID 1513912.1

Trace Assistant (trcasst): Database Net Services Admin Guide

Welche Views und APIs sind Lizenzpflichtig: http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC165

Oracle Performance-Analyse mit frei verfügbaren Mitteln

© OPITZ CONSULTING 2017

möglich

Querverweise: Weitere Vorträge auf der #DOAG2017

Markus Haberstock: „Performance-Analyse und Monitoring für die SE“

Marco Mischke: „AWR und ASH für die Standard Edition“

Oracle Performance-Analyse mit frei verfügbaren Mitteln