This Post Covers the Basic Overview and some Basic Concepts of Performance Tuning you must know.
This post is for beginners as well as for experienced such as DBA’s, Cloud DBA’s APPs DBA who are just started to learn performance tuning.
The nightmare of every Oracle database admin is receiving a flurry of calls from users complaining that their online performance is running slow. When systems are slow or unresponsive, Performance Tuning is one of the best ways to identify bottlenecks and ensure your infrastructure is equipped to handle increased loads
Introduction
Many who undertake SQL tuning projects look for the magic bullet in the form of a software tool for help. While those tools can help tune simple SQL statements, tuning complex ones that contain unions, subqueries, outer joins, etc, give tools problems. I have used a variety of tools and have been less than impressed.
If tools are less than adequate, how do we go about tuning SQL statements and applications? Instead of relying on tools that do not work well, I suggest that you learn how to perform SQL tuning the old-fashioned way, i.e. doing it yourself. Too many people I cross paths with in my work do not understand even the basics when it comes to SQL tuning, so the fundamental goal of this paper is to provide a process that will help you get started down the tuning path.
Many people fail when it comes to tuning, so if you can become good at it, your image and worth to your company will undoubtedly rise
Challenges
There are many challenges in a tuning project. I already said this, but it’s worth saying again – SQL tuning is difficult. To do it correctly you or the people you get on your team, need to be very familiar with many various aspects of the application. From a technical standpoint, you need to understand execution plans or how Oracle is executing the SQL and how the data is being accessed.
You also need to be familiar with SQL design concepts because sometimes tuning the SQL means rewriting it. When you are working with the end users, understand how the application is used and why they do things? Why do you fill in those fields? Why do you use this screen? Understanding the purpose of the SQL and application will help you make better decisions down the road.
- Put a Face on the Project – Many people are impatient, but SQL tuning takes time. That’s why working with other people is also about putting a face on the project. Instead of the users saying something like “I’ve been complaining about this problem for weeks and the DBA team says they’re doing something, but no one knows what.”, instead, they might say “I’ve been working with Bob from the DBA group and he definitely asks a lot of questions and seems to care about my experience.” A very different circumstance for sure.
- Large Number of SQL Statements – Another huge challenge is that there are many SQL statements and how do you know for sure you are working on the right one? I’ll talk about this in more detail, but this is also where the end users can help you focus on the correct things. Instead of worrying about 100s or 1000s of SQL statements, worry about the ones that affect this user and this screen in this application they are complaining about.
- All Statements are Different – all SQL statements are different. Just because you solved the last problem in 30 minutes by tuning a certain way does not mean the next project is that easy or can be tuned the same way.
- Lack of Priority – Some companies, in general, do not care about performance. As long as it gives the correct results, they don’t care as much about how long it takes to get the results.
- Indifference – some users get used to the way things work – “I always press this button the first thing in the morning and then go get coffee, because I know it takes an hour.”. Substandard performance becomes a way of life and sometimes people get upset when the application becomes faster. They can’t walk around interrupting everyone else with what their kids did yesterday.
- Never Ending Task – there always seems to be the next problem. Once you tune something, other people want their application tuned as well, but this is a good thing for you.
Also read: Database statistics on AWR Report.
Process
The performance tuning process centers around four main steps:
- Identify – pick the correct SQL statement to tune and avoid wasting your time.
- Gather – gather the proper information that will help you make the best tuning decisions.
- Tune – tune the SQL statement based on the gathered information.
- Monitor – ensure the SQL statement is tuned and stays tuned. Monitoring also helps you understand the exact benefits achieved. This step also starts the process over again and helps you identify the next project.
Please stay tuned for our coming post on performance Tuning we will be covering how to Identify – Which SQL to Tune
These are just a glimpse of some of the factor which needs to be taken care of while considering the performance of your database.
You will get to know all of this and deep-dive into each concept related to Performance Tuning once you will get enrolled in our Performance Tuning On-Premise & Cloud
Another question, which might come to your mind, What are all the things you will get when you enrolled!!
We are glad to tell you that:
Another question, which might come to your mind, What are all the things you will get when you enrolled!!
We are glad to tell you that:
Things you will get!!
- Live Instructor-led Online Interactive Sessions
- FREE unlimited retake for next 1 Years
- FREE On-Job Support for next 1 Years
- Training Material Presentation with Hands-on Lab Exercises mentioned
- Recording of Live Interactive Session for Lifetime Access
- 100% Money Back Guarantee (If you attend sessions, practice and don’t get results, We’ll do full REFUND, check our Refund Policy)
Next Task
Click on the Image below and Get your FREE Guide to Start your journey with Performance Tuning. Learn from Beginner to Performance Tuning Guide and step forward with no doubts.
Erptree Oracle says
This is both interesting and relevant. We see this sort of thing regularly. You are doing a great job.