Transaction performance is business performance. SQL statement execution is the foundation of transaction performance. Business performance has an economic effect. It justifies time and budget. It may just help your organization thrive in the globally competitive marketplace.
This document focuses on table & index optimization first. Most DBAs start with the top SQL statements. Thus, you may find the order presented here to be backwards. Here is the justification. Object tuning improves the execution time of more SQL statements. If the reorg routines are scripted and scheduled, they will keep your applications running quickly and help avoid locking and waits once implemented. Object tuning allows you to get ahead of the curve, giving you time to elevate to the next level. We believe that Precise can help DBAs go beyond alert & response to avoiding alerts to begin with. The best problem is no problem.
community.idera.com/…/Oracle-Performance-Tuning-Practice-supporting-screens.pptx
Leverage Precise to optimize Oracle. While Oracle 12c offers new functionality that can improve performance in niche situations, these ideas focus on the basics. Doing the basics well will put your Oracle instances in position to make the best use of advanced techniques.
Where to invest time?
-
- The Oracle instance for the application end-users and management are complaining about!
- Stack rank the Oracle instances by business importance.
- Review Precise’s Instance level Findings report for low hanging optimization opportunities.
- Oracle upgrade dates.
A low-risk proactive maintenance practice is reorganizing and rebuilding indices. Please see the Triangle of Oracle Optimization Success: Triangle of Oracle Optimization Success. This will improve processing efficiency for every SQL statement that accesses the defragmented object. Please note that “low-risk” does not mean risk free. It must complete without error.
- Reorganizing and rebuilding tables & indices subject to fragmentation makes each I/O operation more efficient. It will improve efficiency every time the object is accessed. Sometimes quite a bit of space will be recovered. I/O Efficiency – Storage Performance
- Use Precise’s Active Objects list to determine tables & indices for tables and indices that contribute to SQL statement execution time the most. Time is the currency of performance. Busy Tables
- Each of these objects should be in a maintenance routine. The schedule can vary based on how quickly the object becomes fragmented.
- Clicking on an object in Precise will reveal the table & its indices. Create a job & schedule it to reorganize or rebuild the indices.
- Review the indices to see if they are used widely by numerous SQL statements or by just a few. A lightly used index may be a candidate for dropping. Do not drop an index whose purpose is to enforce data integrity aka a unique index.
Create statistics – New statistics represent change. Oracle’s Optimizer may make a different access path decision; thereby, impacting performance. Most times the execution plan will be faster; other times, it will be slower.
Some applications use temp tables. Oracle reads up to the high water mark even if the blocks are empty. Temp tables should be truncated when there are zero rows. This will recover allocated blocks. Statistics should be created when the table is full, aka not empty, so the statistics reflect its status when it is used.
Precise’s Profile Objects report shows the top ten objects, their ranking today, and their baseline rank. If the rank changed, the behavior of the SQL statements accessing the object changed. One reason could be that the object grew fragmented. A change in rank can indicate that an object should be reorganized or rebuilt more frequently. A change in execution plan could increase activity as well. Precise’s Objects view shows the SQL statements that access a table.
It is easy to add an index. Pretty soon, there could be too many indices. Not every index helps. Dropping or changing an index is a step more risky than rebuilding or reorganizing. Precise automatically detects when tables & indices are access in a sub-optimal way. This is a subset of Precise’s Object Level Findings.
- “Index Overhead” – Precise will notify you when too many indices slow processing. For each update, delete, and insert, every index must be maintained. This is a common bottleneck for slow ETL & reporting processes. Clicking on Statements will reveal the statements that are imposing overhead. Consider dropping unnecessary indices. Precise’s Recommendation can be used to model an index drop.
- “High Clustering Factor” – Precise shows when using an index in a scan results in excessive I/O operations.
- “No Available Parallel Processes” – parallel execution can be very effective; however, when every session is attempting to use it, max parallel servers limit can be reached.
Individual SQL statement optimization. Precise ranks SQL statements by resource consumption in descending order. The SQLs at the top of the list have the most potential to release CPU and I/O. These resources when freed up will have ripple effect as those resources become available for other SQLs and processes running on the server. Precise captures the total execution time of SQL statements and segments the time into execution. The execution states show where the performance bottlenecks: locking, logging, RAC, and CPU. Precise captures all execution states including storage. Focusing exclusively on waits and locks may miss where you can have the most impact. Using CPU should not be ignored. If the time spent with the CPU can be minimized, thereby reducing execution time, contention and locking will be also be minimized.
Precise shows the top 10. Better yet, Precise correlates from program, user, machine, aka the application. Improving application performance improves business performance.
-
- Application aware tuning. In the Activity tab, Precise offers the ability to drill down by program, user, machine, JVM, and Java Caller. The Java Caller is the business transaction as seen by the end-user.
-
- Precise allows statement names to be changed. Group SQL statements into transactions.
After a migration, Oracle’s Optimizer may choose a new execution plan. Perhaps a structural change created contention where none existed before. Most SQL statements should see better performance; however, others may see worse.
Precise identifies SQL statements that have experienced a change in behavior. The Oracle Profile Statements report shows the SQL statement ranking before & after. Please see the accompanying PowerPoint for an example.
If the database runs on Windows, set the power plan to “high performance”. This makes optimal use of all of the CPU cores. This change is less expensive than purchasing a new server. I’ve not explored the impact within VMware.
If there are still significant waits, only now is it appropriate to consider parameter changes. Oracle parameter changes are global in impact. A parameter change can affect the execution time of every SQL statement.
Document Before & After and share with your chain of command. This step completes the feedback loop. It shows that improvements are being made. Success will motivates DBAs and managers.