To Do

1
To Do

• ADDM add chart showing advice over time
• Client id draw sessions and client bouncing
  from session to session
• Services show connection diagram
• Get Johns screen shot
• Talk about SQL tuning on SQL details
• Show group bys with ASH by various axes
• Show examples of setting client_id, service,
  turning on
• More test cases, what does ADDM find

2
Performance Tuning in Oracle 10g

• Kyle Hailey

3
EM Product Layout for Performance
Database Home Page
Database Performance Page
Drilldowns
SQL
Session
4
EM Pages Layout
5
Buffer Busy Waits Use Case
6
Three Paths
Home Page
Perf Page
ADDM
Top Session
Wait Detail
Top SQL
ADDM Details
SQL Detail
Session Detail
7
ADDM Path
8
Database Home Page
9
ADDM Home
10
ADDM Home
11
ADDM Details
12
ADDM Details
13
Manual Path
14
Database Home Page
15
Database Home Page
16
Database Home Page
17
Performance Page
18
Database Performance Page
19
Database Performance Page highlight
20
Wait Drill Down
21
Wait Drill Down
22
Wait Drill Down
23
Wait Drill Down
24
Wait Drill Down highlight
25
Wait Drill Down Top SQL
26
SQL Details
27
SQL Details
28
New Features

1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc

Part I
Part II
Part III
29
Metrics

1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc

Part I
Part II
Part III
30
Metrics Motivation

• Performance Statistics
• Indicators of Database performance
• Cumulative Counters since DB Start
• Not Much use in raw form

31
Querying Statistics
Select value from vsysstat where namephysical
reads SQLgt / VALUE
--------------- 1,533,787 SQLgt
32
Statistics are Cumulative
vsysstat physical reads
Statistics just keep growing
33
How many Physical Reads/Sec
vsysstat physical reads
1.5M
What is the rate here?
Statistics just keep growing
34
Low IO
vsysstat physical reads
1.5M
30 minutes
IOs
time
35
High IO - same cumulative
vsysstat physical reads
1.5M
IOs
30 minutes
time
36
Need Deltas to get Rates

• Take value at time A
• Take value at time B
• Rate (B-A)/elapsed time

37
Current Methods

• Utlbstat.sql/Utlestat.sql
• Creates tables, inserts, deletes
• Statspack
• needs to be set up
• Customized Scripts
• Take time to write, no standards
• Bit overkill for just a quick statistic rate query

38
Solution Metrics

• Rates are Automated in 10g
• Deltas at set intervals
• 15 second
• 60 second
• Current Rates
• per second
• per transaction
• Ratios and percentages

39
Which Statistics have Metrics?

• Wait Events
• VEVENTMETRIC (60 secs )
• VWAITCLASSMETRIC (60 secs)
• Statistics
• VSESSMETRIC (15/60 secs)
• VSYSMETRIC (15/60 secs)
• Files
• VFILEMETRIC ( 10 minutes)

40
Short Term History What happened in the
Past?
vsysstat physical reads
Low rate Little IO
High rate Lots of IO
41
Solution Metric History Tables

• Last 60 minutes of history

delta
delta
delta
delta
delta
delta
delta
42
Metric History Tables

• In memory
• Statistics
• VSYSMETRIC_HISTORY (15 and 60 seconds)
• File IO
• VFILEMETRIC_HISTORY (10 minutes)
• Waits
• VWAITCLASSMETRIC_HISTORY ( 60 seconds)

43
More Detail for Last 3 minutes
delta
delta
delta
delta
delta
delta
delta
44
Statistics have 15 sec deltas
vsysstat physical reads
3 Minutes 15 second deltas
45
vsysmetric_history

3 minutes of 15 second deltas
60 minutes of 1 minute deltas
Not saved to disk but summary is
46
Long Term History, 7 days

• On Disk
• Statistics
• DBA_HIST_SYSMETRIC_SUMMARY
• DBA_HIST_SYSSTAT (cumulative)
• DBA_HIST_SYSMETRIC_HISTORY (alerts)
• Waits
• WAITCLASSMETRIC_HISTORY (alerts)
• DBA_HIST_SYSTEM_EVENT (cumulative)
• File IO
• DBA_HIST_FILEMETRIC_HISTORY (alerts)
• DBA_HIST_FILESTATXS (cumulative)

