improve performance of sql query - Tosska Technologies Limited

2023-04-27

How to Tune SQL with DB Link for Oracle I?

Here is an example SQL query used to calculate the average salary of employees on the remote database @richdb in each department in the local database whose department name starts with the letter “D”.

SELECT   Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT dpt_id
                      FROM   department
                      WHERE dpt_name LIKE ‘D%’)
GROUP BY emp_dept

Here the following is the query plan of this SQL, it takes 9.16 seconds to finish. The query plan shows a Nested Loops from DEPARTMENT in local to EMPLOYEE in the remote database. Due to the size of the EMPLOYEE table being much larger than that of the DEPARTMENT table, the nested loop join path is not optimal in this case.

To ask Oracle to consider doing the join operation in the remote database @richdb, I added a Hint /*+ DRIVING_SITE(employee) */ to tell Oracle to use EMPLOYEE table’s database @richdb as the driving site for the distributed query.

SELECT   /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT dpt_id
                      FROM   department
                      WHERE dpt_name LIKE ‘D%’)
GROUP BY emp_dept

The following query shows the driving site is changed to @richdb and remote retrieves DEPARTMENT data from the “local” database. Now the speed is improved to 5.94 seconds. But the query plan shows a little bit complicated, there is a view that is construed by a Hash Join of two “index fast full scan” of indexes from EMPLOYEE and DEPARTMENT.

I further change the SQL and added a dummy operation Coalesce(dpt_id,dpt_id) in the select list of the subquery to block the index fast full scan of the DEMPARTMENT table.

SELECT   /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT Coalesce(dpt_id,dpt_id)
                      FROM   department
                      WHERE dpt_name LIKE ‘D%’)
GROUP BY emp_dept

The change gives the SQL a new query plan shown in the following, the performance significantly improved to 0.71 seconds. You can learn how the dummy operation Coalesce(dpt_id,dpt_id) affected the Oracle SQL optimizer decision in this example.

This kind of rewrite can be achieved by Tosska DB Ace for Oracle automatically, it shows that the rewrite is almost 13 times faster than the original SQL.

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

2023-02-162023-02-18

How to Tune SQL with IN Subquery with Intersect for Oracle?

Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT table with the employee’s grade code in the GRADE table.

SELECT emp_id,
       emp_name,
       dpt_name
FROM   employee,
       department
WHERE  emp_dept = dpt_id
       AND emp_grade IN (SELECT grd_id
                         FROM grade
                         WHERE grd_min_salary < 200000)
and emp_dept < ‘D’

Here the following is the query plan of this SQL, it takes 8.3 seconds to finish. The query plan shows a Hash Join with GRADE and EMPLOYEE and then hash join to DEPARTMENT. It looks like Oracle gave up any Nested Loops operations after the actual number of rows is returned from the GRADE table in this adaptive plan.

In order to ask Oracle to consider the Nested Loops operations, I added an extra Intersect operation in the subquery to rapidly narrow down the result set of grd_id returned from the GRADE table first.

SELECT emp_id,
       emp_name,
       dpt_name
FROM   employee,
       department
WHERE  emp_dept = dpt_id
       AND emp_grade IN (SELECT grd_id
                         FROM   grade
                         WHERE  grd_min_salary < 200000                          INTERSECT SELECT e1.emp_grade
                                   FROM employee e1
                                   WHERE emp_dept < ‘D’)
       AND emp_dept < ‘D’

The rewritten SQL generates a query plan that is entirely different from the original query plan, The new plan is using “Nested Loops” from DEPARTMENT to EMPLOYEE as the first steps and then Hash Join to the GRADE table. The new plan now takes 0.81 seconds only.

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

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

2022-10-10

How to Tune SQL Statement with CASE Expression by Hints Injection for Oracle?

Here the following is a simple SQL statement with a CASE expression syntax.

SELECT *
FROM employee
WHERE
CASE
WHEN emp_salary< 1000
THEN ‘low’
WHEN emp_salary>100000
THEN ‘high’
ELSE ‘Normal’
END = ‘low’

Here the following are the query plans of this SQL, it takes 4.64 seconds to finish. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_salary index. It is because the CASE statement disabled the index range search of the emp_salary index.

Commonly, we will try to enable index search by forcing the SQL with an Index hint as the following:

SELECT /*+ INDEX(@SEL$1 EMPLOYEE) */ *
FROM employee
WHERE CASE
WHEN emp_salary < 1000
THEN ‘low’
WHEN emp_salary > 100000
THEN ‘high’
ELSE ‘Normal’
END = ‘low’

