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.
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?
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.
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.
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.
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.