Performance tuning is a broad and somewhat complex topic area when it comes to Oracle databases. Two of the biggest questions faced by your average DBA concern where to start and what to do. All you may know is that someone (a user) reports a problem about a slow or poor performing application or query. Where do you even begin to start when faced with this situation?
Oracle's Approach to Tuning
For anyone who has taken the Performance Tuning exam for Oracle8i certification, one of the testable areas dealt with Oracle's Tuning Methodology. Oracle's emphasis on this particular methodology changed when Oracle9i was released. The approach has gone from top-down in 8i to that of following principles in 9i/10g. Neither methodology is absolute as each has its advantages and disadvantages. In Oracle8i, the steps consisted of the following:
Tuning the Business Rules
2. Tuning the Data Design
3. Tuning the Application Design
4. Tuning the Logical Structure of the Database
5. Tuning Database Operations
6. Tuning the Access Paths
7. Tuning Memory Allocation
8. Tuning I/O and Physical Structure
9. Tuning Resource Contention
10. Tuning the Underlying Platform(s)
With Oracle9i's principle-based approach, the principles, in order of priority, are:
Define the problem clearly and then formulate a tuning goal.
Examine the host system and gather Oracle statistics.
Compare the identified problem to the common performance problems identified by Oracle in the Oracle9i Database Performance Methods (Release 1)/Database Performance Planning (Release 2)
Use the statistics gathered in the second step to get a conceptual picture of what might be happening on the system.
Identify the changes to be made and then implement those changes.
Determine whether the objectives identified in step one have been met. If they have, stop tuning. If not, repeat steps five and six until the tuning goal is met.
Interestingly, the emphasis on identifying which step an action falls under went away with Oracle9i, and recitation of the principles is not a testable item. The title of documentation even changed between releases one and two, and that should send a clear signal that the art of performance tuning (or, performance and tuning) is still just that an art. When it comes to instance tuning, the steps are even further reduced in Oracle10g.
The performance tuning guide for Oracle10g (Release 2) identifies the overall process as The Oracle Performance Improvement Method. The steps have been expanded, but overall, remain the same.
The Change is Part of the Problem
The change from a top-down structured approach to a principle-based "make it stop hurting" one is part of the problem. Gathering statistics is obviously important because how else do you know if you have improved (or worsened) the problem? Still, to some degree with either approach, you are left with the original two questions: what do I look for, and how do I make it better? If the structured approach left you scratching your head, the principled approach only adds to the confusion.
What would help the novice tuner (disclaimer: I am far from being an expert) is a list of items or areas to evaluate (configure, diagnose, and tune) in each of the following areas:
- Tuning the Buffer Cache
- Tuning the Redo Log Buffer
- Tuning the Shared Pool Memory
- Tuning the Program Global Area
- Optimizing Data Storage
- Optimizing Tablespaces
- Tuning Undo Segments
- Detecting Lock Contention
- Tuning SQL
These areas pretty much cover the Oracle RDBMS and instance from top to bottom. The remainder of this article will focus on tuning SQL, or more precisely, preventing slow SQL execution. Aren't these the same thing? Mostly yes, but a common approach in development is making a statement perform well enough or fast enough. Each and every statement does not have to be optimal, but some thought has to go into coding them. You do not have the time to optimize hundreds or even thousands of SQL statements, but at the same time, there are guidelines you can follow to avoid common mistakes and bad coding.
17 Tips for Avoiding Problematic Queries
The source of these 17 tips is from Oracle9i Performance Tuning: Optimizing Database Productivity by Hassan Afyouni (Thompson Course Technology, 2004). These tips provide a solid foundation for two outcomes: making a SQL statement perform better, and determining that nothing else can be done in this regard (i.e., you have done all you can with the SQL statement, time to move on to another area).
The 17 tips are listed below.
Avoid Cartesian products
2. Avoid full table scans on large tables
3. Use SQL standards and conventions to reduce parsing
4. Lack of indexes on columns contained in the WHERE clause
5. Avoid joining too many tables
6. Monitor V$SESSION_LONGOPS to detect long running operations
7. Use hints as appropriate
8. Use the SHARED_CURSOR parameter
9. Use the Rule-based optimizer if I is better than the Cost-based optimizer
10. Avoid unnecessary sorting
11. Monitor index browning (due to deletions; rebuild as necessary)
12. Use compound indexes with care (Do not repeat columns)
13. Monitor query statistics
14. Use different tablespaces for tables and indexes (as a general rule; this is old-school somewhat, but the main point is reduce I/O contention)
15. Use table partitioning (and local indexes) when appropriate (partitioning is an extra cost feature)
16. Use literals in the WHERE clause (use bind variables)
17. Keep statistics up to date
That is quite a list and overall is thorough and accurate. Step 9, referring to the use of the Rule-based optimizer, may cause a reliance or dependency on a feature Oracle has identified as a future item to be deprecated. You are eventually going to have to solve the problem using the CBO, so you may as well start now and forget about the RBO. Step 14 should be changed to something along the lines of "reduce I/O contention" instead of its currently stated "separate index and table tablespaces" guidance.
In the next article of this series, we will look at some specific steps of these tips. For example, advice given on many Web sites about how to improve a SQL statement's performance typically includes "use bind variables." Well, I am sure many people have this question: "How, exactly, do I do that?" It is actually pretty simple, as are many of the details of how to use many of these tips.