Transcript DB TIME

Average Active Sessions
(AAS)
The Golden Metric ?
Database Performance
How quick can you find
Bottleneck in DB
If DB is idle
Current DB Load

what is DB Load ?
 What do you use?
Statspack/AWR
what the
*!####!*!*? is
the database
doing ?!
V$active_session_history
Alerts

3/28/2016
what do you alert on ?
2
Statspack / AWR
1000 lines of data / 30 pages
What do you look at ?
Top 5 Timed Events?
#.3
188 minutes CPU
Elapsed 15 minutes
48 Processors or
74% idle
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event
Waits
Time (s) Ela Time
------------------------ ------------ ----------- -------CPU time
11,330
81.95
log file sync
163,725
1,012
7.32
enqueue
4,486
615
4.45
latch free
28,786
236
1.71
db file sequential read
1,420,355
149
1.08
-----------------------------------------
Jonathan Lewis - http://www.jlcomp.demon.co.uk/statspack_01.html
3/28/2016
3
In this Session
#.4
1. AAS


Single Metric
Shows DB Performance
2. Yardstick



Max CPU
CPU Count
To measure AAS against
3. Subcomponents



3/28/2016
CPU
Waits
Time series
4
Goal of this Presentation
Simplify the data and empower the DBA
Cut out unnecessary
Hone in on essential
3/28/2016
5
What’s the DB Doing?!
It’s 2am … your manager calls
Whip out the stethoscope:
AAS
what the
*!####!*!*? is the
database doing ?!
3/28/2016
6
Welcome to …
The Cult of AAS
Once you’ve been initiated …
there is no going back
For those of you who are already members, welcome back
#.7
#.8
AAS Calculation
AAS = DB TIME / Elapsed Time
DB Time (DBT) = sum over all sessions of time spent in a
call. A call could be
executing SQL
background work
DBWR writing blocks
LGWR writing redo
Average Active Sessions
#.9
 Centi-seconds per second
 In
the dark ages waits were often as Centi-secs per
sec
 Sometimes called secs/sec
 Usually didn’t include CPU time, which is essential
 Average Active Sessions (AAS)
 OEM
10g Graphs
 ASH Report
 Session Load
I
3/28/2016
often refer to it this way
Copyright 2006 Kyle Hailey
9
AAS
#.10
 Average load of sessions on the database
Average over 15 secs in OEM 10g
 Period varies in other tools
 measured in number of sessions

 Active Sessions Only
Active sessions put load the database
 Inactive Sessions don’t put load ( Except for memory usage )

 The Golden Metric
Powerful
 Multidimensional
 Indispensable

3/28/2016
10
AAS Sources
#.11
1. Manually from


v$sysstat
(9i ) v$system_event
2. Statspack

Need several calculations
3. AWR

One calculation
4. OEM 10g
Directly
3/28/2016
displayed
11
#.12
1. Manually
DB Time (DBT) = sum active session time
DB TIME (10g) =
select value from v$sysstat
‘DBtime’;
time’
where name = ‘DB
Select sum(time_waited) from v$system_event
DB TIME (9i) = where event not in ( ... idle events …);
+
Select value from v$sysstat
where name = ‘CPU used by this session’;
2. Statspack AAS
#.13
Look for
Elapsed
Time
Top 5 Timed Events

3/28/2016
Start at line 52 of about 1300!
13
AWR/Statspack: Cheat Sheet
 Install
 Connect as SYSDBA
 @?/rdbms/admin/spcreate.sql
 Run
 Exec statspack.snap;
 Generate Reports
 @?/rdbms/admin/spreport.sql
#.14
2. Statspack AAS
#.15
 Elapsed Time
STATSPACK report for
DB Name
DB Id
Instance Inst Num Release
RAC
Host
------- ----------- -------- -------- ---------- --- ------LABSF03 1420044432
labsf03
1 10.1.0.2.0 NO labsfr
Snap Id
Snap Time
Sessions Curs/Sess
--------- ------------------ -------- --------Begin Snap:
1 03-Apr-06 12:34:06
18
5.6
End Snap:
2 03-Apr-06 12:34:36
18
4.8
Elapsed:
1.00 (mins)
 Look at Top 5 Timed Events
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
Event
--------------------buffer busy waits
CPU time
free buffer waits
write complete waits
log buffer space
3/28/2016
Waits
--------2,748
1,588
10
306
Time (s)
--------250
32
15
8
5
% Total
Call Time
------78.72
10.16
4.63
2.51
1.51
15
2. Statspack AAS
#.16
Top 5 Timed Events
 DBTIME= CPU + WAITS
 CPU = 32
