Transcript Storage Operations Management Work Plan

Performance Tuning in Oracle
10g
Feel the Power !
Kyle Hailey
[email protected]
http://oraperf.sourceforge.net
New Features
1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc
Part I
Eat your Spinach
(Hold on to the seat of your
pants … )
Part II
Wow – the main course
Part III
Dessert – this is cool
(And that’s not all)
Metrics … a new 10g feature to
make our lives easier
1. Metrics
2. Wait Classes
3. Time Model
4. ASH
5. AWR ( DBA_HIST_ )
6. ADDM
7. Misc
Part I
Part II
Part III
Motivation
 What inspired Metrics?
 Why should you care ?
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
First Tedious Step
SQL> Select value from v$sysstat
where name=‘physical reads’;
VALUE
--------------1,533,787
Not much help …Why? Let see …
This tells you … Nothing
GOOD
BAD
1,533,787
30 minutes
IO’s
30 minutes
time
time
How do You find the Delta?
Where’s the Beef ?
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
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?
YOUR TIME
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)
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
Serious Geek Stuff :
Our Metric Family
 Wait Events
–
–
V$EVENTMETRIC (60 secs )
V$WAITCLASSMETRIC (60 secs)
 Statistics
–
–
V$SESSMETRIC (15 secs Deltas)
V$SYSMETRIC (15 and 60 secs deltas)
 Files
–
V$FILEMETRIC ( 10 minutes)
 SQL (the secret is out)
–
x$kewrtsqlstat (30 Minutes) “not a metric” cumulates values up to
30 minutes, then snapshots it to dba_hist_sqlstat
The Solution Table v$sysmetric
SQL> desc v$sysmetric
BEGIN_TIME
END_TIME
INTSIZE_CSEC
GROUP_ID
METRIC_ID
METRIC_NAME
VALUE
METRIC_UNIT
Now What’s the IO?
No Calculations, just a simple select :
SQL> Select VALUE , METRIC_UNIT
from v$sysmetric
where name=‘Physical Reads’
/
(Gives per second and per transaction)
Answer at your fingertips
GOOD
BAD
3 IO/sec
X
1,533,787
513 IO/sec
30 minutes
IO’s
30 minutes
time
time
We solved what’s 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
That was now. What was then?
v$sysstat
physical reads
Low rate
Little IO
High rate
Lots of IO
Solution!
Metric History Tables
 Last 60 minutes of history, in memory
v$sysstat
physical reads
delta
delta
delta
delta
delta
delta
delta
60 secs
60 secs
60 secs
60 secs
60 secs
60 secs
60 secs
Family of Metric History Tables
At your fingertips :
 Statistics
–
V$SYSMETRIC_HISTORY (60 seconds)
(including 3 minutes of 15 second history as a
bonus!)
 File IO
–
V$FILEMETRIC_HISTORY (10 minutes)
 Waits
–
V$WAITCLASSMETRIC_HISTORY ( 60 seconds)
1 hour of 60 second deltas
v$sysstat
physical reads
delta
delta
delta
delta
delta
delta
delta
60 secs
60 secs
60 secs
60 secs
60 secs
60 secs
60 secs
That’s not all Folks, as a
bonus:
v$sysstat
physical reads
3 Minutes
15 second deltas
3 minutes of 15 second deltas

