Transcript Graphing AWR Data in Excell - Go

Graphing AWR Data in Excel
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Who Am I?
• Oracle Database Specialist
– Independent consultant
• System Performance
tuning
– PeopleSoft ERP
– Oracle RDBMS
• Book
– www.psftdba.com
• UKOUG
– PeopleSoft Technology SIG
Committee
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
2
Resources
• If you can’t hear me say so now.
• Please feel free to ask questions as we go
along.
• The presentation is available from
• UKOUG Library
• www.go-faster.co.uk
• See also http://blog.go-faster.co.uk
– Tip: Print in ‘black and white’ for white
background.
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
3
Background Reading
• Blog Entry:
– http://blog.go-faster.co.uk/2008/12/graphingawr-data-in-excel.html
• Download Excel Sheet:
– http://www.gofaster.co.uk/scripts.htm#awr_wait.xls
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
4
A Picture is worth 1000 metrics!
• If you are lucky you might be able to pick
out that one number in an AWR/Statspack
report that is different to usual.
• But
– How many databases do you have to look after?
– How many reports do you need to examine to
find that rogue metric?
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
5
DBA2.0
• Oracle Enterprise Manager
– Graph Performance Data
– Focus on time
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
6
Oracle Enterprise Manager
/ Grid Control
• If you don’t have it, then I am going to
show you how to graph some of the data
yourself.
• If you have it, it is excellent.
– But it can be a bit tricky to save charts.
• Screen dump, or save graphics.
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
7
Objective
• Chart of
– Time the database spent waiting
– Over the last 7 days
– Broken down by Wait Event
• I am going to use AWR data
– But you can do the same thing with Statspack
• This technique can be applied to any set of metrics
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
8
Recipe
• Ingredients
– A SQL Query
– Microsoft Excel
– SQL*Net Client
• And this is what you do…
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
9
SQL Query
• Wait Information
– DBA_HIST_SYSTEM_EVENT
– Snapshot of v$system_event
• Snapshot Information
– DBA_HIST_SNAPSHOTS
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
11
Analytic Functions
• Wait Event Data are running totals
• Use analytic function LAG() to work out the
change since last snapshot.
• This is generally more efficient than getting
previous row using sub-queries.
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
12
Recipe (1): Data Source
• Create A Blank
Workbook
• Data -> Import
External Query ->
New Database Query
• Do not use the Wizard
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
13
Recipe (2): ODBC Driver
• The first time you do
this you may need to
create a new data
source
• Select ODBC Driver,
either
– Oracle ODBC
– MS ODBC for Oracle
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
14
Recipe (3): MS Query Connect
• Connect to the
database in the
ordinary way
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
15
Recipe (4): MS Query SQL Window
• View SQL
• Paste Query into the SQL Window
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
16
Recipe (5): MS Query Results
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
17
Recipe (6): Import Data
• Return the data to Excel. This time we want
a Pivot table
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
18
Recipe (7): Pivot Wizard
• Start with Layout
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
19
Recipe (8): Pivot Layout Wizard
• Drag fields, adjust titles and formating
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
20
Recipe (9): Pivot Subtotalling
• Turn off Subtotalling
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
21
Recipe (10): Pivot Formatting
• Sort Heading, restrict to Top-n
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
22
Recipe (11): Pivot Options
• Turn off Totals
• Save data with table
layout
• Disable Drilldown
• Save Password?
– (this could be a
security issue)
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
23
Recipe (12): Pivot Chart
• Very easy
– You can continue formatting in the pivot chat
• But works best when data points absolutely
regular along x-axis.
• Usually OK, because AWR snapshot
regularish.
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
24
Tips (1): X-Y Chart Pivot
• Can’t do it by default
• Cheat – copy to somewhere else in
spreadsheet.
• Copy down previous values use cell
formula when run out of data
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
25
Tips (2): Multiple pivots of same
data
• Don’t create a new query, copy the sheet
– The SQL is only issued once, but both sheets
refresh.
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
26
A Real System
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
27
A war story…
• Come back after lunch, and I’ll explain
what was going on here, and where it led…
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
28
And here is one I prepared earlier.
• I can now
– Keep the chart and data in a file
– Cut & Paste to Word document or PowerPoint
• Like this…
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
29
A real system
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
30
A war story…
• Come back after lunch, and I’ll explain
what was going on here, and where it led…
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
31
Questions?
Where’s
lunch?
Graphing AWR Data in Excel
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Questions?
Where’s
lunch?
A war story…
• Come back after lunch, and I’ll explain
what was going on here, and where it led…
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
35
A real system
Graphing AWR Data in Excel
©2009 www.go-faster.co.uk
36