47
EM Exposing Metrics
48
In Resume Statistics

• Raw vsysstat
• Now vsysmetric
• 1 Hour vsysmetric_history (in memory)
• 7 Days dba_hist_sysmetric_summary (with AWR)
• There is also vsessmetric

49
Exposed in EM
50
Session Metrics 15 Ses

• Vsessmetric
• BEGIN_TIME
• END_TIME
• INTSIZE_CSEC
• SESSION_ID
• SESSION_SERIAL_NUM
• CPU
• PHYSICAL_READS
• PGA_MEMORY
• HARD_PARSES
• SOFT_PARSES
• PHYSICAL_READ_PCT
• LOGICAL_READ_PCT

51
Sysmetric 15 Secs
Per Sec and Per Transaction
Buffer Cache Hit Ratio Memory Sorts Ratio Execute
Without Parse Ratio Soft Parse Ratio Database CPU
Time Ratio Library Cache Hit Ratio Shared Pool
Free Txns Per Logon
Physical Reads Physical Writes Physical Reads
Direct Redo Generated Logons User Calls Logical
Reads Redo Writes Total Table Scans Full Index
Scans DB Block Gets Consistent Read Gets DB Block
Changes Consistent Read Changes Executions
Per Sec
User Transaction Per Sec
52
Sysmetric 60 Sec
Buffer Cache Hit Ratio Memory Sorts Ratio Redo
Allocation Hit Ratio User Commits Percentage
User Rollbacks Percentage Cursor Cache Hit
Ratio Rows Per Sort Execute Without Parse
Ratio Soft Parse Ratio User Calls Ratio Global
Cache Average CR Get Time Global Cache Average
Current Get Time Global Cache Blocks Corrupted
Global Cache Blocks Lost
Current Logons Count Current Open Cursors
Count User Limit SQL Service Response Time
Database Wait Time Ratio Database CPU Time
Ratio Row Cache Hit Ratio Row Cache Miss Ratio
Library Cache Hit Ratio Library Cache Miss
Ratio Shared Pool Free PGA Cache Hit
Process Limit Session Limit Txns Per Logon
53
Sysmetric 60 Sec rates sec/txn
Per Second and Transaction
Per Sec
Physical Reads Physical Writes Physical Reads
Direct Physical Writes Direct Physical Reads
Direct Lobs Physical Writes Direct Lobs Redo
Generated Logons Open Cursors User Calls
Recursive Calls Logical Reads Redo Writes Long
Table Scans Total Table Scans Full Index Scans
Total Index Scans Total Parse Count Hard Parse
Count Parse Failure Count
Disk Sort Enqueue Timeouts Enqueue Waits
Enqueue Deadlocks Enqueue Requests DB Block
Gets Consistent Read Gets DB Block Changes
Consistent Read Changes CPU Usage CR Blocks
Created CR Undo Records Applied User Rollback
Undo Records Applied Leaf Node Splits Branch
Node Splits PX downgraded 1 to 25 PX
downgraded 25 to 50 PX downgraded 50 to 75 PX
downgraded 75 to 99 .
User Commits User Rollbacks User Transaction
DBWR Checkpoints Background Checkpoints Network
Traffic Volume
Per Transaction
Response Time
54
Stats ER
Statistics
Metrics
vsysmetric BEGIN_TIME END_TIME INTSIZE_CSEC
GROUP_ID METRIC_ID METRIC_NAME VALUE
METRIC_UNIT
vmetricgroup GROUP_ID NAME INTERVAL_SIZE
MAX_INTERVAL
vsysstat STATISTIC NAME CLASS VALUE HASH
vsysmetric_history BEGIN_TIME END_TIME
INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME
VALUE METRIC_UNIT
vsessmetric BEGIN_TIME END_TIME INTSIZE_CSEC
SESSION_ID SESSION_SERIAL_NUM CPU
PHYSICAL_READS PGA_MEMORY HARD_PARSES
SOFT_PARSES PHYSICAL_READ_PCT LOGICAL_READ_PCT
vsessstat STATISTIC NAME CLASS VALUE HASH
vsysmetric_summary BEGIN_TIME END_TIME
INTSIZE_CSEC -gt intsize GROUP_ID METRIC_ID
METRIC_NAME NUM_INTERVAL MAXVAL MINVAL
AVERAGE STANDARD_DEVIATION METRIC_UNIT
vmetricname GROUP_ID GROUP_NAME METRIC_ID
METRIC_NAME METRIC_UNIT
55
In Resume Waits