WAITS = 250+15+8+5 = 278 secs
DBTIME=320

Event
Time (s)
--------------------buffer busy waits
250
CPU time
32
free buffer waits
15
write complete waits
8
log buffer space
5
 Elapsed Time = 60 secs
 320 secs / 60 secs
AAS = 5.1
3/28/2016
16
3. AWR Report
#.17
AAS = DB Time/Elapsed Time
23.56/59.66 = 0.39
AAS= 0.39
3/28/2016
17
4. OEM 10g
#.18
•OEM 10g
AAS = ~0.75
3/28/2016
18
Got AAS, Now What ?
#.19
We Need one more item:
CPU Count
 # of CPUs available on System
 Shared

with other applications
Need to track CPU used on the system as well
 On
dual & quad cores, lower the CPU count
 Represents max active sessions that can do work
3/28/2016
19
CPU Count
#.20
 # of CPUs available in
 Statspack 10g
 AWR report
 OEM 10g
 Statspack 9i # of CPUs missing # of CPUs
SQLPLUS> show parameters cpu_count
NAME
VALUE
------------------ ---------cpu_count
2
3/28/2016
20
AAS Formulas
#.21
Use CPU count as yardstick:
 <1
Database is not blocked
 AAS ~= 0
Database basically idle
Problems are in the APP not DB
Ideal world –
one database
solution
track CPU at OS
 AAS < # of CPUs
CPU available
Database is probably not blocked
Are any single sessions 100% active?
 AAS > # of CPUs
Could have performance problems
AAS > 1
still want to know
if a single user is
100% active
 AAS >> # of CPUS
There is a bottleneck
3/28/2016
21
Available CPU vs AAS
#.22
AAS = 5.1
# of CPU = 2
AAS far above available CPU => problem
AAS = 0.39
# of CPU = 2
AAS < 1 , database is fine
3/28/2016
22
Going Farther with AAS
#.23
 AAS can tell you a lot
 But it’s components tell you much more
 To go farther need the components of AAS
1.CPU
2.Wait
3.Value
over time
Only OEM 10g has all 3
(Statspack and AWR are aggregated over the snapshot period)
3/28/2016
23
EM DB Home Page
#.24
OEM 10g Perf Pages
#.25
DB Home
AAS Point in Time
Performance
AAS over Time
AAS Components : OEM 10g
#.26
OEM 10g
Performance Page
Real CPU available:
Max CPU - non instance CPU
3/28/2016
Available CPU
AAS:
CPU + WAIT
26
#.27
OEM 10g
Relax
Get to Work!
Looks
OK
But …
3/28/2016
27
Limited Analysis
#.28
 What if you find a problem ? Example “easy” waits
buffer waits
Of the 800 waits most need free
log buffer space
log file switch (archiving needed)
SQL and/or P1,P2,P3
log file switch (checkpoint incomplete)
log file switch completion
to solve
What if there is a wait bottleneck ?
What SQL
Example “hard” waits
Which sessions
Buffer busy wait
Row cache lock
Values of P1, P2 and P3
Latch free
row lock contention
Statspack and AWR fail
Latch: cache buffers chains
3/28/2016
28
#.29
Alternative AAS Calculations
AAS = DB TIME / Elapsed Time
But there is another way …
AAS based on ASH
#.30
 ASH - Active Session History
 v$active_session_history
 AAS = count(*) / elapsed_seconds
A
statistical approximation, but surprisingly
close
 ASH data source empowers drilldowns
 Top
Sql
 Top Waits
 Details p1,p2,p3 and more
3/28/2016
30
AAS from ASH
#.31
1. ASHRPT

Based entirely on
v$active_session_history

@?/rdbms/admin/ashrpt
Exec ASH_REPORT_TEXT/HTML

2. OEM
select 10g
* from table
(dbms_workload_repository.ash_report_text(
Top Activity page
(select dbid
from v$database),
Displays
it directly
1,
 Enables
Drilldowns
sysdate
– 1/24,
3. Custom
queries
on
sysdate )) ;

