I finished a session this weekend and there were a lot of questions regarding query tuning. The answers (for the questions) were around the following points. So I thought let me have it all put together so that next time, I can refer someone to this post. Nothing in this post is new(hence the word “dummies”) and if you have spent any time learning/doing performance/tuning work, all of these pointers must be nothing but obvious for most.
Analyze the SQL statement: The first step in query tuning is always to analyze the SQL statement. This includes understanding the table structures, different types of joins that can be used. Remember that besides the joins, other things, like DB Caching, Result Cache, IMCS, they can all improve performance. Oracle provides various tools like EXPLAIN PLAN, SQL Tracing(10053/10046), SQLTXplan, and SQL Monitoring to analyze the SQL statement.
Optimize the SQL statement by rewriting: Once the SQL statement has been analyzed, the next step is to optimize it. This involves rewriting the SQL statement to make it more efficient. Some techniques that can be used include optimizing the join order, reducing the number of subqueries, and using appropriate indexing.
Use indexes effectively: Indexes can significantly improve query performance by allowing Oracle to retrieve data more quickly. However, indexes can also have a negative impact on performance if they are not used effectively. It is important to ensure that indexes are created on the appropriate columns and are being used by the query optimizer. If youare not sure what to do, take the advantage of Auto-indexing feature, or even better, autonomous databases.
It’s important to note that creating an index is not always the best solution for improving query performance. Creating too many indexes can lead to overhead and slower performance during data modification operations like inserts, updates, and deletes. It’s important to strike a balance between query performance and data modification performance when deciding which indexes to create.
Last but certainly not the least, do read Richard Foote’s blog for the same.
Optimize the database design: The database design can also have an impact on query performance. Normalization can help reduce data duplication and improve query performance. Additionally, partitioning large tables can also improve query performance.
Monitor performance: Finally, it is important to monitor query performance to identify any potential performance issues. Oracle provides various tools like Automatic Workload Repository (AWR) and Active Session History (ASH) to monitor query performance.
Check Tanel Poder’s website for the tools like Session Snapper which uses ASH in a marvellous way.
In addition to these, the following must also be taken into consideration,
Statistics: Oracle maintains statistics about the data in the database to help the query optimizer make efficient execution plans. These statistics can become outdated over time, leading to suboptimal query performance. It’s important to regularly gather statistics to ensure the query optimizer has accurate information about the data. Oracle database, since 11g has done several improvements in gathering statistics. So take advantage of that.
In addition to the object statistics, do consider taking the system statistics. Note that unlike, object statistics, system statistics can be collected using CLI mode only.
Bind variables: Using bind variables in SQL statements can improve query performance by reducing the amount of work done by the optimizer. When bind variables are used, the optimizer can reuse execution plans, leading to faster query performance. But it can be counter productive too, when you do not want to reuse the execution plan.
Do take advantage of Adaptive Cursor Sharing(enabled by default) and like always, test, test, test.
Cursor sharing: CURSOR_SHARING parameter control how the cursors can be shared among the queries. This parameter had three values in the past, but now there are only two values, FORCE and EXACT. Unless you are not sure, keep the parameter to its default value- EXACT. Other parameters like OICA etc. must be used only as a last resort.
SQL plan management: Oracle has a feature called SQL plan management, which allows you to capture and use historical execution plans. This can be useful for ensuring stable query performance and preventing suboptimal plans from being used.
Also remember, database works on the top of OS and storage, using CPU for accessing both. If you are slow in these factors, DB will be the victim (not the culprit). That’s why using tools like ADDM can help immensely where database itself can tell you, not just the problem area but the solution also. Remember that AWR, ASH, ADDM, they are also going to require additional license for being used.
Lastly, performance tuning is (should be) a collective effort.
Hope it helps.
Aman….
Recent Comments