average active sessions - oaktableworld 2013
DESCRIPTION
A more recent presentation on the all-important topic of Average Active Sessions (AAS) for Oracle performance analysis.TRANSCRIPT
![Page 1: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/1.jpg)
Average Active Sessions
John Beresniewicz, Oracle America
![Page 2: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/2.jpg)
What is this?
![Page 3: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/3.jpg)
Agenda
• DB Time
• Average Active Sessions
• Performance Monitoring
• Comparing Load
• Diving Deep with ASH
• Relationship to Queuing Theory
![Page 4: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/4.jpg)
Database Time (DB Time)
• Time spent in the database by foreground sessions
• Includes CPU time, IO wait time and active wait time
• Excludes idle wait time
Database time is total time spent by user processes either actively working or actively waiting in a
database call.
![Page 5: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/5.jpg)
EM Performance Page
• DB Time per Second • Broken down into CPU + Wait Classes
• Averaged over 1 minute intervals
• Sources: v$waitclassmetric_history; v$sysmetric_history
• Question: What are the chart units and label?
![Page 6: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/6.jpg)
Historical Note
on Naming
• We knew what we were graphing, but what was it?
• Team met multiple times to discuss
• This is from when we finally “got it”
![Page 7: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/7.jpg)
What Are the Units?
• Time / time = unit-less (?)
• DB time accumulates in micro, milli, or centi-seconds
• Time-normalized sysmetrics 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
![Page 8: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/8.jpg)
Average Active Sessions
• NOTE: Time units must synchronize
AAS = DB time / elapsed time
(during some workload)
![Page 9: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/9.jpg)
Average Active Sessions
• A time-based measure of “user load” on the database
• Where the “time” is really user time…important
• The derivative (calculus) of DB Time over time
• This is why Top Activity and Perf Page are literal pictures of DB Time
• The “velocity” of DB Time accumulation in the database
![Page 10: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/10.jpg)
Performance Monitoring
• DB Time increases when user load increases
• Average Active Sessions measures the rate of increase
• DB Time increases when performance degrades
• Average Active Sessions captures the rate of increase of DB Time over time
• Therefore, AAS is the best single metric to monitor for overall performance
![Page 11: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/11.jpg)
Performance monitoring
• Average Active Sessions captures both load and performance • Severe performance degradation will spike the metric
• Server-generated metrics for monitoring: • Database Time Per Sec (10g)
• Average Active Sessions (11g)
• Adaptive Thresholds technology • Set thresholds to high percentile (99th) values (unusual spike)
• Moving window baseline and seasonality adjust to expected load changes
![Page 12: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/12.jpg)
Bad Friday?
![Page 13: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/13.jpg)
Comparing Database Load
• How to compare load on two different databases?
• How to compare load on same database from two different time periods?
• Answer: normalize DB Time by time
• That is, use Average Active Sessions!
• Remember, DB Time is “user time”
• It is “fungible”
![Page 14: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/14.jpg)
Enterprise Loadmap
![Page 15: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/15.jpg)
Enterprise Loadmap
![Page 16: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/16.jpg)
Enterprise Loadmap
![Page 17: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/17.jpg)
Diving Deep with ASH
• ASH is used to estimate DB Time accumulation over some time interval, and thus also AAS
• With ASH we can break down total AAS within a Database across many dimensions of interest
• Come to “ASH Deep-dive: Advanced Performance Analysis Tips” • Wednesday 3:30 pm, Moscone South Room 104
![Page 18: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/18.jpg)
EM Top Activity
![Page 19: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/19.jpg)
ASH Analytics
![Page 20: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/20.jpg)
Breakdown by SQL_ID
![Page 21: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/21.jpg)
Historical Note: ASH Analytics Mockup
![Page 22: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/22.jpg)
ASH Analytics Loadmap
![Page 23: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/23.jpg)
Relationship to Queuing Theory
• Consider the Oracle database as a black-box service center
• User calls come in (SQL) from outside
• Results computed inside and returned to user
• What is the relationship of Average Active Sessions to black-box queuing models?
• Average Active Sessions measures an important queuing theoretic concept
![Page 24: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/24.jpg)
Little’s Law for Queuing Systems
N = X * R
N = number of active requests in system
X = serviced request throughput
R = average service time per request
![Page 25: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/25.jpg)
Little’s Law: Database Black-box server
N = X * R
N = ????
X = User Calls per Second
R = Response (DB time) per Call
Average Active Sessions
This explains why DB time increases with both performance degradation and load increase.
![Page 26: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/26.jpg)
Compute AAS from AWR snapshots
• Step 1: Prepare raw data stream by joining:
• DBA_HIST_SNAPSHOT
• DBA_HIST_SYS_TIME_MODEL
• Statistic “DB Time”
• Step 2: Compute elapsed time and DB Time deltas per snapshot
• Step 3: Compute Average Active Sessions per snapshot
• DELTA(DB Time) / DELTA(Elapsed time)
![Page 27: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/27.jpg)
Step 1: Prepare Raw Data Stream
WITH snapDBtime as (select SN.snap_id as snap_id ,SN.instance_number as inst_num ,ROUND(SN.startup_time,'MI') as startup_time ,ROUND(SN.begin_interval_time,'MI') as begin_time ,ROUND(SN.end_interval_time,'MI') as end_time ,TM.value / 1000000 as DBtime_secs from dba_hist_snapshot SN ,dba_hist_sys_time_model TM where SN.dbid = TM.dbid and SN.instance_number = TM.instance_number and SN.snap_id = TM.snap_id and TM.stat_name = 'DB time' ),
![Page 28: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/28.jpg)
Step 2: Compute Time Deltas
DeltaDBtime as (select inst_num ,snap_id ,startup_time ,end_time ,DBtime_secs ,CASE WHEN begin_time = startup_time THEN DBtime_secs ELSE DBtime_secs - LAG(DBtime_secs,1) OVER (PARTITION BY inst_num, startup_time ORDER BY snap_id ASC) END as DBtime_secs_delta ,(end_time - begin_time)*24*60*60 as elapsed_secs from snapDBtime order by inst_num, snap_id ASC )
![Page 29: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/29.jpg)
Step 3: Compute Avg Active Sessions
select inst_num ,snap_id ,ROUND(DBtime_secs,1) as DBtime_secs ,ROUND(DBtime_secs_delta / elapsed_secs,3) as AvgActive_sess from DeltaDBtime /
![Page 30: Average Active Sessions - OaktableWorld 2013](https://reader034.vdocuments.us/reader034/viewer/2022042813/546ff6d8b4af9fa90a8b45dc/html5/thumbnails/30.jpg)