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 with LIKE ‘%ROGER%’ Expression for Oracle?

The LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters. The LIKE operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching.

Here is an example SQL that retrieves data from EMPLOYEE table employee’s name with a string pattern like “ROGER%”. If the emp_name is indexed, the following SQL will utilize index of the emp_name and the speed of the SQL will be fine.

select *
from employee
where emp_name like ‘ROGER%’;

If user is looking for emp_name with pattern like ‘%ROGER%’, Oracle SQL Optimizer cannot utilize emp_name index to speed up the process, full table scan is normally be used and the performance will be bad.

select *
from employee
where emp_name like ‘%ROGER%’;

Here the following are the query plan of this SQL, it takes 5.88 seconds to finish. The query shows a “Full Table Scan” of employee table.

You can see that this SQL cannot utilize index scan even the emp_name is indexed. Let me add a hints /*+ INDEX(@SEL$1 EMPLOYEE) */ to the SQL and ask Oracle to use index to retrieve records.

SELECT    /*+ INDEX(@SEL$1 EMPLOYEE) */ *
FROM        employee
WHERE    emp_name LIKE ‘%ROGER%’

Here is the query plan of the rewritten SQL and it is now running faster. The new query plan shows that an Index Full Scan is used although the estimated cost is higher than the Original SQL.

You can also use hints /*+ index(employee EMP_NAME_INX) */  explicitly, if the  /*+ INDEX(@SEL$1 EMPLOYEE) */ hints doesn’t work for you.

SELECT    /*+ index(employee EMP_NAME_INX) */ *
FROM        employee
WHERE    emp_name LIKE ‘%ROGER%’

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. There is a SQL hints injection 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 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/

Oracle Database and SQL: Tips for MySQL Database Performance Tuning

Until now we have learned a lot about Oracle database and SQL performance tuning. Now, let’s discuss MySQL database performance tuning. Just like any other relational database, MySQL could also be a nightmare to many. It can crawl to a halt at a moment’s notice and in the next moment, you will find it leaving your apps in the lurch and your business on the line. The fact is, regular errors underlie most MySQL performance issues. 

To ensure your MySQL server bustles along at great speed, providing consistent and stable performance, it’s imperative to eradicate such mistakes that are often confused by some subtlety in your configuration trap or workload. Just like any other performance tuning tips for Oracle database and SQL, MySQL too has some of its own performance tuning techniques that we have described in this blog. Let’s get started without wasting more words-

Oracle Database and SQL- Know How to Tune MySQL Database Performance

Fortunately, many MySQL performance problems have similar solutions which make both tuning MySQL and troubleshooting a manageable task. 

Here are a few tips for getting great performance out of MySQL.

Tip 1# Profile Your Workload

The best way you can understand how your server invests its time is to profile its workload. By doing so you could expose the most expensive queries for further tuning. When you issue a query against the server, you don’t have to care much about anything else except how quickly it completes. Therefore, time is the most crucial metric to consider here. 

The most significant way of profiling your workload is using a tool such as MySQL Enterprise Monitor’s query analyzer. Such a tool is meant to capture queries that are executed by the server. Furthermore, it returns a table of tasks sorted by decreasing order of response time, quickly bubbling up the most time-consuming and expensive jobs to the top so that you can find where you need to put your efforts. Workload-profiling tools group identical queries together that allow you to find slow queries as well as the queries that are fast but executed multiple times. 

Tip 2# Recognize the Four Basic Resources

For functioning, a database server requires four basic resources- Network, CPU, Disk, and memory. If any of these resources are weak, overloaded, or erratic, then the database server is most likely to perform badly. Recognizing these fundamental resources is crucial in two specific areas: selecting troubleshooting and hardware issues. 

While you choose hardware for MySQL, you must ensure the components perform well all around. Just as essential, ensure to balance them reasonably well against each other. Often, businesses choose servers having fast CPUs and disks but that are starved for memory. In some cases, adding memory is a cheap way of enhancing performance by order of magnitude, specifically on workloads that are disk-bound. This might seem counterintuitive, but in various cases, disks are overused as there isn’t sufficient memory to hold the server’s working set of data. 

Another good example of this perspective relates to CPUs. In most conditions, MySQL performs well with fast CPUs as every query runs in a single thread and can’t be parallelized across CPUs. 

During troubleshooting, examine the performance and utilization of all the four resources with much care and determine whether they are performing badly or are simply being asked to execute excessive work. This knowledge can help solve issues instantly. 

Tip 3# Don’t use MySQL as a Queue

Queues and queue-like access patterns can sneak into your application without even letting you know. For instance, if you set the status of an item so that a specific work process can claim it before working on it, then you are ignorantly formulating a queue. Marking emails as unsent, sending them and then remarking them as sent in a common example. 