• Raw vsystem_event
• Now veventmetric (60 seconds)
• 1 Hour -----
• 7 Days dba_hist_system_event (cumulative)

56
In Resume Files

• Raw vfileio
• Now vfilemetric ( 10 Minutes)
• 1 Hour vfilemetric_history ( 1 hour, 7 points)
• 7 Days dba_hist_filemetric_history (alerts
  only)

57
System Statistics View
Stats ( Vstat_name) Waits ( Vevent_name ) Files
Raw Vsysstat vsystem_event vevent_histogram vsystem_wait_class vfileio
Now vmetricgroup vmetricname Vsysmetric veventmetric vwaitclassmetric vfilemetric
1 Hour Vsysmetric_history Vsysmetric_summary vwaitclassmetric_history vfilemetric_history
7 days DBA_HIST_ SYSMETRIC_SUMMARY SYSSTAT (cumulative) SYSMETRIC_HISTORY (alerts) SYSTEM_EVENT(cumulative) WAITCLASSMETRIC_HISTORY (alert) FILESTATXS (cumulative) TEMPSTATXS (cumulative) FILEMETRIC_HISTORY (alert)
58
Metrics Summary

• Current rates are easy and efficient
• Last hour quickly accessible
• History kept for a week

59
Metrics

• VMETRICGROUP
• VMETRICNAME
• VSYSMETRIC
• VSESSMETRIC
• VFILEMETRIC
• VEVENTMETRIC
• VWAITCLASSMETRIC
• VSVCMETRIC
• VSYSMETRIC_HISTORY
• VFILEMETRIC_HISTORY
• VWAITCLASSMETRIC_HISTORY
• VSVCMETRIC_HISTORY
• VSYSMETRIC_SUMMARY

• VMETRIC
• VMETRIC_HISTORY
• Combined view onto the other
• metric tables

60
Metric Group and vmetric
2 is a superset of 3 4 is a superset of 5
1 select from vmetricgroup GID NAME
INTS M_INT GROUP_ID
COUNT() --- -----------------------------------
------ ----- -------- ---------- 0 Event
Metrics 6000 1
0 3 (per event) 1 Event Class Metrics
6000 60 1 4
(per waitclass) 2 System Metrics Long Duration
6000 60 2 114 3 System
Metrics Short Duration 1500 12
3 40 4 Session Metrics Long Duration
6000 60 4 1 (blcoked
user) 5 Session Metrics Short Duration
1500 1 5 8 (per session) 6
Service Metrics 6000 60
6 2 (per service) 7 File Metrics
Long Duration 60000 6 7
6 (per file)
Ie, vsysmetric_history keeps the last hour for
long duration deltas, 60s, and the last 3 minutes
for short duration, 15 second deltas
Max Interval
61
Wait Classes

1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc

Part I
Part II
Part III
62
Wait Classes

• Configuration (20)
• log file size
• Enqueues ST, HW, ITL
• Latch redo copy,shared pool
• Idle (56)
• Network (25)
• System I/O (19)
• Scheduler (6)
• User I/O (12)
• Other (485)

• Administrative (39)
• switch logfile
• rebuild index
• Application (11)
• enqueues
• sqlnet break/reset
• Cluster (113)
• Commit (1)
• Log file Sync
• Concurrency (12)
• Latches cbc, lbc,
• Lib cache locks
• Buffer busy wait

63
Wait Classes in EM
64
Wait Class Tables

• vsystem_wait_class
• cumulative
• vwaitclassmetric
• Current rates
• vwaitclassmetric_history
• Hour of rates (60 second intervals)

