In part 1 of this series, we discussed how Oracle Exadata deals with performance issues, including smart scans and smart flash cache, among other processes. Now that you know how Exadata handles performance, then you should learn how to tune it to its best performance.
First, you should know what to look for when you are performance tuning with Exadata. These are:
- SQL statement response time monitoring
- The usage and efficiency of the database and the storage cell layers
- Advanced metrics and monitoring for Exadata
- Others such as SQL statements v$views, cellcli and OSWatcher
Oracle Database and the cells give you a lot of metrics to work with. The thing is, you are not expected to tune them all. You should, however, measure and tune the metrics that matter to you and your company. For most, it is usually the response time. In an ideal world where databases are simple, you should be monitoring the end user response time and work from there. This might take you to the database server, app server and the network. This is because it is not only the database that could get you in trouble.
But because real-world database systems are multi-tiered and complex, this kind of process is not feasible.
So you do the second best thing, which is to monitor and measure the response time of the queries and transactions to your database. After checking the database for some performance issues, you might also want to check on the application layer or higher.
Another trick is to ask your users. See if you have unhappy users and work with them to pinpoint performance issues with your database.
Then how do you remedy performance problems? It depends on the workload.
Reporting workloads often have long running SQL statements but they are run less often. To get the most performance, then you should do smart scans, offloading and storage indexes. Your SQL statements should have hash joins and full table scans.
Transaction or OLTP workloads are shorter but are run very often. Use flash cache for these types of workload. Avoid Smart Scans for OLTP queries.
Another thing about smart scans is that it can make your data retrieval faster, but only when you use direct paths and full segment scans. You should also check other metrics, such as cell smart table/index scan events or V$SESSTAT to see if the smart scan did run, because you cannot see it in the execution plan. This will help you understand and troubleshoot even the more advanced performance problems such as Smart Scan is throttled by chained rows, running out of cell server resources and other special conditions.
If you need to boost the performance of your Exadata platform and have no idea where to start, contact Four Cornerstone. We can help you set up your Exadata and maintain it so that you get optimum performance and get the most out of your investments. With Four Cornerstone, you will be working with a team of Oracle certified experts with years of experience. No guesswork, just the best practices!
Photo by Oracle.
