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/

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

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 MySQL 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_subsidiary 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_subsidiary b
where   a.emp_id = b.emp_id
         and a.emp_grade < 1050
         and b.emp_salary < 5000000
order by a.emp_id

Here the following is the query plan and execution statistics of the SQL, it takes 5.48seconds to extract all 3645 records and the first records return time ”Response Time(Duration)” is 5.39 seconds. The query shows a “Full Table Scan b (emp_subsidiary)” to Nested-Loop “a (employee)” table, an ORDER operation is followed by sorting the returned data by emp_id. You can see there is a Sort Cost=7861.86 at the ORDER step on the query plan. It is the reason that users have to wait at least 5 seconds before they can see anything shows on the screen.

To reduce the sorting time of a.emp_id, since a.emp_id=b.emp_id, so I can rewrite the order by clause from “order by a.emp_id” to “order by b.emp_id”, MySQL now can eliminate the sorting time by using the EMPLOYEE_PK after the nested loop operation.

select  *
from    employee a,
         emp_subsidiary b
where   a.emp_id = b.emp_id
         and a.emp_grade < 1050
         and b.emp_salary < 5000000
order by b.emp_id

Although the overall Elapsed Time is higher in the new query plan, you can see that the response time is reduced from 5.397 seconds to 0.068, so the users can see the first page of information on the screen instantly and they don’t care whether there are 2 more seconds for all 3,645 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 MySQL automatically.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

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.

How to Tune Bad Performance SET ROWCOUNT SQL Statements for SQL Server?

sql performance monitoring

Some SQL statements will be running very slow after SET ROWCOUNT or TOP is used.  SET ROWCOUNT and TOP are used to tell SQL Server to select a specific number of rows from the SQL statements instead of extracting all records. Not many people know that SQL Server will try to re-optimize your SQL statements after you adding SET ROWCOUNT or TOP, the result is normally good after re-optimization of your SQL statements that can generate query plans for retrieving the first few records as fast as possible.

Good Example for Query Re-optimization for SET ROWCOUNT

Here the following is an example that shows the SQL takes 6.78 seconds to retrieve 217500 rows from the database, the query plan shows a good plan with a Hash Match for two Table Scan of [DEPARTMENT] and [EMPLOYEE].

The following screen shows the new query plan is generated after the SET ROWCOUNT 1 is used, the query plan is changed from Hash Match to Nested Loops. Nested Loops operation normally provides faster first few records retrieval time but may not be good for overall records extraction in certain situations. It is good to see that SQL Server uses only 0.013 seconds to extract the first row for this SQL.

Bad Example for Query Re-optimization for SET ROWCOUNT

Let’s see a bad example that shows how SQL Server degrades a good query plan to a bad query plan after the SET ROWCOUNT 1 is used. Here the following is an example that shows the SQL takes 0.118 seconds to retrieve 1613 rows from the database, the query plan is a little bit complex but it is a good query plan to retrieve all 1613 rows.

The following screen shows the new query plan is generated after the SET ROWCOUNT 1 is used, the query plan is now changed to Nested Loops with two Table Scans. The new query plan takes 1.312 seconds to extract only the first record, it is even slower than the 0.118 seconds that is used to extract all 1613 rows from the database.

How to Solve This Problem?

We can use Hints injection or SQL syntax rewrite to influence SQL Server to get back the original plan or generate an even better query plan for the SET ROWCOUNT or TOP operation. The following Hints injection generated a good query plan that is almost 90 times better than the original SQL with SET ROWCOUNT 1.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

How to Tune Cold Cache SQL Statements for SQL Server?

sql optimizer for sql server

For SQL statements that are not executed frequently, so that the relevant data is no longer exists in the buffer cache, a cold cache will significantly affect the performance of a SQL statement. A good performance SQL for hot cache may not be performing well in a cold cache environment. Experience developers will tune their SQL running well for both environments.

Here the following is an example SQL:

select * from
EMPLOYEE A
 where  A.EMP_ID IN (SELECT B.EMP_ID from EMP_SUBSIDIARY B
                      where B.EMP_DEPT < ‘D’)

Here the following is the query plan in the Tosska proprietary tree format, it takes 8.024 seconds for the first execution with cache delay and it takes 3.7 seconds for the second execution without caching time.

According to the query plan, you may find that the most significant IO consumption is the Table Scan of [EMPLOYEE] table. To simulate the cold cache environment, we can use the DBCC DROPCLEANBUFFERS command to clear the data cache before each execution of rewritten or optimized SQL statement.

