Transcript DB Time-based Oracle Performance Tuning: Theory and

<Insert Picture Here>
DB-Time-based Oracle Performance Tuning: Theory and Practice
Graham Wood, Uri Shaft, John Beresniewicz
Oracle Corporation
RMOUG
Feb 2008
The following is intended to outline our
general product direction. It is intended for
information purposes only, and may not be
incorporated into any contract. It is not a
commitment to deliver any material, code, or
functionality, and should not be relied upon in
making purchasing decisions.
The development, release, and timing of any
features or functionality described for
Oracle’s products remains at the sole
discretion of Oracle.
Agenda
•
•
•
•
Brief History of Oracle Performance Tuning Methods
DB Time: What is it?
DB Time: Data Sources
DB Time Method
Oracle Tuning Methods
• Prehistory
• Debug code
• Dark Ages
• Counters/Ratios
• BSTAT/ESTAT
• SQL*Trace
• Renaissance
• Increased instrumentation inc. Wait Events
• Move from counters to timers
• STATSPACK
Oracle Tuning Methods
• More Recent Methods – Time-based methods
• YAPP
• Instance tuning - instance statistics
• Non intrusive
• Always on
• Method R
• Session tuning – sql trace based
• Tightly scoped
• Must be highly selective
• Modern advances
• DB Time Tuning
• Instrumentation improvements
• ASH, AWR,ADDM
Why Do We Care About Time?
Performance Is Always About Time
• Human time is critical to the enterprise
• System time includes human and IT resource time to
accomplish business goals
• System performance affects business goals
• “Time is money.”
• Performance improvement usually means doing
things faster
• Method: find where system time is spent – reduce it!
The DB Time Method
• Uses combination of cumulative and sampled DB Time
• ‘Always on’ data only
• Combines best of current methods
• Low intrusion
• Detailed data
• No scope necessary for collections
• No requirement to reproduce problem
• Works for concurrency problems such as locking
The DB Time Method
• Supports multiple scopes for diagnosis
•
•
•
•
•
•
Database
Instance
Session
Client id
Module/Action
SQL ID
• More inclusive, less intrusive
Database time (DB Time)
• Time spent in database calls by foreground sessions
• Includes CPU time, IO time and wait time
• Excludes idle wait time
• The lingua franca for Oracle performance analysis
Database time is total time spent by user
processes either actively working or actively
waiting in a database call.
A Single Session
Single session with Database Black Box server
Browse
Read Reviews
Books
For One Book
Add to Cart
= time spent in database
Checkout
TIME
Fundamental concepts
Active Session =
Session currently spending time in a database call
Database Time (DB Time) =
Total time session spent in all database calls
Average Activity of the Session (% Activity) =
The ratio of time active to total wall clock time
Browse
Read Reviews
Books
For One Book
Add to Cart
= time spent in database
Checkout
TIME
Active sessions
• Foreground sessions in a database call
• Backgrounds are also interesting
• Either on CPU, waiting for IO, or waiting (not idle)
• V$ACTIVE_SESSION_HISTORY is a collection of
timed regular samples of active session attributes
Active sessions are foreground sessions
contributing to DB time in any given moment.
Multiple Sessions
DB Time = Sum of DB Time Over All Sessions
Avg. Active Sessions = Sum of Avg. Activity Over All Sessions
At time t we have 2 active sessions
User 1
User 2
User 3
User n
= time spent in database
t
TIME
The Basic Relationship
Database Time
Avg. Active Sessions =
Wall Clock (Elapsed) Time
Browse
Books
Read Reviews
For One Book
Add to Cart
= time spent in database
Checkout
TIME
DB time
System load and DB time
• More users
• => More calls
• => DB time increases
• Larger transactions
• => Longer calls
• => DB time increases
DB time increases as system load increases.
System performance and DB time
• IO performance degrades
• => IO time increases
• => DB time increases
• Application performance degrades
• => Wait time increases
• => DB time increases
DB time increases as system performance degrades.
System performance and DB time
Where to find DB time?
• V$SYS_TIME_MODEL
• STAT_NAME = ‘DB time’
• Accumulated value over entire instance
• V$WAITCLASSMETRIC_HISTORY
• AVERAGE_WAITER_COUNT
• It is precisely Average Active Sessions
• V$SYSMETRIC_HISTORY
• “Database Time Per Second”, “CPU Usage Per Sec”
• Units are Centi-seconds per second
• Value is 100 x Average Active Sessions
Where to find DB time?
• V$SQL
• ELAPSED_TIME
• Also wait class times
• V$ACTIVE_SESSION_HISTORY
• Sample per second
• Count = time
Estimating DB time with ASH
Active sessions
DB time is area
under curve
ASH sample count is value
of active sessions function
at sample times
t = 1 sec
DB Time
t0
time
t1
Integral approximation using ASH
t1
DB time   ActiveSessions (t ) dt
t0
 n

 lim  ActiveSessions (t k ) * t 
