The production code database professionals create can make a big difference in the database's performance. Most understand this and specify what columns the query intends to fetch. Some don’t and, as a result, face not only performance issues but also problems related to application stability.
Application data processing can suffer from changes as little as single column additions and column reordering. Here, we will look at SELECT *-one component that needs Oracle query performance tuning for many reasons.
Why SELECT * Proves Inefficient and Requires Optimization in SQL
Let’s look at the most obvious reasons why SELECT * causes performance problems in SQL. You’ll be better off avoiding it unless the application requires all the columns:
Higher Client-Side CPU Consumption
CPU usage on the client-side of the application often depends on factors, such as row quantity and column wideness. So, the bigger the number of columns and rows the user processes on the client-side, the longer the CPU will stay engaged. In many situations, CPU usage involves fetching the records and formatting them before using an output file to write them in.
Rises in Network Traffic
This is also quite apparent as retrieving significantly more columns will obviously result in larger data transfers. Sending exponentially more bytes over the network with every SELECT * request will make it busier. That’s because there will be more network packets to transport which could also mean more database-application network roundtrips. Consequently, you will need optimization in SQL to use cost-effective alternatives to SELECT *.
Fewer Plan Possibilities
Generally, the optimizer present within the database management system can improve the structure of the statement. It will make changes as long as it considers the changes will optimize SQL query and keep the query logically equivalent. The effects range from opening new possibilities for accessing data cost-effectively to enabling users to skip some parts of query execution.
For instance, Oracle will only scan a restricted index scan in the presence of an index that includes all the necessary columns. It will not conduct a thorough table-wide scan in this case, nor will the quicker scan use tree-walking. It will involve a complete table scan conducted via index blocks.
Retrieving LOB Columns
Database performance takes a hit from queries that try to fetch LOB columns from tables. That’s because of the increase in the number of network roundtrips to display each row separately. This is one of the ways how SELECT * causes SQL queries to perform poorly since it includes LOB fetching.
As an example, you can fix the array size to a thousand but decide to select a LOB column. The result will be as many roundtrips as there are LOB values waiting to be fetched-1000 in this case! The same query that takes some thirty milliseconds will take several seconds with negligible activities occurring in the database.
This is due to the majority of the response time going into the roundtrips from the client to the server and vice versa. This is a problem no amount of CPUs or indexes can get rid of. It will only get better if you make changes to your application code instead.
To Conclude
When you notice an issue with database performance, try to understand why a query or a task is taking an abnormal amount of time. Once you know what’s causing the hold-up, you can work on reducing the wait time. Most database professionals follow this approach for a reason.
After all, the alternative is to involve more hardware which doesn’t always prove helpful. If you are wondering how to decrease the load or the wait time, see if you can optimize Oracle query.
For that, you must know exactly what you want from the database and change the query to ask for the same. And one way to do that, as mentioned above, is to ensure your queries only fetch the data you actually need.