Let me add an optimizer hint OPTION(LOOP JOIN) to the SQL and try to change the query plan from a Hash Match to a Nested Loop join. So, the EMP_ID(EMPLOYEE_PK) and a RID Lookup to [EMPLOYEE] will be used instead of using Table Scan. I hope that the RID Lookup can select fewer data from hard disk with matched EMP_ID in both [EMPLOYEE] and [EMP_SUBSIDIARY].

select *
from  EMPLOYEE A
where A.EMP_ID in (select B.EMP_ID
          from   EMP_SUBSIDIARY B
          where   B.EMP_DEPT < ‘D’) OPTION(LOOP JOIN)

Here the following is the query plan, the time is reduced from 8.024 seconds to 1.565 seconds with data cache overhead, and the physical reads are also dropped from 190,621 to 39,044. It shows a wrong IO estimation If you just rely on the SQL Server’s EstimateIO x EstimiateExecutions in the query plan.

There are other even better tuning solutions for this SQL with the A.I. SQL tuning tool in the following:

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

The following SQL with an optimizer hint generate a more complicated query plan with the best execution time of 0.7 seconds. The SQL is tuned by cold cache simulation that data will be flushed before each execution of SQL alternatives.

Optimization in SQL: Determining Stale Statistics in Oracle

Optimization in SQL

You can determine whether statistics are stale in Oracle using two methods. The first is to make Oracle tell you if it considers the stats are stale.

The second involves a comparison of the statistics of what the DBMS assumes a table to be – and what it really is. Here, we’ll help you understand and determine whether the stats are stale.

Other Reasons Why Oracle May Pick a Bad Plan (Aside from Stale Statistics)

Good statistics aren’t necessarily always perfect – they only have to be correct to a degree for the information in the table. In case your statements are running sluggishly because Oracle picked a bad plan, you may also take it as a fair sign that your statistics are stale.

That said, stale statistics aren’t the only culprits behind Oracle selecting a bad plan, triggering the need for optimization in SQL. There are others, such as the following:

  • If your data doesn’t include enough histograms, extended statistics, or joins (both correlated and anti-correlated ones), it may not be uniform enough for Oracle to perceive it as it should.
  • Oracle might find it hard to calculate the amount of data to expect. This generally happens when a query is written in a way that confuses the DBMS.
  • When Oracle lacks a precise representation of the duration involving the completion of one or more operations. The system statistics may be incorrect in such cases.
  • The data set may not be sufficiently represented in Oracle’s version of the statistics. This usually happens in large tables with highly varied data that a histogram cannot accurately represent.
  • Certain indexes typically used by Oracle may have become invalid.

Coaxing Oracle to Determine Stale Statistics

You can have Oracle tell you about stale stats easily if your priority is to improve performance of SQL query.  The catch is, you won’t be able to determine how stale those stats are. However, you will know if there have been enough changes in a table for Oracle to consider regathering statistics on it.

To find out if that’s the case, you’ll need to view the stale stats column in DBA_STATISTICS which you can do with the following query:

select stale_stats

from dba_statistics

where owner = ‘<name of table owner>’’

AND table_name = ‘<name of table>’

The column may return “YES”, “NO” or other results, indicating Oracle’s stance on the stats. “YES” means Oracle is ready to re-gather statistics, whereas “NO” shows Oracle believes the statistics don’t need updating.

The column may return null, indicating incomplete or absent stats altogether. Take care to enter the correct table owner and table names as the query won’t return any rows otherwise.

Checking Stats on Your Own: What to Do in Oracle Database and SQL

When you do this manually, you can find out “how stale” your stats are. That’s because you’ll be able to collect stats on the “stalest” tables first, reducing the number of changes needed to be made to the database. This way, you could also avoid accumulating stats in situations that could lead to contention.

The goal here is to draw a comparison between Oracle’s values and the table’s actual values. Typically, a difference of up to 10 per cent between the two is acceptable. Also, this method requires us to check two separate kinds of statistics –

  • Table Level Statistics – As the name suggests, you can verify various aspects of the table like the following:
  1. A number of rows and empty blocks – You can use a query like this:

DBA_TAB_STATISTICS.NUM_ROWS

select count(*)

from <table name>;

  1. Number of Empty blocks and
  2. A number of blocks taken up by the table – The statement below will help you retrieve the number of “occupied blocks”:

