V$-Views

IT-Symposium 2007
19.04.2007
IT-Symposium 2007
www.ordix.de
www.it-symposium2007.de
1
IT-Symposium 2007
19.04.2007
TRACING 007
Im Geheimdienst Ihrer Majestät
www.it-symposium2007.de
2
IT-Symposium 2007
19.04.2007
Überblick
V-Dollar jagt Dr. No
Tracing, im Geheimdienst Ihrer Majestät
dbms_application_info, Leben und sterben lassen
Raw Traces, der Hauch des Todes
TKProf, stirb an einem anderen Tag
TRCA, Casino Royale
12 Zylinder
6l Hubraum
460 PS
0 - 100 Km/h in 5s
www.it-symposium2007.de
3
IT-Symposium 2007
19.04.2007
Sir Sean Connery
V-Dollar jagt Dr. No
Tracing, im Geheimdienst Ihrer Majestät
dbms_application_info, Leben und sterben lassen
Raw Traces, der Hauch des Todes
TKProf, stirb an einem anderen Tag
TRCA, Casino Royale
www.it-symposium2007.de
4
IT-Symposium 2007
19.04.2007
V$, jagt Do. No
Kenntnis über einige V$-Tabellen notwendig für Tracing
Gute Kenntnis wichtig bei Systemen mit vielen Session, Usern ...
Sichtweise auf Sessions, Prozesse, SQL Befehle,
Ausführungspläne, Wait-Events ...
V$-Views
Client Informationen
Ausgangspunkt für Analysen
v$session
osuser
machine
terminal
program
username
sid = Eindeutig zu einem Zeitpunkt
sid, serial# = Eindeutig für Laufzeit der Instanz
www.it-symposium2007.de
5
IT-Symposium 2007
19.04.2007
V$-Views
Informationen über den Betriebssystemprozess
PGA Informationen
v$process
addr=paddr
v$session
spid = Prozess-ID
V$-Views
aktuelle Wait Events - sehr hohe Fluktuation
v$process
v$session
v$session_wait
sid=sid
v$session_wait_class
Aggregiert nach Klassen
www.it-symposium2007.de
v$session_wait_history
die letzten 10 Events
6
IT-Symposium 2007
19.04.2007
V$-Views
Wait Events - kumuliert
v$process
v$session
v$session_wait
sid=sid
v$session_event
kumuliert über alle Sessions
v$event_name
v$event_histogram
v$eventmetric
V$-Views
SQL-Befehle im Shared Pool
Analyse der Top-Verbraucher
sql_text
executions
disk_reads
buffer_gets
cpu_time
elapsed_time
v$process
v$session
v$session_wait
v$session_event
status =
'ACTIVE'
v$sql
Join
sql_address = address
sql_hash_value = hash_value
www.it-symposium2007.de
sql_id = sql_id
7
IT-Symposium 2007
19.04.2007
V$-Views
Vollständiger SQL-Text
Auswertung mit dbms_xplan
v$process
v$session
v$session_wait
Join & ID
v$session_event
v$sql
cursor_number
sql_id
v$sql_plan
v$sqltext
v$sql_plan_statistics[_all]
V$-Views
SQL> SELECT * FROM
2>
TABLE( DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------SQL_ID cqgs1gmk0w26b, child number 0
------------------------------------SELECT * FROM bla, test
Plan hash value: 2400081574
-------------------------------------------------------------------------------| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
|
dbms_xp
-------------------------------------------------------------------------------lan
|
0 | SELECT STATEMENT
|
|
|
|
301 (100)|
|
|
1 | MERGE JOIN CARTESIAN
|
|
1 |
106 |
301
(2)| 00:00:04 |
|
2 |
PARTITION RANGE SINGLE|
|
1 |
13 |
2
(0)| 00:00:01 |
|
3 |
TABLE ACCESS FULL
| TEST |
1 |
13 |
2
(0)| 00:00:01 |
|
4 |
BUFFER SORT
|
| 99927 | 9075K|
299
(3)| 00:00:03 |
|
5 |
TABLE ACCESS FULL
| BLA | 99927 | 9075K|
299
(3)| 00:00:03 |
--------------------------------------------------------------------------------
Dritter Parameter format = { BASIC, TYPICAL, ALL }
www.it-symposium2007.de
8
IT-Symposium 2007
19.04.2007
V$-Views
Session-Statistiken
v$process
v$session
sid=sid
v$sesstat
v$session_wait
v$session_event
v$sql
v$sqltext
V$-Views
Session-I/O (Statistiken und Wait Events)
v$process
v$sess_io
v$session
v$sesstat
v$session_wait
v$session_event
sid=sid
v$sql
v$sqltext
www.it-symposium2007.de
9
IT-Symposium 2007
19.04.2007
V$ - Pro und Contra
Ausgangspunkt für Analysen
alle notwendigen Informationen verfügbar
Verständnis für die Arbeitsweise von Oracle
keine Lesekonsistenz
zu langsam für Detailanalysen
aufwendig zu implementieren
Georg Lazenby
V-Dollar jagt Dr. No
Tracing, im Geheimdienst Ihrer Majestät
dbms_application_info, Leben und sterben lassen
Raw Traces, der Hauch des Todes
TKProf, stirb an einem anderen Tag
TRCA, Casino Royale
www.it-symposium2007.de
10
IT-Symposium 2007
19.04.2007
Tracing,
im Geheimdienst Ihrer Majestät
Protokollierung einer Session
SQL Befehle, Wait-Events, Bind-Variablen
INSERT ...
Tracing
aktivieren
Tracing
deaktivieren
SELECT ...
UPDATE ...
INSERT ...
SELECT ...
UPDATE ...
<db_name>_ora_<spid>.ora
INSERT ...
user_dump_dest
Tracing - aktivieren (I)
SQL> ALTER SESSION SET sql_trace = true;
Session altered
SQL> ....
t
e
d
SQL> ALTER SESSION SET sql_trace = false;
Session altered
d
e
p
r
e
c
a
sql_trace={true|false}
user_dump_dest=/udump
init.ora
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( <sid>,<serial#>,true|false);
www.it-symposium2007.de
11
IT-Symposium 2007
19.04.2007
Tracing - aktivieren (II)
e
d
SQL> ALTER SESSION SET EVENTS
2> '10046 trace name context forever, level <n>';
SQL> ....
o
r
t
SQL> ALTER SESSION SET EVENTS
2> '10046 trace name context off';
u
n
s
u
p
p
11 wie
wieSQL
SQLTrace
Trace
44 Bind
BindVariablen
Variablen
88 Wait
WaitEvents
Events
12
12 alles
alles
exec dbms_system.set_ev( <SID>,<SERIAL#>, 10046, <n>, '' )
Tracing - aktivieren (III)
SQL> oradebug ...
www.it-symposium2007.de
rt d
o te
p
p or
u
S pp
su
12
IT-Symposium 2007
19.04.2007
Tracing - aktivieren (IV)
SQL> exec dbms_support.start_trace_in_session( sid => 140 >
serial => 566 -
>
waits => true -
>
binds => false )
-- Installation
@?\rdbms\admin\dbmssupp.sql
rt d
o e
p rt
p
u po
S p
su
Tracing - aktivieren (V)
SQL> exec dbms_monitor.session_trace_enable( > session_id => 135, > serial_num => 314, > waits => true, > binds => false )
PL/SQL procedure successfully completed.
SQL> ...
v$session (10gR2)
dba_enabled_traces
sql_trace
sql_trace_waits
sql_trace_binds
www.it-symposium2007.de
13
IT-Symposium 2007
19.04.2007
Traces - beeinflussen
SQL> ALTER SESSION SET max_dump_file_size = [nnn | unlimited];
SQL> ALTER SESSION SET tracefile_identifier = 'ORDIX';
SQL> ALTER SYSTEM
SET user_dump_dest = '/tmp';
v$process.traceid
<db_name>_ora_<spid>_ORDIX.ora
/tmp
Traces - identifizieren
SELECT par1.value
||
'\'
||
inst.instance_name
||
'_ora_'
||
spid
||
decode( par2.value, NULL, NULL, '_' || par2.value )
||
'.trc'
AS "Trace Dateiname"
FROM
v$process p
,
v$session s
,
v$instance inst
,
v$parameter par1
,
v$parameter par2
WHERE
p.spid = '&PID_eines_server_prozesses'
AND
p.addr = s.paddr
AND
par1.name = 'user_dump_dest'
AND
par2.name = 'tracefile_identifier';
www.it-symposium2007.de
14
IT-Symposium 2007
19.04.2007
Traces - Pro und Contra
einfach und flexibel
sehr detaillierte Informationen
Wait Events
Bind Variablen
Auswertung schwierig
teilweise sehr große Tracedateien
beeinflusst Performance
Sir Roger Moore
V-Dollar jagt Dr. No
Tracing, im Geheimdienst Ihrer Majestät
dbms_application_info, Leben und sterben lassen
Raw Traces, der Hauch des Todes
TKProf, stirb an einem anderen Tag
TRCA, Casino Royale
www.it-symposium2007.de
15
IT-Symposium 2007
19.04.2007
Sessions identifizieren?
Client
Client
Client
ora
ora
DB
Client
ora
Client
Webserver
Dispatcher
Server
Tracing mit Oracle 9 sehr schwierig
dbms_application_info
SQL> dbms_application_info.set_module( 'Moonraker','Prepare');
SQL> ...
SQL> dbms_application_info.set_module( 'Moonraker','Init');
SQL> ...
SQL> dbms_application_info.set_module( 'Moonraker','Start');
SQL> ...
www.it-symposium2007.de
16
IT-Symposium 2007
19.04.2007
dbms_monitor
SQL> dbms_application_info.set_module( 'Moonraker','Init');
SQL> ??????
SQL> dbms_application_info.set_module( 'Moonraker','Start');
SQL> exec dbms_monitor.serv_mod_act_trace_enable
( service_name => 'majesty'
, module_name => 'Moonraker'
, action_name => 'Init'
, waits => true
, binds => false
)
-
dbms_monitor
Parameter
SERVICE
MODULE
ACTION
majesty
NULL
NULL
Alle Clients des Service majesty
majesty
sqlplus
NULL
Alle SQL*Plus Clients
des Service majesty
majesty
Moonraker
Init
www.it-symposium2007.de
Bedeutung
Alle Clients des Service
majesty mit Module=Moonraker
und ACTION=Init
17
IT-Symposium 2007
19.04.2007
Begleitende CD
... gibt es an der ORDIX
Infoinsel
• mit allen ORDIX
Vorträgen
• Themenverwandten
Fachartikeln
• Nützlichen Zusatzinfos
dbms_monitor
SQL> exec dbms_monitor.serv_mod_act_trace_enable
( service_name => majesty
, module_name => 'Moonraker'
, action_name => 'Init'
, waits => true
, binds => false);
Client
Client
ora
DB
Client
Jede Session im Modul
Moonraker.Init
erzeugt eine Tracedatei
www.it-symposium2007.de
ora
Webserver
18
IT-Symposium 2007
19.04.2007
trcsess
trcsess erzeugt aus mehreren Trace-Dateien eine Neue
Aufruf auf Betriebssystemebene
$ trcsess output=moonraker.trc \
service_name=majesty
module=Moonraker \
action=Init \
*.*
ora
DB
ora
trcsess
tkprof
moonraker.trc
dbms_monitor Pro und Contra
Traces für Teile einer Applikation
für mehrschichtige Architekturen geeignet
Statistiken
Wait Events, Bind Variablen
Anwendungsentwicklung
Komplex
www.it-symposium2007.de
19
IT-Symposium 2007
19.04.2007
Timothy Dalton
V-Dollar jagt Dr. No
Tracing, im Geheimdienst Ihrer Majestät
dbms_application_info, Leben und sterben lassen
Raw Traces, der Hauch des Todes
TKProf, stirb an einem anderen Tag
TRCA, Casino Royale
Raw Traces,
der Hauch des Todes
Basis für tkprof
Informationen nicht aufbereitet, also „raw“
Zeitlicher Verlauf sichtbar
Sehr detaillierte Analysen möglich
www.it-symposium2007.de
20
IT-Symposium 2007
19.04.2007
Raw Traces vs. tkprof
call
count
------- -----Parse
3
Execute
3
Fetch
5922
------- -----total
5928
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
6.71
6.72
0
264993
0
-------- ---------- ---------- ---------- ---------6.71
6.73
0
264993
0
rows
---------0
0
59220
---------59220
Misses in library cache during parse: 1
Parsing user id: 41
Elapsed times include waiting on following events:
Event waited on
Times
Max. Wait Total Waited
---------------------------------------Waited ---------- -----------SQL*Net message to client
5926
0.00
0.00
SQL*Net more data to client
30006
0.00
0.80
SQL*Net message from client
5926
0.06
6.91
SQL*Net break/reset to client
2
0.01
0.01
********************************************************************************
Tatsächliche Laufzeit 10 Minuten, warum?
Raw Traces
FETCH #35:c=0,e=1250,p=0,cr=47,cu=0,mis=0,r=10,dep=0,og=4,tim=..
WAIT #35: nam='SQL*Net message from client' ela= 1013 p1=...
WAIT #0: nam='SQL*Net message to client' ela= 0 p1=...
WAIT #0: nam='SQL*Net message from client' ela= 306 p1=...
WAIT #0: nam='SQL*Net message to client' ela= 0 p1=...
...
WAIT #0: nam='SQL*Net message from client' ela= 1014 p1=...
WAIT #0: nam='SQL*Net message to client' ela= 0 p1=...
WAIT #0: nam='SQL*Net message from client' ela= 559 p1=....
WAIT #0: nam='direct path read (lob) ' ela= 28 p1=....
120 MB Tracedatei
möglicherweise fehlte das Ende, max_dump_file_size?
www.it-symposium2007.de
21
IT-Symposium 2007
19.04.2007
Raw Traces vs. Statspack
Top 5 Timed Events Laufzeit = 3600s
~~~~~~~~~~~~~~~~~~
% Total
Event
Waits
Time (s)
DB Time
Wait Class
------------------------------ ------------ ----------- --------- -------------enq: HW - contention
13,163
2,626
50.52 Configuration
db file sequential read
104,888
1,444
27.77
User I/O
CPU time
776
14.93
log file sync
16,505
143
2.74
Commit
log file parallel write
17,942
116
2.24
System I/O
-------------------------------------------------------------
Problem?
Avg
Total Wait
wait
Waits
Event
Waits
Time (s)
(ms)
/txn
--------------------------- ------------ ---------- ------- --------enq: HW - contention
13,163
2,626
200
0.9
Raw Traces vs.
v$event_historgram
EVENT#
---------423
423
423
423
423
423
423
423
423
423
423
423
423
EVENT
WAIT_TIME_MILLI WAIT_COUNT
------------------------------ --------------- ---------enq: HW - contention
1
7471
enq: HW - contention
2
9044
enq: HW - contention
4
19950
enq: HW - contention
8
41759
enq: HW - contention
16
94489
enq: HW - contention
32
211894
enq: HW - contention
64
281753
enq: HW - contention
128
273350
enq: HW - contention
256
220828
enq: HW - contention
512
132596
enq: HW - contention
1024
67016
enq: HW - contention
2048
28693
enq: HW - contention
4096
9251
Seit Start der Datenbank wurde 9.251 mal
zwischen 2 und 4 Sekunden gewartet !
www.it-symposium2007.de
22
IT-Symposium 2007
19.04.2007
Raw Traces - Details...
PARSING IN CURSOR #11 len=76 dep=0 uid=37 oct=3 lid=37 tim=...
SELECT xxx FROM xxx WHERE xxx = :1 FOR UPDATE
END OF STMT
EXEC #11:c=0,e=217,p=0,cr=17,cu=2,mis=0,r=0,dep=0,og=1,tim=1119934391667186
WAIT #11: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0
FETCH #11:c=0,e=66,p=0,cr=4,cu=0,mis=0,r=2,dep=0,og=1,tim=1119934391667375
WAIT #11: nam='SQL*Net message from client' ela= 141 p1=1952673792 p2=1 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 2 p1=1952673792 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 96 p1=1952673792 p2=1 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 228 p1=1952673792 p2=1 p3=0
WAIT #12: nam='enq: HW - contention' ela= 605003 p1=1213661190 p2=5 p3=37749523
WAIT #12: nam='db file sequential read' ela= 45040 p1=10 p2=229337 p3=1
'enq: HW - contention' ela= 605003
= 0,6 Sekunden
Raw Traces
Parameter p1, p2, p3 siehe v$session_wait
p1 ... p3 ab Oracle 10g im Klartext
WAIT: nam='db file...' ela=581 p1=4 p2=007 p3=1 ...
WAIT: nam='db file...' ela=581 file#=4 block#=007 blocks=1..
www.it-symposium2007.de
23
IT-Symposium 2007
19.04.2007
Raw Traces
sporadisch Zeitstempel
Uhrzeit am Ende des darunter stehenden Events
09:30:10 - 09:30:59 Warten auf Client
49,2 Sekunden Warten
*** 2006-10-04 09:30:59.237
WAIT #9: nam='SQL*Net message from client' ela=49222111 ...
....
....
raw traces - Pro und Contra
sehr detailliert
zeitliche Reihenfolge
Wait Events, Binds
aufwendig zu analysieren
www.it-symposium2007.de
24
IT-Symposium 2007
19.04.2007
Pierce Brosnan
V-Dollar jagt Dr. No
Tracing, im Geheimdienst Ihrer Majestät
dbms_application_info, Leben und sterben lassen
Raw Traces, der Hauch des Todes
TKProf, stirb an einem anderen Tag
TRCA, Casino Royale
TKProf,
stirb an einem anderen Tag
Transient Kernel Profiler
Erzeugt aus Raw-Traces Aggregationen und Zusammenfassung
www.it-symposium2007.de
25
IT-Symposium 2007
19.04.2007
TKProf
Syntax
tkprof tracefile outputfile [explain= ] [table= ]
[insert= ]
[sys= ] [sort= ]...
explain & table für Explain Plan Erstellung
insert = SQL Befehle um TKProf Ergebnis in DB zu speichern
sys = Rekursive SQL Befehle
TKProf & trcsess
Bei Verwendung von dbms_monitor
W
ie
r
e
d
l$
o
h
g
n
u
trcsess output=moonraker.trc \
service_name=majesty
module=Moonraker \
action=Init \
*.*
ora
DB
ora
trcsess
tkprof
moonraker.trc
www.it-symposium2007.de
26
IT-Symposium 2007
19.04.2007
TKProf - Pro und Contra
Guter Gesamtüberblick
Einfach zu bedienen
Wait Events
Binds
Zeitliche Reihenfolge
geht verloren
Daniel Craig
V-Dollar jagt Dr. No
Tracing, im Geheimdienst Ihrer Majestät
dbms_application_info, Leben und sterben lassen
Raw Traces, der Hauch des Todes
TKProf, stirb an einem anderen Tag
TRCA, Casino Royale
www.it-symposium2007.de
27
IT-Symposium 2007
19.04.2007
m
e
v
o
N
.
23
6
0
0
2
r
e
b
Trace Analyzer (TRCA),
Casino Royale
Download je nach Oracle Version (siehe Note 224270.1)
SQL> dbms_monitor...
TRCA
BOND
SQL> connect / as sysdba
SQL> @tacreate.sql
bond_ora_87.trc
exec trca$i.trace_analyzer('bond_ora_87.trc' )
trcanlzr_87_4.html
www.it-symposium2007.de
28
IT-Symposium 2007
19.04.2007
TRCA
Diverse Übersichten, z. B. Wait Events
TRCA
Hot Blocks: Segment, File-ID, Block-ID
Wait I/O je Segment
www.it-symposium2007.de
29
IT-Symposium 2007
19.04.2007
TRCA
Top SQL
Details je SQL: Ausführungsplan, Waits
Details je SQL: Statistiken, Indizes ...
TRCA
Zeiten ohne Aktivität
usw. usw. usw.
www.it-symposium2007.de
30
IT-Symposium 2007
19.04.2007
TRCA - Pro und Contra
Sehr guter Überblick
detaillierte Informationen
leicht zu bedienen
Open Source (PL/SQL)
Zeitliche Reihenfolge geht verloren
muss installiert werden
www.it-symposium2007.de
31
IT-Symposium 2007
19.04.2007
Weitere
Vorträge
Trainingskurs:
•
Oracle Hochverfügbarkeitslösungen
Freitag, 20.04.07, 09:00 - 17:00 Uhr
Referent: Herr Martin Hoermann, ORDIX AG, Münster
V-Dollar 007
Jagt Do. No
Sir Sean Connery
................................................................................. als James Bond
Ursula Andress
..................................................................................... als Honey Ryder
Do. No
............................................................................................ als Do. No
www.it-symposium2007.de
32
IT-Symposium 2007
19.04.2007
Tracing 007
Im Geheimdienst Ihrer Majestät
George Lazenby
.................................................................................... als James Bond
Diana Rigg
............................................................................................ als Tracy
Telly Savalas
......................................................................... als Ernst Stavro Blofeld
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
http://www.oracle.com/technology/
pub/articles/10gdba/nanda_10gr2dba_part3.html
dbms_application_info 007
Leben und Sterben lassen
Sir Roger Moore
................................................................................... als James Bond
Jane Seymour
..................................................................................... als Solitaire
Yaphett Kotto
..................................................................................... als Kananga
Metalink Note:281787.1
Oracle Database 10g Statistic Aggregation
using DBMS_MONITOR
www.it-symposium2007.de
33
IT-Symposium 2007
19.04.2007
Raw Traces 007
Der Hauch des Todes
Timothy Dalton
.................................................................................. als James Bond
Maryam d‘Abo
........................................................................ als Kara Milovy
Joe Don Baker
............................................................................. als Brad Whitaker
Metalink-Note 39817.1
TKPROF 007
Stirb an einem anderen Tag
Pierce Brosnan
....................................................................................... als James Bond
Halle Berry
................................................................................................. als Jinx
Toby Stephens
........................................................................... als Sir Gustav Graves
www.it-symposium2007.de
34
IT-Symposium 2007
19.04.2007
TRCA 007
Casino Royale
Daniel Craig
..................................................................................... als James Bond
Caterina Mourino
................................................................................ als Solange
Mads Mikkelsen
........................................................................................ als Le Chiffre
TraceAnalyzer_IOUG.pdf
(Note:224270.1)
das war...
...007 mit der Lizenz zum Tracen
www.it-symposium2007.de
35
IT-Symposium 2007
19.04.2007
Ihr Fragen beantworten wir gerne
an unserem Stand...
www.it-symposium2007.de
36