t  0
 k 1


sampletime t1
 ASHsamples *1
sampletime t 0
(where t  1 second)
EM Top Activity page
• ASH-estimated DB time by wait class
• Aggregated over 15 second intervals
Sampled vs. cumulative DB time
Where is DB time used?
• ADDM
• AWR and AWR compare periods reports
• EM Performance page and drill downs
• ASH report
• Server-generated Alerts
EM Performance page
• Cumulative DB time by wait class
• v$waitclassmetric_history and v$sysmetric_history
• 1 minute intervals
DB Time Tuning
• DB Time can be aggregated at multiple levels:
•
•
•
•
Database / instance
Service / module / action
Session / user / client id
SQL id / rowsource
• Performance improvement for Oracle database
means doing the same work in less DB Time
Performance Problem Resolution 101
•
Discover the problem:
•
•
•
User phone call or other complaint
Metric threshold alert or system monitoring
Scope the problem:
•
•
•
How widespread is it?
How severe is it?
In other words: Who or what is wasting DB Time and how
much is being wasted?
•
Diagnose the problem
•
Scope the solution
•
How much of the pain can be relieved?
The DB Time Method
1.
2.
3.
4.
5.
6.
Scope
Set Goal
Investigate DB time distribution
Identify the largest potential for improvement
Modify system
Evaluate against Goal
The DB Time Method
•
Scope
•
What is the problem?
• Business Requirements
• Resource capacity
• Resource contention
• System wide or individual Business Function
The DB Time Method
•
Set Goal
•
•
•
Quantitative
Establishes the STOP TUNING criteria
Should be business driven for applications (X per day)
The DB Time Method
•
Investigate DB time distribution
•
•
Identify major contributors to DB time at the selected scope
System scope
• V$SYS_TIME_MODEL
• V$ACTIVE_SESSION_HISTORY
• V$SQL
• Identify high load service, sessions and SQL
• Identify resource constraints or contention
The DB Time Method
•
Investigate DB time distribution
•
Session scope
• V$SESS_TIME_MODEL
• V$ACTIVE_SESSION_HISTORY
• Identify if database is the problem
• Identify high load SQL
• Identify application efficiency issues
• Identify resource constraints or contention
The DB Time Method
•
Identify the largest potential for improvement
•
•
What can be changed that will produce the greatest
reduction in ‘scoped’ DB time?
• Parameters
• System
• Application
• SQL
• Design
Modify system
The DB Time Method
•
Evaluate against Goal
•
•
•
Did our changes to the system achieve our goal?
If not return to step 3 and repeat
If we have reached our goal STOP
Summary
DB Time is the fundamental performance metric
The DB Time Method uses many different sources of DB
time within the database to allow many different scopes of
performance tuning
Time based diagnosis removes ‘value judgments’ from
performance analysis
New In 11g – Enhancements for RAC
• ADDM has “Database” analysis mode
• New AWR “Database” report
• EM Performance screens for RAC enhanced to
support new server capabilities.