Target slow application speeds in Oracle and SQL

The scoop: 

If you want a car to go faster, what’s your plan to maximize its performance?  Sure, you could buy a bigger engine – it makes sense to think the larger the engine, the faster the car. But that’s an expensive upgrade. And is your car even able to handle the engine; the higher speeds, the higher fuel consumption? What do you need to use the power for, racecar track driving or just getting around the slowpokes on I95? Are there other tools that can help your car go faster, or is there anything currently slowing it down? As you can see, “bigger is better” can’t always be the automatic mantra. It’s more complicated than that. 

Databases are even more complex. If you struggle with slow application speed, knowing why is half the battle. Do you have monitoring and diagnostic tools, automated or manual, but no team to implement them or make sense of the wealth of information they provide? Do you have the manpower but not the experience or knowledge of how to pull the most important data from thousands of performance datapoints that can be monitored? Do you have expertise but no tools? Fixing slow application speeds does not come with a one-size-fits-all solution. Here are a few options you might want to explore whether you use Oracle, SQL, or another service and toolset.  

General optimization for faster application speed (application neutral): 

We’re agnostic – we'll work with what you’ve got. Are things working well with what you’ve got? Stay on top of the performance curve for whatever platforms, tools, etc you’re using by: 

• Creating crucial and optimal indexes 
• Using a sufficiently powerful cpu (core processing unit) in your physical systems and checking i/o speed of I/O subsystems it uses 
• Making sure your hardware has enough memory to allow the various caching capabilities of your database platform to optimize access to frequently used data 
• Working with ssd disks and/or appropriate cloud storage options 
• Staying up to date with latest versions of your database 
• Investigating usage hotspots and bottlenecks 
• Deploying monitoring solutions that alert you when issues arise or performance thresholds are not being met 
• Investigating openly instead of immediately blaming database queries 

Oracle best practices: 

• Review issues between client machine and application server 
• Minimize full table scans 
• Maintain Optimum CPU Utilization Levels  
• Review resource-intensive queries 
• Use automated diagnostic and tuning tools that you may be paying expensive licensing costs for anyway 
• Gather optimizer statistics specific to usage and processing needs 

SQL best practices: 

• Use optimization tools optimize queries 
• Troubleshoot slow-running queries 
• Keep up with defragmentation 
• Assign optimal data types and optimize indexes 
• Troubleshoot query performance difference between database application and SSMS  

Cloud vs on premise: 

Does being in the Cloud make a difference for slow application speed? Not necessarily, but issues can potentially be handled differently. You have more power with a physical on premise server - you can walk down the hall, pop in another CPU on a motherboard, and reboot. But if your application doesn’t understand how to parallel process and use that CPU power, your database still might not run as well as it could. A poorly built database is a poorly built database just like a poorly built car won’t magically go faster just because you stick in a bigger engine. For Cloud, there’s more and less flexibility. You’re not responsible for any of the hardware, and have limited physical control, but with a few button clicks, you can move from class 1 to class 5 CPU or I/O configuration without a second thought. It’s all about how much you and your team want to set up or automate, and how strong your database is in the first place.  

Solvaria’s got you covered: 

If you’re worried we just gave away all our secrets, rest assured that our DBAs will still be in business. We add value with our experience – give us thousands of running queries and we’ll let you know which five are most negatively impacting your business. We’ll sort out what times of day are best or worst for running something intensive. If the tools you’ve purchased are working for you, or if they’re wasting money. How to use the tools you have. What tools to buy, if you need a direction and don’t have any automated systems.  

We usually start with a new company by collecting information on infrastructure performance – either you have this data already pulled with what resources you have, or we set up shop and get it done ourselves. Then we pull out the most relevant pieces of information, decide what’s most important to fix, and come up with solutions from there.  

Interested? We can help you out.