Queues create problems for two big reasons: they serialize your workload which prevents tasks from being done in parallel, and they mostly result in a table that includes work in process as well as earlier data from jobs that were executed long ago. Both add concealment to the application and load to MySQL. 

Tip 4# Firstly Filter Results by the Cheapest 

The best way of optimizing MySQL is to do imprecise, cheap work first, then the precise and hard work on the smaller that will result in a set of data. 

For instance, consider you are searching for something within an assigned radius of a geographical point. The initial tool in many programmers’ toolbox is the great-circle (Haversine) formula. This will compute the distance along the sphere’s surface. The trouble with this technique is that the formula needs a variety of operations related to trigonometry, which are very intensive toward the CPU. Great-circle calculations tend to perform slowly and make the machine’s CPU utilization skyrocket. 

Before you implement the great-circle formula, cut down your records to a small subset of the total, and scrape the resulting set to a precise circle. A square that comprises the circle either precisely or precisely is a simple way of doing this. That way, the world outside the square never gets hit with all those expensive trig functions. 

Tip 5# Understand the Two Scalability Death Traps

Scalability isn’t as fuzzy as you may think. In fact, there are explicit mathematical definitions of scalability that are defined as equations. Such equations emphasize why systems don’t scale and why they should. Consider the Universal Scalability Law, a definition that is convenient in expressing and quantifying a system’s scalability qualities. It explains scaling issues in terms of two elementary costs: crosstalk and serialization. 

Parallel processes that must hold for something serialized to take place are inherently limited in their scalability. Similarly, if such processes are required to chat with each other all the time for coordinating their work, they restrict each other. If you avoid crosstalk and serialization, your application could scale much better.

Don’t Overlook Oracle Database and SQL Performance: Here’s why

oracle database and sql

Being a DBA is not always a fun job, thanks to certain time-consuming tasks that it entails. One of these is to ensure optimal Oracle database and SQL performance. Typically, it is done by spending a lot of time tuning the long list of SQL statements and software code in order to improve efficiency and enhance access. However, SQL is just one aspect that is related to the performance of database systems.  

Database Administrators also need to invest their time in enhancing the design, physical structure, and specifications of the database objects. These objects are the tables, indices, and the information stored over several files. In the case of data inefficiency, it becomes necessary to observe and modify the actual construction and composition of database objects on a consistent basis. This is because any amount of SQL performance tuning is bound to fall short in a database that is improperly organized or poorly constructed.  

Optimizing Oracle Database and SQL: 5 Important Techniques 

The DBA has to be aware of all the specifications that the database management systems consist of as this knowledge will enable them to use the right techniques to optimize database constructs.

A majority of the most popular DBMSs are compatible with all the methods we have mentioned below, though they may be used differently depending on the database. Let’s take a look:

  1. Indexing: An essential aspect of the Oracle database and SQL performance tuning process is by selecting the right indices and alternatives in order to enable efficient queries. 
  2. Clustering: This involves implementing the physical pattern of data on the disk so that it is clustered on the same page whenever accessed in a particular order. 
  3. Compressing: Data is compressed by decreasing storage requirements, thereby allowing more of it to be stored in a smaller amount of space. This also reduces storage expenses and enhances access if you can add a larger number of rows per page.
  4. Freeing Up Space: Assigning extra room for data growth allows new data to be added to its table easily without leaving the table disorganized.
  5. Partitioning: This entails the segregation of one database table into various sections that are saved in several files. This can be done in multiple ways; by partitioning one file in the same computer, partitioning using shared-disk clustering or by shared-nothing partitioning, depending on the DBMS in question.
  6. File Organizing and Placement: Allocating data from both – database systems and data files – to the correct places is a big step in organizing data and improving Oracle and SQL database performance.
  7. Checking the Page Size: The size of the block or the page determines how efficiently data can be stored and accessed, which is why it is vital to use the suitable page size. The smaller the size of the page, the fewer rows per page, which increases sequential data access requirements.  
  8. Interleaving: Merging all the data from several tables in a sequence into a file helps enhance join performance. However, this method seems to have become less popular than it used to be.
  9. Reorganizing Database Objects: Eliminating the defects from the database by reorganizing and arranging database objects is a well-used technique in SQL performance tuning. In fact, it enormously increases performance, especially if the data was previously fragmented, disorganized or scattered in some way.
  10. Denormalization: This method is considered as a last resort attempt in case the database is unable to perform optimally with a completely normalized implementation. This is because it differs from the logical design. 

All of these techniques are useful and should be considered when the DBA creates a plan for tuning and monitoring the database. Each aspect may not necessarily be applicable to every database object but it must be analyzed for its applicability all the same. Moreover, techniques that are not applicable during initial implementation may turn out to be useful as the application undergoes changes over time in various aspects like data volume, usage, and database characteristics.