Although the CASE statement disabled the index range search of the emp_salary index, an index full scan is now enabled to help filter the result more quickly compared with the original full table scan of the EMPLOYEE table.

This hint injection takes 0.38 seconds and it is 12 times faster than the original SQL will full table scan. For this kind of SQL statement that you cannot change your source code, you can use SQL Patch with the hints and SQL text deployed to the database without the need of changing your source code.

If you can modify your source code, the best performance will be to rewrite the CASE expression into the following syntax with multiple OR conditions.

SELECT *
FROM employee
WHERE emp_salary < 1000
AND ‘low’ = ‘low’
OR NOT ( emp_salary < 1000 )
  AND emp_salary > 100000
  AND ‘high’ = ‘low’
OR NOT ( emp_salary < 1000
  OR emp_salary > 100000 )
  AND ‘Normal’ = ‘low’

The new query plan shows an INDEX RANGE SCAN OF emp_salary index.

This kind of rewrite and hints injection can be achieved by Tosska SQL Tuning Expert Pro for Oracle automatically,

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

2022-09-06

How to Tune SQL Statement with LCASE function on index field?

Some business requirements may need to compare the lower case of an indexed column to a given string as a data retrieval criterion.

Here is an example SQL that retrieves records from the EMPLOYEE table employee if the lower case of the name is equal to the string ‘richard’.

select *
from employee
where LCASE(emp_name)=‘richard’

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

You can see that this SQL cannot utilize index scan even if the emp_name is an indexed field. Let me add a “Force Index(emp_name_inx)“hint 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_name >= ””, it is an always true condition that emp_name should be greater or equal to a smallest empty character, it is used to increase the cost of not using emp_name_inx index. There is another condition added “emp_name is null” to correct this condition if emp_name is a null value.

select *
from employee force index(EMPS_NAME_INX)
where LCASE(emp_name) = ‘richard’
and ( emp_name >= ”
or emp_name is null )

Here is the query plan of the rewritten SQL and it is running much faster. The new query plan shows that an Index Scan is used now and takes 2.79 seconds only.

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

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

2022-07-052022-07-05

How to use ROWID to improve an UPDATE statement for Oracle?

Here the following is an Update SQL with a subquery that updates the EMPLOYEE table if the emp_dept satisfies the records returned from a subquery.

update  employee
set  emp_name = ‘testing’
where  emp_dept IN (select dpt_id
from department
where dpt_name like ‘A%’)
and emp_grade>2000

You can see Oracle uses a Hash join of the DEPARTMENT table and EMPLOYEE table to execute the update process. This query plan takes 1.96 seconds to complete and no index is used even though emp_dept, dpt_id, and emp_grade are indexed columns. It looks like the most expansive operation is the Table Access Full scan of the EMPLOYEE table.

Let’s rewrite the SQL into the following syntax to eliminate EMPLOYEE’s Table Access Full operation from the query plan. The new subquery with the italic Bold text is used to force the EMPLOYEE to extract records with emp_dept in the DEPARTMENT table with the dpt_name like ‘A%’. The ROWID returned from the EMPLOYEE(subquery) is to make sure a more efficient table ROWID access to the outer EMPLOYEE table.

UPDATE  employee
SET emp_name=‘testing’
WHERE   ROWID IN (SELECT  ROWID
FROM employee
WHERE emp_dept IN (SELECT  dpt_id
FROM department
WHERE dpt_name LIKE‘A%’))
AND emp_grade > 2000

You can see the final query plan with this syntax has a better cost without full table access to the EMPLOYEE table. The new syntax takes 0.9 seconds and it is more than 2 times faster than the original syntax.

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

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

2022-07-052022-07-05

How to build indexes for multiple Max() functions for SQL Server?

For some SQL statements with multiple Max() functions in the select list and nothing in the Where clause, we have different methods to create new indexes to improve the SQL speed.

Here is an example SQL, it is to retrieve the maximum name and age from the employee table.
select max(emp_name),
max(emp_age)
from employee

The following is the query plan that takes 9.27 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS cannot recommend any index to improve the SQL.

For this kind of SQL that we can consider building a composite index or two individual indexes for emp_name and emp_age. A new composite of these two columns (emp_age, emp_name) can improve the SQL around 7 times. The following is the query plan shows that the new composite index is used, but it has to scan the entire index for these two stream aggregate operations before getting the max(emp_name) and max(emp_age).

