Transcript TITLE

Simplifying Database Performance
Analysis using Oracle Enterprise
Manager 12c to Monitor Database &
Storage Together
Presented on Wednesday, April 23th
© Copyright 2014 EMC Corporation. All rights reserved.
1
Topics
Oracle Customer Challenges found in IOUG
Studies
Real World Examples of OEM 12c plug-in
How to get your FREE copy
© Copyright 2014 EMC Corporation. All rights reserved.
2
Lead Database Administrative Challenges
Troubleshooting performance leads the list
47%
Identifying resource-intensive SQL statements
44%
Keeping databases at current patch levels
31%
Managing larger number of databases
31%
Increase in security threats
29%
Resource and capacity planning
28%
Diagnosing database performance problems
From Database Clouds to Big Data: 2013 IOUG Survey on Database Manageability
© Copyright 2014 EMC Corporation. All rights reserved.
3
DBA Responsibilities Increasing
Managing Availability, Performance, Backup & Data Protection
Monitor Availability
77%
Diagnose Performance Issues
75%
Manage Backup & Recovery
Capacity Planning
71%
61%
Manage Data Storage & Retrieval
60%
Patch Systems
59%
Source: 2013 IOUG Data Science Skills Survey
© Copyright 2014 EMC Corporation. All rights reserved.
4
Database Management Gap Today
Source: 2014 IOUG Data Science Skills Survey
Database
Admins
Need…
Say DBA & Storage Administrator
Collaboration Is Key To Organization
• Proactive Performance &
Capacity Planning
• Fast & Complete Backup &
Recovery
• Optimized Availability & Data
Protection
© Copyright 2014 EMC Corporation. All rights reserved.
80%
Storage
Admins
Have…
• Detailed Storage Expertise
• Advanced Backup &
Recovery Solutions
• Highest RPO/RTO Data
Protection Solutions
5
Oracle Monitoring & Tuning Today
DBA SEES ISSUE
DBA ADJUSTS
DATABASE
STORAGE ADMIN
RESPONDS TO DBA
© Copyright 2014 EMC Corporation. All rights reserved.
DBA ENGAGES
STORAGE ADMIN
STORAGE ADMIN
DOES ANALYSIS
6
EMC Plug-in for Oracle Enterprise Manager 12c
Traditionally, the DBA had to…
With the EMC Plug-in the DBA can…
Use OEM and AWR reports to analyze
storage wait times as reported by the
database
See dynamic, real-time and
historical performance graphs
generated by the EMC storage array
rely on the Storage Administrator for
both configuration details and
performance analysis
independently investigate
configuration details and storage
performance metrics
Spend time trying to size storage
without the tools to quickly and
accurately do the job
Easily use the EMC OEM plug-in to not
only size storage but develop a
performance configuration too
© Copyright 2014 EMC Corporation. All rights reserved.
7
SINGLE& DASHBOARD
DBAS
REAL-TIME
HISTORICAL FOR
PERFORMANCE
ANALYSIS ON
EMC VMAX
VNXVMAX & VNX
CAPACITY UTILIZATION
ANALYSIS
ON&EMC
© Copyright 2014 EMC Corporation. All rights reserved.
8
Managing Multiple EMC Storage Arrays
Both EMC VMAX and VNX supported and clicking on the
array brings you to the home page
© Copyright 2014 EMC Corporation. All rights reserved.
9
Home Page for EMC OEM Plug-in
© Copyright 2014 EMC Corporation. All rights reserved.
10
Home Page for EMC OEM Plug-in
What ASM
disk group?
RAID 5 Data
Pool 95%
Full
© Copyright 2014 EMC Corporation. All rights reserved.
How much
free space?
Add
another
LUN
11
Recommendation: Create Multiple ASM Disk Groups
Add Another ASM Disk Group If More Capacity Is Needed
1
Allocation
Unit
1 4 7
© Copyright 2014 EMC Corporation. All rights reserved.
2
2 5
3
4
5
6
Under
3ms
315 ms
320 ms
7
3 6
EFD
10-15K
7.2K
FAST VP
Granularity Is
12 Symmetrix
Tracks Or
768K
Rebalancing
An ASM
Disk Group
Could
Impact The
Cache
Efficiency
12
Two Areas of Investigation: Monitoring & Configuration
Configuration
•
•
•
•
•
•
•
•
•
•
•
•
•
Application
ASM Disk Group
Storage Array
Device Mapping
Disks
FAST Cache
HBAs
LUNs
Meta LUNs
Port
Processor
RAID and Storage Groups
Storage Pools
Tend to be more static and are collected
every 24 hours. Click on Refresh to run
on-demand collection of configuration.
© Copyright 2014 EMC Corporation. All rights reserved.
Monitoring (Performance)
Over 50 metrics to monitor
• Disk Statistics
• LUN Statistics
• Response
• Disk Stats
• Environmental Info
• LUN Stats
• Meta LUN Stats
• SP Bus (for VNX)
• SP Stats (for VNX)
• Other collected items
These metrics are collected every 5 to 10
minutes or on-demand when clicked.
13
Reporting
•
•
•
•
•
•
•
•
•
•
VNX Host Connection Report
VNX SP and Storage Configuration Report
VNX Space Usage
Host-based Mapping of Oracle to Symmetrix
VMAX Host-based Performance Report
VMAX Space Usage (all hosts)
VNX DB-ASM Information Report
Escalated Incidents Reports
Incident History Report
Top Events Report
And many other reports…
© Copyright 2014 EMC Corporation. All rights reserved.
Quickly Drill Down From Oracle to EMC
Reporting in the EMC OEM Plug-in
Oracle
Server
EMC Storage
ASM Disk
Group
RAID
Group
Disks
14
Configuration Topology
With the topology
display you can:
•
•
•
•
See a summary of
all related targets
in a glance
Zoom in on any
particular target
Hover the mouse
over a relationship
to see details
Pan & zoom, …
EMC Storage
ASM Groups
DB Listeners
Oracle Homes
Hosts
© Copyright 2014 EMC Corporation. All rights reserved.
15
Real Examples
EMC Plug-in for OEM
with best practices
Scenario 1:
Poor Database Performance
© Copyright 2014 EMC Corporation. All rights reserved.
16
Initial AWR Analysis
Top 5 Timed Events
Event
Waits
Time(s)
Avg Wait(ms)
% Total Call Time
Wait Class
control file sequential read
682,903
369,493,811
541,063
522,885.1
System I/O
log file sequential read
336,667
350,093,962
1,039,882
495,431.6
System I/O
Streams capture: waiting for
archive log
146,169
32,815,418
224,503
46,438.4
Other
db file sequential read
61,995
12,951,372
208,910
18,328.0
User I/O
log file parallel write
10,822
4,404,430
406,989
6,232.9
Average wait time
.208 to 1.039
seconds
System I/O
8529 days of System I/O over 60.32 minutes between snaps
Wait Class
Wait Class
System I/O
Other
User I/O
Waits
%Time -outs
Total Wait Time (s)
Avg wait (ms)
Waits /txn
1,039,289
0.00
726,007,629
698562
253.86
156,561
75.21
36,100,312
230583
38.24
82,542
0.00
16,690,096
202201
20.16
© Copyright 2014 EMC Corporation. All rights reserved.
System I/O is
exponentially larger
than other wait
classes
17
Identify the Database LUNs
LUN 10
Meta: two or more LUNs glued
(concatenated or striped)
together for create one larger
LUN
© Copyright 2014 EMC Corporation. All rights reserved.
18
Navigation to Monitoring Metrics
Top menu for EMC VNX > All
Metrics > LUN Statistics Stats
© Copyright 2014 EMC Corporation. All rights reserved.
We are
interested in
IOPS as this is
an OLTP
database
19
LUN Read IOPS Performance
Monitoring > All Metrics > Last Collected >
VNX LUN Stats > Read IOPS
© Copyright 2014 EMC Corporation. All rights reserved.
20
DBA uses OEM 12c Plug-in for Remediation
Why are my databases slowing down?
After some investigation the DBA found all
the databases were using the same
storage pool. This database storage
consolidation is impacting read latency.
The plug-in makes it very easy to see this
kind of situation
© Copyright 2014 EMC Corporation. All rights reserved.
LUN 1,6
0,2
14,4
13,3
5
21
If IOPS Were Free…
EFD
Control files
Extremely low latencies benefits OLTP workloads
Moderate for active random reads and writes
Best Write
Performance
Better Resiliency
50% overhead
Database files
Control files
Redo Log files
CRS
Low latencies benefits services & OLTP workloads
7.2K
RAID 10
Better Write
Performance
Good Resiliency
12.5% overhead
Highly active random reads and writes
Database files
10-15K
RAID 5
Archive Log files
Less for random reads and writes
Redo Log files
CRS
Archive Log files
Low latencies benefits services & OLTP workloads
The FRA Pool & TEMP would be in separate ASM disk groups on EFD
© Copyright 2014 EMC Corporation. All rights reserved.
22
Real Examples
EMC Plug-in for OEM
with best practices
Scenario 2:
How to investigate Online
Redo Logs
© Copyright 2014 EMC Corporation. All rights reserved.
23
Oracle AWR: Log file parallel write
The LGWR process writes the redo buffer to the online redo files in
parallel and waits on the log file parallel write event until the last
I/O is on disk.
The log file parallel write latency is typically a symptom of poor
placement of online redo log files
© Copyright 2014 EMC Corporation. All rights reserved.
24
Identify the Redo LUNs
REDO Group 1
LUN 7
REDO Group 2
LUN 8
© Copyright 2014 EMC Corporation. All rights reserved.
Storage Pool: Redo
RAID 10
IO across all disks
25
Reviewing Disk Write IOPS
Monitoring > All Metrics > VNX LUN Stats > Write IOPS
Appears the LUNs 7 and 8 are have low write IOPS
© Copyright 2014 EMC Corporation. All rights reserved.
26
Database Consolidation on LUNs
Monitoring > Configuration > Last Collected > VNX LUNs
During normal database operation the LGWR sequentially writes to the
Redo Logs. One consideration to investigate is enabling the storage
array write cache for improved performance.
© Copyright 2014 EMC Corporation. All rights reserved.
27
Guidelines for Online Redo Logs
Findings
• Consider adding more disks to the RAID Group 1 (0 + 1)
• Consider adding more LUNs
Guidelines
• Use external redundancy unless 3rd party requires duplexing
• External redundancy reduces host CPU and I/O cycles
• Where possible dedicate physical disk groups
• Where possible dedicated I/O channels might reduce response times
• Flash disks are not good candidates for sequential I/O
© Copyright 2014 EMC Corporation. All rights reserved.
28
Real Examples
EMC Plug-in for OEM
with best practices
Scenario 3:
Comparisons
“Detecting a change in
storage”
© Copyright 2014 EMC Corporation. All rights reserved.
29
Saving Storage Baseline Configuration
Save the baseline
Time passes and performance gets bad. Let’s do a comparison
between current configuration and the baseline we saved
originally.
Select the baseline.
© Copyright 2014 EMC Corporation. All rights reserved.
30
DBA Uses OEM 12c Plug-in For Remediation
Recently Physical Database Writes Have Experienced Very Poor Performance, Why?
Somebody Turned Off The Write Cache. That Might Explain It!
© Copyright 2014 EMC Corporation. All rights reserved.
31
Real Examples
EMC Plug-in for OEM
with best practices
BI Publisher Enterprise
Reports
© Copyright 2014 EMC Corporation. All rights reserved.
32
Many Reports for the Oracle DBA
Enterprise > Reports > BI Publisher Enterprise Reports
© Copyright 2014 EMC Corporation. All rights reserved.
33
VNX Database and ASM Report
© Copyright 2014 EMC Corporation. All rights reserved.
34
Oracle Enterprise Manager Integration
Storage Visibility: Configuration, Performance, Availability Data
ORACLE ENTERPRISE
MANAGER PLUG-IN
Free & downloadable from Oracle extensibility exchange: www.oracle.com/goto/emextensibility
© Copyright 2014 EMC Corporation. All rights reserved.
35
Oracle Enterprise Manager Integration
Storage Visibility: Configuration, Performance, Availability Data
Important Links:
• VMAX Storage Plug-in for Oracle Enterprise Manager 12c
(EMC Data Sheet OEM 12c v1.4)
• NEW OEM 12c Plug-in from EMC
(Blog)
• VMAX Storage Plug-in for Oracle Enterprise Manager 12c – Technical
Enablement (Video and Presentations)
• Oracle Enterprise Manager 12c Plug-in for EMC VMAX Demo
(Video)
© Copyright 2014 EMC Corporation. All rights reserved.
36
WHAT ARE YOUR AREAS OF INTEREST?
OPTIMIZE ORACLE INFRASTRUCTURE
Oracle Performance
Optimization
MAXIMIZE DBA PRODUCTIVITY
Oracle Monitoring & Tuning
Oracle & Virtualization
Oracle & Converged
Infrastructure
Oracle Backup &
Recovery
Oracle Continuous
Availability
EMC
COMMUNITY
FOR ORACLE DBAS
www.emc.com/everythingoracle
© Copyright 2014 EMC Corporation. All rights reserved.
37
Summary
COLLABORATE
The OEM 12c plug-in enables the DBA to
collaborate with the storage administrator
ENABLEMENT
A view into VMAX and VNX storage arrays
means faster remediation
MANAGEMENT
The DBA now has a complete performance and
configuration view of the entire Oracle stack
© Copyright 2014 EMC Corporation. All rights reserved.
38
DBA & Storage Admin
Tools
for Oracle on VMAX
Better Collaboration Can Mean Faster Action And Hopefully Time Saved Not Having To Go To Long Meetings
Free
Offered By
Pre-sales
Download
Today!
Click here to
learn more
Click here to
learn more
Storage
Admins
Full Service Time
Analysis and FAST
VP Customization
Click here to learn more
DBA
Professional Service
© Copyright 2014 EMC Corporation. All rights reserved.
39
Oracle Sessions at EMC World
•
•
•
•
•
•
•
•
•
•
•
Bridging The Gap Between The Oracle DBA And Storage Administrator
Millions Of IOPs in A Flash! EMC Solution Guidelines for Increasing Oracle
Performance (Birds-Of-Feather)
VMAX & Oracle: High Performance Solutions
Architecting Oracle Databases On VMware vSphere 5 with EMC Storage
Best Practices For Oracle Backup with Data Domain
Speed Backups & Empower Application Owners with Data Domain Boost
Oracle: Managing Heterogeneous Storage Environments With Oracle
Enterprise Manager
Oracle: EMC & Oracle Simplifies Cloud Infrastructure Deployment
VPLEX: Continuous Availability For All Business-Critical Applications
VNX With VPLEX: Making Continuous Operations Seamless
EMC IT Business Transformation: Running SAP Applications & SAP HANA On
VMware vCloud Suite
© Copyright 2014 EMC Corporation. All rights reserved.
40
Customers Have Seen The Benefits
“ Our DBAs are now responsible for their own
environment and don’t have to look to a
Storage Administrator to help them expedite
the data recoveries. The DBAs have control of
their backups and they’re very happy with that.
”
3x
25x
LESS
Longer retention
periods for backups
Backup storage
required
NANCY MAGERS
Associate IT Director
9X
“ We’re saving a tremendous amount on
network bandwidth and our Oracle database
backups are completing in only two hours
compared to 18 hours previously.
”
ALEX SKOVRONEK
IT Manager
© Copyright 2014 EMC Corporation. All rights reserved.
FASTER
24X
Oracle database
backups
FASTER
Oracle database
restores
14X
Less storage &
bandwidth required
42
Customers Have Seen The Benefits
“ Before at best we could recover with 45
minutes to an hour delay. With RecoverPoint I
can bring that database up in 2 minute
intervals. Not only did the EMC people have
deep expertise about virtualized Oracle
environments, but they collaborated with an
Oracle team to test it all before cutting over.
”
MARK SMITHEY
VP Technology Services
“ We run everything on our VPLEX.
We run
financial applications, high end Oracle, SQL,
Exchange. Anything we want to be able to
move around the data center.
”
JOHN BOWLING
Storage Architect
90%
92%
20X
Virtualized Oracle, SQL
Server & Exchange
Reduction in physical
servers
FASTER
85%
Virtualized Oracle, SQL
Server & Exchange
Zero
For data migrations
and workload
DOWNTIME rebalancing
FULL
RECOVERY
© Copyright 2014 EMC Corporation. All rights reserved.
Time to recovery of
Oracle environments
To any point-in-time
within three days with
EMC RecoverPoint
43