65
Waits Metrics
Cumulative
Current deltas
Last Hour
Last 7 days
Wait Classes
dba_hist_ waitclassmetric_history Only gets
populated with alerts
vwaitclassmetric_history BEGIN_TIME END_TIME
INTSIZE_CSEC WAIT_CLASS_ID WAIT_CLASS
NUM_SESS_WAITING TIME_WAITED WAIT_COUNT
vsystem_wait_class WAIT_CLASS_ID WAIT_CLASS
WAIT_CLASS TOTAL_WAITS TIME_WAITED
vwaitclassmetric BEGIN_TIME END_TIME INTSIZE_CSEC
WAIT_CLASS_ID WAIT_CLASS NUM_SESS_WAITING TIME_W
AITED WAIT_COUNT
vevent_name EVENT EVENT_ID NAME PARAMETER1
PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS
WAIT_CLASS
Wait Events
No in memory history
dba_hist_system_event (Cumulative)
vsystem_event EVENT TOTAL_WAITS
TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
TIME_WAITED_MICRO EVENT_ID
66
Time Model

1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc

Part I
Part II
Part III
67
Time Model

• New concept
• DB Time
• Total time for all database calls
• cpu time
• wait time

68
Time Model areas

• Total Time
• CPU
• Elapsed Time
• SQL execution
• Plsql execution
• Java execution
• Connection time

69
Time Model Components
1) background elapsed time      2) background
cpu time1) DB time     2) DB CPU     2)
connection management call elapsed time     2)
sequence load elapsed time     2) sql execute
elapsed time     2) parse time
elapsed           3) hard parse elapsed
time                4) hard parse (sharing
criteria) elapsed time                     5)
hard parse (bind mismatch) elapsed
time           3) failed parse elapsed
time                4) failed parse (out of
shared memory) elapsed time     2) PL/SQL
execution elapsed time     2) inbound PL/SQL rpc
elapsed time     2) PL/SQL compilation elapsed
time     2) Java execution elapsed time
70
Total Time
Total Database Time
71
Total and CPU
DB Wait Time
DB CPU Time
72
Total and CPU and Parse Time
DB Wait Time
DB CPU Time
Parse Elapsed Time
73
Parse Time
Parse Elapsed Time
74
Hard Parse
hard parse elapsed time
Parse time elapsed
75
Hard Parse Sharing Criteria
hard parse (sharing criteria) elapsed time
Parse time elapsed
hard parse elapsed time
76
Hard Parse Bind Mismatch
hard parse (bind mismatch) elapsed time
hard parse (sharing criteria) elapsed time
Parse time elapsed
hard parse elapsed time
77
Time Model Tables

• Current cumulative values
• VSYS_TIME_MODEL
• VSESS_TIME_MODEL
• AWR Snapshots of cumulative values
• DBA_HIST_SYS_TIME_MODEL

78
ASH

1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc

Part I
Part II
Part III
79
ASH

• Active Session History
• History of vsession_wait
• Every Second
• All Active sessions
• In a database call, either
• CPU
• Wait
• Between 1M 128M
• Flushed every 30 minutes or when buffer is full

80
ASH Fields
SQLgt vactive_session_history Name
Null? Type
-----------------------------------------
-------- ---------------------------- SAMPLE_ID
NUMBER
SAMPLE_TIME
TIMESTAMP(3) SESSION_ID
NUMBER SESSION_SERIAL
NUMBER USER_ID
NUMBER SQL_ID

VARCHAR2(13) SQL_CHILD_NUMBER
NUMBER SQL_PLAN_HASH_VALUE
NUMBER SQL_OPCODE
NUMBER
SERVICE_HASH
NUMBER SESSION_TYPE
VARCHAR2(10) SESSION_STATE
VARCHAR2(7)
QC_SESSION_ID
NUMBER QC_INSTANCE_ID
NUMBER EVENT
VARCHAR2(64) EVENT_ID
NUMBER EVENT

NUMBER SEQ
NUMBER P1
NUMBER P2
NUMBER P3
NUMBER
WAIT_TIME
NUMBER TIME_WAITED
NUMBER CURRENT_OBJ
NUMBER CURRENT_FILE
NUMBER
CURRENT_BLOCK
NUMBER PROGRAM
VARCHAR2(48) MODULE
VARCHAR2(48) ACTION