How about if we build two individual indexes for emp_name and emp_age. The following is the result and query plan of these two indexes created. A Top operator selects the first row from each index and returns to the Stream Aggregate operation, and then a Nested Loops join the two maximum results together. It is 356 times much faster than the original SQL.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically:
Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited

2022-05-262023-02-18

Measures to Improve Performance of SQL Query?

That’s not bad enough that you call it SQL whereas your boss pronounces it ‘sequel’. But also, you now suffer from “Super Slow Query Syndrome,” and sometimes, your questions bomb without effect.

Don’t worry. We have your back. We recently had a powwow with a lot of caffeine to think about our favorite tips to fix queries. With the help of this article, we will dig into how we can resolve SQL queries and improve the performance of SQL queries with new tips and tricks, such as action plans, references, wild cards, and much more.

In fact, we have combined all our famous skills into one, so you can increase your SQL intelligence by six minutes flat.

The issues faced by the companies in SQL Server performance often lead to focusing on using tuning tools and development strategies. This will help to analyze and process queries faster and eliminate operational issues, troubleshoot poor performance, avoid any chaos, or reduce the impact on the SQL Server database.

What is SQL Query Optimization?

Optimizing SQL Query is the process of writing considerate SQL queries to improve database performance. During development, the amount of data accessed and tested is not much. Thus, it becomes easy for developers to get a prompt response to their raised questions. But the problem starts when the project becomes live and large data starts to flood the database. These kinds of situations reduce the resolving process and performance.

A request for data or information from a database is called a Query, and you need to write a pre-defined set of code that is understandable to the database. Structured Query Language (SQL) and other query languages recover or manage data from related databases.

There are different formats to write a query in the database, using various algorithms. A query that is incomplete or written poorly can lead to a lot of resource consumption, and also can take a lot of time in execution, which possibly causes a loss in services. A proper query can reduce implementation time and lead to better SQL results.

SQL query optimization’s main purpose is to reduce response time and improve query performance, Reduce CPU performance time for faster results and reduce the number of resources used to improve the output.

Ways to Improve SQL Query Performance

Avoiding unnecessary columns in the SELECT section

To improve MySQL functionality, it’s recommended to specify columns in the SELECT section, instead of using SELECT*. As irrelevant columns create more load in the database, it slows down the performance of the whole system.

Using internal joining, rather than external joining if possible

Use external joining only if necessary. Excessive use of it not only limits database performance but also limits MySQL query options, resulting in slower SQL statements.

Using DISTINCT and UNION only if necessary

By using UNION and DISTINCT operators while there are no major objective results in unwanted filtering and reduced SQL performance. To improve the performance and bring efficiency to the process we can always use UNION ALL, rather than UNION.

Using the ORDER BY clause

To get more clear results it is important to use the ORDER BY clause. It not only brings

advantages for database admins but also increases performance in its execution.

SQL Query Performance Tuning: Best Practice

SQL Query tuning is one of the fastest ways to improve the performance of SQL Server. Set procedures and processes are used to improve the performance and resolve the database-related queries this is called Tuning the SQL server. SQL tuning includes several features, including identifying which queries are slower and utilizing them to work more efficiently. Multiple communication databases like MySQL and SQL Server will benefit from SQL tuning.

The Database Performance Analyzer can attempt to troubleshoot server performance issues in the system. But these measures are expensive, and they may not work to solve the problem of slow-moving queries. Tuning SQL functionality helps you to identify poorly written SQL queries and invalid indexing conditions. After doing so, you may find that you do not need to invest in hardware upgrades or technical details.

Tuning SQL functionality can be difficult, especially if done manually. Believe it or not, the slight changes can have major effects on SQL Server and database performance. Hence, there is a need for practical SQL Query performance tools.

To conclude, generally, the best practices of SQL Query performance Tuning include proper indexing that can be done by the Execution Plan tool in SQL Server. Additionally, avoiding coding loops and correlating SQL subqueries.

Tosska SQL Tuning Expert (TSE™) v4 is one of the best SQL tuning tool available in the market. It helps in tuning the SQL even without any source code.

2021-04-232023-02-19

Optimization in SQL: Determining Stale Statistics in Oracle

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:

A number of rows and empty blocks – You can use a query like this:

DBA_TAB_STATISTICS.NUM_ROWS

select count(*)

from <table name>;

Number of Empty blocks and
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:

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>;

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

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)

(

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