Only then can you take full advantage of the many features Oracle provides that can greatly improve performance when properly used in a well-designed system. Even the performance of well-designed systems can degrade with use. Ongoing tuning is, therefore, an important part of proper system maintenance.
- 1. Tune Inefficient SQL for Better Oracle Database Performance
Tuning the application SQL to use the proper query plan is the fastest, least risky and most cost effective way to resolve Oracle performance problems.
Some tips for doing this effectively:
- Identify and target the SQL running for the problem only from the user’s perspective. This needs to be something along the lines of “when I open my Accounts Payable enquiry form at 09:45 on a Wednesday morning it takes 35 seconds to open – this needs to be 2s”. Until you have this level of detail keep on working with the users to define the problem.
- Trace and measure the specific database session using standard tracing tools to identify the SQL statistics, execution plan and wait time cost.
- Target the tuning efforts to the particular statements that are consuming the largest response time for the particular function.
- I can not overstate how important it is to correctly define and measure system performance problems before any attempts are made to address the issues. Do not waste time “attempting” to optimise your databases without having very well defined problem statements and measurements.
- 2. Improve Storage Performance
When you have exhausted all SQL tuning opportunities one option is to consider the storage platform on which the the database runs. Reviewing storage options for your Oracle database server can be a cost effective approach to removing bottlenecks.
For instance flash storage technology can offer significantly higher bandwidth and reduced latency to IO bound workloads.
Targeted provisioning of flash storage for Oracle database can provide significant performance improvements without having to refresh your entire database infrastructure. You can place specific database objects on flash storage that have the most performance benefit – leave the remainder of the database on conventional storage.
Also, analytic queries that process large amounts of data can require huge amounts of sorting before the query completes.
Performance of analytic queries – or any query that processes large volumes of data – can be highly dependent on storage performance. Locating Oracle’s temporary tablespace on flash storage is a cost effective opportunity to reduce the query response time.
There are many options to consider here. The main takeaway being that a specific, targeted approach can be taken when dealing with storage related performance problems. There may not be a need to replace the entire storage platform that your Oracle database is running on to deliver considerable performance gains.
- 3. Provide Dedicated Database Infrastructure
In some cases, regardless of the tuning efforts applied, poor system performance is a result of other systems on a common infrastructure platform.
The most efficient way to deal with this is to isolate the Oracle database workloads on to dedicated infrastructure.
While the direct cost of this approach is additional infrastructure and associated support costs, this can be offset by the following benefits:
No noisy neighbour impact on Oracle database IO response times.
More predictable performance for database workloads.
Potentially a reduced infrastructure footprint and more concentrated use of your Oracle software licenses.
Simplified management, monitoring and security.
Attempting to measure database performance issues on consolidated infrastructure across multiple vendor technologies is a time consuming and difficult exercise.
Whilst performance, capacity and scalability are headline features of the Oracle Exadata platform – it is our experience that one of the major benefits of deploying Oracle Exadata is the ability to measure, diagnose and optimise critical workloads in isolation.
In many cases this diagnosis can be completed in a fraction of the time on Oracle Exadata compared to alternative platform solutions.
Resilient and scalable database platforms require investment. When evaluating the costs of different approaches consider the indirect cost of poor performance when evaluating your options.
Oracle Performance Tuning Tips:
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. If your database performance just isn’t up to par, there are several tactics you can use to fix any possible issues. Here are five performance tuning tips that will help you get the most out of your Oracle database efforts and keep your environment fully operational.
- 1) Understanding the importance of baselines for Oracle performance tuning
One of the most effective tuning methods is to maintain an established performance baseline that you can reference when a performance problem occurs. By easily identifying peak usage periods, you can set performance targets and have a firm understanding of your system capabilities. Consistently measuring existing performance provides exact expectations of how your database should be running, which is quite helpful when tuning any bottlenecks that will inevitably come up in the future.
- 2) Utilize automatic workload repository as an Oracle monitoring tool.
One of the most useful Oracle Database performance tuning features is the Automatic Workload Repository (AWR), which collects, processes, and maintains performance statistics. The AWR offers a daily glimpse into the health of your database, and from there you can utilize the Automatic Database Diagnostic Monitor (ADDM) that provides actionable insights on the information collected by the AWR. Utilizing both tools will directly enhance your performance tuning abilities and save time/money in the process.
- 3) Checking your statistics is key to Oracle performance monitoring
Along with taking advantage of AWR and ADDM, it is important to always have a full compilation of operating system, database, and application statistics from your systems when performance tuning. There is more to tuning than simply fixing a performance issue; it should be a central component throughout the entire life cycle of any application. The best way of staying on top of this is by continually checking your statistics and refining the process of how they are gathered/presented. The symptoms of performance issues tend to be found within the data, so don’t ignore the information that will be a deciding factor in your performance tuning efficiency.
- 4) Ensure an optimal level of CPU utilization
Manual processes may be required for performance tuning when Oracle’s automatic diagnostic features aren’t suitable for the problem at hand. When investigating any performance issues of this nature, it is always wise to ensure that all your CPU is being utilized. Checking CPU utilization in the user space will verify if there are any non-database tasks consuming CPU on the system, which limits the amount of shared CPU resources. Once you finalize CPU usage, taking care of any performance issues becomes much easier and it brings clarity to your decision-making.
- 5) Improve Oracle monitoring via SQL tuning
SQL is considered as a messaging language because queries are issued and data is returned. However, client tools often generate inefficient SQL statements and poorly written SQL queries can negatively impact database performance. Be sure to consider SQL that consumes significant system resources when undergoing performance tuning. By leveraging Oracle’s SQL tools (such as the Query Optimizer) and keeping a close eye on your SQL processing engine, you will be able to execute performance tuning with better results and make sure your SQL is working for you, not against you.
