DB-Time-based Oracle Performance Tuning: Theory and Practice

1
(No Transcript)
2
DB-Time-based Oracle Performance Tuning Theory
and Practice
RMOUG Feb 2008

• Graham Wood, Uri Shaft, John Beresniewicz
• Oracle Corporation

3
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
Oracles products remains at the sole discretion
of Oracle.
4
Agenda

• Brief History of Oracle Performance Tuning
  Methods
• DB Time What is it?
• DB Time Data Sources
• DB Time Method

5
Oracle Tuning Methods

• Prehistory
• Debug code
• Dark Ages
• Counters/Ratios
• BSTAT/ESTAT
• SQLTrace
• Renaissance
• Increased instrumentation inc. Wait Events
• Move from counters to timers
• STATSPACK

6
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

7
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!

8
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

9
The DB Time Method

• Supports multiple scopes for diagnosis
• Database
• Instance
• Session
• Client id
• Module/Action
• SQL ID
• More inclusive, less intrusive

10
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.
11
A Single Session
Single session with Database Black Box server
12
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
13
(No Transcript)
14
Active sessions

• Foreground sessions in a database call
• Backgrounds are also interesting
• Either on CPU, waiting for IO, or waiting (not
  idle)
• VACTIVE_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.
15
Multiple Sessions
DB Time Sum of DB Time Over All Sessions
Avg. Active Sessions Sum of Avg. Activity Over
All Sessions
User 1
User 2
User 3
User n
TIME
16
The Basic Relationship
Database Time
Avg. Active Sessions
Wall Clock (Elapsed) Time
Browse Books
Read Reviews For One Book
Checkout
Add to Cart
time spent in database
TIME
17
(No Transcript)
18
Breaking down DB Time (example)
Sessions do different database things at
different times
User 1
User 2
User 3
User n
TIME
19
Breaking down DB Time (example)
Maybe I should investigate other wait time?
CPU
I/O
Other Waits
User 1
User 2
User 3
User n
TIME
20
Database time (DB Time)

• Database time vs Wall clock time
• Database time vs Response time

21
System load and DB time

• More users
• gt More calls
• gt DB time increases
• Larger transactions
• gt Longer calls
• gt DB time increases

DB time increases as system load increases.
22
System performance and DB time

• IO performance degrades
• gt IO time increases
• gt DB time increases
• Application performance degrades
• gt Wait time increases
• gt DB time increases

DB time increases as system performance degrades.
23
System performance and DB time
24
Where to find DB time?

• VSYS_TIME_MODEL
• STAT_NAME DB time
• Accumulated value over entire instance
• VWAITCLASSMETRIC_HISTORY
• AVERAGE_WAITER_COUNT
• It is precisely Average Active Sessions
• VSYSMETRIC_HISTORY
• Database Time Per Second, CPU Usage Per Sec
• Units are Centi-seconds per second
• Value is 100 x Average Active Sessions

25
Where to find DB time?

• VSQL
• ELAPSED_TIME
• Also wait class times
• VACTIVE_SESSION_HISTORY
• Sample per second
• Count time

26
Active Session History (ASH)

• Persisted samples of active session information
• Sessions contributing to DB time at time of
  sampling
• One-second sampling interval is a great default
• Allows simplified AAS computations
• DB time and Average active sessions can be
  computed by aggregating ASH samples

27
Estimating DB time with ASH
28
Integral approximation using ASH
29
EM Top Activity page

• ASH-estimated DB time by wait class
• Aggregated over 15 second intervals

30
Sampled vs. cumulative DB time
31
Where is DB time used?

• ADDM
• AWR and AWR compare periods reports
• EM Performance page and drill downs
• ASH report
• Server-generated Alerts

32
(No Transcript)
33
Average active sessions
Average active sessions is the rate of change of
DB time over time.

• Time-normalized DB time
• Full-time equivalent sessions
• Not whole sessions
• How many full-time virtual sessionsto do the
  work?
• Comparable
• Across systems
• Across time periods

34
What are the units?

• Time / time unitless?
• DB time accumulates in micro- or centi-seconds
• Time-normalized metrics are per second of elapsed
• Centi-seconds (foreground time) per second
  (elapsed)
• Centi-users per second
• User seconds per elapsed second (normalize time
  units)
• Active session seconds per second
• Active sessions

35
EM Performance page

• Cumulative DB time by wait class
• vwaitclassmetric_history and vsysmetric_history
• 1 minute intervals

36
Percent Activity
activity DB time 100 / elapsed time Used
for individual sessions
37
Percent Activity
38
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

39
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?

40
The DB Time Method

1. Scope
2. Set Goal
3. Investigate DB time distribution
4. Identify the largest potential for improvement
5. Modify system
6. Evaluate against Goal

41
The DB Time Method

• Scope
• What is the problem?
• Business Requirements
• Resource capacity
• Resource contention
• System wide or individual Business Function

42
The DB Time Method

• Set Goal
• Quantitative
• Establishes the STOP TUNING criteria
• Should be business driven for applications (X per
  day)

43
The DB Time Method

• Investigate DB time distribution
• Identify major contributors to DB time at the
  selected scope
• System scope
• VSYS_TIME_MODEL
• VACTIVE_SESSION_HISTORY
• VSQL
• Identify high load service, sessions and SQL
• Identify resource constraints or contention

44
The DB Time Method

• Investigate DB time distribution
• Session scope
• VSESS_TIME_MODEL
• VACTIVE_SESSION_HISTORY
• Identify if database is the problem
• Identify high load SQL
• Identify application efficiency issues
• Identify resource constraints or contention

45
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

46
The DB Time Method

• Need to have examples here
• Instance level and SQL level maybe?

47
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

48
(No Transcript)
49
(No Transcript)
50
(No Transcript)
51
(No Transcript)
52
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

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

54
(No Transcript)