How to Tune SQL Statement with DECODE Expression for Oracle?

Here the following is an example SQL statement with a DECODE expression syntax.

select  *  from employee
where  decode(emp_dept , ‘AAA’ , ‘ADM’ , ‘AAB’ , ‘ACC’ , emp_dept) = ‘ADM’

Here the following are the query plans of this SQL, it takes 6.41 seconds to finish. The query shows a Full Table Scan of EMPLOYEE table due to the DECODE expression cannot utilize the EMP_DEPT column’s index.

We can rewrite the DECODE expression into the following semantical equivalent SQL statement with multiple OR conditions.

SELECT   *
FROM      employee
WHERE  emp_dept = ‘AAA’
         AND ‘ADM’ = ‘ADM’
         OR  NOT ( emp_dept = ‘AAA’ )
              AND emp_dept = ‘AAB’
              AND ‘ACC’ = ‘ADM’
         OR  NOT ( emp_dept = ‘AAA’
                       OR emp_dept = ‘AAB’ )
              AND emp_dept = ‘ADM’

Here is the query plan of the rewritten SQL and the speed is 0.41 seconds. It is 15 times better than the original syntax. The new query plan shows a BITMAP OR of two INDEX RANGE SCAN of EMP_DEPT index.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other rewrites with even better performance, but it is not suitable to discuss in the 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 with IN Subquery in Certain Environment for Oracle?

Here is an example SQL that retrieves data from EMPLOYEE table with employee’s ID code in EMP_SUBSIDIARY table.

select * from employee a
where a.emp_id
       in (select b.emp_id
             from emp_subsidiary b)

Here the following are the query plan of this SQL, it takes 10.03 seconds to finish.  The query plan is very simple since the syntax of the SQL is not complicated. The query plan shows a full table scan of EMPLOYEE and then nested to the index of EMPSB_EMP_ID, it looks like a good query plan, but I wonder if it is too expensive to have a full table scan of EMPLOYEE table?

In order to ask Oracle to consider other query plans, I added a dummy function Coalesce(b.emp_id,b.emp_id) in the subquery’s select list that artificially adding cost to the driving path from EMPLOYEE to EMP_SUBSIDIARY due to the index EMPSB_EMP_ID is disabled by this dummy function.

SELECT  *
FROM      employee a
WHERE  a.emp_id   IN (SELECT Coalesce(b.emp_id,b.emp_id)
                           FROM     emp_subsidiary b)

The rewritten SQL generates an adaptive query plan and a “nested loops” from EMPSB_EMP_ID to EMPLOYEE by EMPLOYEE_PK index. You can remove the steps in “Id” column with marked ‘-‘ from the plan to verify the result plan. The new plan now takes 4.13 seconds to finish only.

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

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

How to Tune SQL with Exists Operator in Certain Environment for Oracle?

Here is an example SQL that retrieves data from EMPLOYEE table with “emp_id < 710000” and employee’s department code exists in DEPARTMENT table.

select  *
  from   employee
 where emp_id < 710000
      and  exists (select  ‘x’
                    from department
                 where dpt_id = emp_dept)

Here the following are the query plan of this SQL, it takes 34.22 seconds to finish.  The query plan is very complicated, although the SQL is quite simple. It is not abnormal that Oracle uses a complex solution to solve simple data retrieval.  This kind of complex plan steps is suitable for certain environments, but not for a simple database like this. I call it over-optimized query plan, which is due to the under estimated cost of this query plan. For complex plan like this, the cost estimation error is easily be amplified from step to step within the chain of plan steps.

In order to ask Oracle to consider other query plans, I rewrite the EXISTS to IN with a new “group by dpt_id” operation that force Oracle SQL optimizer to execute the subquery first.

SELECT  *
FROM     employee
WHERE  emp_id < 710000
       AND  emp_dept IN (SELECT      dpt_id
                            FROM         department
                            GROUP BY  dpt_id)

The rewritten SQL generates a simpler query plan and it is actually running faster with 5.59 seconds only.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for Oracle automatically, it shows that the rewrite is more than 6 times faster than the original SQL. There is a SQL rewrite with even better performance, it is a little bit complicated to discuss in this short article here. May be we can discuss later.

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

How to Tune SQL with SEMI JOIN by Hints INDEX_DESC Injection for Oracle?

Semi-join is introduced in Oracle 8.0. It provides an efficient method of performing a WHERE EXISTS or WHERE IN sub-queries. A semi-join returns one copy of each row in first table for which at least one match is found in second table, there is no need of further scanning of the second table once a record is found.