VARCHAR2(32) CLIENT_ID
VARCHAR2(64)
SAMPLE_ID/SAMPLE_TIME EVENT SESSION_STATE
SESSION_ID USER_ID SQL_ID SERVICE_HASH
CURRENT_OBJ CURRENT_FILE CURRENT_BLOCK
PROGRAM MODULE ACTION CLIENT_ID
81
Ash Group bys

• Select sid, count() from vactive_session_history
  where stateCPU and SAMPLE_TIME between
  sysdate ad sysdate (5/2460) group by sid
• Select sid, event, count() from
  vactive_session_history where stateWAIT and
  SAMPLE_TIME between sysdate ad sysdate
  (5/2460) group by sid, event

82
Wait Time vs Time Waited

• SESSION_STATE
• Waiting, on CPU
• Based on WAIT_TIME
• WAIT_TIME
• 0 gt waiting
• gt0 gt CPU (value is time of last wait)
• TIME_WAITED
• Actual time waited for event
• 0 until wait finishes
• Fix up values (no one else can do this)

83
Session Polling View ASH
stats
V Vsession_wait
Metrics
30 Minutes extremely variable Vactive_session_history Polling at 1 second (Vsession_wait_history)
AWR Wrh_active_session_history (1 in 10 values from )
84
ASH ER
7 days (disk)
Half hour
current
10 samples

• wrhactive_session_history
• SNAP_D
• DBIDINSTANCE_NUMBER
• SAMPLE_ID
• SAMPLE_TIME
• SESSION_ID
• SESSION_SERIAL
• USER_ID
• SQL_ID
• SQL_CHILD_NUMBER
• SQL_PLAN_HASH_VALUE
• SQL_OPCODE
• SERVICE_HASH
• SESSION_TYPE
• SESSION_STATE
• QC_SESSION_ID
• QC_INSTANCE_ID
• SEQ
• EVENT_ID

vsession_wait SID SEQ EVENT P1TEXT P1
P1RAW P2TEXT P2 P2RAW P3TEXT P3
P3RAW WAIT_CLASS_ID WAIT_CLASS WAIT_CLASS
WAIT_TIME SECONDS_IN_WAIT STATE
vevent_name EVENT_ID EVENT EVENT_ID NAME
PARAMETER1 PARAMETER2 PARAMETER3
WAIT_CLASS_ID WAIT_CLASS WAIT_CLASS
85
ASH in Wait Drilldown
86
AWR

1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc

Part I
Part II
Part III
87
Automatic Workload Repository

• Statspack on Steroids
• More efficient than Statspack
• Keeps stats every hour
• Stores by default the last 7 days

88
Snapshoting

• BEGINDBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
  ()END
• /

