Real World Tales Realizing the Full Potential of Oracle Database On Premise and in The Cloud
Bob Carlin, Andrew Holdsworth, Graham Wood Real World Performance Group, Oracle RDBMS Dev October 27, 2015
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Introduction
Connections
Cursor Management
1
2
3
4
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Introduction
Connections
Cursor Management
1
2
3
5
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Real-World Performance Root Causes
The database is not being used as it was designed to be used
The application architecture/code design is sub-optimal
There is a sub optimal algorithm in the database
11/4/2015
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Perception
DB Usage and Application Code
DB Internals
Focus in on exploring what is going on inside the database
11/4/2015
Reality
DB Usage and Application Code
DB Internals
Focus should be spent on how the database is being used
Where is the Performance Problem ?
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
This is a Dog
11/4/2015
This is a Dog on Premise This is a Dog in the Cloud
If Your Application is a Dog !
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Introduction
Connections
Cursor Management
1
2
3
9
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
85% OLTP Escalations coming into the Real World Performance Group are related to connection management
11
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Connections: What We Hear
We want connections to grow as load increases
We never want the middle tier to have to wait for a connection to the database
We need to be ‘Web scale’
The database can’t handle the load
12
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Demo Connections
13
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Connections: Digging deeper
It’s fine most of the time
We sometimes see logon spikes
We have big response time variations as load increases
We see lots of concurrency waits when it’s bad
21
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Connections: The Truth
Dynamic connection pools will cause logon storms
High numbers of processes will cause outages
Maximum connection count should be related to capacity i.e. cores
The middle tier is the most effective place to queue
22
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
23
Introduction
Connections
Cursor Management
1
2
3
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Cursor Management: What We Hear
We hire the brightest and best developers
Our development organization is highly agile
We shouldn’t need to train our developers to use Oracle
24
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Demo Cursors
25
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Cursor Management: Digging Deeper
Unable to get hardware busy
Lots of shared pool contention events
There is a security issue
Spending lots of CPU on parsing
27
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Cursor Management: The Truth
Orders of magnitude of performance unrealized
Using bind variables protects you from SQL injection
Soft parses are not free
Untrained developers will not produce the most scalable Oracle applications
28
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Summary
Architecture and build methods are critical for performance and reliablility
Aim for less than ten processes per CPU core
Static connection pools
Train your developers to use the product as it was designed to be used
29
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Real-World Red Flags for OLTP
High numbers of connections
Application Servers regularly cycled
Killer scripts
30
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Where to get more info
• Real-World Performance Engineers discussing and demonstrating performance issues, root causes and when to apply the correct techniques
– The Optimizer
– Core DB Performance
– Extreme OLTP
– Extreme DW
• http://www.oracle.com/goto/oll/rwp
11/4/2015
Real-World Performance Online Video Series