Both Stored in Same Table v$sysmetric_history
3 minutes of 15 second deltas
60 minutes of 1 minute deltas
Not saved to disk but summary is
What was IO 30 minutes ago?
Once again the answer is at your fingertips
SQL> Select
VALUE ,
METRIC_UNIT
from
v$sysmetric_history
where
METRIC_NAME = ‘Physical Reads’ and
END_TIME < ( sysdate - (30/(24*60))) and
END_TIME > ( sysdate - (35/(24*60)))
/
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
EM Exposing Metrics
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
Statistics
• Raw : v$sysstat
• Current Rates: v$sysmetric 15 & 60 seconds
•
•
•
15 Second
60 Second
Session Stats
• 1 Hour : v$sysmetric_history (in memory)
• 7 Days : dba_hist_sysmetric_summary (with AWR)
v$sysmetric 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
Per Sec
User Transaction Per Sec
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
v$sysmetric 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
hour
Per Second and Transaction
Per Sec
User Commits
User Rollbacks
User Transaction
DBWR Checkpoints
Background Checkpoints
Network Traffic Volume
Per Transaction
Response Time
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%
.
v$sessmetric: Session Metric
V$sessmetric
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
Stats Family of Tables
v$sysmetric
BEGIN_TIME
END_TIME
INTSIZE_CSEC
GROUP_ID
METRIC_ID
METRIC_NAME
VALUE
METRIC_UNIT
v$sysmetric_history
Statistics Metrics
v$metricgroup
GROUP_ID
NAME
v$sysstat
INTERVAL_SIZE
STATISTIC#
NAME
CLASS
VALUE
HASH
MAX_INTERVAL
BEGIN_TIME
BEGIN_TIME
END_TIME
END_TIME
INTSIZE_CSEC
INTSIZE_CSEC
GROUP_ID
SESSION_ID
METRIC_ID
SESSION_SERIAL_NUM
METRIC_NAME
CPU
VALUE
PHYSICAL_READS
METRIC_UNIT
v$sysmetric_summary
BEGIN_TIME
END_TIME
INTSIZE_CSEC -> intsize
GROUP_ID
METRIC_ID
METRIC_NAME
NUM_INTERVAL
MAXVAL
MINVAL
AVERAGE
STANDARD_DEVIATION
METRIC_UNIT
v$sessmetric
PGA_MEMORY
v$sessstat
HARD_PARSES
STATISTIC#
NAME
CLASS
VALUE
HASH
SOFT_PARSES
PHYSICAL_READ_PCT
v$metricname
GROUP_ID
GROUP_NAME
METRIC_ID
METRIC_NAME
METRIC_UNIT
LOGICAL_READ_PCT
Waits
•
•
•
•
Raw : v$system_event
Current Deltas : v$eventmetric (60 seconds)
1 Hour : n/a
7 Days : dba_hist_system_event (cumulative)
Desc v$eventmetric
SQL> desc v$eventmetric
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
Select from
v$eventmetric
SQL> select
en.name name,
num_sess_waiting WAITERS,
time_waited,
wait_count
from
v$eventmetric em,
v$event_name en
where
wait_count > 0
and en.event# = em.event#
/
v$eventmetric 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
SQL*Net message to client
0
0
47
SQL*Net more data to client
0
1
4
SQL*Net message from client
4
14721
47
SQL*Net 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
Files
•
•
•
•
Raw : v$fileio
Current Delta : v$filemetric ( 10 Minutes)
1 Hour : v$filemetric_history ( 1 hour, 7 points)
7 Days : dba_hist_filemetric_history (alerts only)
V$FILEMETRIC_HISTORY
SQL> select
BEGIN_TIME,
FILE_ID,
PHYSICAL_READS
from
V$FILEMETRIC_HISTORY;
BEGIN_TI
FILE_ID PHYSICAL_READS
-------- ---------- -------------04:12:16
1
208
04:12:16
2
600
04:02:18
04:02:18
1
2
600
189
03:52:15
03:52:15
1
2
1922
2082
... For the last hour
Metrics – in summary
 Current rates automatically
calculated
 History of Rates for an hour in
memory
 History kept for a week on disk
Metrics


V$METRICGROUP
V$METRICNAME
V$METRIC
V$METRIC_HISTORY
Current Values






V$SYSMETRIC
V$SESSMETRIC
V$FILEMETRIC
V$EVENTMETRIC
V$WAITCLASSMETRIC
V$SVCMETRIC
Combined view onto the other
metric tables
Week of History



dba_hist_sysmetric_summary
dba_hist_system_event (cumulative)
dba_hist_filemetric_history (alerts)
Last Hour




V$SYSMETRIC_HISTORY
V$FILEMETRIC_HISTORY
V$WAITCLASSMETRIC_HISTORY
V$SVCMETRIC_HISTORY

V$SYSMETRIC_SUMMARY – avg, std dev, max,
Metrics Family of Tables
Stats ( V$stat_name)
Waits ( V$event_name )
Files
Raw
V$sysstat
v$system_event
v$event_histogram
v$system_wait_class
v$fileio
Now
V$sysmetric
v$eventmetric
v$waitclassmetric
v$filemetric
V$sysmetric_history
V$sysmetric_summary
v$waitclassmetric_history
v$filemetric_history
SYSMETRIC_SUMMARY
SYSSTAT (cumulative)
SYSTEM_EVENT(cumulative)
WAITCLASSMETRIC_HISTORY (alert)
FILESTATXS (cumulative)
v$metricgrou
p
v$metricname
1 Hour
7 days
DBA_HIST_*
SYSMETRIC_HISTORY (alerts)
TEMPSTATXS (cumulative)
FILEMETRIC_HISTORY (alert)
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
Wait Classes
 Administrative (39)  Configuration (20)
