How to Tune SQL Statements to Run SLOWER… but Make Users Feel BETTER (Oracle)?

MySQL database and SQL

Your end-users may keep on complaining about some functions of their database application are running slow, but you may found that those SQL statements are already reached their maximum speed in the current Oracle and hardware configuration. There may be no way to improve the SQL unless you are willing to upgrade your hardware. To make your users feel better, sometimes, you don’t have to tune your SQL to run faster but to tune your SQL to run slower for certain application’s SQL statements.

This is an example SQL that is used to display the information from tables Emp_sal_hist and Employee if they are satisfied with certain criteria. This SQL is executed as an online query and users have to wait for at least 5 seconds before any data will be shown on screen after the mouse click.

select * from employee a,emp_sal_hist c
where a.emp_name like ‘A%’
     and a.emp_id=c.sal_emp_id
     and c.sal_salary<1800000
order by c.sal_emp_id

Here the following is the query plan and execution statistics of the SQL, it takes 10.41 seconds to extract all 79374 records and the first records return time ”Response Time” is 5.72 seconds. The query shows a MERGE JOIN of EMPLOYEE and EMP_SAL_HIST table, there are two sorting operations of the corresponding tables before it is being merged into the final result. It is the reason that users have to wait at least 5 seconds before they can see anything shows on the screen.

As the condition “a.emp_id = c.sal_emp_id”, we know that “ORDER BY c.sal_emp_id“ is the same as “ORDER BY a.emp_id“,  as SQL syntax rewrite cannot force a specified operation in the query plan for this SQL, I added an optimizer hint /*+ INDEX(@SEL$1 A EMPLOYEE_PK) */ to reduce the sorting time of order by a.emp_id.

SELECT  /*+ INDEX(@SEL$1 A EMPLOYEE_PK) */ *
FROM    employee a,
      emp_sal_hist c
WHERE a.emp_name LIKE ‘A%’
    AND a.emp_id=c.sal_emp_id
    AND c.sal_salary<1800000
ORDER BY c.sal_emp_id

Although the overall Elapsed Time is 3 seconds higher in the new query plan, the response time is now reduced from 5.72 seconds to 1.16 seconds, so the users can see the first page of information on the screen more promptly and I believe most users don’t care whether there are 3 more seconds for all 79374 records to be returned. That is why SQL tuning is an art rather than science when you are going to manage your users’ expectations.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

How to Tune SQL Statement with “< ANY (subquery)” Operator for Oracle?

database query optimization

Here the following is a simple SQL statement with a “< ANY (Subquery)” syntax.

SELECT  *
FROM    employee
WHERE  emp_salary< ANY (SELECT emp_salary
              FROM  emp_subsidiary
              where  emp_dept=‘AAA’
              )

Here the following is the query plan of the SQL, it takes 18.49 seconds to finish. The query shows a “TABLE ACCESS FULL” of EMPLOYEE table and “MERGE JOIN SEMI” to a VIEW that is composed of a HASH JOIN of two indexes “INDEX RANGE SCAN” of EMP_SUBSIDIARY.

You can see that it is not an efficient query plan if we know that the emp_salary of EMP_SUBSIDIARY is a not null column, we can rewrite the SQL into the following syntax. The Nvl(Max(emp_salary),-99E124)is going to handle the case that if the subquery returns no record, the -99E124 representing the minimum number that the emp_salary can store to force an unconditional true for the subquery comparison.

SELECT  *
FROM    employee
WHERE  emp_salary < (SELECT  Nvl(Max(emp_salary),-99E124)
            FROM   emp_subsidiary
            WHERE  emp_dept = ‘AAA’)

Here is the query plan of the rewritten SQL and the speed is 0.01 seconds which is 1800 times better than the original syntax. The new query plan shows an “INDEX RANGE SCAN” instead of “TABLE ACCESS FULL” of EMPLOYEE.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other rewrites with similar performance, but it is not suitable to discuss in this short article, maybe I can discuss later in my blog.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Optimization in SQL: Answering 4 Commonly-Asked Questions

optimization of sql queries

A SQL query or statement is tasked with fetching the required information from the database. While the same output can be gained from different statements, they are likely to work at different performance levels.

The difference in performance output makes a lot of difference because a millisecond of lapse in query execution can result in huge losses for the organization. This makes it extremely necessary to ensure the best statement is being used, which is where optimization in SQL is considered.

#1: What is Query Optimization in Databases?

Query optimization in databases is the general process of picking out the most efficient way of obtaining data from the database i.e. carrying out the best query for a given requirement. Since SQL is nonprocedural, it can be processed, merged, and reorganized as seen fit by the optimizer and the database.

The database enhances each query on the basis of various statistics gathered about the information fetched from it. On the other hand, the optimizer selects the optimal plan for a query after assessing different access techniques including index and full-table scans. Various join methods and orders are also used along with certain probable transformations.

