Transcript Storage Operations Management Work Plan

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 John’s 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
Performance Tuning in Oracle
10g
Kyle Hailey
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
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
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
Metrics Motivation
 Performance Statistics
 Indicators of Database performance
 Cumulative Counters since DB Start
 Not Much use in raw form
Querying Statistics
Select value from v$sysstat where
name=‘physical reads’;
SQL> /
VALUE
--------------1,533,787
SQL>
Statistics are Cumulative
v$sysstat
physical reads
Statistics just
keep growing
How many Physical
Reads/Sec
v$sysstat
physical reads
1.5M
What is the rate
here?
Statistics just keep
growing
Low IO
v$sysstat
physical reads
1.5M
30 minutes
IO’s
time
High IO - same cumulative
v$sysstat
physical reads
1.5M
IO’s
30 minutes
time
Need Deltas to get Rates
 Take value at time A
 Take value at time B
 Rate = (B-A)/elapsed time
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
Solution : Metrics
Rates are Automated in 10g
 Deltas at set intervals
–
–
15 second
60 second
 Current Rates
–
–
–
per second
per transaction
Ratios and percentages
Which Statistics have
Metrics?
 Wait Events
–
–
V$EVENTMETRIC (60 secs )
V$WAITCLASSMETRIC (60 secs)
 Statistics
–
–
V$SESSMETRIC (15/60 secs)
V$SYSMETRIC (15/60 secs)
 Files
–
V$FILEMETRIC ( 10 minutes)
Short Term History:
What happened in the
Past?
v$sysstat
physical reads
Low rate
Little IO
High rate
Lots of IO
Solution: Metric History Tables
 Last 60 minutes of history
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
Metric History Tables
In memory
 Statistics
–
V$SYSMETRIC_HISTORY (15 and 60 seconds)
 File IO
–
V$FILEMETRIC_HISTORY (10 minutes)
 Waits
–
V$WAITCLASSMETRIC_HISTORY ( 60 seconds)
More Detail for Last 3
minutes
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
Statistics have 15 sec
deltas
v$sysstat
physical reads
3 Minutes
15 second deltas
v$sysmetric_history
3 minutes of 15 second deltas
60 minutes of 1 minute deltas
Not saved to disk but summary is
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)
EM Exposing Metrics
In Resume: Statistics
•
•
•
•
Raw : v$sysstat
Now : v$sysmetric
1 Hour : v$sysmetric_history (in memory)
7 Days : dba_hist_sysmetric_summary
There is also v$sessmetric
(with AWR)
Exposed in EM
Session Metrics 15 Ses
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
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
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
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
Sysmetric 60 Sec rates sec/txn
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%
.
Stats – ER
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
In Resume: Waits
•
•
•
•
Raw : v$system_event
Now : v$eventmetric (60 seconds)
1 Hour : ----7 Days : dba_hist_system_event (cumulative)
In Resume: Files
•
•
•
•
Raw : v$fileio
Now : v$filemetric ( 10 Minutes)
1 Hour : v$filemetric_history ( 1 hour, 7 points)
7 Days : dba_hist_filemetric_history (alerts only)
System Statistics View
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)
Metrics Summary
 Current rates are easy and efficient
 Last hour quickly accessible
 History kept for a week
Metrics


V$METRICGROUP
V$METRICNAME
V$METRIC
V$METRIC_HISTORY






V$SYSMETRIC
V$SESSMETRIC
V$FILEMETRIC
V$EVENTMETRIC
V$WAITCLASSMETRIC
V$SVCMETRIC




V$SYSMETRIC_HISTORY
V$FILEMETRIC_HISTORY
V$WAITCLASSMETRIC_HISTORY
V$SVCMETRIC_HISTORY

V$SYSMETRIC_SUMMARY
Combined view onto the other
metric tables
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
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




Active Session History
History of v$session_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
ASH Fields
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
SQL> v$active_session_history
Name
Null?
----------------------------------------- -------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
EVENT
EVENT_ID
EVENT#
SEQ#
P1
P2
P3
WAIT_TIME
TIME_WAITED
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
PROGRAM
MODULE
ACTION
CLIENT_ID
Type
---------------------------NUMBER
TIMESTAMP(3)
NUMBER
NUMBER
NUMBER
VARCHAR2(13)
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(10)
VARCHAR2(7)
NUMBER
NUMBER
VARCHAR2(64)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(48)
VARCHAR2(48)
VARCHAR2(32)
VARCHAR2(64)
Ash Group bys
 Select sid, count(*) from
v$active_session_history where state=‘CPU’
and SAMPLE_TIME between sysdate ad sysdate –
(5/24*60) group by sid;
 Select sid, event, count(*) from
v$active_session_history where state=‘WAIT’
and SAMPLE_TIME between sysdate ad sysdate –
(5/24*60) group by sid, event;
Wait Time vs Time Waited
 SESSION_STATE
–
–
Waiting, on CPU
Based on WAIT_TIME
 WAIT_TIME
–
–
0 => waiting
>0 => 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)
Session Polling View : ASH
stats
V$
V$session_wait
Metrics
30 Minutes *
V$active_session_history *
*extremely
Polling at 1 second
variable
AWR
(V$session_wait_history)
Wrh$_active_session_history
(1 in 10 values from “”) *
ASH ER
current
7 days (disk)
Half hour
10 samples
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#
EVENT
SAMPLE_ID
SNAP_D
SAMPLE_TIME
DBID
INSTANCE_NUMBER
SESSION_ID
P1TEXT
SESSION_SERIAL#
P1
USER_ID
P2TEXT
SQL_ID
P2
SQL_CHILD_NUMBER
P3TEXT
P3
WAIT_TIME
SQL_PLAN_HASH_VALUE
SQL_OPCODE
SERVICE_HASH
SESSION_TYPE
SESSION_STATE
QC_SESSION_ID
QC_INSTANCE_ID
SEQ#
v$event_name
wrh$active_session_history
EVENT#
P1
EVENT_ID
P2
EVENT#
P3
EVENT_ID
WAIT_TIME
NAME
TIME_WAITED
PARAMETER1
CURRENT_OBJ#
PARAMETER2
CURRENT_FILE#
PARAMETER3
CURRENT_BLOCK#
WAIT_CLASS_ID
PROGRAM
WAIT_CLASS#
MODULE
WAIT_CLASS
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
ASH in Wait Drilldown
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





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
ADDM 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
Scott
John
Sue
Randy
Mike
Mary
Tim
Set client_id =
SCOTT
APP
Server
1
Oracle
Sessions
S1
S2
S3
S4
S4
Oracle Database Host
S5
S6
S7
dbms_monitor.client_id_trace_enable
(client_id, TRUE, FALSE);
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 multi app serv
Scott
John
Sue
APP
Server
3
APP
Server
2
APP
Server
1
Oracle
Sessions
S1
S2
S3
S4
Mary
Tim
Randy
Mike
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