photo by Tom Woodward
Summary of responses to questions from Solarwinds #datachat back in the fall.
1. Why do performance issues happen?
Performance issues happen mainly because of bad design. Performance issues occasionally happen because of hardware undersizing (IO, CPU, Memory, Network) but that’s the 20 (or less) of 80/20. At IOUG a last year someone tweeted out a slide that said 80% of performance problems were from IO and Kevin Closson tweeted the other 80% are from bad SQL 😛 . I thought that was pretty funny. In other words bad SQL , which is part of bad design, is the cause of most performance problems and not I/O per say. Performance design problems arise primarily due to unnecessary work . As I once heard Cary Millsap say “The fastest query is the query that doesn’t have to be run.”
#1 problem is impedance between Object Oriented design as well as functional programming verse relational set operation paradigm
My top 5 perf issues
1. Entity Attribute Value schema, aka the stuff table.
One of the worst performance designs is Entity Attribute Value schemas, aka stuff table, which is ironically the design of many noSQL implementations .
2. single row operations
Using databases without leveraging set operations leads to single row inserts followed by commits. Single row operations are like going to the grocery store buying one item, coming home, then going back
When fetching data, its much more efficient to multiple rows at a time in an array interface rather than single row “Refactoring SQL Applications” by Stephane Faroult is great for understanding and fixing bad database code access design flip-side corollary: batching too much, collecting hour of data before inserting thus application data is up to hour out of date
3. joining tables in code instead of SQL
3. One of the most disturbing performance issues is coders joining tables in application code instead of the database
4. using count(*) to define loop iterations or array sizes
using count(*) to define things like how make loops to make instead of just looping over a cursor
running count(*) to find out how many iterations to make in a loop running count(*) to find out how big an array to make
5. flooding connection pools with new sessions when the application perceives a slowdown
when using a connection pool, increasing the sessions when performance goes down which usually makes performance worse
2. What is the number one performance tuning myth?
Probably the greatest performance myth is that there are silver bullets to fix your problem like Exadata instead of good design As Cary Milsap once said, the most common problem is people looking for the most common problem instead of measuring for it myth: 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 myth: better yet that BCHR is the myth that adding CPU, faster disks and hardware will make performance scale
3. What do you look for first when performance tuning?
According to the theory of constraints “Any improvement not made at the constraint is an illusion.” 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
As Kellyn Pot’vin said, “if your not tuning for time you are waisting time” Graphical tools that visualize quantitative information are the most powerful way to find the constraints in Oracle To find the constraint in Oracle, I use Average Active Session load in Top Activity Screen in OEM or a tool like Confio Ignite To find the constraint without Graphic tools look at Top 5 Timed Events in AWR and/or run ashrpt.sql
4. What 12c features do you see as having the greatest performance impact?
EM Express where developers can actually see the impact of their code 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 EM Express can give developers safe visibility into performance impact and load their code puts on the system Other things I’m looking forward to in 12c are Real Time ADDM, Compare period ADDM and ASH Analytics http://www.aioug.org/sangam12/Presentations/20159.pdf http://allthingsoracle.com/oracle-database-12c-new-features-part-i/ http://allthingsoracle.com/oracle-database-12c-new-features-part-2/
5. How does virtualization impact performance tuning?
Virtualization massively helps performance at the level of a company RealGeneKim, author of “The Phoenix Project” points out, provisioning environments is the biggest constraint in IT today Highly recommend “The Phoenix Project” to DBAs and everyone in IT to understand finding the constraints and optimizing them For provisioning environments, VMware, Puppet, Chef etc have alleviated enormous constraints on provisioning environments
The remaining major constraint in IT is provisioning copies of databases and data for environments Delphix eliminates the enormous bureaucracy and resource constraints of provisioning database and data copies by virtualizing data
Performance of the company as a whole is orders of magnitude more important the performance impact of virtualization on a database Happy to say though that in my experience VMware overhead at a database performance level is usually negligible On the other hand, since multiple VMs can run on the same shared hardware resource, other VMs can cause problems Finding performance problems due to other VMs or the saturation of resources on ESX can be difficult Confio’s tools that provide direct insight into VM performance correlated with Oracle performance are exciting
6. How often do poor configuration options lead to poor performance?
Configuration performance problems are pretty common but usually get ironed out quickly by competent DBAs Typical configuration issues: redo logs too small, PGA too small , Parallel query over configured, SQL plans changing
Comments