DBA_TAB_STATISTICS.BLOCKS –

DBA_TAB_STATISTICS.EMPTY_BLOCKS

select count(distinct substr(rowid, 7, 9))

from <table name>;

  • Column Level Statistics – These will help you determine things like:
  1. How many distinct values exist in a column – This proves useful for expressions that include “COLUMN = <any value>”.

Try the following:

select count(distinct <columnname>)

from <table name>;

  1. The column’s high and low values – These values prove useful for situations that need range-based predicates, such as those involving COLUMN <= <any value> or COLUMN between <START> and <END>.
  1. The number of nulls in a column – This query should help if you want to view the stats of a particular column: 

with cte (x)

as

(

   select /*+ inline */ <column name>

   from <table owner name>.<table name that you want to check>

)

select (select approx_count_distinct(x) from cte) distinct_values

     , (select count(*) – count(x) from cte) num_nulls

     , (select min(x) from cte) low_value

     , (select max(x) from cte) high_value

from dual

How to Tune SQL Statement with EXISTS Subquery for SQL Server I ?

sql server tuning tools

The following is an example that shows a SQL statement with an Exists subquery. The SQL retrieves records from the DEPARTMENT table that DPT_ID is found in emp_dept of employee table with emp_id > 2700000.

SELECT *
FROM DEPARTMENT
where exists (select ‘x’
         from employee
         where emp_id > 2700000
         and emp_dept=DPT_ID)

Here the following is the query plan in the Tosska proprietary tree format, it takes 2.23 seconds to finish.

The query plan shows two Hash Match from [EMPLOYEE].[EMPLOYEE_PK] to [EMPLOYEE].[EMPS_DPT_INX] and then Merge Join to a sorted [DEPARTMENT] table. This query plan looks reasonable, but the number of records scan from [EMPLOYEE] is too expensive at the first stage, can we use the small [DEPARTMENT] table to scan back the [EMPLOYEE] table to improve the SQL.

Let me rewrite the EXISTS subquery into an IN subquery in the following, but the query plan is not changed as expected.

select  *
from DEPARTMENT
where  DPT_ID in (select   emp_dept
         from     employee
         where  emp_id > 2700000)

I further rewrite the SQL and add the dummy function “isnull(emp_dept,emp_dept)” in the select list, but it cannot stop the operation of Hash Match to [EMPLOYEE].[EMPS_DPT_INX].

select  *
from DEPARTMENT
where  DPT_ID in (select    isnull(emp_dept,emp_dept)
         from      employee
         where   emp_id > 2700000)

To further enforce the restriction for stoping the operation “Hash Match to [EMPLOYEE].[EMPS_DPT_INX]”, I try to add a dummy “group by emp_dept” operation in the subquery.

select  *
from DEPARTMENT
where  DPT_ID in (select    isnull(emp_dept,emp_dept)
         from      employee
         where   emp_id > 2700000
         group by emp_dept)

Here the following is the query plan after the final rewrite, SQL server first uses a Table Scan of [DEPARTMENT] table and Nested Loop of “EMPS_DPT_INX index seek to RID Lookup of [EMPLOYEE]” with the Top 1 operation, so each record from [DEPARTMENT] table will match at most one record from [EMPLOYEE] only. The speed now is 0.024 seconds and is much faster than the original SQL.

Although the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 90 times faster than the original SQL.  

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

How to Tune SQL Statements with CONCAT Operator for MySQL?

oracle sql performance tuning

There may be some business requirements that need to compare concatenate strings and column with a given unknown length of the bind variable. Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables where employee’s department ID must concatenate two strings before it is compared to an unknown length of variable @dpt_var

select * from employee,department
where concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
and  emp_dept= dpt_id

Here the following are the query plans of this SQL, it takes 23.8 seconds to finish. The query shows a “Full Table Scan Employee” to nested loop Department table.

You can see that this SQL cannot utilize index scan even the emp_dept is an indexed field. Let me add a “force index(EMPS_DPT_INX) hints to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition emp_dept >= ” , it is an always true condition that emp_dept should be greater or equal to a smallest empty character. It is to fool MySQL SQL optimizer that emp_dept’s index is a reasonable step.

select  *
from  employee force index(EMPS_DPT_INX),
     department
where  concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
     and emp_dept >= ”
     and emp_dept = dpt_id

Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows that an Index Range Scan is used for Employee table first and then nested loop Department table.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

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!