Performance Tuning in Oracle 10g Feel the Power

1
Performance Tuning in Oracle 10gFeel the Power !

• Kyle Hailey
• Kyle.hailey_at_oracle.com
• http//oraperf.sourceforge.net

2
New Features

• Metrics
• Wait Classes
• Time Model
• ASH
• AWR ( DBA_HIST_ )
• ADDM
• Misc

3
Metrics a new 10g feature to make our lives
easier

• Metrics
• Wait Classes
• Time Model
• ASH
• AWR ( DBA_HIST_ )
• ADDM
• Misc

Part I
Part II
Part III
4
Motivation

• What inspired Metrics?
• Why should you care ?

5
Eating your Spinach ?

• How do we find Performance Problems
• With Statistics
• Statistics have always been a Pain
• How do YOU find bottlenecks with statistics?
• YOU DO THE MATH
• This is TEDIOUS
• (and your boss probably doesnt even appreciate
  you for it )

6
First Tedious Step
SQLgt Select value from vsysstat where
namephysical reads VALUE
--------------- 1,533,787 Not much help
Why? Let see
7
This tells you Nothing
GOOD
BAD
1,533,787
30 minutes
IOs
30 minutes
time
time
8
How do You find the Delta?

• Wheres the Beef ?

9
YOU Need to do MATH to find Out

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

10
Current Methods

• Oracle 6 Utlbstat.sql/Utlestat.sql
• Creates tables, inserts, deletes
• Oracle 8 introduced Statspack
• Improvement, but needs to be set up and
  administered (by guess who)
• Another Option
• Write your own Scripts
• Take time to write, no standards
• In Summary
• These options take time whose time?

11
YOUR TIME
12
Solution ! 10g Metrics

• Available Immediately at your fingertips for
  your enjoyment and relaxation,
• Introducing Metrics
• Automated
• Immediate
• Always there
• Time saving (whose time? YOUR time)

13
Metrics dependability at your fingertips

• Pre-Set intervals
• 15 second
• 60 second
• 10 minutes
• 30 minutes
• Current Value for
• Deltas
• Rates
• per second
• per transaction
• Ratios
• Percentages

14
Serious Geek Stuff Our Metric Family

• Wait Events
• VEVENTMETRIC (60 secs )
• VWAITCLASSMETRIC (60 secs)
• Statistics
• VSESSMETRIC (15 secs Deltas)
• VSYSMETRIC (15 and 60 secs deltas)
• Files
• VFILEMETRIC ( 10 minutes)
• SQL (the secret is out)
• xkewrtsqlstat (30 Minutes) not a metric
  cumulates values up to 30 minutes, then snapshots
  it to dba_hist_sqlstat

15
The Solution Table vsysmetric

• SQLgt desc vsysmetric
• BEGIN_TIME
• END_TIME
• INTSIZE_CSEC
• GROUP_ID
• METRIC_ID
• METRIC_NAME
• VALUE
• METRIC_UNIT

16
Now Whats the IO?

• No Calculations, just a simple select
• SQLgt Select VALUE , METRIC_UNIT
• from vsysmetric
• where namePhysical Reads
• /
• (Gives per second and per transaction)

17
Answer at your fingertips
GOOD
BAD
X
3 IO/sec
513 IO/sec
1,533,787
30 minutes
IOs
30 minutes
time
time
18
We solved whats Happening now but

• What if your problem happened 10 minutes ago?
• How do we get History?
• What happened in the past?
• More work, Time and calculations by YOU

19
That was now. What was then?
vsysstat physical reads
Low rate Little IO
High rate Lots of IO
20
Solution! Metric History Tables

• Last 60 minutes of history, in memory

21
Family of Metric History Tables

• At your fingertips
• Statistics
• VSYSMETRIC_HISTORY (60 seconds)
• (including 3 minutes of 15 second history
  as a bonus!)
• File IO
• VFILEMETRIC_HISTORY (10 minutes)
• Waits
• VWAITCLASSMETRIC_HISTORY ( 60 seconds)

22
1 hour of 60 second deltas
delta
delta
delta
delta
delta
delta
delta
23
Thats not all Folks, as a bonus
vsysstat physical reads
3 Minutes 15 second deltas
3 minutes of 15 second deltas ?
24
Both Stored in Same Table vsysmetric_history