89
DBA_HIST
DBA_HIST_DATABASE_INSTANCE DBA_HIST_SNAPSHOT -
DBA_HIST_SNAP_ERROR - DBA_HIST_BASELINE -
DBA_HIST_WR_CONTROL ? (work load repository ?) -
DBA_HIST_DATAFILE DBA_HIST_FILESTATXS only file
number, no name - DBA_HIST_TEMPFILE DBA_HIST_TEMPS
TATXS only file number DBA_HIST_SQLSTAT group
by parent cursor plus DELTAs was
(statssql_summary) DBA_HIST_SQLTEXT DBA_HIST_SQL
_SUMMARY identify litterals (was
statsql_statistics) DBA_HIST_SQL_PLAN -DBA_HIST_S
QLBIND -DBA_HIST_OPTIMIZER_ENV -DBA_HIST_EVENT_NAM
E DBA_HIST_SYSTEM_EVENT DBA_HIST_BG_EVENT_SUMMARY
sum of backgrounds DBA_HIST_WAITSTAT DBA_HIST_EN
QUEUE_STAT -DBA_HIST_LATCH_NAME DBA_HIST_LATCH DBA
_HIST_LATCH_CHILDREN DBA_HIST_LATCH_PARENT DBA_HIS
T_LATCH_MISSES_SUMMARY summed over parent
latch DBA_HIST_LIBRARYCACHE DBA_HIST_DB_CACHE_ADVI
CE DBA_HIST_BUFFER_POOL_STAT DBA_HIST_ROWCACHE_SUM
MARY summed over rowcache entries DBA_HIST_SGA D
BA_HIST_SGASTAT DBA_HIST_PGASTAT
DBA_HIST_RESOURCE_LIMIT DBA_HIST_SHARED_POOL_ADVIC
E ?DBA_HIST_SQL_WORKAREA_HSTGRM DBA_HIST_PGA_TARGE
T_ADVICE DBA_HIST_INSTANCE_RECOVERY DBA_HIST_JAVA_
POOL_ADVICE DBA_HIST_THREAD - logswitches -DBA_HIS
T_STAT_NAME DBA_HIST_SYSSTAT -DBA_HIST_SYS_TIME_MO
DEL -DBA_HIST_OSSTAT_NAME -DBA_HIST_OSSTAT DBA_HIS
T_PARAMETER_NAME DBA_HIST_PARAMETER DBA_HIST_UNDOS
TAT DBA_HIST_ROLLSTAT DBA_HIST_SEG_STAT DBA_HIST_S
EG_STAT_OBJ -DBA_HIST_METRIC_NAME -DBA_HIST_SYSMET
RIC_HISTORY alert -DBA_HIST_SYSMETRIC_SUMMARY
max, min, avg standard deviation -DBA_HIST_SESSMET
RIC_HISTORY alert -DBA_HIST_FILEMETRIC_HISTORY
alert -DBA_HIST_WAITCLASSMET_HISTORY
alert DBA_HIST_DLM_MISC -DBA_HIST_RCVRY_FILE_DEST_
STAT -DBA_HIST_RMAN_PERFORMANCE -DBA_HIST_ACTIVE_S
ESS_HISTORY every 10th point from
vactive_session_history -DBA_HIST_TABLESPACE_STAT
-DBA_HIST_LOG DBA_HIST_MTTR_TARGET_ADVICE -DBA_HI
ST_TBSPC_SPACE_USAGE - ?
New in 10g
90
ADDM

1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc

Part I
Part II
Part III
91
Automatic Database Diagnostic Monitor

• Every Hour with AWR
• Analyze
• Identify problems
• Proposes solutions

92
ADDM Page
93
ADDM Details
94
ADDM Advice
95
ADDM

• dba_advisor_findings     TASK_NAME, TASK_ID (is
  indexed)
• dba_advisor_recommendations  TASK_ID,FINDING_ID
• dba_advisor_actions          TASK_ID and REC_ID
• dba_advisor_rationale        TASK_ID and REC_ID
• dba_advisor_objects  TASK_ID , OBJECT_ID from
  actions or rationale.

96
ADDM tables
Add Free lists or move to ASSM
Actions
BBW Read write contention on a block
Segment Tuning
objects
Findings
Tables
Recommendations
Rational
SQL
objects
97
ADDM tables
Could be just a message w/o command or rational
Like investigate application logic
Types Application Analysis DB Configuration Host
Configuration SQL Tuning Segment Tuning Schema
Types PROBLEM SYMPTOM INFORMATION
Actions
Findings
Recommendations
Rational
Doesnt use type
ADDM does not use this connection (maybe SQL
Tuning Advisor )
22 types of which ADDM uses 7 SQL DATABASE
OBJECT (Tables, Indexes, ...) TABLESPACE DATABASE
BLOCK DATABASE FILE DATABASE LATCH DATABASE ENQ
objects
98
Miscellaneous

1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc

Part I
Part II
Part III
99
Miscellaneous

• Field naming conventions
• Wait Improvements
• Wait histograms
• Waits in vsession and vsql
• Waits broken out for latches and locks
• Waits show the blocker
• Wait history of last 10 waits
• Services
• Client Id

100
Fields Names

• Names
• Event
• Statistic
• Waitclass
• Ids
• Event
• Statistic
• Waitclass
• Name Hash
• Event_id
• Statistic_id
• Waitclass_id

101
Wait Histograms

• Vevent_histogram
• 1ms to 1 hour buckets
• 23 buckets
• lt 1 ms, lt 2 ms, lt 4 ms, lt 8 ms, ..., lt 222 ms

102
Waits in vsession and vsql

