This post covers Overview of four generic processes you need to take care in order to enhance your database performance.
It is a follow – up of our previous post on Introduction to Performance Tuning In Oracle Database where we have covered things like the introduction to Performance tuning, various challenges faced during performing tuning task and much more
This post is for beginners as well for advanced who are working as DBA’s, Cloud DBAs, Apps DBA and spending much of the time in tuning their databases.
In the Previous post we have discussed that, 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 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.
In this post, we will be revolving around the first process which is Identify
Identify Which SQL to Tune?
Once you get back to the office and want to tune something, where do you start? Don’t just pick one that looks interesting, and do have some method to choose the SQL. The SQL statement could come from a discussion with users and understanding their complaints. It may stem from a batch job that continues to run longer and longer. It could also come from tracing a session because of user complaints. Have some reason for tuning, even if it’s because the state is the number one SQL in the database from a wait time perspective, or it does the most logical I/O (LIO) on a daily basis. You may notice your application performs a lot of full table scans and from that, you determine the top SQL statements affected by the problem. Maybe it’s a known poor performing statement that comes from the developer asking your help
(be careful, as the top SQL statement from a developer may not be the one affecting your end users the most). Whatever it is, have a method for picking the SQL statements. Use the DB Time based method, Database time is total time spent by user processes either actively working or actively waiting in a database call. The best tool to analyze this is the Oracle ASH Analytics and Real-Time SQL Monitoring pages of Enterprise Manager or Enterprise Manager Express.
Identify End to End View
The end-to-end view is to think of the performance of the application from the web browser or client application, through the application server and to the database. That is technically correct, but from a business perspective, you should also know the SQL and application end-to-end. I encourage you to understand what the SQL is used for, why does the business need to know this information, how often is this data needed and who consumes the information. It’s helpful (I think it should be a requirement) to get the big picture when jumping into a tuning project. When the user complains, how do you know the problem is with the database and a SQL statement? The problem could be in the Java application tier or the Oracle E-Business forms tier. Even if you tuned the worst performing SQL statement for that process, you may not make that much of a perceived difference if only 10% of the total time was spent in the database. However, if you know that 90% of the time is spent in the database, and most of that time is spent executing a specific SQL statement performing a full table scan you can start to make predictions about the performance gains. To get this type of information you will probably need a tool of some sort, but there are also ways to get this information via debugging, logging times to files, the instrumenting code among many others.
Identify Database Time View
When SQL statements execute, Oracle has instrumented their code to give information about where the SQL statement is spending time. If it runs for 3 minutes, where did it spend its time? This is where v$session and v$session_wait give those clues but ASH Analytics gives you the answers and visual depiction of them. Speaking of DB Time, here is a quick test. Which of these scenarios is worse?
SQL Statement 1
- executed 1000 times
- made the end users wait for 10 minutes
- waited 99% of the time on “db file sequential read”.
SQL Statement 2
- executed 1 time
- made the end user wait for 10 minutes
- waited 99% of its time on a locking problem
The answer is that both are equally bad because they both made the end user wait for 10 minutes. End users don’t care what they wait for, only that it took 10 minutes. SQL statement 2 may be harder to tune because locking problems are typically application design issues, but both are equal in the eyes of the user. However, one caveat is that if you are faced with two tuning projects like this, Oracle ADDM is there to rescue which gives you a DB Time based quantification on where you should focus to get the maximum improvements. But also apply your own logic where in this case, focusing on SQL Statement 1 first may be easier to get results for someone in a shorter amount of time.
So far, we have identified:
- The users or business impact of the performance issue
- An end-to-end view of the process that specifies the layer of the application where performance is suffering
- SQL statements being executed in the database and the ones (there may be more than one) causing the problems.
So, we now have identified some things to work on and these statements are not typically
“select * from table1 where col1 = X”, they are usually much more difficult.
They may be pages long, include subqueries, unions, not exists and all sorts of things. The first step is to simplify. Break the SQL down into manageable pieces that can be more easily comprehended. Complex SQL statements may become 5 smaller SQL components that are simpler. Tune each of these separately. If you have 5 statements all unioned together, tune each statement separately. If views are used, get the definitions and tune the views separately as well. If synonyms are used, know where they point.
This is all about Identify, please stay tuned for our next blog where I will be covering the next Performance tuning process which is Gather
Do You Know,
Performance Tuning can be most interesting and at the same time most complex task for Oracle DBAs?
Want To Make it easy?
Click down the image below and download your FREE Guide on Beginners Guide To Performance Tuning that helps you with various methods to increase the performance of your database.