3 minutes of 15 second deltas
60 minutes of 1 minute deltas
Not saved to disk but summary is
25
What was IO 30 minutes ago?
Once again the answer is at your fingertips

• SQLgt Select
• VALUE ,
• METRIC_UNIT
• from
• vsysmetric_history
• where
• METRIC_NAME Physical Reads and
• END_TIME lt ( sysdate - (30/(2460))) and
• END_TIME gt ( sysdate - (35/(2460)))
• /

26
How about a 2 days ago?

• Long Term History, 7 days
• Statistics
• DBA_HIST_SYSMETRIC_SUMMARY
• DBA_HIST_SYSMETRIC_HISTORY (alerts)
• DBA_HIST_SYSSTAT (cumulative)
• Waits
• WAITCLASSMETRIC_HISTORY (alerts)
• DBA_HIST_SYSTEM_EVENT (cumulative)
• File IO
• DBA_HIST_FILEMETRIC_HISTORY (alerts)
• DBA_HIST_FILESTATXS (cumulative)
• SQL
• DBA_HIST_SQLSTAT

27
EM Exposing Metrics
28
That was the Introduction to Metrics

• We saw
• Current deltas Metrics Tables
• Hour History Metric History Tables
• Week of History DBA_HIST Tables
• Now lets Look at the Groupings
• Statistics
• Waits
• File I/O

29
Statistics

• Raw vsysstat
• Current Rates vsysmetric 15 60 seconds
• 15 Second
• 60 Second
• Session Stats
• 1 Hour vsysmetric_history (in memory)
• 7 Days dba_hist_sysmetric_summary (with AWR)

30
vsysmetric 15 Secs for 3 minutes
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
31
vsysmetric 60 Sec for an hour
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
32
vsysmetric 60 Sec rates sec/txn for an hour
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
33
vsessmetric Session Metric

• 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

• No History
• Only a one 15 second Delta

34
Stats Family of Tables
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
35
Waits

• Raw vsystem_event
• Current Deltas veventmetric (60 seconds)
• 1 Hour n/a
• 7 Days dba_hist_system_event (cumulative)

36
Desc veventmetric

• SQLgt desc veventmetric
• Name Type
• ---------------------------------- ------
• BEGIN_TIME DATE
• END_TIME DATE
• INTSIZE_CSEC NUMBER
• EVENT NUMBER
• EVENT_ID NUMBER
• NUM_SESS_WAITING NUMBER
• TIME_WAITED NUMBER
• WAIT_COUNT NUMBER

37
Select from veventmetric
SQLgt select en.name name,
num_sess_waiting WAITERS, time_waited,
wait_count from veventmetric
em, vevent_name en where
wait_count gt 0 and en.event em.event
/
38
veventmetric results

• NAME WAITERS
  TIME_WAITED WAIT_COUNT
• ------------------------------ ----------
  ----------- ----------
• pmon timer 1
  5875 20
• process startup 0
  13 1
• rdbms ipc message 7
  41104 168
• control file sequential read 0
  0 10
• control file parallel write 0
  2 20
• log file parallel write 0
  0 1
• SQLNet message to client 0
  0 47
• SQLNet more data to client 0
  1 4
• SQLNet message from client 4
  14721 47
• SQLNet more data from client 0
  0 1
• queue messages 2
  12012 24
• Queue Monitor Wait 1
  3000 1
• Queue Monitor Task Wait 0
  0 1

39
Files

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

40
VFILEMETRIC_HISTORY
SQLgt select BEGIN_TIME, FILE_ID,
PHYSICAL_READS from VFILEMETRIC_HISTORY
BEGIN_TI FILE_ID PHYSICAL_READS --------
---------- -------------- 041216 1
208 041216 2
600 040218 1 600 040218
2 189 035215 1
1922 035215 2
2082 ... For the last hour
41
Metrics in summary

• Current rates automatically calculated
• History of Rates for an hour in memory
• History kept for a week on disk

42
Metrics

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