v$active_session_history
3/28/2016
31
1. ASHRPT
#.32
ASH Report For TESTDB/testdb
DB Name
DB Id
Instance
Inst Num Release
RAC Host
------------ ----------- ------------ -------- ----------- --- -----------TESTDB
2371570538 testdb
1 10.2.0.1.0 NO sdbe604a
CPUs
SGA Size
Buffer Cache
Shared Pool
ASH Buffer Size
---- ------------------ ------------------ ------------------ -----------------2
1,000M (100%)
468M (46.8%)
112M (11.2%)
4.0M (0.4%)
Analysis Begin Time:
21-Apr-06 12:00:01
Analysis End Time:
21-Apr-06 12:05:01
Elapsed Time:
5.0 (mins)
Sample Count:
3,716
Average Active Sessions:
12.39
Avg. Active Session per CPU:
6.19
Report Target:
None specified
Top User Events
DB/Inst: TESTDB/testdb (Apr 21 12:00 to 12:05)
Avg Active
Event
Event Class
% Activity
Sessions
----------------------------------- --------------- ---------- ---------CPU + Wait for CPU
CPU
67.98
8.42
enq: TX - row lock contention
Application
23.98
2.97
buffer busy waits
Concurrency
4.66
0.58
latch: cache buffers chains
Concurrency
2.26
0.28
3/28/2016
32
#.33
1. ASH RPT
1) General info
2) Top User Events ***
3) Top Background Events
4) Top Event P1/P2/P3 Values
5) Top Service/Module
6) Top Client IDs
7) Top SQL Command Types
8) Top SQL Statements ***
3/28/2016
9) Top SQL using literals
10) Top Sessions ***
11) Top Blocking Sessions
12) Top Sessions running PQs
13) Top DB Objects
14) Top DB Files
15) Top Latches
16) Activity Over Time ***
33
#.34
1. ASHRPT over Time
Waits over Time
 Not
in AAS
Difficult but better
than nothing
Compare to …
3/28/2016
34
2. OEM 10g : Top Activity
#.35
•Top Activity
•Based on ASH
•Enables Drilldowns
•Top SQL
•Top Session
•Drill into a session
• Stats
• Raw waits
• Open cursors
• General info
•Drill into a SQL
• Stats and text
• Users executing
• Explain plan
• Tuning options
3/28/2016
35
3. Custom Scripts
#.36
Hate Graphics ?
 Query v$active_session_history directly
 Join to dba_hist_active_sess_history for week of data
act.sql
 Like
top 5 timed events
Aveact.sql
 Charts
with text AAS by hour (15 minute, minute , etc)
Aveactn.sql
 Ditto,
with top 2 wait events per bucket
Following Scripts Available on
 http://perfvision.com/ashscripts.php