–
–
 Application (11)
–
–
enqueues
sqlnet break/reset
 Cluster (113)
 Commit (1)
–
Log file Sync
 Concurrency (12)
–
–
–
–
switch logfile
rebuild index
Latches: cbc, lbc,
Lib cache locks
Buffer busy wait
–
–






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)
Wait Classes in EM
Wait Class Tables
 v$system_wait_class
–
cumulative
 v$waitclassmetric
–
Current rates
 v$waitclassmetric_history
–
Hour of rates (60 second intervals)
Waits Metrics
Cumulative
Current deltas
Last Hour
v$waitclassmetric
v$waitclassmetric_history
BEGIN_TIME
BEGIN_TIME
END_TIME
END_TIME
INTSIZE_CSEC
INTSIZE_CSEC
WAIT_CLASS_ID
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS#
NUM_SESS_WAITING
NUM_SESS_WAITING
TIME_WAITED
TIME_WAITED
WAIT_COUNT
WAIT_COUNT
Last 7 days
Wait Classes
v$system_wait_class
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
TOTAL_WAITS
TIME_WAITED
v$event_name
EVENT#
dba_hist_
waitclassmetric_history
Only gets populated with alerts
EVENT_ID
NAME
PARAMETER1
PARAMETER2
PARAMETER3
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
Wait Events
v$system_event
EVENT
TOTAL_WAITS
TOTAL_TIMEOUTS
TIME_WAITED
AVERAGE_WAIT
TIME_WAITED_MICRO
EVENT_ID
v$eventmetric
BEGIN_TIME
END_TIME
INTSIZE_CSEC
EVENT_ID
NUM_SESS_WAITING
TIME_WAITED
WAIT_COUNT
dba_hist_system_event
(Cumulative)
No in memory history
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
Time Model
New concept
• DB Time
Total time for all database calls
• cpu time
• wait time
Time Model areas







Total Time
CPU
Elapsed Time
SQL execution
Plsql execution (sql execute subtracted out)
Java execution
Connection time
Time Model Components
1) background elapsed time
2) background cpu time
1) 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
Total Time
Total Database
Time
Total and CPU
=~ DB Wait
Time
DB CPU
Time
Total and CPU and Parse
Time
DB Wait
Time
DB CPU
Time
Parse
Elapsed
Time
Parse Time
Parse
Elapsed
Time
Hard Parse
Parse
time
elapsed
hard
parse
elapse
d time
Hard Parse Sharing Criteria
Parse
time
elapsed
hard
parse
elapse
d time
hard
parse
(sharing
criteria)
elapsed
time
Hard Parse Bind Mismatch
Parse
time
elapsed
hard
parse
hard (sharing
parse criteria)
elapse elapsed
d time
time
hard
parse
(bind
mismat
ch)
elapsed
time
Time Model Tables
Current cumulative values
 V$SYS_TIME_MODEL
 V$SESS_TIME_MODEL
AWR Snapshots of cumulative values
 DBA_HIST_SYS_TIME_MODEL
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
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
It’s a Revolution and it’s 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
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
ASH – In Memory


•
•
Collects active session data only
History v$session_wait + v$session + extras
Circular Buffer - 1M to 128M (~2% of SGA)
Flushed every hour to disk or when buffer 2/3
full
ASH Sizing …
Bigger isn’t always Better




Avg row around 150bytes
3600 secs in an hour
~ ½ Meg per Active Session per hour
That’s generally over an hour of ASH
v$active_session_history
SAMPLE_ID
SAMPLE_TIME
NUMBER
TIMESTAMP(3)
SESSION_ID
SESSION_SERIAL#
USER_ID
SERVICE_HASH
SESSION_TYPE
PROGRAM
MODULE
ACTION
CLIENT_ID
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(10)
VARCHAR2(64)
VARCHAR2(48)
VARCHAR2(32)
VARCHAR2(64)
SESSION_STATE
WAIT_TIME
EVENT
EVENT_ID
EVENT#
SEQ#
P1
P2
P3
WAIT_TIME
TIME_WAITED
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
VARCHAR2(7)
NUMBER
VARCHAR2(64)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER0
State
Wait
SQL_ID
SQL_CHILD_NUMBER
SQL_PLAN_HASH_VALUE
SQL_OPCODE
QC_SESSION_ID
QC_INSTANCE_ID
VARCHAR2(13)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
SQL
TIME_WAITED
NUMBER
When
Session
Duration
Consumers