• Vsession exposes all the fields from
  vsession_wait
• SEQ
  NUMBER
• EVENT
  NUMBER
• EVENT
  VARCHAR2(64)
• P1TEXT
  VARCHAR2(64)
• P1
  NUMBER
• P1RAW
  RAW(4)
• P2TEXT
  VARCHAR2(64)
• P2
  NUMBER
• P2RAW
  RAW(4)
• P3TEXT
  VARCHAR2(64)
• P3
  NUMBER
• P3RAW
  RAW(4)
• WAIT_CLASS_ID
  NUMBER
• WAIT_CLASS
  NUMBER
• WAIT_CLASS
  VARCHAR2(64)
• WAIT_TIME
  NUMBER
• SECONDS_IN_WAIT
  NUMBER
• STATE
  VARCHAR2(19)

103
VSQL

• SQL_FULLTEXT
• SQL_ID
• FETCHES
• END_OF_FETCH_COUNT
• DIRECT_WRITES
• APPLICATION_WAIT_TIME
• CONCURRENCY_WAIT_TIME
• CLUSTER_WAIT_TIME
• USER_IO_WAIT_TIME
• PLSQL_EXEC_TIME
• JAVA_EXEC_TIME
• CPU_TIME
• ELAPSED_TIME

104
Wait Types Broken Out

• 800 waits
• Latches broken out
• Ex) Latch library cache latch
• Enqueues broken out
• Ex) Enq HW - contention

105
Waits show Blocking Session

• Vsession. BLOCKING_SESSION -gt can build a wait
  tree

106
Waits History of last 10 waits

• select sid, event, p1,p2,p3 from
  vsession_wait_history
• SID EVENT P1
  P2 P3
• ---------- --------------------------- ----------
  ---------- ----------
• 36 db file sequential read 1
  953 1
• 36 SQLNet message from client 1413697536
  1 0
• 36 SQLNet message from client 1413697536
  1 0
• 36 SQLNet message to client 1413697536
  1 0
• 36 db file sequential read 1
  658 1
• 36 db file sequential read 1
  828 1
• 36 db file sequential read 1
  569 1
• 36 db file sequential read 1
  827 1
• 36 db file sequential read 1
  19199 1
• 36 db file sequential read 1
  29 1

107
Services

• Services is a new way to measure resource usage
  and statistics. A session is associated with a
  services when the session connects to the
  database via the listener.

108
Client Id

• Setting Client ID
• dbms_session.set_identifier
• (client_id)
• Enabling trace for a client ID
• dbms_monitor.client_id_trace_enable
• (client_id, TRUE, FALSE)
• Enabling statistics aggregation by client id
• dbms_monitor.client_id_stat_enable
• (client_id)
• Script to Extract Client Trace
• trcsess