3/28/2016
36
3. Custom Scripts
@act
Analysis Begin Time :
Analysis End
Time :
Start time, mins ago:
Request Duration
:
Collections
:
Data Values
:
Elapsed Time: 15 mins
#.37
2007-07-24 11:04:48
2007-07-24 11:19:45
15
15
528
3327
WAIT_EVENT
CNT
% Active Ave_Act_Sess
-------------------------------------- ---------- -----------latch free
10
.3
.02
log buffer space
13
.39
.02
buffer busy waits
14
.42
.03
db file scattered read
15
.45
.03
library cache pin
78
2.34
.15
log file sync
213
6.40
.40
ON CPU
726
21.82
1.38
enqueue
855
25.70
1.62
db file sequential read
1399
42.05
2.65
-----------sum
6.30
3/28/2016
37
#.38
3. Custom Scripts
@aveact
TM
NPTS AVEACT GRAPH
---------------- ------ ------- ---------------------06-AUG 13:00:00
270
.33 +2
06-AUG 14:00:00
1040
2.24 ++--------2--06-AUG 15:00:00
623
6.67 ++++------2---------06-AUG 16:00:00
1088
2.59 ++--------2---06-AUG 17:00:00
1104
1.26 ++----2
06-AUG 18:00:00
1093
1.38 +++---2
06-AUG 19:00:00
1012
1.74 ++------- 2
06-AUG 20:00:00
1131
.99 +---2
06-AUG 21:00:00
1111
1.22 ++----2
06-AUG 22:00:00
1010
1.66 ++------ 2
06-AUG 23:00:00
1120
1.08 +---2
07-AUG 00:00:00
1024
.83 +--2
07-AUG 01:00:00
1006
1.74 ++------- 2
07-AUG 02:00:00
1090
2.47 ++--------2---07-AUG 03:00:00
687
6.59 +++-------2---------07-AUG 04:00:00
1004
1.95 ++++++--- 2
07-AUG 05:00:00
1104
3.08 +++++-----2-----07-AUG 06:00:00
1122
1.91 +++++++-- 2
3/28/2016
CPU WAITS
---- ----29
59
341 1984
438 3718
335 2486
349 1043
663
842
373 1388
304
820
344 1012
414 1259
298
913
273
576
319 1428
347 2345
382 4142
1299
659
1170 2226
1582
558
38
3. Custom Scripts
#.39
@aveactn
TO_CHAR(STA
AAS AAS1 FIRST
AAS2 SECOND
GRAPH
----------- ------- ----- --------------- ----- --------------06 13:00:00
.30
.11 db file sequent
.10 CPU
06 14:00:00
2.24
.87 direct path wri
.49 log file sync
06 15:00:00
6.67 2.07 direct path wri 1.93 log file sync
06 16:00:00
2.59 1.01 direct path wri
.57 log file sync
06 17:00:00
1.26
.63 log file sync
.32 CPU
06 18:00:00
1.38
.61 CPU
.45 log file sync
06 19:00:00
1.86
.97 log file sync
.39 CPU
06 20:00:00
.99
.38 log file sync
.27 CPU
06 21:00:00
1.22
.57 log file sync
.31 CPU
06 22:00:00
1.66
.81 log file sync
.41 CPU
06 23:00:00
1.08
.49 log file sync
.27 CPU
07 00:00:00
.83
.37 log file sync
.27 CPU
07 01:00:00
1.74
.96 log file sync
.31 CPU
07 02:00:00
2.48 1.21 direct path wri
.52 log file sync
07 03:00:00
6.59 2.63 direct path wri 1.58 log file sync
07 04:00:00
1.95 1.29 CPU
.27 log file sync
07 05:00:00
3.08 1.05 CPU
.83 enqueue
3/28/2016
-----------------------2
++--------2--++++------2---------++--------2---++----2
+++---2
++------- 2
+---2
++----2
++------ 2
+---2
+--2
++------- 2
++--------2---+++-------2---------++++++--- 2
+++++-----2------
39
Samples VS Counters
#.40
Samples
v$active_session_history
Slight Lags
v$system_event
3/28/2016
Counters
40
CPU in ASH vs Stats
3/28/2016
#.41
41
In Review: Two Sources
#.42
1. v$system_event & v$sysstat




Indirect
Accurate
Lags (especially CPU)
Limits analysis
2. v$active_session_history




3/28/2016
Direct
Real time
Approximation
***Allows drilldowns***
42
The Power ASH gives AAS
DB Home
Performance
events
statistics
Top Activity
ASH
#.43
ASH in OEM
Top Activity gives
more information
#.44
#.45
Top Activity : Based on ASH
missing
Thanks
To
ASH
#.46
Top Activity : ASH Dimensions
AAS – %Session Time Issue
Shown in % DB Time
Missing % Session Time
#.47
#.48
Top Activity: ASH Sessions
Many Users Active
On Performance Page, no way to tell how many users
But Top Activity Page fixes that
Top Activity: ASH Sessions
Two Users Active
#.49
OEM 10g Perf Pages
DB Home
Performance
Top Activity
SQL
Session
#.50
OEM 10g Perf Pages
Top Activity
SQL
Session
#.51
Session : ASH Activity
#.52
SQL : ASH Activity
#.53
#.54
Getting the Most out of AAS
 Need to know the System’s Profile
 What your application is like?



Data Warehouse
OLTP
Typical load
 Once you get to know it you can see anomalies
Is AAS near 0 when it should be higher
 Is that Data Warehouse query running normal



Do you know what it looks like?
Is there an unusual bottleneck
Knowing your DB Profile
#.55
#.56
When to tune?
General rules of Thumb
 Waits >> CPU
 CPU > Max CPU
#.57
Waits > CPU
CPU > Max CPU
#.58
#.59
Idle Database
 Value of proving the database is Idle
 It’s the Databases Fault
 How
many times do you hear that?
 Database Idle
 No
load on database
 Database “performance” is fine

Under utilized
 Problem lies elsewhere
 Saved me time and stress many times
#.60
Idle Database – Perf Page
Idle Database – Top Activity
#.61
Harnessing AAS
 Statspack/AWR report




Lacking
Statspack free and any version
AWR automatically installed in10g
Both


Needs numerical massaging
Lacking detailed SQL and Session info
 OEM 10g


Clearest, easiest, most powerful
Overview


Breakdown –



Top SQL and Session
Or Service, Module, Action, Objects, Files
Drill downs



Load chart - AAS
SQL
Session
Plus


ADDM – Automatic Database Diagnostics Monitor
SQL Advisor – SQL tuning advice
#.62
OEM 10g Perf Pages
DB Home
Performance
Top Activity
Session
SQL
#.63
#.64
In summary
 AAS is simple and Powerful
 AAS’s components are even more powerful
 CPU
 WAIT
 Value
over Time
 Use # of CPUs as a yardstick
 Know your application load profile to see
anomalies
 Monitor AAS with OEM 10g