Top Session
Top User
Top SQL
Top Object
Top Module.Action
Top Program
Top Service
Top Client
Top Wait
X
• CPU
• Waits
• Event
• I/O
• File
• Block
• Time
Top CPU Session
Top CPU Session in last 5 minutes
Select
session_id,
count(*)
from
v$active_session_history
where
session_state= ‘ON CPU‘ and
SAMPLE_TIME > sysdate – (5/(24*60))
group by
session_id
order by
count(*) desc;
Results Top CPU Session
SESSION_ID
COUNT(*)
---------- ---------265
236
264
115
257
52
271
22
276
1
ASH in OEM 10g
Family of ASH Tables
v$session_wait
v$active_session_history
V$session_wait_history
wrh$active_session_history
ASH Tables
current
v$session_wait v$session_wait_history v$active_session_history
SID
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
SEQ#
EVENT#
SAMPLE_TIME
DBID
INSTANCE_NUMBER
P1TEXT
SESSION_SERIAL#
P1
USER_ID
P2TEXT
SQL_ID
P2
SQL_CHILD_NUMBER
WAIT_TIME
SQL_PLAN_HASH_VALUE
SQL_OPCODE
SERVICE_HASH
SESSION_TYPE
SESSION_STATE
QC_SESSION_ID
QC_INSTANCE_ID
SEQ#
EVENT#
P1
P2
v$event_name
P3
WAIT_TIME
EVENT_ID
TIME_WAITED
EVENT#
CURRENT_OBJ#
EVENT_ID
CURRENT_FILE#
NAME
CURRENT_BLOCK#
PARAMETER1
PARAMETER2
PARAMETER3
WAIT_CLASS_ID
WAIT_CLASS#
wrh$active_session_history
SNAP_D
EVENT
P3
1 in 10
SAMPLE_ID
SESSION_ID
P3TEXT
7 days (disk)
hour
10 samples
PROGRAM
MODULE
ACTION
CLIENT_ID
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
P1
P2
P3
WAIT_TIME
TIME_WAITED
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
PROGRAM
MODULE
ACTION
CLIENT_ID
WAIT_CLASS
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
Automatic Workload
Repository




Statspack on Steroids
More efficient than Statspack
Keeps stats every hour
Stores by default the last 7 days
Snapshoting
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
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_TEMPSTATXS – onl;y file number
+DBA_HIST_SQLSTAT group by parent cursor plus DELTAs was (stats$sql_summary)
DBA_HIST_SQLTEXT
+DBA_HIST_SQL_SUMMARY – identify litterals (was stat$sql_statistics)
DBA_HIST_SQL_PLAN
-DBA_HIST_SQLBIND
-DBA_HIST_OPTIMIZER_ENV
-DBA_HIST_EVENT_NAME
DBA_HIST_SYSTEM_EVENT
DBA_HIST_BG_EVENT_SUMMARY – sum of backgrounds
DBA_HIST_WAITSTAT
DBA_HIST_ENQUEUE_STAT
-DBA_HIST_LATCH_NAME
DBA_HIST_LATCH
DBA_HIST_LATCH_CHILDREN
DBA_HIST_LATCH_PARENT
DBA_HIST_LATCH_MISSES_SUMMARY – summed over parent latch
DBA_HIST_LIBRARYCACHE
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_BUFFER_POOL_STAT
DBA_HIST_ROWCACHE_SUMMARY – summed over rowcache entries
DBA_HIST_SGA
DBA_HIST_SGASTAT
DBA_HIST_PGASTAT
New in 10g
DBA_HIST_RESOURCE_LIMIT
DBA_HIST_SHARED_POOL_ADVICE
?DBA_HIST_SQL_WORKAREA_HSTGRM
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_INSTANCE_RECOVERY
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_THREAD - logswitches
-DBA_HIST_STAT_NAME
DBA_HIST_SYSSTAT
-DBA_HIST_SYS_TIME_MODEL
-DBA_HIST_OSSTAT_NAME
-DBA_HIST_OSSTAT
DBA_HIST_PARAMETER_NAME
DBA_HIST_PARAMETER
DBA_HIST_UNDOSTAT
DBA_HIST_ROLLSTAT
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
-DBA_HIST_METRIC_NAME
-DBA_HIST_SYSMETRIC_HISTORY alert
-DBA_HIST_SYSMETRIC_SUMMARY – max, min, avg standard deviation
-DBA_HIST_SESSMETRIC_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_SESS_HISTORY – every 10th point from v$active_ses
-DBA_HIST_TABLESPACE_STAT
-DBA_HIST_LOG
DBA_HIST_MTTR_TARGET_ADVICE
-DBA_HIST_TBSPC_SPACE_USAGE - ?
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
Automatic Database Diagnostic
Monitor