SELECT *
     FROM DEPARTMENT
where dpt_id
     in (select emp_dept from EMPLOYEE
        where emp_id >3300000)

Here the following is the query plan of this SQL, it takes 13.59 seconds to finish. The query shows a “NESTED LOOPS SEMI” from DEPARTMENT to EMPLOYEE table.

Basically, this SQL is difficult to optimize by just syntax rewrite due to the simplicity of the SQL syntax that Oracle is easily transformed into a canonical syntax internally, so not much alternative query plan can be triggered by syntax rewrite.

Let’s use Hints injection to the SQL and see if there any brutal force of hints injection can trigger a better performance plan. With our A.I. Hints Injection algorithm applying to the SQL, it comes up with a SQL with extraordinary performance improvement that even I cannot understand at the first glance.

SELECT  /*+ INDEX_DESC(@SEL$2 EMPLOYEE) */ *
FROM     department
WHERE  dpt_id IN (SELECT emp_dept
                           FROM     employee
                          WHERE  emp_id > 3300000)

Here is the query plan of the hints injected SQL and it is now running much faster. The new query plan shows that the “INDEX RANGE SCAN” of EMP_DPT_INX to EMPLOYEE table is changed to “INDEX RANGE SCAN DESCENDING” and the estimated cost is the same as the Original SQL.

The Hints /*+ INDEX_DESC(@SEL$2 EMPLOYEE) */  injected SQL takes only 0.05 second, it is much faster than the original SQL, the reason behind is the employee records creation order in EMPLOYEE table, the higher the emp_id will be created later, so the corresponding records will be inserted into the right hand side of the EMP_DPT_INX index tree nodes. The “INDEX RANGE SCAN” in the original SQL plan that needs to scan a lot of records from left to right direction before it can hit one record for  the condition “WHERE  emp_id > 3300000”.  In contrast, the Hints injected SQL with the “INDEX RANGE SCAN DESCENDING” operation that can evaluate the WHERE condition with only one scan from right to left on EMP_DPT_INX index tree nodes. That explains why the Hints injected SQL outperformed the original SQL by more than 270 times.

It is common that we employ “transaction id”, “serial no” or “creation date” in our application design, this kind of the records are normally created alone with an increasing sequence order, there may be some SQL in your system can be improved by this technique.

This kind of rewrites or Hints injection can be achieved by Tosska SQL Tuning Expert for Oracle automatically, it shows that the Hints injected SQL is more than 270 times faster than the original SQL.

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

How to Tune SQL Statement with Multiple OR Subqueries for Oracle?

Here the following is SQL statement with multiple OR subquery.

SELECT *
FROM employee
WHERE emp_id IN (SELECT emp_id FROM emp_subsidiary where emp_dept = ‘ACC’)
    OR emp_id IN (SELECT emp_id FROM employee where emp_dept = ‘COM’)
    OR emp_id = 600000

Here the following are the query plans of this SQL, it takes 29 seconds to finish. The query plan shows that the OR conditions are partially converted to Union All statement, the “OR emp_id = 600000” condition is not converted to Union All operation, so three is a full table access on Employee in the query plan is found and most of the time is spent on this step.

Let me rewrite the OR conditions in to a subquery with UNION ALL operations in the following.

SELECT *
FROM  employee
WHERE  emp_id IN (SELECT emp_id
                        FROM   (SELECT emp_id
                              FROM    emp_subsidiary
                              WHERE   emp_dept = ‘ACC’
                              UNION ALL
                              SELECT emp_id
                              FROM    employee
                              WHERE   emp_dept = ‘COM’
                              UNION ALL
                              SELECT600000
                              FROM    dual) dt1)

Here is the query plan of the rewritten SQL and the speed is 0.06 seconds. It is 480 times better than the original syntax. The extra “SELECT emp_id” from the “UNION ALL” subquery in green color is used to force the subquery have to be processed in a whole without merging into the main query.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other rewrite with even better performance, but it is not suitable to discuss in the 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 Statement with multiple MAX() functions for Oracle?

Here the following is a very simple SQL statement with two Max() functions in select list.

select max(emp_salary),max(emp_id)
from employee;

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

You can see that this SQL cannot utilize index scan even though the emp_id and emp_salary are indexed. If I change the SQL to select max(emp_salary) only like the following: select max(emp_salary) from employee;

The SQL will run much faster and the emp_salary index will be used.