#2: What is Query Cost in Optimization?

Query cost is a metric that helps examine execution plans and determine the optimal one. Depending on the SQL statement and the environment, the optimizer sets an estimated numerical cost for every step throughout potential plans and considers an aggregate to derive the overall cost estimate for it.

The total query cost of a query is the sum of the costs incurred at every step in it. Since query cost is a comparative estimate of the resources needed to carry out every step of an execution plan, it doesn’t have any unit. The optimizer picks out the plan with the least cost projection once it has completed all its calculations of all the available plans.

#3: Is Query Cost the Best Way to Judge Performance?

In a word: No. Why? Although query cost proves useful in comprehending the manner in which a specific query is optimized, we must bear in mind its main goal: helping the optimizer select decent execution plans.

It does not offer a direct measure of parameters such as CPU, IO, memory, duration that are significant to users waiting for a statement to finish running. In other words, a low query cost won’t necessarily mean the plan is optimal or the query in question is the quickest. Similarly, a high query cost can prove more efficient in comparison, which is why it is not recommended to depend too much on query cost when considering performance.

Being a CPU-intensive operation query optimization in SQL takes a lot of resources to determine the best plan among the ones present. Time also needs to be factored in here as the user may not always have the time it may take for this entire process to take place. 

Therefore, the resources required to optimize a statement, those required to run the statement, and the time it takes for all of this to be done with shouldn’t exceed each other. 

#4: How Can We Optimize a SQL Query?

Query optimization often needs extra resources, such as the addition of indexes. However, we can boost query performance by simply rewriting a statement to decrease resource consumption without further expenses.

This lets us save significant resources, money, and time (if a query optimization tool is used). Through query optimization in SQL, we can focus on specific areas that are causing latency instead of examining the entire procedure. In such cases, looking for sections that are taking up more resources will help us narrow down the search and fix issues more quickly.

SQL Performance Tuning: Frequent Questions about Indexes

SQL performance tuning

A database is a piece of software operating on a computer, which means it is dependent and likely to face the same limitations as other software present on that computer. In other words, it will only be able to process as much data as the hardware can handle.

One of the best ways to speed up queries is to perform SQL performance tuning. In this post, we will answer some of the most frequent questions involving databases and indexes.

What is Indexing in SQL Query Optimization?

Indexing is one of the first things you may have come across while learning the ropes of your database. It is a wonderful tool that enables users to enhance the efficiency of their database. However, bear in mind that not every database requires indexing, and not all indexes are helpful in SQL performance tuning.

Let’s learn more about indexing: what it is and how it helps in enhancing database performance.

How do Indexes Affect SQL Query Performance?

An Index can locate data swiftly without having to go through each row in the table. This saves plenty of time! 

Certain data columns are required before you can create an index. These are –

  • The Search Key which holds a duplicate of the primary key
  • The Data Reference which has a set of pointers

All of these constitute the structure of one index. To understand how an index works, let us take an example. Suppose you need to look for a bit of data in your database. Rather than scour every line yourself, you make the computer search each row till it locates the information. Remember that the search is bound to take much longer if the requisite information is located at the end. Fortunately, you have the option to sort alphabetically to shorten the length of such queries.

What are the Types of Database Indexes?

Database indexes are of two kinds –

Clustered indexes – These arrange data using the primary key. The reason behind using a clustered index is to make sure the primary key is saved in ascending order. This is the same order in which the table stores memory.

A clustered index is automatically created when the primary key is set, which helps in SQL tuning for Oracle in the long run as well.

Non-clustered indexes – A non-clustered index is a data structure that boosts data fetching speed. It is different from clustered indexes, as they are made by data analysts or developers.

When and How Should We Use Indexes?

Since indexes are intended to accelerate database performance, you should apply them whenever you think they can simplify the use of the database. Although smaller databases may not have several opportunities to use indexes, they are likely to see the benefits of indexing as they grow into larger databases. 

You can make sure your indexes keep performing well, if you test run a set of queries on your database first. Clock the time those queries take to execute and begin creating your indexes after that. Keep rerunning these ‘tests’ for continuous improvements.

Conclusion

Indexing has its challenges, the biggest one being determining the best ones for every table.

For instance, heaps require clustered indexes because searching for a record in a heap table is comparable to finding a needle in a haystack: it’s inefficient and time-consuming, thanks to the heap’s unordered structure.

On the other hand, locating data is simpler and faster from a table that contains a proper clustered index, just like finding a name in a list that’s alphabetically ordered. DBAs, therefore, recommend that every SQL table contains a proper clustered index. Now that you know how indexes work and how they can optimize database performance, you should be able to use them to reduce query times substantially. If you would like more tips on how to use indexing, or you need a SQL query optimization tool for your database, let our experts know!