• VMETRICGROUP
• VMETRICNAME
• Current Values
• VSYSMETRIC
• VSESSMETRIC
• VFILEMETRIC
• VEVENTMETRIC
• VWAITCLASSMETRIC
• VSVCMETRIC
• Last Hour
• VSYSMETRIC_HISTORY
• VFILEMETRIC_HISTORY
• VWAITCLASSMETRIC_HISTORY
• VSVCMETRIC_HISTORY
• VSYSMETRIC_SUMMARY avg, std dev, max, min

• Week of History
• dba_hist_sysmetric_summary
• dba_hist_system_event (cumulative)
• dba_hist_filemetric_history (alerts)

43
Metrics Family of Tables
44
Wait Classes

• Metrics
• Wait Classes
• Time Model
• ASH
• AWR ( DBA_HIST_ )
• ADDM
• Misc

Part I
Part II
Part III
45
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

46
Wait Classes in EM
47
Wait Class Tables

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

48
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
49
Time Model

• Metrics
• Wait Classes
• Time Model
• ASH
• AWR ( DBA_HIST_ )
• ADDM
• Misc

Part I
Part II
Part III
50
Time Model

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

51
Time Model areas

• Total Time
• CPU
• Elapsed Time
• SQL execution
• Plsql execution (sql execute subtracted out)
• Java execution
• Connection time

52
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
53
Total Time
Total Database Time
54
Total and CPU
DB Wait Time
DB CPU Time
55
Total and CPU and Parse Time
DB Wait Time
DB CPU Time
Parse Elapsed Time
56
Parse Time
Parse Elapsed Time
57
Hard Parse
hard parse elapsed time
Parse time elapsed
58
Hard Parse Sharing Criteria
hard parse (sharing criteria) elapsed time
Parse time elapsed
hard parse elapsed time
59
Hard Parse Bind Mismatch
hard parse (bind mismatch) elapsed time
hard parse (sharing criteria) elapsed time
Parse time elapsed
hard parse elapsed time
60
Time Model Tables

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

61
ASH

• Metrics
• Wait Classes
• Time Model
• ASH
• AWR ( DBA_HIST_ )
• ADDM
• Misc

Part I
Part II
Part III
62
ASH A Revolution in Monitoring

• Active Session History
• New 10g
• Every Second it collects data
• 1 hour of history in Memory for immediate access
  at your fingertips
• This hour of data could change your life

63
Its a Revolution and its an Evolution

• Oracle 6 ie the dark ages there was Cache
  Buffer Hit Ratio
• Oracle 7 turned the lights on Wait Events
  hallelujah I can see the light
• Oracle 10g ASH has landed

64
ASH Intelligence for the new
Millennium

• Selectively Collects Data
• More active, more data collected
• Less active, less data collected
• It self adjusts for your needs
• Old methods collect everything
• Costly
• Limits fine Granularity

65
ASH In Memory

• Collects active session data only
• History vsession_wait vsession extras
• Circular Buffer - 1M to 128M (2 of SGA)
• Flushed every hour to disk or when buffer 2/3
  full

66
ASH Sizing Bigger isnt always Better

• Avg row around 150bytes
• 3600 secs in an hour
• ½ Meg per Active Session per hour
• Thats generally over an hour of ASH

67
vactive_session_history
SAMPLE_ID
NUMBER SAMPLE_TIME
TIMESTAMP(3)
When
Session
SESSION_ID
NUMBER SESSION_SERIAL
NUMBER USER_ID
NUMBER SERVICE_HASH
NUMBER SESSION_TYPE
VARCHAR2(10) PROGRAM
VARCHAR2(64) MODULE
VARCHAR2(48) ACTION
VARCHAR2(32) CLIENT_ID
VARCHAR2(64)
State
SESSION_STATE
VARCHAR2(7) WAIT_TIME
NUMBER
Wait
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
NUMBER0
SQL
SQL_ID
VARCHAR2(13) SQL_CHILD_NUMBER
NUMBER SQL_PLAN_HASH_VALUE
NUMBER SQL_OPCODE
NUMBER QC_SESSION_ID
NUMBER QC_INSTANCE_ID
NUMBER
Duration
TIME_WAITED
NUMBER
68
Consumers

• Top Session
• Top User
• Top SQL
• Top Object
• Top Module.Action
• Top Program
• Top Service
• Top Client
• Top Wait

• CPU
• Waits
• Event
• I/O
• File
• Block
• Time

