So far, we have covered a wide range of tips related to optimization in SQL. As a Database Administrator, you may also be aware of these and about what to do when a SQL statement results in a degradation of performance.
These include the following:
- Using DMV for locating slow statements that take time to process
- Creating execution plans
- The utilisation of indexes
- Checking resource utilisation
And other such tasks.
Still, even the most careful professionals tend to miss a few things or include excessive measures that can worsen instead of improving performance. This blog explains a few such things to avoid using during query performance tuning to make sure the results are satisfactory.
What to Avoid During Oracle SQL Query Performance Tuning
The most common mistakes made during tuning and optimization include –
- Using coding loops – Here’s a simple example to explain why coding loops must be avoided in SQL. Suppose there are a thousand consequent queries aimed at the database. If you want to improve database performance by modifying the code, it’ll be better to apply WHERE clauses into INSERT or UPDATE statements instead of adding a loop to reduce the number of times the database is traversed.
Keep in mind – as long as the saved set matches the current value, it won’t be updated by the WHERE clause, leading to far fewer processes and, subsequently, much faster results.
- Using SELECT – This is quite important but it is often neglected. A faster, more efficient approach is to separately add all the new columns as needed instead of inserting them using SELECT. For instance, “SELECT * from Clients;” would be of greater help in boosting Oracle SQL query performance than its longer version such as “SELECT FName, Id, Address from Clients;”.
- Using Correlated Subqueries – This type of statement generally accesses values being reached by the parent query. During statement processing, data is traversed row by row and the query is executed once for every row the outer statement execution fetches.
The extra processing increases the workload and takes more time, thereby slowing the database. You can save both by eliminating redundant processes – the same rows won’t have to be accessed for every subquery if a join is used instead.
- Shrinking database files – Not only is it cumbersome to do on a regular basis, but it also has a negative effect on the performance of a database, and therefore, query performance tuning. That’s because it leads to fragmentation, which drastically reduces the speed at which subsequent statements are carried out in the database.
That said, certain situations call for mandatory data file shrinking. Even so, it is recommended to observe the effects and consequences of shrinking the files and whether it is completely unavoidable before the deed is carried out.
In Conclusion
Make sure to avoid the Oracle SQL query aspects explained above as much as possible. Doing this will help you enhance the rate at which queries are processed, speeding up the database. Consider every situation carefully before deciding what to include and what you must refrain from applying.
Tosska Technologies 