db time performance tuning - theory and practice
TRANSCRIPT
![Page 1: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/1.jpg)
DB Time Performance Tuning: Theory and Practice John Beresniewicz, Graham WoodOracle Corporation
![Page 2: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/2.jpg)
Agenda
• DB Time and Average Active Sessions• Time Model• Active Session History (ASH)• Performance Tuning• EM Diagnostic and Tuning Packs• Data Visualization
![Page 3: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/3.jpg)
Oracle tuning methods: a history
• Prehistoric (v5)• Debug code
• Dark Ages (v6)• Counters/Ratios• BSTAT/ESTAT• SQL*Trace
• Renaissance (v7/v8)• Introduction of Wait Event instrumentation• Move from counters to timers• STATSPACK
• Modernity (v10)• DB Time Tuning – Tuning using fundamental notion of time spent in database• Multiple scoping levels • Always on, non-intrusive• Built into infrastructure: instrumentation, ASH, AWR, ADDM, EM
![Page 4: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/4.jpg)
<Insert Picture Here>
DB Time and Average Active Sessions
![Page 5: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/5.jpg)
Database time (DB time)
• Time spent by clients in database calls• Foreground session time• Active = on CPU or in-Wait (non-idle)
• Some waits problematic: idle or not?
• Active Session• Session on CPU or in timed active wait (or on run-queue)• Active sessions are contributing to DB time
• DB time is also time spent servicing calls• Database as black-box queuing model server
![Page 6: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/6.jpg)
Single client session with DB black-box server
Browse Books
Read ReviewsFor One Book
Add to Cart Checkout
TIME= time spent in database
Single client session
![Page 7: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/7.jpg)
Active Session = Session currently spending time in a database call
Database Time = Total time session spent in all database calls
Average Activity of session (% Activity) = The ratio of time active to total wall clock time
Browse Books
Read ReviewsFor One Book
Add to Cart Checkout
TIME= time spent in database
Fundamental concepts
![Page 8: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/8.jpg)
![Page 9: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/9.jpg)
= time spent in database
Multiple client sessions
TIME
User 1
User 2
User 3
User n
t
At time t we have 2 active sessions
DB Time = Sum of DB Time over all sessions
Avg. Active Sessions = Sum of Avg. Activity over all sessions
![Page 10: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/10.jpg)
Avg. Active Sessions =Wall Clock (Elapsed) Time
Total Database Time
Charting DB time
TIME
User 3User 2User 1
User n
t0 t1
1234
Active Sessions over time
![Page 11: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/11.jpg)
EM Performance Page
• Active Sessions by wait class over time• Colored area = amount of DB time• “Click on the big stuff”
![Page 12: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/12.jpg)
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.
![Page 13: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/13.jpg)
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 when performance degrades.
![Page 14: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/14.jpg)
Host performance and DB time
• Host is CPU-bound• => foregrounds accumulate active run-queue time
• => wait event times are artificially inflated• => DB time increases
• CPU starvation distorts time counter values• Always tune for CPU first• Be wary of run queue distortion
Tune for CPU before waits when CPU constrained
![Page 15: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/15.jpg)
CPU run-queue and DB time
Db file sequential readRun-queue
On CPU Db file sequential readRun-queue
On CPU
DB time is inflated when host is CPU-bound
User 1
Actual wait time Actual wait time
Recorded wait time Recorded wait time
![Page 16: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/16.jpg)
System performance and DB time
CPU or I/O problem?
![Page 17: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/17.jpg)
Average Active Sessions
• Time-normalized DB time• Time units in numerator and denominator must
synchronize to produce the proper metric
= DB time / elapsed time
![Page 18: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/18.jpg)
Average Active Sessions
• Full-time equivalent sessions • Not whole sessions• How many full-time virtual sessions
to do the work?
• Comparable• Across systems• Across time periods
![Page 19: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/19.jpg)
<Insert Picture Here>
Time Model
![Page 20: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/20.jpg)
Counting in-database time
• DB time and background process time• Sessions post time to counters
• Call completion (9i)• In-call posting: 5 secs (10g), 1 sec (11g)
• Instance: V$SYS_TIME_MODEL• Session: V$SESS_TIME_MODEL• Cursor: V$SQL• Metrics
• V$WAITCLASS_METRIC_HISTORY• V$SYSMETRIC_HISTORY
![Page 21: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/21.jpg)
System and session time models
• V$SYS_TIME_MODEL• Sum of active session time and expired session time• Statistics are “nested” (with some overlap)• “DB time” and “DB CPU” most useful
• V$SESS_TIME_MODEL• Provide insight into session CPU consumption:
• “hard parse elapsed time”• “PL/SQL compilation elapsed time”
• Counting time cleanly in complex call stack difficult
![Page 22: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/22.jpg)
Time model in V$SQL
• ELAPSED_TIME• CPU_TIME
• APPLICATION_WAIT_TIME• CONCURRENCY_WAIT_TIME• CLUSTER_WAIT_TIME• USER_IO_WAIT_TIME
• PLSQL_EXEC_TIME• JAVA_EXEC_TIME
![Page 23: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/23.jpg)
Time model in metrics
• V$SYSMETRIC_HISTORY• 10g: “Database Time Per Second”
• Units = centi-secs/sec (100x Avg. Active Sessions)• 11g: “Average Active Sessions” (new metric, proper units)• “CPU Usage Per Sec”
• V$WAITCLASSMETRIC_HISTORY• Time model stats by wait class for last hour• AVERAGE_WAITER_COUNT ~ Avg Active Sessions• DBTIME_IN_WAIT = percent of DB time in the wait class
![Page 24: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/24.jpg)
Time Model in AWR
• DBA_HIST_SYS_TIME_MODEL• Hourly trends of DB time and average active sessions
• DBA_HIST_WAITCLASSMET_HISTORY• Historical DB time by wait class
• DBA_HIST_SQLSTAT
• DBA_HIST_SYSMETRIC_HISTORY• Avg Active Sessions, Response per Txn, Response per Call• 1-minute metric values persisted over
SYSTEM_MOVING_WINDOW baseline
![Page 25: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/25.jpg)
<Insert Picture Here>
ASH: Active Session History
![Page 26: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/26.jpg)
ASH Components
• V$ACTIVE_SESSION_HISTORY• Sampling-recording of session state objects• MMON task with 1-second sampling rate• Circular memory buffer mechanism
• DBA_HIST_ACTIVE_SESS_HISTORY• Sub-sampling to disk, 1-in-10 samples by default• Persisted (flushed) with AWR snapshots
• Emergency flush if circular buffer overwrites since last snapshot
![Page 27: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/27.jpg)
ASH Math
COUNT(*) = DB Time
GROUP BY ?
![Page 28: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/28.jpg)
ASH Math: COUNT(*)=DB Time
• ASH is a big fact table• Each row represents 1-second of active session time
• V$ACTIVE_SESSION_HISTORY• COUNT(*) = DB time in seconds
• DBA_HIST_ACTIVE_SESS_HISTORY• COUNT(*) * 10.24 = DB time in seconds
![Page 29: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/29.jpg)
ASH Math: Group By ?
• Find skewed dimensions:• User session
• Sid, serial#, user, client_id• Application
• Service, module, action, program,• SQL
• Sql_id, plan_hash, rowsource• Wait events
• Wait class, event, p1, p2, p3• Database
• DBID, instance, service• Parallel query
• QC_sid, instance, sid
![Page 30: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/30.jpg)
Example: DB Time by SQL IDselect sql_id
, count(*) DBTime, round(count(*)*100/sum(count(*))
over (), 2) pctloadfrom v$active_session_history
where sample_time > sysdate - 1/24/60and session_type <> 'BACKGROUND‘
group by sql_idorder by count(*) desc;
SQL_ID DBTIME PCTLOAD ------------- --------- -------6bmxrabnwwsxd 60 63.83 azzsynmz43nrr 8 8.51 28pb73sbwhmm8 5 5.32 58psyvgau23s2 3 3.19 amrq8hk767tuz 2 2.13 2r5qhb3fb63vm 1 1.06 f3919usqp5wj2 1 1.06
![Page 31: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/31.jpg)
ASH Timing operations
• Some important operations are still too frequent and short-lived for timing• No “bind” wait event
• A session-level bit vector is updated in binary fashion before/after an operation• Cheaper than timer call
• The bit vector is sampled into ASH• ASH math allows us to estimate time spent in these
un-timed transient operations
![Page 32: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/32.jpg)
Compare ASH and time model (10g)
select
M.end_time
,(M.value / 100) as Mdbtime
,SUM(DECODE(A.session_type,'FOREGROUND',1,0)) / ((M.end_time - M.begin_time) * 86400 )
as ASHdbtime
,COUNT(1) as ASHcount
from
v$active_session_history A
,v$sysmetric_history M
where
A.sample_time between M.begin_time and M.end_time
and M.metric_name = 'Database Time Per Sec'
and M.group_id = 2
group by
M.end_time,M.begin_time, M.value
order by
M.end_time
/
![Page 33: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/33.jpg)
Comparison results: email DB
0.00
10.00
20.00
30.00
40.00
50.00
60.00
70.00
MDBTIME ASHDBTIME
![Page 34: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/34.jpg)
Comparison results: apps DB
0.00
20.00
40.00
60.00
80.00
100.00
120.00
140.00
160.00
MDBTIME ASHDBTIME
Theory: long operation not incrementally updating time model
![Page 35: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/35.jpg)
Compare ASH with time model
![Page 36: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/36.jpg)
The calculus of DB time
• The number of active sessions at any time is the rate of change of the “DB time function” at that time.
• DB time is the integral of the Active Session function.
∫=1
0
t
tionsActiveSessDBtime
ionsActiveSesstDBtime =δδ /
![Page 37: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/37.jpg)
timet0 t1
Active Sessions and DB Time
Active sessions
Δt = 1 sec
ASH samples = active sessions function values
Counting samples = adding integral estimate bars
DB Time
DB time is area under curve
![Page 38: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/38.jpg)
New in 11.2: session statistics
• Key session counters/deltas are sampled into ASH• PGA allocated• Temp space allocated
• Deltas over time since session last sampled• Time model: DB time, DB CPU• IO: read/write requests/bytes• Interconnect IO bytes
• Used by SQL monitoring
![Page 39: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/39.jpg)
<Insert Picture Here>
DB Time Performance Tuning
![Page 40: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/40.jpg)
Why Do We Care About Time?
• Human time is critical to the enterprise
• Systems performance affects business goals
• “Time is money”
• Performance improvement means doing things faster
Performance is always and only about time
![Page 41: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/41.jpg)
DB Time tuning: short course
Determine where database time is spent, and reduce it!
orjust ask ADDM
![Page 42: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/42.jpg)
DB Time tuning: process
1. Identify performance issue2. Scope the issue3. Set goals4. Data capture (NO OP)5. Investigate DB time distribution
• Identify the largest potential for improvement
6. Modify system to tune for largest gain7. Evaluate against goals
• Repeat from step 4 if goals not met
Performance tuning by removing excess DB time
![Page 43: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/43.jpg)
Investigate DB Time distribution
• Identify uneven distributions of DB time (skew)• => Largest potential improvement within scope
• System scope:• Resource limits – is problem outside the DB?
• Application scope:• Service, module, action• Resource contention (e.g. latches)• SQLID, rowsource
• Session scope:• Long running SQL• Resource contention (e.g. enqueues)
![Page 44: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/44.jpg)
Modify system
• Start with the largest DB time issues first• Address root causes, not symptoms
• Match solution scope to problem scope• Don’t tweak optimizer parameters before tuning SQL
• Proceed iteratively one fix at a time• Concurrent fixes should be orthogonal
• Measure and validate results at each successive step
• Stop when goals are met
![Page 45: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/45.jpg)
DB Time tuning: advantages
• Tunes the one thing that affects users: Time
• Data capture scoping not necessary• ‘Always on’ data collection• No requirement to reproduce problem
• Works for concurrency problems such as locking
• Combines best of current methods• Less intrusive, more inclusive
![Page 46: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/46.jpg)
Method summary
• DB time is the fundamental performance metric
• The method allows DB time analysis at many scopes• Proper scoping of problems and solutions is critical to success
• DB time based diagnosis removes value judgments• Scientific method, not sorcerer’s magic
• Performance improvement means doing the same work in less DB Time
![Page 47: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/47.jpg)
<Insert Picture Here>
Enterprise Manager
![Page 48: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/48.jpg)
EM Performance Page
• Host load chart• CPU-bound?
• Average Active Sessions by wait class• Current: V$WAITCLASSMETRIC_HISTORY• Historical: DBA_HIST_WAITCLASSMET_HISTORY
• Other charts• Txn throughput: is app slowed down?• IO throughput: is IO performing?• Separate cause and effect
![Page 49: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/49.jpg)
EM Top Activity page
• Data sourced from ASH• Average Active Session chart
• Color-coded wait class breakdown (same as Perf Page)• Aggregated into 15-second buckets• Five-minute “time picker”
• Top SQL and Top Sessions lists• Details over selected five-minute time period• Color-coded wait class breakdown• Click to SQL Tuning Advisor
• Top Object, File, etc.
![Page 50: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/50.jpg)
![Page 51: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/51.jpg)
![Page 52: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/52.jpg)
![Page 53: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/53.jpg)
![Page 54: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/54.jpg)
ADDM
• Embedded expert system using DB time analysis• Identifies root causes behind the symptoms
• Variably scoped: • Host to instance to SQL and even database block• Scoped to database for RAC (new in 11g)
• Findings prioritized by impact on DB time• Finding history allows flexible time scoping• Directives can filter findings
• Recommendations by benefit (reduction) to DB time
![Page 55: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/55.jpg)
![Page 56: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/56.jpg)
![Page 57: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/57.jpg)
![Page 58: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/58.jpg)
Grid Control DB Loadmap
![Page 59: DB Time Performance Tuning - Theory and Practice](https://reader031.vdocuments.us/reader031/viewer/2022013111/54ffd24f4a7959e6728b4ccc/html5/thumbnails/59.jpg)
Grid Control DB Loadmap