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.

Query Performance Tuning: Making an SQL Monitor Report

Creating a SQL Monitor Report plays an important role in database optimization as it helps the user observe other occurrences during the execution of long-running statements. 

In this post, we’ll discuss how to create one such report that may help you during query performance tuning

SQL Monitor Report: Bringing DBAs One Step Closer to Database Query Optimization

To begin with, you need to make sure your database has the tuning and diagnostic pack. Otherwise, Oracle will not authorize the creation of SQL Monitor Reports.

Also, such reports can be made after an adequate amount of time has passed. The wait is to allow query bottlenecks to reveal themselves. This is typically done for seemingly endless queries that run for long periods of time. However, in general, creating SQL monitor reports is recommended for completed queries.  

Let’s look at an example: A DBA has a simple plan with a hash join involving two big tables. Suppose one of these tables takes two seconds to undergo a complete table scan, whereas the second one takes nine seconds. 

Although only around two seconds out of a total of eleven seconds are sent on the first table, it will appear as though a hundred percent of the query time is being spent on it if you create a SQL Monitor report during the first two seconds.

Creating Reports for Excessively Long Execution Plans

Really long execution plans – those that exceed three hundred lines – don’t have a SQL monitor report generated for them by default. This gets cumbersome because long execution plans are where these reports are needed the most! 

In such cases, there are two things you can do to make the database generate a report. These are – 

  1. Prior to issuing the query in question, generate the following in the session operating the query:

alter session set “_sqlmon_max_planlines” = 800;

2. Apply the following hint while executing the query: 

/* + monitor */ 

How to Create an HTML Version of the Monitor Report 

The HTML version of a SQL Monitor report offers some more details as compared to its text report. This is why it is often recommended by database professionals, with the help of the following query: 

Select dbms_sqltune.report_sql_monitor(

sql_id => ‘&v_sql_id.’,

Session_id => ‘&v_session_id.’,

Session_serial => ‘&v_serial.’,

Type => ‘HTML’,

Report_level => ‘ALL’,

Inst_num => ‘&v_instance.’ )report

from dual;

Not every variable needs to be plugged in – you just require variables sufficient to enable Oracle to recognize the particular SQL\session combination. And if there is only a single session executing the statement on the entire database, only the sql_id is enough.

Creating a Text Monitor Report Instead

In case you’d rather make a text report – whether if it’s due to some problems with an HTML report, or simply preference – here’s how to do it – 

Select dbms_sqltune.report_sql_monitor(

sql_id => ‘&v_sql_id.’,

Session_id => ‘&v_session_id.’,

Session_serial => ‘&v_serial.’,

Type => ‘TEXT’,

Report_level => ‘ALL’,

Inst_num => ‘&v_instance.’ )report

from dual;