Every Hour with AWR
Analyze
Identify problems
Proposes solutions
ADDM Page
ADDM Details
ADDM Advice
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.
ADDM tables
Actions
BBW:
Read & write contention
on a block
Findings
Add Free lists or
move to ASSM
Segment Tuning
objects
Tables
Recommendations
Rational
SQL
objects
Advisory Framework tables
Types:
PROBLEM
SYMPTOM
INFORMATION
Findings
Types:
Application Analysis
DB Configuration
Host Configuration
SQL Tuning
Segment Tuning
Schema
Actions
Could be just a
message w/o
command or
rational
Like investigate
application logic
Recommendations
Rational
Doesn’t
use type
ADDM does not
use this
connection
(maybe SQL
Tuning Advisor )
objects
22 types of which ADDM uses 7:
SQL
DATABASE OBJECT (Tables, Indexes, ...)
TABLESPACE
DATABASE BLOCK
DATABASE FILE
DATABASE LATCH
DATABASE ENQ
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
Miscellaneous
 Field naming conventions
 Wait Improvements
–
–
–
–
–
Wait histograms
Waits in v$session and v$sql
Waits broken out for latches and locks
Waits show the blocker
Wait history of last 10 waits
 Services
 Client Id
Fields Names
 Names
–
–
–
Event
Statistic
Waitclass
 Ids
–
–
–
Event#
Statistic#
Waitclass#
 Name Hash
–
–
–
Event_id
Statistic_id
Waitclass_id
Wait Histograms
V$event_histogram
 1ms to 1 hour buckets
 23 buckets
 < 1 ms, < 2 ms, < 4 ms, < 8 ms, ..., < 2^22 ms
Waits in v$session and v$sql
 V$session exposes all the fields from v$session_wait
SEQ#
EVENT#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
WAIT_TIME
SECONDS_IN_WAIT
STATE
NUMBER
NUMBER
VARCHAR2(64)
VARCHAR2(64)
NUMBER
RAW(4)
VARCHAR2(64)
NUMBER
RAW(4)
VARCHAR2(64)
NUMBER
RAW(4)
NUMBER
NUMBER
VARCHAR2(64)
NUMBER
NUMBER
VARCHAR2(19)
V$SQL













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
Wait Types Broken Out
 800 waits
 Latches broken out
–
Ex) Latch: library cache latch
 Enqueues broken out
–
Ex) Enq: HW - contention
Waits show Blocking Session
V$session. BLOCKING_SESSION -> can build
a wait tree:
Waits History of last 10 waits
 select sid, event, p1,p2,p3 from v$session_wait_history
