Transcript Using the Dell PowerPoint template

One Picture is Worth a
Thousand Words
Amihay Gonen,
Oracle Domain expert, Dell
Who Am I
More than 20 years work as an Oracle DBA and developer
Worked with Oracle since version 7
Most of the time in R&D departments on BIG DATA projects
Managed several DBA teams for more than 10 years.
Currently works as an Oracle domain expert in DELL, as part of the
FogLight development team.
• Hobbies : Playing chess , running and developing database-oriented
tools
•
•
•
•
•
[email protected]
http://il.linkedin.com/in/agonen
https://twitter.com/agonen
2
7/16/2015
Preface
David McCandless said in his TED talk:
"By visualizing information, we turn it into a landscape that
you can explore with your eyes, a sort of information map.
And when you're lost in information, an information map is
kind of useful."
3
7/16/2015
Preface
4
7/16/2015
Agenda
• Oracle performance Tools
• Data visualization methods
• Poor man’s Heat Map from AWR
• Root cause analysis
• Future
5
7/16/2015
• Oracle
performance
Tools
6
7/16/2015
The Evolution of Oracle Performance Tools
Active Session
History (Oracle 10)
Automatic Database
Diagnostic Monitor
Automatic Workload
Repository –
(Oracle 10G and
above)
StatsPack (Oracle
8,8i)
UtlBstat-UtlEstat
(Oracle 7)
7
7/16/2015
Oracle performance Tools- StatsPack
√ Collects more data, including high-resource
SQL.
√ Pre-calculates many ratios such as cache hit
ratios, rates, and transaction statistics.
√ Separates data collection from report
generation.
! Support only 9i metrics
! No built-in retention scheme.
! Focus on history only
√ No extra license ($$)
Farther reading:
http://jonathanlewis.wordpress.com/statspack-examples/
8
7/16/2015
The evolution of Oracle performance
utilities - AWR
ADDM
AWR/
ASH
Server
alerts
9
Confidential
7/16/2015
Advisors
The evolution of Oracle performance Tools
- AWR
• A DWH like repository for all performance metrics
• Repository tables are part of the database (SYSAUX tablespace )
• Data is retrieved in a “snapshot” once an hour and retained for 7
days (configurable)
• Baseline enables to compare between different periods (snapshot
range)
• Basic Report - awrrpt.sql
10
7/16/2015
The Evolution of Oracle Performance Tools
- Server Alerts
• Database Self-Monitoring
• Alerts triggered when
– A problem occurs
– Data does not match expected values for metrics
• Alert thresholds are configurable (OEM or DBMS_SERVER_ALERT)
• View alerts
– OEM
– DBA_OUTSTANDING_ALERTS
• Asynchronous notification (for example: email)
11
7/16/2015
Oracle performance Tools- ADDM
ADDM
• A report from the AWR to identify potential performance
bottlenecks (“human like”)
• Report is combined of the following parts :
–
–
–
–
Finding issues
Description of the issue
Fix it Recommendation
Symptoms which lead to its discovery
• ADDM report (task) generation is part of AWR snapshot (default
one an hour)
12
7/16/2015
The Evolution of Oracle Performance Tools
- Advisory
• (Automatic) SQL Tuning Advisor –
provides recommendations for improving the efficiency of an
SQL query or a set of SQL queries
• Memory Advisors
–
–
–
–
Memory
SGA
Buffer Cache
PGA
• Undo Advisor
• (Automatic) Segment Advisor
13
7/16/2015
• Data
Visualization
Methods
14
7/16/2015
Data Visualization Methods
Details …
 Simplest form of data
 Compare between different metrics in a point in time
 Enables to detect macro patterns
and also to pay attention for detail information
15
7/16/2015
Data Visualization Methods
16
7/16/2015
Data Visualization Methods - HeatMap
• Computers are good with numbers
People are good with pictures
• Quickly focus “hot” areas
• Get the “global” picture
17
7/16/2015
Data Visualization Methods - TimeLine
• Answer What/When questions
• Know the process to have better understanding
18
7/16/2015
Data Visualization Methods - Magic Square
• Compare 2-dimension easily
• Use common standard in the
industry
19
7/16/2015
• Poor Man’s
Heat Map from
AWR
20
7/16/2015
Data Visualization Methods - HeatMap
• Display in one picture data of
one week or more
• Focus in the general picture
21
7/16/2015
What are the
busiest hours ?
Data Visualization Methods - HeatMap
Detect
patterns
22
Confidential
7/16/2015
Data Visualization Methods - HeatMap
How to bake your “own” heat map – my receipt ….
• Use popular HeatMap.js library – http://www.patrick-wied.at/static/heatmapjs
• Query the AWR views to get
performance data :
o X axis – hour in a day
o Y axis – Day list
o Count – Value of the
metric
o Max – Max value ( calculate
heat values)
23
7/16/2015
Data Visualization Methods - HeatMap
How to bake your “own” heat map – my receipt ….
• Use smart queries
select mod((s.snap_id-first_value(s.snap_id) over (order by s.snap_id)),24) x,
trunc((s.snap_id-first_value(s.snap_id)
over (order by s.snap_id))/24) y,
nvl(round((value-lag(value) over (order by s.snap_id))/1000000),0) count
from
DBA_HIST_SYS_TIME_MODEL m
join DBA_HIST_SNAPSHOT s
on m.snap_id=s.snap_id
where stat_name='DB time';
24
7/16/2015
Data Visualization Methods - HeatMap
25
7/16/2015
• Root Cause
Analysis
26
7/16/2015
Root Cause Analysis
Hi I’m Bob , I’m the DBA
on duty ,
How may I assist you ?
My name is John.
My DB suddenly
works slower than
I am used to
No problem, please
provide me
connection details ?
Sure , I’ll send
by email the
SSH connection
details
27
Confidential
7/16/2015
Root Cause Analysis
What to-do
next ?
28
Confidential
7/16/2015
Root Cause Analysis – Top Down
Information Visualization
* What are the busiest hours ?
* is there a trend ?
* What is the problematic resource (Cpu, I/O , Application behavior )
HeatMap
Data Visualization
Line Chart
* What are the peak time ?
* Is the metric out of it’s baseline
Data Visualization
Table Chart (awr report)
29
7/16/2015
* Focus on the sql detail
Root Cause Analysis
Hmm…
let’s focus
on 11/2/2014
30
\
7/16/2015
Root Cause aAnalysis
Hmm…
Interesting
wait class changed
from User I/O to
Application
31
\
7/16/2015
Root Cause Analysis
Ok , got the
problem…
32
\
7/16/2015
• Future
33
7/16/2015
Future
• The ratio databases per DBA is increasing .
• DBAs expected to achieve more in less time
• One of Keys to success is to pick the right tool for the right
problem.
For code example :
https://github.com/agonen/oracle_reports
34
7/16/2015
Thank you
35
7/16/2015