X
69
Top CPU Session

• Top CPU Session in last 5 minutes
• Select
• session_id,
• count()
• from
• vactive_session_history
• where
• session_state ON CPU and
• SAMPLE_TIME gt sysdate (5/(2460))
• group by
• session_id
• order by
• count() desc

70
Results Top CPU Session
SESSION_ID COUNT() ----------
---------- 265 236 264
115 257 52 271
22 276 1
71
ASH in OEM 10g
72
Family of ASH Tables
vsession_wait
vactive_session_history
Vsession_wait_history
wrhactive_session_history
73
ASH Tables
7 days (disk)
hour
current
10 samples
1 in 10

• 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
74
AWR

• Metrics
• Wait Classes
• Time Model
• ASH
• AWR ( DBA_HIST_ )
• ADDM
• Misc

Part I
Part II
Part III
75
Automatic Workload Repository

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

76
Snapshoting

• BEGINDBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
  ()END
• /

77
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
78
ADDM

• Metrics
• Wait Classes
• Time Model
• ASH
• AWR ( DBA_HIST_ )
• ADDM
• Misc

Part I
Part II
Part III
79
Automatic Database Diagnostic Monitor

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

80
ADDM Page
81
ADDM Details
82
ADDM Advice
83
ADDM tables

• 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.

84
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
85
Advisory Framework 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
86
ADDM tables
87
Miscellaneous

• Metrics
• Wait Classes
• Time Model
• ASH
• AWR ( DBA_HIST_ )
• ADDM
• Misc

Part I
Part II
Part III
88
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

89
Fields Names

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

90
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

91
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)

92
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

93
Wait Types Broken Out

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

94
Waits show Blocking Session

• Vsession. BLOCKING_SESSION -gt can build a wait
  tree

95
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

96
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.

97
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

98
Session Dedicated
Mary
Randy
Tim
Sue
Mike
Scott
John
Oracle Sessions
S6
S4
S4
S5
S1
S2
S3
Oracle Database Host
99
Session Dedicated trace
Mary
Randy
Tim
Sue
Mike
Scott
John
SQL_TRACETRUE
Oracle Sessions
S6
S4
S4
S5
S1
S2
S3
Oracle Database Host
100
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
101
Session Pooling
Mary
Randy
Tim
Sue
Mike
Scott
John
Set client_id SCOTT
dbms_monitor.client_id_trace_enable (client_id,
TRUE, FALSE) waits, binds
APP Server 1
Oracle Sessions
S6
S7
S4
S4
S5
S1
S2
S3
Oracle Database Host
102
------ Reference -------------
103
System Statistics View
104
Session Statistics View
105
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
106
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
107
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
108
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
109
Sql metrics xkewrtsqlstat

• xkewrtsqlstat

110
ASH init.ora

• Statistics_leveltypical
• _active_session_historytrue

111
INIT.ORA

• ASH
• _ash_enable false A dynamic parameter will
  turn off ASH sampling, flushing and the V views
  on ASH
• ADDM
• _addm_auto_enable false A dynamic parameter
  to turn off automatic ADDM runs after every AWR
  snapshot
• AWR
• "_swrf_mmon_flush" FALSE AWR
• metrics
• "_swrf_mmon_metrics" FALSE METRICS
• DB Feature Usage
• "_swrf_mmon_dbfus" FALSE DB Feature Usage
• "_swrf_on_disk_enabled" FALSE disable all (on
  disk, including manual) AWR operations

112
EM Product Layout for Performance
Database Home Page
Database Performance Page
Drilldowns
SQL
Session
113
EM Pages Layout
114
Buffer Busy Waits Use Case
115
Three Paths
116
ADDM Path
117
Database Home Page
118
ADDM Home
119
ADDM Home
120
ADDM Details
121
ADDM Details
122
Manual Path
123
Database Home Page
124
Database Home Page
125
Database Home Page
126
Performance Page
127
Database Performance Page
128
Database Performance Page highlight
129
Wait Drill Down
130
Wait Drill Down
131
Wait Drill Down
132
Wait Drill Down
133
Wait Drill Down highlight
134
Wait Drill Down Top SQL
135
SQL Details
136
SQL Details
137
Combined
138
Comparison