SID
---------36
36
36
36
36
36
36
36
36
36
EVENT
--------------------------db file sequential read
SQL*Net message from client
SQL*Net message from client
SQL*Net message to client
db file sequential read
db file sequential read
db file sequential read
db file sequential read
db file sequential read
db file sequential read
P1
P2
P3
---------- ---------- ---------1
953
1
1413697536
1
0
1413697536
1
0
1413697536
1
0
1
658
1
1
828
1
1
569
1
1
827
1
1
19199
1
1
29
1
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.
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
Session Dedicated
Scott
John
Sue
Randy
Mike
Oracle
Sessions
S1
S2
S3
S4
S4
Oracle Database Host
S5
Mary
Tim
S6
Session Dedicated trace
Scott
John
Sue
Randy
Mike
Mary
Tim
SQL_TRACE=TRUE
Oracle
Sessions
S1
S2
S3
S4
S4
Oracle Database Host
S5
S6
Session Pooling trace
Scott
John
Sue
Randy
Mike
Mary
Tim
APP
Server
1
S1, sql_trace=true
Oracle
Sessions
S1
S2
S3
S4
S4
Oracle Database Host
S5
S6
S7
Session Pooling
Scott
John
Sue
Randy
Mike
Set client_id =
SCOTT
APP
Server
1
dbms_monitor.client_id_trace_enable
(client_id, TRUE, FALSE);
waits, binds
Oracle
Sessions
S1
S2
S3
S4
Mary
Tim
S4
Oracle Database Host
S5
S6
S7
------ Reference -------------
System Statistics View
Stats ( V$stat_name)
Waits ( V$event_name )
Time Model
sql
Raw
V$sysstat
v$system_event
v$event_histogram
v$system_wait_class
v$sys_time_model
V$sql
Now
V$sysmetric
v$eventmetric
v$waitclassmetric
Some goes into
sysmetric
ASH
x$kewrtsqlstat
1 Hour
7 days
DBA_HIST_*
V$sysmetric_history
V$sysmetric_summary
SYSMETRIC_SUMMARY
SYSSTAT
(cumulative)
SYSMETRIC_HISTORY
(alerts)
v$waitclassmetric_history
SYSTEM_EVENT
(cumulative)
WAITCLASSMETRIC_HISTORY
(alert)
ASH
SYS_TIME_MODEL
(cumulative)
SQLSTAT
Session Statistics View
stats
waits
waitclasses
Time Model
sql
V$
V$sessstat
V$session_event
----------------
V$sess_time_model
*
----------------(some stats
possible
from ASH)
Metrics
V$sessmetric
-----------------
-----------------
-----------------
-----------------
Metrics
-----------------
-----------------
-----------------
-----------------
-----------------
----------------(some stats
possible from
ASH)
----------------
-----------------
----------------(some stats
possible
from ASH)
history
AWR
WRH$_SESSMET
RIC_HISTORY *
(alert only)
V$metric
v$metric
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
ASH
ASH provides two x$ fixed tables
x$kewash - returns a row for every ASH sample taken
x$ash- 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_value, 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 x$kewash s, x$ash a WHERE s.sample_addr
= a.sample_addr and
s.sample_id = a.sample_id
No range scans on x$ tables, so full scan x$kewash and then use equality in query on x$ash
X$ Desc x$ash
SQL> desc x$kewash
ADDR
INDX
INST_ID
SAMPLE_ID
SAMPLE_TIME
SAMPLE_ADDR
SAMPLE_LENGTH
ROW_COUNT
desc x$ash
RAW(4)
NUMBER
NUMBER
NUMBER
TIMESTAMP(3)
NUMBER
NUMBER
NUMBER
ADDR
INDX
INST_ID
SAMPLE_ADDR
SAMPLE_ID
SAMPLE_TIME
SESSION_ID
SESSION_SERIAL#
USER_ID
SQL_ID
SQL_CHILD_NUMBER
SQL_PLAN_HASH_VALUE
SERVICE_HASH
SESSION_TYPE
SQL_OPCODE
QC_SESSION_ID
QC_INSTANCE_ID
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
SEQ#
EVENT#
P1
P2
P3
WAIT_TIME
TIME_WAITED
PROGRAM
MODULE
ACTION
CLIENT_ID
RAW(4)
NUMBER
NUMBER
NUMBER
NUMBER
TIMESTAMP(3)
NUMBER
NUMBER
NUMBER
VARCHAR2(13)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(48)
VARCHAR2(48)
VARCHAR2(32)
VARCHAR2(64)
Sql metrics: x$kewrtsqlstat
 x$kewrtsqlstat
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:
EM Product Layout for Performance
Database Home Page
Database Performance Page
Drilldowns
SQL
Session
EM Pages Layout
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
Buffer Busy Waits Use Case
Three Paths
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
ADDM Path
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
Database Home Page
ADDM Home
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
ADDM Home
ADDM Details
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
ADDM Details
Manual Path
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
Database Home Page
Database Home Page
Database Home Page
Performance Page
Home Page
Perf Page
Top Session
Top SQL
SQL Detail
ADDM
Wait Detail
Session Detail
ADDM Details
Database Performance Page
Database Performance Page
highlight
Wait Drill Down
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
Wait Drill Down
Wait Drill Down
Wait Drill Down
Wait Drill Down highlight
Wait Drill Down – Top SQL
SQL Details
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
SQL Details
Combined
Home Page
Perf Page
Top Session
ADDM
Wait Detail
SQL Detail
Top SQL
Session Detail
ADDM Details
Comparison