Oracle Performance: 80 Percent Design + 20 Percent Hardware

By:


Performance problems with the database cause as much as 70% of all application performance issues in production, according to consensus among database practitioners and experts. A host of factors impact Oracle database performance, including the CPU, memory, IO and the network. But what are the real causes of database performance issues that Oracle experts see? When database performance is declining, where do they first look? And, are there any new features in Oracle 12c that could potentially improve performance?

To find out more about Oracle database performance, we convened a Twitter chat (#datachat) with Oracle and performance experts, including: Kyle Hailey (Oracle ACE and Delphix Evangelist),  Arup Nanda (Oracle ACE and Principal Database Architect at Starwood Hotels & Resorts), Marcin Przepiorowski (Oracle ACE and Principal Oracle Consultant at Version 1), Dominic DelMolino (Oaktable member and Vice President of Systems Architecture at Agilex Technologies), Toon Koppelaars (Oracle ACE and founder at Rule-Gen), Eric Grancher (Oaktable member and Database Engineer at CERN), and Dean Richards (Manager of Sales Engineering, SolarWinds).

(And you can read the full Twitter transcript here.)

Busting myths about Oracle database performance

Our panel strongly agreed that there are a number of myths that prevent many in IT from accurately assessing and addressing performance issues with the database. First and foremost among these is the belief that there is a silver bullet, as Kyle Hailey pointed out, leading some to the conclusion that expensive solutions like Exadata can address the problems caused by lack of good design:

Hailey No Silver Bullets

A closely related second myth is the belief that “throwing hardware at it” will solve most performance issues, as Arup Nanda added:

Nanda Must Solve Problem Not Symptom

Connor McDonald agreed and added that sometimes it’s not a lack of hardware so much as a poor balance of hardware and configuration, such as pairing a very fast CPU with a slow path to storage:

Mcdonald Poor Hardware Balance

Differentiating between a performance issue and its measurable impact is another issue in assessment, and Hailey quoted renowned Oracle performance expert Cary Millsap in noting that it’s a common problem to have people looking for the common problem instead of measuring it:

Hailey Problem In Not Measuring Problem

This focus on hardware as the silver bullet can also be seen in the mistaken belief that adding CPU and faster disks—and having a good buffer hit cache ratio (or BCHR)—will help scale performance, as Hailey noted:

Hailey Adding Hw Does Not Always Scale Performance

Toon Koppelaars noted that sometime developers mistakenly belief that processing data outside the RDBMS can relieve load and thus improve speed, but it actually makes performance slower because it is using database resources much less efficiently:

Koppelaars Myth In Data Outside Database

Koppelaars Not Using Database Efficiently

Hailey added that one of the more disturbing performance issues he’s encountered is developers joining tables in the application code rather than in the database:

Hailey Coders Join Tables Outside Database

McDonald shared that this belief is often expressed as, “Oh, we’ll just cache all that!” And that this can also contribute to data corruption:

Mcdonald Just Cache It

The reality: design matters in database performance

Experts on our panel agreed unanimously with Kyle Hailey that database performance issues are caused more by issues in database design than by hardware:

Hailey Database Performance Impacted By Design

These design issues are often the result of a requirements process that doesn’t account for performance, as Marcin Przepiorowski noted:

Przepiorowski Performance Must Be Design Requirement

 

Even if the requirement for performance is included in database design, the testing process and environment must adequately replicate the true demands of the production environment, as Arup Nanda reminded everyone:

Nanda Database Performance Impacted By Testing

 

Dominic Delmolino pointed out that while it’s critical to align results with resource requests, it’s equally important to align results with expectations, so that application performance adequately reflects business needs:

Delmolina Align Requirements And Expectations

 

One of the primary issues in design is bad SQL, according to Hailey:

Hailey Bad SQL Causes Performance Issues

 

Hailey went on to observe that Entity Attribute Value schemas, sometimes referred to as object–attribute–value or vertical database models:

Hailey EAV Can Lead To Bad Design

 

EAV schemas are “anti-pattern,” and store properties of a single entity as a set of name-value pairs in multiple rows, instead of in a single row, and their use can lead to very poor performance.

Another common culprit in poor performance is failure to leverage set operations, leading to single row inserts and commits, a very inefficient use of database resources, as both Hailey and Dean Richards concurred:

Hailey Set Operations

Richards Singleton Commits

Hailey compares single row operations such as these to going to the grocery store, buying a single item, carrying it home and then returning to the store to buy the next item.

 

Read the full Twitter chat transcript here!

Although there are many factors that impact database performance, and some are outside the control of the DBA or database developer, our expert panel covered many ideas about ensuring optimal performance. You can read the full Twitter transcript here:

 

 

SWI_DatabaseDean

4/15/14 19:00

@SWI_Database Hello everyone, glad to be part of the #Oracle #datachat today on #performance

 

SWI_Database

4/15/14 19:01

Let us know you’re here – just #datachat to your tweet 🙂 Q1 coming right up!

 

ToonKoppelaars

4/15/14 19:02

Hello.  #datachat

 

pioro

4/15/14 19:02

Hello #datachat

 

SWI_Database

4/15/14 19:02

Q1: Why do performance issues happen? #oracle #datachat

 

SQLRockstar

4/15/14 19:02

RT @SWI_Database: Q1: Why do performance issues happen? #oracle #datachat

 

nadineCO

4/15/14 19:02

hello #datachat

 

SWI_Database

4/15/14 19:03

Welcome @pioro @ToonKoppelaa #datachat

 

arupnanda

4/15/14 19:03

#datachat hello

 

kylehhailey

4/15/14 19:03

Performance issues happen mainly because of bad design #datachat

 

KyleQuass

4/15/14 19:03

Hello #datachat

 

SQLRockstar

4/15/14 19:03

Hi. –> RT @nadineCO: hello #datachat

 

kylehhailey

4/15/14 19:03

Performance occasionally happen because of hardware undersizing (IO, CPU, Memory, Network) but that’s the 20 (or less) of 80/20 #datachat

 

nlitchfield

4/15/14 19:03

@SWI_DatabaseDean @SWI_Database @kylehhailey so is it useful to define #performance first? #datachat

 

arupnanda

4/15/14 19:04

#datachat perf issues mostly occur due to under provisioning or over exterting by consumers

 

SWI_Database

4/15/14 19:04

Welcome @nadineco @arupnanda @nlitchfield @kylequass #datachat

 

ToonKoppelaars

4/15/14 19:04

They happen for lack of understanding of how things work.  #datachat

 

SWI_Database

4/15/14 19:04

RT @arupnanda: #datachat perf issues mostly occur due to under provisioning or over exterting by consumers

 

pioro

4/15/14 19:05

#datachat Performance is another system requirements and has to be included in design

 

kylehhailey

4/15/14 19:05

@ IOUG last week a slide said 80% of performance problems were from IO & Kevin Closson tweeted the other 80% are from bad SQL 😛 #datachat

 

ddelmoli

4/15/14 19:05

@SWI_Database #datachat Oracle perf problems are born in dev not adequately simulating production

 

arupnanda

4/15/14 19:06

#datachat other cause of perf issues: testing for a single consumer while production systems are always for multiple users in parallel

 

SWI_DatabaseDean

4/15/14 19:06

#datachat I get the bad SQL part of that, but 80% problems caused by storage seems very high

 

SWI_Database

4/15/14 19:06

RT @ddelmoli: @SWI_Database #datachat Oracle perf problems are born in dev not adequately simulating production

 

nadineCO

4/15/14 19:06

RT @ddelmoli: @SWI_Database #datachat Oracle perf problems are born in dev not adequately simulating production

 

ToonKoppelaars

4/15/14 19:06

Understanding why relational databases exist and ergo how they should be used.  #datachat

 

kylehhailey

4/15/14 19:06

Performance design problems happen often  due to unnecessary work  #datachat

 

kylehhailey

4/15/14 19:07

As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

 

SWI_Database

4/15/14 19:07

RT @kylehhailey: As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

 

arupnanda

4/15/14 19:07

#datachat who specifically said “80% of prob are due to IO”?

 

ChrisAntognini

4/15/14 19:08

Performance problems happen because performance expectations are not carefully defined #datachat

 

kylehhailey

4/15/14 19:08

Yes, and the other (the real) performance issues were caused by bad SQL #datachat (not sure how many people understood Kevin’s response!)

 

SQLRockstar

4/15/14 19:08

RT @nadineCO: RT @ddelmoli: @SWI_Database #datachat Oracle perf problems are born in dev not adequately simulating production

 

SWI_Database

4/15/14 19:08

RT @ChrisAntognini: Performance problems happen because performance expectations are not carefully defined #datachat

 

SQLRockstar

4/15/14 19:08

RT @kylehhailey: As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

 

pioro

4/15/14 19:08

#datachat avoid unnecessary work is a key

 

kylehhailey

4/15/14 19:09

#1 problem is impedance between Object Oriented design as well as functional programming verse relational set operation paradigm  #datachat

 

kylehhailey

4/15/14 19:09

My top 5 perf issues 1 EAV schema 2 single row access 3 joins in code instead of db 4 abuse of count(*) 5 flooding connection pool #datachat

 

netofrombrazil

4/15/14 19:09

RT @kylehhailey: As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

 

arupnanda

4/15/14 19:10

#datachat most issues are manifested as I/O bcoz the demand on the I/O is unusually high due to bad SQL. Perhaps that’s what author meant

 

ddelmoli

4/15/14 19:10

Perf problems also come from not aligning results with resource reqs and associated expectations #datachat

 

kylehhailey

4/15/14 19:10

1. One of the worst performance designs is Entity Attribute Value schemas, stuff table, which is ironically the design of noSQL #datachat

 

ddelmoli

4/15/14 19:10

RT @kylehhailey: My top 5 perf issues 1 EAV schema 2 single row access 3 joins in code instead of db 4 abuse of count(*) 5 flooding connect…

 

pioro

4/15/14 19:11

#datachat Kyle – I would put nr 2  using single block access in loop to process whole table

 

kylehhailey

4/15/14 19:11

Entity Attribute Value schemas http://t.co/6Oh80r35QZ #datachat

 

kylehhailey

4/15/14 19:11

2. Using databases without leveraging set operations leads to single row inserts followed by commits #datachat

 

kylehhailey

4/15/14 19:12

@netofrombrazil @kylehhailey and the other 80% are psychological aspects commonly related to IO 🙂 #datachat

 

SWI_DatabaseDean

4/15/14 19:12

#datachat I definitely run into the singleton commit issue very often

 

ddelmoli

4/15/14 19:12

@kylehhailey #datachat 6. Adding more threads when things get slow in order to have “more capacity”

 

SWI_Database

4/15/14 19:12

Q2 coming up #datachat

 

ToonKoppelaars

4/15/14 19:12

Shipping all data out to a JVM for processing, can be avoided by processing data in PLSQL VM: often lot faster  #datachat

 

kylehhailey

4/15/14 19:12

yep ! 5. when using a connection pool, increasing the sessions when performance goes down which usually makes performance worse #datachat

 

kylehhailey

4/15/14 19:12

2. Single row operations are like going to the grocery store buying one item, coming home, then going back #datachat

 

SWI_Database

4/15/14 19:12

Q2: What is the number one performance tuning myth? #datachat

 

arupnanda

4/15/14 19:12

@netofrombrazil #datachat completely agree.

 

SQLRockstar

4/15/14 19:13

RT @kylehhailey: 2. Single row operations are like going to the grocery store buying one item, coming home, then going back #datachat

 

kylehhailey

4/15/14 19:13

2. “Refactoring SQL Applications” by Stephane Faroult is great for understanding and fixing bad database code access design  #datachat

 

mickey4nay

4/15/14 19:13

RT @nadineCO: RT @ddelmoli: @SWI_Database #datachat Oracle perf problems are born in dev not adequately simulating production

 

SWI_DatabaseDean

4/15/14 19:13

#datachat 2. Good analogy

 

kylehhailey

4/15/14 19:13

Probably the greatest performance myth is that there are silver bullets to fix your problem like Exadata instead of good design  #datachat

 

netofrombrazil

4/15/14 19:13

RT @kylehhailey: @netofrombrazil @kylehhailey and the other 80% are psychological aspects commonly related to IO 🙂 #datachat

 

kylehhailey

4/15/14 19:13

As Cary Milsap once said, the most common problem is people looking for the most common problem instead of measuring for it #datachat

 

arupnanda

4/15/14 19:14

#datachat #2 That you can have a silver bullet for evrything

 

nlitchfield

4/15/14 19:14

@ChrisAntognini I think  add , and usage characteristics are not well understood. Specifically number & type of transactions #datachat

 

connor_mc_d

4/15/14 19:14

poor hw balance,eg, superfast CPU and slow path to storage #datachat

 

kylehhailey

4/15/14 19:14

A2 It’s such a beaten dead horse but it still comes to mind having a good buffer cache hit ratio means I/O performance is good #datachat

 

SWI_Database

4/15/14 19:14

@connor_mc_d welcome Connor! #datachat

 

arupnanda

4/15/14 19:14

#datachat #2: solving the symptom will take care of the problem

 

galobalda

4/15/14 19:14

RT @ddelmoli: @SWI_Database #datachat Oracle perf problems are born in dev not adequately simulating production

 

kylehhailey

4/15/14 19:15

A2 myth: better yet that BCHR is the myth that adding CPU, faster disks and hardware will make performance scale #datachat

 

ToonKoppelaars

4/15/14 19:15

Nr 1 myth for me: processing data outside dbms, relieves dbms. It actually does the opposite #datachat

 

fidelinho

4/15/14 19:15

RT @ChrisAntognini: Performance problems happen because performance expectations are not carefully defined #datachat

 

ChrisAntognini

4/15/14 19:15

RT @nlitchfield: @ChrisAntognini I think  add , and usage characteristics are not well understood. Specifically number & type of transactio…

 

galobalda

4/15/14 19:15

RT @kylehhailey: As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

 

ToonKoppelaars

4/15/14 19:15

This is due to fact that dbms will be used less efficiently.  #datachat

 

kylehhailey

4/15/14 19:16

Wow, yes, One of the  most disturbing performance issues is coders joining tables in application code instead of the database #datachat

 

connor_mc_d

4/15/14 19:16

perf myth – that an index will makes things better #datachat

 

SQLRockstar

4/15/14 19:16

RT @kylehhailey: Probably the greatest performance myth is that there are silver bullets to fix your problem like Exadata instead of good d…

 

SWI_Database

4/15/14 19:16

MT @SWI_Database: Q2: What is the number one performance tuning myth? #datachat

 

SQLRockstar

4/15/14 19:16

RT @kylehhailey: Wow, yes, One of the  most disturbing performance issues is coders joining tables in application code instead of the datab…

 

connor_mc_d

4/15/14 19:16

re: outside the database, similar issue is “oh, we’ll cache all that”.  Performance maybe…corruption as well 🙂 #datachat

 

ChrisAntognini

4/15/14 19:17

Number one performance tuning myth? Performance doesn’t have to be planned. #datachat

 

galobalda

4/15/14 19:17

RT @kylehhailey: 2. Using databases without leveraging set operations leads to single row inserts followed by commits #datachat

 

pioro

4/15/14 19:17

+1 RT @ChrisAntognini: Number one performance tuning myth? Performance doesn’t have to be planned. #datachat

 

fidelinho

4/15/14 19:18

RT @ChrisAntognini: Number one performance tuning myth? Performance doesn’t have to be planned. #datachat

 

galobalda

4/15/14 19:18

RT @ToonKoppelaars: Shipping all data out to a JVM for processing, can be avoided by processing data in PLSQL VM: often lot faster  #datach…

 

SWI_Database

4/15/14 19:18

RT @ChrisAntognini: Number one performance tuning myth? Performance doesn’t have to be planned. #datachat

 

arupnanda

4/15/14 19:18

#datachat time for another question @SWI_Database 🙂

 

jdanton

4/15/14 19:18

RT @kylehhailey: Probably the greatest performance myth is that there are silver bullets to fix your problem like Exadata instead of good d…

 

galobalda

4/15/14 19:18

RT @kylehhailey: 2. Single row operations are like going to the grocery store buying one item, coming home, then going back #datachat

 

SWI_Database

4/15/14 19:18

@arupnanda coming right up! Q3 #datachat

 

SWI_Database

4/15/14 19:19

Q3: What do you look for first when performance tuning? #datachat

 

kylehhailey

4/15/14 19:19

@connor_mc_d  read two Bitcoin sites lost over $100M due to lack of ACID? hackers withdrew 1000s of times as fast as they could #datachat

 

galobalda

4/15/14 19:19

RT @ToonKoppelaars: Nr 1 myth for me: processing data outside dbms, relieves dbms. It actually does the opposite #datachat

 

EricGrancher

4/15/14 19:19

Not understanding the difference between latency and throughput (for IO, for SQL, …) #datachat

 

arupnanda

4/15/14 19:19

#datachat A3 wait events ar ethe first thing you should look

 

kylehhailey

4/15/14 19:19

A3 According to the theory of constraints “Any improvement not made at the constraint is an illusion.” #datachat

 

kylehhailey

4/15/14 19:19

The first step in tuning is to find the constraint. To find the constraint you need to find where the most time is being spent #datachat

 

ToonKoppelaars

4/15/14 19:19

Alse denormalizing for performance: huge myth.  #datachat

 

SWI_Database

4/15/14 19:19

RT @arupnanda: #datachat A3 wait events ar ethe first thing you should look

 

connor_mc_d

4/15/14 19:20

A3 the user.  They define the performance problem, then you go from there #datachat

 

SQLRockstar

4/15/14 19:20

RT @kylehhailey: The first step in tuning is to find the constraint. To find the constraint you need to find where the most time is being s…

 

SQLChicken

4/15/14 19:20

RT @kylehhailey: The first step in tuning is to find the constraint. To find the constraint you need to find where the most time is being s…

 

kylehhailey

4/15/14 19:20

Forgot about that one ! THe EAV strikes again http://t.co/6Oh80r35QZ @ToonKoppelaars #datachat

 

SWI_Database

4/15/14 19:21

RT @connor_mc_d: A3 the user.  They define the performance problem, then you go from there #datachat

 

ddelmoli

4/15/14 19:21

@SWI_Database a3 – look for mismatches between query result size, work performed by DB and expectations #datachat

 

SWI_DatabaseDean

4/15/14 19:21

#datachat A3- first thing is to understand the performance issue and the effects, then constraints/waits

 

EricGrancher

4/15/14 19:21

A1: Not understanding the difference between latency and throughput (for IO, for SQL, …) #datachat

 

kylehhailey

4/15/14 19:21

I like this: as Kellyn Pot’vin said, “if your not tuning for time you are waisting time” #datachat

 

SWI_Database

4/15/14 19:21

RT @ddelmoli: @SWI_Database a3 – look for mismatches between query result size, work performed by DB and expectations #datachat

 

ToonKoppelaars

4/15/14 19:21

Profiling R. The response time. Then target biggest contributor.  #datachat

 

pioro

4/15/14 19:22

#datachat find a bottleneck, profile it and reduce a work which can be avoid

 

SWI_Database

4/15/14 19:22

RT @pioro: #datachat find a bottleneck, profile it and reduce a work which can be avoid

 

kylehhailey

4/15/14 19:22

First thing I use are Graphical tools that visualize quantitative information powerfully  showing the  constraints in Oracle #datachat

 

SWI_DatabaseDean

4/15/14 19:22

#datachat A3 if you don’t fully understand the issue, you could bark up the wrong tree

 

EricGrancher

4/15/14 19:22

RT @connor_mc_d: A3 the user.  They define the performance problem, then you go from there #datachat

 

SWI_Database

4/15/14 19:22

RT @SWI_DatabaseDean: #datachat A3 if you don’t fully understand the issue, you could bark up the wrong tree

 

kylehhailey

4/15/14 19:23

To find the constraint in Oracle, I use Average Active Session load in Top Activity Screen in OEM or a tool like SolarWinds DPA #datachat

 

ddelmoli

4/15/14 19:23

A3 – find ways to produce same business results by having the DB do less work #datachat

 

SWI_Database

4/15/14 19:23

RT @kylehhailey: To find the constraint in Oracle, I use Average Active Session load in Top Activity Screen in OEM or a tool like SWI_Database Ig…

 

kylehhailey

4/15/14 19:23

To find the constraint without Graphic tools I look at Top 5 Timed Events in AWR and/or run ashrpt.sql #datachat

 

connor_mc_d

4/15/14 19:23

dont jump into the tech right away.  “Work the problem” #datachat

 

EricGrancher

4/15/14 19:23

A3: based on what the user indicates is slow, what has changed? (from the user and the system perspective) #datachat

 

arupnanda

4/15/14 19:24

#datachat @ddelmoli or, to clarify, make the db do less “unnecessary” work.

 

ChrisAntognini

4/15/14 19:24

RT @connor_mc_d: dont jump into the tech right away.  “Work the problem” #datachat

 

arupnanda

4/15/14 19:25

#datachat if the issue is occuring right now, I prefer to look in v$session directly instead of AWR or ASH reports.

 

SWI_DatabaseDean

4/15/14 19:25

#datachat A3 @arupnanda @ddelmol Love those answers!

 

ddelmoli

4/15/14 19:25

If you can’t measure or understand the work you DB does for each request, you can’t tune it #datachat

 

SWI_Database

4/15/14 19:25

RT @ddelmoli: If you can’t measure or understand the work you DB does for each request, you can’t tune it #datachat

 

arupnanda

4/15/14 19:25

RT +1 @ddelmoli: If you can’t measure or understand the work you DB does for each request, you can’t tune it #datachat

 

pioro

4/15/14 19:25

RT @ddelmoli: If you can’t measure or understand the work you DB does for each request, you can’t tune it #datachat

 

netofrombrazil

4/15/14 19:26

@kylehhailey If you can’t convince them, confuse them (Truman’s Law) #datachat

 

EricGrancher

4/15/14 19:26

me: ASH RT @arupnanda: #datachat if the issue is occuring right now, I prefer to look in v$session directly instead of AWR or ASH reports.

 

kylehhailey

4/15/14 19:26

RT +1 @ddelmoli: If you can’t measure or understand the work you DB does for each request, you can’t tune it #datachat @ddelmoli

 

SWI_Database

4/15/14 19:26

Ready for Q4 everyone? #datachat

 

arupnanda

4/15/14 19:26

RT “@netofrombrazil: @kylehhailey If you can’t convince them, confuse them (Truman’s Law) #datachat” Now that’s a real mccoy answer 🙂

 

ChrisAntognini

4/15/14 19:26

RT @ddelmoli: If you can’t measure or understand the work you DB does for each request, you can’t tune it #datachat

 

SWI_Database

4/15/14 19:27

Q4: What 12c features do you see as having the greatest performance impact? #datachat

 

nlitchfield

4/15/14 19:27

@kylehhailey I almost always ask. What specifically is slow. What does good look like.  How else do I know it’s the right problem #datachat

 

arupnanda

4/15/14 19:27

#datachat q4 hybrid histograms

 

netofrombrazil

4/15/14 19:27

@arupnanda @kylehhailey My favorite one 🙂 #datachat – Well, that is no discussion right, it’s all in the queries the problem 🙂

 

ddelmoli

4/15/14 19:27

If you withhold resource consumption data from devs they’ll write code w/o regard to perf impact #datachat

 

pioro

4/15/14 19:27

RT @nlitchfield: @kylehhailey I almost always ask. What specifically is slow. What does good look like.  How else do I know it’s the right …

 

kylehhailey

4/15/14 19:28

A4 EM Express where developers can actually see the impact of their code #datachat

 

kylehhailey

4/15/14 19:28

It is not fair when a DBA sees a performance problem in OEM top activity and then yells at a developer who is flying blind  #datachat

 

ddelmoli

4/15/14 19:28

RT @nlitchfield: @kylehhailey I almost always ask. What specifically is slow. What does good look like.  How else do I know it’s the right …

 

arupnanda

4/15/14 19:28

#datachat just to clarify an q4: if I were to choose exactly one 12c feature, it would be hybrid histogram. There are others.

 

SWI_Database

4/15/14 19:28

RT @kylehhailey: It is not fair when a DBA sees a performance problem in OEM top activity and then yells at a developer who is flying blind…

 

SWI_Database

4/15/14 19:28

RT @arupnanda: #datachat just to clarify an q4: if I were to choose exactly one 12c feature, it would be hybrid histogram. There are others.

 

ChrisAntognini

4/15/14 19:29

Adaptive execution plans RT @SWI_Database: Q4: What 12c features do you see as having the greatest performance impact? #datachat

 

kylehhailey

4/15/14 19:29

Other things I’m looking forward to in 12c are Real Time ADDM, Compare period ADDM and ASH Analytics #datachat

 

arupnanda

4/15/14 19:29

#datachat q4 online partition global index building and partition operation are othjers

 

ddelmoli

4/15/14 19:29

RT @kylehhailey: It is not fair when a DBA sees a performance problem in OEM top activity and then yells at a developer who is flying blind…

 

pioro

4/15/14 19:29

RT @arupnanda: #datachat q4 online partition global index building and partition operation are othjers

 

SWI_DatabaseDean

4/15/14 19:30

#datachat A4 I think 12c advanced network compression could be big in the days of #cloud

 

ToonKoppelaars

4/15/14 19:30

a4 I like temporary undo. But only helpfull in specific situation. Like all features btw. #datachat

 

SWI_Database

4/15/14 19:30

RT @ToonKoppelaars: a4 I like temporary undo. But only helpfull in specific situation. Like all features btw. #datachat

 

nlitchfield

4/15/14 19:30

@kylehhailey admittedly in face of ill defined problem statements “click on the big stuff in my graphic” has a fair hit rate. #datachat

 

netofrombrazil

4/15/14 19:30

@kylehhailey It’s always the storage… the problem they need FLASH #datachat

 

arupnanda

4/15/14 19:31

#datachat @ToonKoppelaars temp undo in 12c is great for using unprotected flash storages

 

ddelmoli

4/15/14 19:31

Q4 – negative impact due to poorly thought out consolidation plans using 12c PDBs #datachat

 

nlitchfield

4/15/14 19:31

RT @kylehhailey: It is not fair when a DBA sees a performance problem in OEM top activity and then yells at a developer who is flying blind…

 

nlitchfield

4/15/14 19:31

RT @ddelmoli: If you can’t measure or understand the work you DB does for each request, you can’t tune it #datachat

 

EricGrancher

4/15/14 19:31

A4: like @ToonKoppelaars : temporary undo is great #datachat

 

SWI_Database

4/15/14 19:32

RT @kylehhailey: It’s not fair when a DBA sees a problem in OEM top activity & then yells at a developer who is flying blind  #datachat

 

netofrombrazil

4/15/14 19:32

@kylehhailey Oh no… CPU… on server…. Select * from foo and it’s not going faster… Parallel – is it only geometry right? #datachat

 

nadineCO

4/15/14 19:32

RT @kylehhailey: It is not fair when a DBA sees a problem in OEM top activity and then yells at a developer who is flying blind  #datachat

 

arupnanda

4/15/14 19:33

#datachat on Q1: mistaking that logical I/O is much better than physical I/O

 

ToonKoppelaars

4/15/14 19:33

A4 and of course all ongoing optimizer improvements. Not used in any java project btw… #datachat

 

netofrombrazil

4/15/14 19:34

@kylehhailey I found – it’s fragmentation in the DB. I knew the DB was the problem. Can we run DEFRAG? alter db defrag start? #datachat

 

kylehhailey

4/15/14 19:34

A1. “Refactoring SQL Applications” by Stephane Faroult is great for understanding and fixing bad database code access design  #datachat

 

SWI_Database

4/15/14 19:34

Moving to Q5 next, ok? #datachat

 

SWI_Database

4/15/14 19:34

Q5: How does virtualization impact performance tuning? #datachat

 

arupnanda

4/15/14 19:35

#datachat #a5 it makes assessment and tuning much more difficult

 

SWI_DatabaseDean

4/15/14 19:35

#datachat more things to go wrong, extra layers of abstraction

 

ToonKoppelaars

4/15/14 19:35

RT @arupnanda: #datachat #a5 it makes assessment and tuning much more difficult

 

kylehhailey

4/15/14 19:35

A5 Virtualization massively helps performance tuning of the IT department as a whole #datachat

 

kylehhailey

4/15/14 19:36

@RealGeneKim, author of “The Phoenix Project” points out, provisioning environments is often  the biggest constraint in IT today #datachat

 

kylehhailey

4/15/14 19:37

Recommend “The Phoenix Project” to DBAs and everyone in IT to understand finding the constraints and optimizing them #datachat @RealGeneKim

 

SWI_DatabaseDean

4/15/14 19:37

#datachat I was thinking of perf tuning from DBA perspective, but from IT perspective I agree with @kylehhailey

 

ddelmoli

4/15/14 19:37

DBAs who blame virtualization for problems are just as bad as devs blaming the DB – demand the perf data you need #datachat

 

ToonKoppelaars

4/15/14 19:38

Virtualization requires even more explaining of what could be the cause. Great for dev test. Maybe not so for acc prod? #datachat

 

EricGrancher

4/15/14 19:38

A5: for the Oracle database, virtualisation is a layer which a challenge as it is not instrumented from the database point of view #datachat

 

kylehhailey

4/15/14 19:38

Happy to say that in most of my experience VMware overhead at a database performance level is usually negligible #datachat

 

kylehhailey

4/15/14 19:38

On the other hand, since multiple VMs can run on the same shared hardware resouce, other VMs can cause problems #datachat

 

SQLRockstar

4/15/14 19:38

RT @kylehhailey: Happy to say that in most of my experience VMware overhead at a database performance level is usually negligible #datachat

 

ddelmoli

4/15/14 19:38

RT @EricGrancher: A5: for the Oracle database, virtualisation is a layer which a challenge as it is not instrumented from the database poin…

 

kylehhailey

4/15/14 19:38

Finding performance problems due to other VMs or the saturation of resources on ESX can be extremely difficult #datachat

 

SQLRockstar

4/15/14 19:38

RT @kylehhailey: Recommend “The Phoenix Project” to DBAs and everyone in IT to understand finding the constraints and optimizing them #data…

 

SWI_DatabaseDean

4/15/14 19:38

#datachat the noisy neighbor syndrome

 

kylehhailey

4/15/14 19:39

SWI_Database’s tools that provide direct insight into VM performance correlated with Oracle performance are exciting #datachat

 

pioro

4/15/14 19:39

from other hand virtualization can help with scale out system automatically – of course if system is horizontally scaled #datachat

 

SQLRockstar

4/15/14 19:39

RT @kylehhailey: SWI_Database’s tools that provide direct insight into VM performance correlated with Oracle performance are exciting #datachat

 

SWI_Database

4/15/14 19:39

RT @pioro: from other hand virtualization can help with scale out system automatically – of course if system is horizontally scaled #datach…

 

SWI_Database

4/15/14 19:40

RT @kylehhailey: SWI_Database’s tools that provide direct insight into VM performance correlated with Oracle performance are exciting #datachat

 

kylehhailey

4/15/14 19:40

In general, despite the tuning investigation challenges, virtualization is awesome for improving IT and systems performance #datachat

 

kylehhailey

4/15/14 19:40

For provisioning environments, VMware, Puppet, Chef etc have alleviated enormous constraints on provisioning environments #datachat

 

pioro

4/15/14 19:40

Virtualization (actually overprovisioning) was a root cause of strange system issues with clock running backward from time to time #datachat

 

kylehhailey

4/15/14 19:41

The remaining major constraint in IT is provisioning copies of databases and data for  environments #datachat

 

pioro

4/15/14 19:41

RT @kylehhailey: For provisioning environments, VMware, Puppet, Chef etc have alleviated enormous constraints on provisioning environments …

 

kylehhailey

4/15/14 19:41

Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualizing data #datachat

 

pioro

4/15/14 19:41

RT @kylehhailey: The remaining major constraint in IT is provisioning copies of databases and data for  environments #datachat

 

SWI_Database

4/15/14 19:41

RT @kylehhailey: Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualiz…

 

ddelmoli

4/15/14 19:42

Re-provisioning, re-building and building simulated prod envs are under appreciated benefits of virt #datachat

 

OracleAlchemist

4/15/14 19:42

RT @ChrisAntognini: Number one performance tuning myth? Performance doesn’t have to be planned. #datachat

 

kylehhailey

4/15/14 19:42

RT @ddelmoli: Re-provisioning, re-building and building simulated prod envs are under appreciated benefits of virt #datachat

 

OracleAlchemist

4/15/14 19:42

RT @kylehhailey: As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

 

OracleAlchemist

4/15/14 19:43

Virtualization is another layer that MUST be taken into account. RT @SWI_Database Q5: How does virtualization impact performance tuning? #datachat

 

SWI_DatabaseDean

4/15/14 19:44

#datachat many people incorrectly think they will no longer be supported once virtualized, hence they don’t try it

 

SWI_Database

4/15/14 19:44

Q6: How often do poor configuration options lead to poor performance? #datachat

 

OracleAlchemist

4/15/14 19:44

Like all things, you must decide whether the benefits of virtualization outweigh architecture considerations. Many times they do. #datachat

 

kylehhailey

4/15/14 19:45

A6 Configuration performance problems are pretty common but usually get ironed out quickly by competent DBAs  #datachat

 

kylehhailey

4/15/14 19:45

Typical configuration issues: redo logs too small, PGA too small , Parallel query over configured, SQL plans changing #datachat

 

nimbosa

4/15/14 19:45

RT @ddelmoli: Re-provisioning, re-building and building simulated prod envs are under appreciated benefits of virt #datachat

 

arupnanda

4/15/14 19:45

#datachat A6: almost always

 

kylehhailey

4/15/14 19:46

Things that don’t get ironed out are applications that connect per SQL or Session pools that expand when performance slows down #datachat

 

pioro

4/15/14 19:46

#datachat using defaults to help vender to setup environment is a bad idea

 

ToonKoppelaars

4/15/14 19:46

I see poorly configured redo log more than I would like to.  #datachat

 

arupnanda

4/15/14 19:46

#datachat A6: poor config is always a result of not understanding the requirements.

 

ddelmoli

4/15/14 19:47

RT @arupnanda: #datachat A6: poor config is always a result of not understanding the requirements.

 

OracleAlchemist

4/15/14 19:47

Q6: Here’s my long response: Poor conditions will ALWAYS HAPPEN at least once. Poor execution will too. And poor mistakes. #datachat

 

ToonKoppelaars

4/15/14 19:47

Also too small sequence cache sizes.  #datachat

 

ChrisAntognini

4/15/14 19:47

Today, much less frequently than 10 years ago. RT @SWI_Database: Q6: How often do poor configuration options lead to poor performance? #datachat

 

EricGrancher

4/15/14 19:47

RT … or not taking them into account (like copy / paste) @arupnanda: #datachat A6

 

ddelmoli

4/15/14 19:48

RT @ToonKoppelaars: Also too small sequence cache sizes.  #datachat

 

OracleAlchemist

4/15/14 19:48

Q6: Many things can & will go wrong. Poor config options guarantee bad or exacerbated outcomes for the life of the environment. #datachat

 

SWI_Database

4/15/14 19:48

Great content everyone! We’d like now to allow some time for open questions #datachat

 

SWI_Database

4/15/14 19:49

Open-Q: Would you have questions for all the experts here? Ask now if you would #datachat

 

ToonKoppelaars

4/15/14 19:49

And let’s not get started about RAC and lack of data distribution/partitioning.  #datachat

 

arupnanda

4/15/14 19:50

@EricGrancher #datachat +1

 

SWI_Database

4/15/14 19:50

RT @OracleAlchemist When is it worth sacrificing good design for perf/UX? How important is perf compared to security & stability? #datachat

 

arupnanda

4/15/14 19:51

#datachat @OracleAlchemist like everything else, the trick is to find the balance.

 

kylehhailey

4/15/14 19:51

wow – that’s a big one ! #datachat @OracleAlchemist

 

ddelmoli

4/15/14 19:52

“@arupnanda: #datachat @OracleAlchemist like everything else, the trick is to find the balance.” +1

 

pioro

4/15/14 19:52

RT @ddelmoli: “@arupnanda: #datachat @OracleAlchemist like everything else, the trick is to find the balance.” +1

 

OracleAlchemist

4/15/14 19:52

@arupnanda Of course. But there is no formula…any personal stories of the tug of sides? #datachat

 

SWI_Database

4/15/14 19:52

RT @ddelmoli: “@arupnanda: #datachat @OracleAlchemist like everything else, the trick is to find the balance.” +1

 

ToonKoppelaars

4/15/14 19:53

“Good design” is a very ambiguous/subjective term in our profession 😉  #datachat

 

SWI_Database

4/15/14 19:53

… RT @OracleAlchemist: @arupnanda Of course. But there is no formula…any personal stories of the tug of sides? #datachat

 

Lawjb

4/15/14 19:53

RT @kylehhailey: It is not fair when a DBA sees a performance problem in OEM top activity and then yells at a developer who is flying blind…

 

OracleAlchemist

4/15/14 19:54

When you have time, here’s a fun old story of bad tuning through temporary fixes. The Sin of Band-Aids http://t.co/1BHz57b5ls #datachat

 

kylehhailey

4/15/14 19:54

Asked if regretted using Ruby, slideshare founder, said they wouldn’t have succeeded w/o it – performance sacrificed for dev speed #datachat

 

j9soto

4/15/14 19:55

RT @kylehhailey: Things that don’t get ironed out are applications that connect per SQL or Session pools that expand when performance slows…

 

arupnanda

4/15/14 19:56

#datachat glad to have participated. have a great day, y’all.

 

SWI_Databasemindy

4/15/14 19:56

@SWI_Database @OracleAlchemist #datachat NEVER 🙂 Design RULZ. Seriously, the design should support the requirements.

 

ddelmoli

4/15/14 19:56

Good designs are stable, secure and have good performance – only question is how much effort to put into each one #datachat

 

kylehhailey

4/15/14 19:56

My nemesis EAV is useful for early stage development but has to be replaced by relational at load – sacrifice dev speed for design #datachat

 

ToonKoppelaars

4/15/14 19:57

RT @ddelmoli: Good designs are stable, secure and have good performance – only question is how much effort to put into each one #datachat

 

OracleAlchemist

4/15/14 19:57

@SWI_Databasemindy @SWI_Database I really like that answer. If design doesn’t support requirements, then it’s not a design…it’s a liability. #datachat

 

SWI_Database

4/15/14 19:57

With a few minutes left, we wanted to thank all of you for participating! #datachat especially @kylehhailey @SWI_DatabaseDean for co-hosting

 

SWI_Database

4/15/14 19:58

@kylehhailey had mentioned about #otw42 and we wanted ask Kyle what the future holds for this hashtag #datachat

 

connor_mc_d

4/15/14 19:59

who wouldda thought I’d be in #datachat at 3am.  The joy of afterhours change control process 🙂

 

kylehhailey

4/15/14 19:59

Thanks SWI_Database. Wanted to thank the Oaktable members who made this #datachat great. Look forward to oaktable chats in future using #otw42

 

kylehhailey

4/15/14 20:00

the hash #otw42 (Oaktable World 42 (42 is meaning of life) ) will be used for a future Oaktable #datachat flashmob 🙂

 

SWI_Database

4/15/14 20:00

RT @kylehhailey: Thx SWI_Database. Wanted to thank Oaktable mbers who made this #datachat great. Look fwd to oaktable chats in future using #otw42

 

SWI_Database

4/15/14 20:02

Thanks everyone! #datachat – Look for the summary blog coming in a few days! http://t.co/eppvEqQv74

 

agonen

4/15/14 20:06

RT @kylehhailey: the hash #otw42 (Oaktable World 42 (42 is meaning of life) ) will be used for a future Oaktable #datachat flashmob 🙂

 

ChrisAntognini

4/15/14 20:06

I agree. But “no design” is worse… RT @ToonKoppelaars: “Good design” is a very ambiguous/subjective term in our profession 😉  #datachat

 

delphix

4/15/14 20:12

RT @kylehhailey: Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualiz…

 

kylehhailey

4/15/14 20:15

RT @ddelmoli: @SWI_Database #datachat Oracle perf problems are born in dev not adequately simulating production

 

ChadCardenas

4/15/14 20:34

RT @kylehhailey: Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualiz…

 

SWI_Database

4/15/14 20:37

RT @kylehhailey: the hash #otw42 (Oaktable World 42 (42 is meaning of life) ) will be used for a future Oaktable #datachat flashmob 🙂

 

yannr

4/15/14 20:37

RT @kylehhailey: the hash #otw42 (Oaktable World 42 (42 is meaning of life) ) will be used for a future Oaktable #datachat flashmob 🙂

 

dba_jay

4/15/14 20:38

RT @ddelmoli: @SWI_Database #datachat Oracle perf problems are born in dev not adequately simulating production

 

dba_jay

4/15/14 20:40

RT @kylehhailey: Asked if regretted using Ruby, slideshare founder, said they wouldn’t have succeeded w/o it – performance sacrificed for d…

 

SWI_Database

4/15/14 20:40

@leight0nn Next time then 🙂 #datachat

 

TheJrDBA

4/15/14 20:46

RT @kylehhailey: 2. Single row operations are like going to the grocery store buying one item, coming home, then going back #datachat

 

SWI_Database

4/15/14 20:51

RT @SWI_DatabaseDean: #datachat many people incorrectly think they will no longer be supported once virtualized, hence they don’t try it

 

kylehhailey

4/15/14 20:52

“There is nothing quite so useless, as doing with great efficiency, something that should not be done at all.” Peter Drucker #datachat 🙂

 

RachaelFavela1

4/15/14 20:54

RT @kylehhailey: Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualiz…

 

OracleAlchemist

4/15/14 20:57

RT @kylehhailey: “There is nothing quite so useless, as doing with great efficiency, something that should not be done at all.” Peter Druck…

 

SWI_Database

4/15/14 20:59

RT @kylehhailey “There is nothing quite so useless, as doing w/ gr8 efficiency, smthing that should not be done at all” P. Drucker #datachat

 

SWI_Database

4/15/14 21:01

@OracleAlchemist @leight0nn I thought that was the case Steve! Leighton I agree…do repeat #datachat for the gr8 topics w/ slightly diff Qs

 

infaworld

4/15/14 21:38

Why Twitter needs #DataChat http://t.co/k5jJPu2hv9 http://t.co/daKOSH7UhO

 

InformaticaCorp

4/15/14 21:38

Why Twitter needs #DataChat http://t.co/rS7Yx6cWSt http://t.co/dQiq009XKR

 

mgilkey13

4/15/14 21:42

RT @InformaticaCorp: Why Twitter needs #DataChat http://t.co/rS7Yx6cWSt http://t.co/dQiq009XKR

 

KnowledgeBishop

4/15/14 21:44

Why Twitter needs #DataChat: http://t.co/pysZK962s9 #GovIT

 

Ravi_Shankar_

4/15/14 21:45

Talk Amongst Yourselves: Why Twitter Needs #DataChat http://t.co/fMLBmOEW3E

 

DFConferences

4/15/14 21:47

Talk Amongst Yourselves: Why Twitter Needs #DataChat: Within Government organizations, technologists are up ag… http://t.co/93y0DSOn8r

 

Biglizard13

4/15/14 22:09

RT @kylehhailey: “There is nothing quite so useless, as doing with great efficiency, something that should not be done at all.” Peter Druck…

 

IT__agent

4/15/14 22:27

Talk Amongst Yourselves: Why Twitter Needs #DataChat http://t.co/SPfoxvR0YP #IT

 

FindFreeman

4/15/14 22:30

RT @kylehhailey: It is not fair when a DBA sees a performance problem in OEM top activity and then yells at a developer who is flying blind…

 

FindFreeman

4/15/14 22:32

RT @ddelmoli: DBAs who blame virtualization for problems are just as bad as devs blaming the DB – demand the perf data you need #datachat

 

FindFreeman

4/15/14 22:34

RT @kylehhailey: Typical configuration issues: redo logs too small, PGA too small , Parallel query over configured, SQL plans changing #dat…

 

Healthy_Data

4/15/14 23:09

RT @Ravi_Shankar_: Talk Amongst Yourselves: Why Twitter Needs #DataChat http://t.co/fMLBmOEW3E

 

Ahmed_Aboulnaga

4/15/14 23:27

RT @kylehhailey: Asked if regretted using Ruby, slideshare founder, said they wouldn’t have succeeded w/o it – performance sacrificed for d…

 

hayesdrumwright

4/15/14 23:40

RT @kylehhailey: Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualiz…

 

euclid_project

4/15/14 23:54

RT @SemanticWeb: Semantic Tech’s Role in Travel in 2024 http://t.co/YDt8KSAz7U #semtech #datachat

 

richardfoote

4/16/14 0:01

Sometimes the most effective tuning you can perform is to tune user expectations. Get that right and the rest naturally follows. #datachat

 

VladoBarun

4/16/14 0:32

RT @kylehhailey: 2. Single row operations are like going to the grocery store buying one item, coming home, then going back #datachat

 

VladoBarun

4/16/14 0:32

RT @kylehhailey: Probably the greatest performance myth is that there are silver bullets to fix your problem like Exadata instead of good d…

 

VladoBarun

4/16/14 0:32

RT @kylehhailey: As Cary Milsap once said, the most common problem is people looking for the most common problem instead of measuring for i…

 

VladoBarun

4/16/14 0:34

RT @kylehhailey: It is not fair when a DBA sees a performance problem in OEM top activity and then yells at a developer who is flying blind…

 

marmentrout5

4/16/14 0:43

RT @kylehhailey: Recommend “The Phoenix Project” to DBAs and everyone in IT to understand finding the constraints and optimizing them #data…

 

haroric

4/16/14 1:31

RT @InformaticaCorp: Why Twitter needs #DataChat http://t.co/rS7Yx6cWSt http://t.co/dQiq009XKR

 

santiajoel

4/16/14 1:55

RT @kylehhailey: As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

 

santiajoel

4/16/14 1:56

RT @kylehhailey: Probably the greatest performance myth is that there are silver bullets to fix your problem like Exadata instead of good d…

 

adnanmkm

4/16/14 2:50

RT @arupnanda: #datachat on Q1: mistaking that logical I/O is much better than physical I/O

 

adnanmkm

4/16/14 2:52

RT @arupnanda: #datachat A6: poor config is always a result of not understanding the requirements.

 

samirq1

4/16/14 3:08

RT @kylehhailey: “There is nothing quite so useless, as doing with great efficiency, something that should not be done at all.” Peter Druck…

 

amritash99

4/16/14 5:25

RT @richardfoote: Sometimes the most effective tuning you can perform is to tune user expectations. Get that right and the rest naturally f…

 

amritash99

4/16/14 5:26

RT @kylehhailey: “There is nothing quite so useless, as doing with great efficiency, something that should not be done at all.” Peter Druck…

 

OyvindIsene

4/16/14 7:08

I think I just became the 10Kth follower of @SWI_Database Had to figure out where that #datachat came from

 

juanitocpv

4/16/14 7:18

RT @kylehhailey: As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

 

CloudSurgeon

4/16/14 10:34

RT @kylehhailey: Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualiz…

 

FirstLiberated

4/16/14 12:19

RT @kylehhailey: Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualiz…

 

DBAMontreal

4/16/14 19:15

RT @kylehhailey: As I once heard Cary Millsap say  “The fastest query is the query that doesn’t have to be run” #datachat

Leave a Reply