In order to solve this problem, let me rewrite the SQL into the following syntax. I use two WITH (common table expression) to select each max() function independently and it fully utilize the index in each column.

WITH dt1
     AS (SELECT Max(emp_salary)
             FROM   employee),
     dt2
     AS (SELECT Max(emp_id)
             FROM   employee)
  SELECT *
  FROM   dt1,
                   dt2;

Here is the query plan of the rewritten SQL and the speed is 0.00 seconds which cannot be detected in our timing scale. The new query plan shows that two Index Full Scan (MIN/MAX) for each column are used now.

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

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

Improve Oracle Database Performance Tuning with Tosska’s AI-Enabled Tools

improve oracle database performance tuning

Oracle database performance tuning is a crucial method for accelerating the application function and data retrieval process. In order to speed up the performance of the application, the developer or the database administrator has to expedite query response time. This implies that they must have a deep understanding of how well the database is organized and how it satisfies its purpose.

Generally, SQL tuning means minimizing the query plan steps which in turn decreases the wait time and time cost. There are a number of ways you can implement to performance tune your Oracle database. In most of the techniques, you would require changing the source code. However, at Tosska Technologies, you will get products that are AI-based and don’t require touching the source code. Let us introduce you to our amazing products that are extremely helpful in Oracle database performance tuning.

3 Brilliant Tools Offered by Tosska for Improve Oracle Database Performance Tuning

Tosska provides solutions for SQL and database related performance optimizations and improvements. We are one of the very few companies in the world that uses artificial intelligence technology to solve different database performance problems. We help our customers to minimize their hardware investment and increase their level of database application service.

We have enlisted below some tools provided by Tosska Technologies that help to improve Oracle database performance successfully.

  1. Tosska SQL Tuning Expert (TSE™) for Oracle®

It’s an innovative machine or SQL tuning tool that upgrades your SQL statements without involving the user. By just point and click, the tool will help you with the ultimate SQL performance solution. The only thing you are required to do is to enter your problematic SQL statement into the product and press the button.

You are free from doing any testing, guessing or analyzing during the complete SQL tuning process. The enhanced SQL statement will get benchmarked with your original SQL statement side by side without any doubt. It isn’t just another tool but provides users with better statistics analysis, query plan visualization, and more.

2. Tosska SQL Tuning Expert Pro (TSE Pro™) for Oracle®

One of the best parts of having this tool is that it will tune your Oracle database without touching your source code. Surprised? But, it’s true. Tosska SQL Tuning Expert Pro is a tool that helps in upgrading SQL performance without even touching the source code of the program. Besides this, users can even use various other performance query plans for different sizes of production databases without the need of keeping a number of versions of the program source. It is specially designed for package application users who don’t hold the source code of their applications.

With our SQL Tuning Expert Pro, SQL tuning gets even easier for application developers than ever before. You can improve the SQL statement without conducting any time-consuming test, program implementation, and integration test that are essential in the software development cycle. Apart from these brilliant SQL tuning attributes, strong indexes recommendation function is also given that assists users in reviewing and discovering more potential indexes that are extremely crucial but may be missing in the current database schema.

To be more specific, it’s an extremely brilliant and cost-aware index advice engine that lets users analyze their existing database schema- in case any new indexes are available that are helpful in enhancing an assigned SQL workload. The engine is capable of handling up to thousands of SQL statements and offers you a reasonable recommendation that even human experts are not able to accomplish.

3. Tosska In-Memory Maestro (TIM™) for Oracle®

Tosska In-Memory Maestro for Oracle is a tool that automates the process of optimizing In-memory SQL and offers recommendations for In-memory objects for a given SQL workload. Our proprietary artificial intelligence engine is highly beneficial in achieving the aforesaid. The tool also offers a user-friendly In-memory simulation function for users so that they can virtually estimate their in-memory table objects for one or more SQL workload captured from AWR or SGA without actually populating those table objects.

Final Words

Nowadays, most of the highly succeeding companies make use of artificial intelligence to upgrade themselves, then why not you too implement it! With our AI-enabled tool, you can improve oracle database performance easily and in less time. Even, in one of our tools, the source code of the program will remain untouched, yet the problem will be solved. The tools really don’t require the user’s involvement in fixing the queries. Therefore, if you are searching for any such product, then browse our products and get the one that suits you the best.

Tosska Technologies aims at helping users to smooth out the problem with the new and advanced technologies in the market. You should surely give it a try; we assure you will love the product offered by us.