109
Session Dedicated
Mary
Randy
Tim
Sue
Mike
Scott
John
Oracle Sessions
S6
S4
S4
S5
S1
S2
S3
Oracle Database Host
110
Session Dedicated trace
Mary
Randy
Tim
Sue
Mike
Scott
John
SQL_TRACETRUE
Oracle Sessions
S6
S4
S4
S5
S1
S2
S3
Oracle Database Host
111
Session Pooling
Mary
Randy
Tim
Sue
Mike
Scott
John
Set client_id SCOTT
APP Server 1
Oracle Sessions
S6
S7
S4
S4
S5
S1
S2
S3
dbms_monitor.client_id_trace_enable
(client_id, TRUE, FALSE)
Oracle Database Host
112
Session Pooling trace
Mary
Randy
Tim
Sue
Mike
Scott
John
APP Server 1
S1, sql_tracetrue
Oracle Sessions
S6
S7
S4
S4
S5
S1
S2
S3
Oracle Database Host
113
Session Pooling multi app serv
Mary
Randy
Tim
Sue
Mike
Scott
John
APP Server 1
APP Server 2
APP Server 3
Oracle Sessions
S6
S7
S4
S4
S5
S1
S2
S3
Oracle Database Host
114
------ Reference -------------
115
System Statistics View
Stats ( Vstat_name) Waits ( Vevent_name ) Time Model sql
Raw Vsysstat vsystem_event vevent_histogram vsystem_wait_class vsys_time_model Vsql
Now Vsysmetric veventmetric vwaitclassmetric Some goes into sysmetric ASH xkewrtsqlstat
1 Hour Vsysmetric_history Vsysmetric_summary vwaitclassmetric_history ASH
7 days DBA_HIST_ SYSMETRIC_SUMMARY SYSSTAT (cumulative) SYSMETRIC_HISTORY (alerts) SYSTEM_EVENT (cumulative) WAITCLASSMETRIC_HISTORY (alert) SYS_TIME_MODEL (cumulative) SQLSTAT
116
Session Statistics View
stats waits waitclasses Time Model sql
V Vsessstat Vsession_event ---------------- Vsess_time_model ----------------- (some stats possible from ASH)
Metrics Vsessmetric ----------------- ----------------- ----------------- -----------------
Metrics history ----------------- ----------------- ----------------- ----------------- -----------------
AWR WRH_SESSMETRIC_HISTORY (alert only) ----------------- (some stats possible from ASH) ---------------- ----------------- ----------------- (some stats possible from ASH)
117
Vmetric
vmetric BEGIN_TIME END_TIME INTSIZE_CSEC
GROUP_ID ENTITY_ID session id, wait_class_id,
etc ENTITY_SEQUENCE METRIC_ID METRIC_NAME
VALUE METRIC_UNIT
118
ASH
ASH provides two x fixed tables xkewash -
returns a row for every ASH sample taken xash-
returns a row for every active session in every
ASH sample
SELECT / no_merge ordered use_nl(s,a) /
a.inst_id, a.sample_id, a.sample_tim e,
a.session_id, a.session_serial, a.user_id,
a.sql_id, a.sql_child_number, a.sql_plan_hash_val
ue, a.sql_opcode, a.service_hash,
decode(a.session_type, 1,'FORE GROUND',
2,'BACKGROUND', 'UNKNOWN'), decode(a.wait_time,
0, 'WAITING', 'ON CPU') , a.qc_session_id,
a.qc_instance_id, a.seq, a.event, a.p1, a.p2,
a.p3, a.wait_ time, a.time_waited,
a.current_obj, a.current_file,
a.current_block, a.progra m, a.module, a.action,
a.client_id FROM xkewash s, xash a WHERE
s.sample_addr a.sample_addr and
s.sample_id a.sample_id No range scans on x
tables, so full scan xkewash and then use
equality in query on xash
119
X Desc xash
desc xash ADDR
RAW(4) INDX
NUMBER INST_ID
NUMBER
SAMPLE_ADDR
NUMBER SAMPLE_ID
NUMBER SAMPLE_TIME
TIMESTAMP(3) SESSION_ID
NUMBER
SESSION_SERIAL
NUMBER USER_ID
NUMBER SQL_ID
VARCHAR2(13) SQL_CHILD_NUMBER
NUMBER
SQL_PLAN_HASH_VALUE
NUMBER SERVICE_HASH
NUMBER SESSION_TYPE
NUMBER SQL_OPCODE
NUMBER QC_SESSION_ID
NUMBER
QC_INSTANCE_ID
NUMBER CURRENT_OBJ
NUMBER CURRENT_FILE
NUMBER CURRENT_BLOCK
NUMBER SEQ
NUMBER
EVENT
NUMBER P1
NUMBER P2
NUMBER P3
NUMBER WAIT_TIME
NUMBER
TIME_WAITED
NUMBER PROGRAM
VARCHAR2(48) MODULE
VARCHAR2(48) ACTION

VARCHAR2(32) CLIENT_ID
VARCHAR2(64)
SQLgt desc xkewash ADDR
RAW(4) INDX NUMBER
INST_ID NUMBER SAMPLE_ID
NUMBER SAMPLE_TIME
TIMESTAMP(3) SAMPLE_ADDR
NUMBER SAMPLE_LENGTH NUMBER
ROW_COUNT NUMBER
120
Sql metrics xkewrtsqlstat

• xkewrtsqlstat

121
ASH init.ora

• Statistics_leveltypical
• _active_session_historytrue

122
ASH structure
Insert point
Select reads backwards
x addr
123
ADDM tables