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.
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,
We know the order of the columns in a composite index will determine the usage of the index or not against a table. A query will use a composite index only if the where clause of the query has at least the leading/left-most columns of the index in it. But, it is far more complicated in correlated subquery situations. Let’s have an example SQL to elaborate the details in the following.
SELECT D.*
FROM department D
WHERE EXISTS (SELECT Count(*)
FROM employee E
WHERE E.emp_id < 1050000
AND E.emp_dept = D.dpt_id
GROUP BY E.emp_dept
HAVING Count(*) > 124)
Here the following is the query plan of the SQL, it takes 10 seconds to finish. We can see that the SQL can utilize E.emp_id and E.emp_dept indexes individually.
Let’s see if a new composite index can help to improve the SQL’s performance or not, as a rule of thumb, a higher selectivity column E.emp_id will be set as the first column in a composite index (E.emp_id, E.emp_dept).
The following is the query plan of a new composite index (E.emp_id, E.emp_dept) and the result performance is not good, it takes 11.8 seconds and it is even worse than the original query plan.
If we change the order of the columns in the composite index to (E.emp_dept, E.emp_id), the following query plan is generated and the speed is improved to 0.31 seconds.
The above two query plans are similar, the only difference is the “2” operation. The first composite index with first column E.emp_id uses an INDEX RANGE SCAN of the new composite index, but the second query plan uses an INDEX SKIP SCAN for the first column of E.emp_dept composite index. You can see there is an extra filter operation for E.emp_dept in the Predicate Information of INDEX RANGE SCAN of the index (E.emp_id, E.emp_dept). But the (E.emp_dept, E.emp_id) composite index use INDEX SKIP SCAN without extra operation to filter the E.emp_dept again.
So, you have to test the order of composite index very carefully for correlated subqueries, sometimes it will give you improvements that exceed your expectation.
This kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.
Here the following is the description of the ORDERED hint.
The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.
If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.
We usually use an ORDERED hint to control the john order, but how this hint causes a SQL with a subquery. Let’s use the following SQL as an example to see how ORDERED hint works for a subquery.
SELECT *
FROM DEPARTMENT
where dpt_id
in (select emp_dept from employee
where emp_id >3300000)
Here the following is the query plan of the SQL, it takes 68.84 seconds to finish. The query shows a “TABLE ACCESS FULL” of the DEPARTMENT table and “NESTED LOOPS SEMI” to an “INDEX RANGE SCAN” of EMPLOYEE.
If you think it is not an effective plan, you may want to try to reorder the join path and see if an ORDERED hint is working or not in a subquery case like this:
SELECT /*+ ORDERED */ *
FROM department
WHERE dpt_id IN (SELECT emp_dept
FROM employee WHERE emp_id > 3300000)
Here is the query plan of the hinted SQL and the speed is 3.44 seconds which is 20 times better than the original SQL. The new query plan shows the new join order that EMPLOYEE is retrieve first and then hash join DEPARTMENT later. You can see the ORDERED hint will order the subquery’s table first. This new order clauses a new data retrieval method from the EMPLOYEE table, it makes the overall performance much better than the original query plan.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other hints-injection SQL with better performance, but it is not suitable to discuss in this short article, maybe I can discuss later in my blog.
Some mission-critical SQL statements are already reached their maximum speed within the current indexes configuration. It means that those SQL statements are not able to be improved by syntax rewrite or Hints injection. Most people may think that the only way to improve this kind of SQL may be by upgrading hardware. For example, the following SQL statement has every column in WHERE clause is indexed and the best query plan is generated by Oracle already. There is no syntax rewrite or hints injection that can help Oracle to improve the SQL performance.
SELECT EMP_ID,
EMP_NAME,
SAL_EFFECT_DATE,
SAL_SALARY
FROM EMPLOYEE,
EMP_SAL_HIST,
DEPARTMENT,
GRADE
WHERE EMP_ID = SAL_EMP_ID
AND SAL_SALARY <200000
AND EMP_DEPT = DPT_ID
AND EMP_GRADE = GRD_ID
AND GRD_ID<1200 AND EMP_DEPT<‘D’
Here the following is the query plan and execution statistics of the SQL, it takes 2.33 seconds to extract all 502 records. It is not acceptable for a mission-critical SQL that is executed thousands of times in an hour. Do we have another choice if we don’t want to buy extra hardware to improve this SQL?
Introduce new plans for Oracle’s SQL optimizer to consider
Although all columns in the WHERE clause are indexed, can we build some compound indexes to help Oracle’s SQL optimizer to generate new query plans which may perform better than the original plan? Let’s see if we adopt the common practice that the following EMPLOYEE’s columns in red color can be used to compose a concatenated index (EMP_ID, EMP_DEPT, EMP_GRADE).
WHERE EMP_ID = SAL_EMP_ID
AND SAL_SALARY <200000
AND EMP_DEPT = DPT_ID
AND EMP_GRADE = GRD_ID
AND GRD_ID<1200
AND EMP_DEPT<‘D’
CREATE INDEX C##TOSSKA.TOSSKA_09145226686_V0043 ON C##TOSSKA.EMPLOYEE
(
EMP_ID,
EMP_DEPT,
EMP_GRADE
)
The following is the query plan after the concatenated index is created. Unfortunately, the speed of the SQL is 2.40 seconds although a new query plan is introduced by Oracle’s SQL optimizer.
To be honest, it is difficult if we just rely on common practices or human knowledge to build indexes to improve this SQL. Let me imagine that if we got an AI engine that can help me to try the most effective compound indexes to explore Oracle’s SQL optimizer potential solutions for the SQL. The following concatenated indexes are the potential recommendation by the imagined AI engine.
CREATE INDEX C##TOSSKA.TOSSKA_13124445731_V0012 ON C##TOSSKA.EMP_SAL_HIST
(
SAL_SALARY,
SAL_EFFECT_DATE,
SAL_EMP_ID
)
CREATE INDEX C##TOSSKA.TOSSKA_13124445784_V0044 ON C##TOSSKA.EMPLOYEE
(
EMP_GRADE,
EMP_DEPT,
EMP_ID,
EMP_NAME
)
The following is the query plan after these two concatenated indexes are created and the speed of the SQL is improved to 0.13 seconds. It is almost 18 times better than that of the original SQL without the new indexes.
The above indexes include some columns that appear on the SELECT list of the SQL and there is a correlated indexes relationship for Oracle’s SQL optimizer to generate the query plan, it means that missing any columns of the recommended indexes or reshuffling of the column position of the concatenated indexes may not be able to produce such query plan structure. So, it is difficult for a human expert to compose these two concatenated indexes manually. I am glad to tell you that this kind of AI engine is actually available in the following product.
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.
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.
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.
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
SELECT 600000
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.
Application package software is a collection of software programs that is developed for the purpose of being licensed to third-party organizations. Although a package software may be tailored for a user’s specific needs through parameters or tables, the software itself is not developed specifically for an organization. So, users do not own the source code and have no way to modify the embedded SQL statements for performance tuning purpose. There are a lot of application packages built on Oracle RDBMS such as Siebel, PeopleSoft JD Edwards, SAP and so on. In order to help application packages’ users, Oracle provides some features for helping users to tune their SQL statements without the need to change their source code.
SQL profile
It is a profile generated by Oracle SQL Tuning Advisor. A SQL profile contains corrections for wrongly estimated statistics, auxiliary information. Therefore, SQL profile just guides the optimizer to a better plan, but they do not guarantee the same plan each time the statement is parsed. For certain SQL statements, no matter how good the statistics are corrected, Oracle SQL optimizer is still not able to generate a better plan in specific environments. For these kinds of SQL statements, human intervention is necessary, but SQL profile is not a convenient tool for developers to force Oracle to pick up a new plan without changing the program source code.
SQL plan baselines and stored outlines
Due to the Oracle environment changes or Oracle database version upgrade, it might target Oracle SQL optimizer to generate new plans for certain SQL statements. If it is not good, and we need something to preserve the old plans for the new environment. To achieve SQL plan stability, stored outlines was the major tool in earlier releases of Oracle Database. This feature is still supported in Oracle Database 11g; however, it might be depreciated in the future releases and replaced by SQL plan management. The mechanism of SQL Plan Baselines is to preserve the performance of specified SQL statements, regardless of changes in the database environment or release upgrade. Furthermore, create Plan Baselines manually for a SQL statement is possible, and this technique can help developers to guide Oracle SQL optimizer to generate a specific plan for a bad performance SQL statement. So, when Oracle SQL optimizer receive the same SQL statement next time, a better performance plan will be composed according to the new plan baselines stored in database. There is no need to change the SQL syntax in source programs.
For example, if you want to tune a SQL with execution plan-A that is currently used by Oracle SQL optimizer in your database, and you want to tune the SQL with Hints to make Oracle SQL optimizer to generate a new execution plan-B. What you have to do is to execute the tuned SQL with new Hints and use the following method provided by Oracle:
Execute the tuned SQL with Hints and plan B cached in SGA.
To enable the use of the tuned plan, manually alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.
To enable the use of SQL plan baselines, make sure the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter is set to TRUE.
Weaknesses of using SQL Plan baselines for SQL tuning
As the SQL plan baselines was designed to preserve the performance of SQL statements such as after the following environment changes:
New optimizer version
Changes to optimizer statistics and optimizer parameters
Changes to schema and metadata definitions
Changes to system settings
SQL profile creating
You can see that it is not designed for the purpose of manual SQL tuning. There are also some additional limitations such as Parallel Hints is not supported by SQL Plan Baselines, you cannot load a Plan-B with Parallel Hints applied your SQL with bad performance of original Plan-A. Parallel Hints sometimes are very important for a better plan generation by Oracle SQL optimizer.
SQL Patches
SQL Patches is part of the features provided by SQL Repair Advisor which is used to fix a SQL statement’s critical failures such as returning wrong result. The SQL Repair Advisor analyzes the problematic statement and in many cases recommends a SQL patch to repair the statement. The SQL patch is to influence the Oracle SQL optimizer to choose an alternate execution plan for future executions, instead of using the original problematic execution plan. There is a public API call to create SQL patches manually provided by Oracle Database 12c Release 2 onwards. The DBMS_SQLDIAG.CREATE_SQL_PATCH package can help users to create a SQL Patch for specific SQL statement for SQL tuning purpose. You can change a bad performance SQL statement’s execution plan without the need to modify the program source code as the following example:
DECLARE
Patch_name VARCHAR2(32767);
BEGIN
Patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
sql_text => 'SELECT *
FROM employees
WHERE emps_dept IN
(SELECT dpts_id
FROM departments
WHERE dpts_avg_salary <200000)', hint_text => 'INDEX(@SEL$1 EMPLOYEES) INDEX(@SEL$2 DEPARTMENTS)',
name => 'my_sql_patch_name');
END;
If your database version is before Oracle database 12c Release 2, you must use this package DBMS_SQLDIAG_INTERNAL.i_create_patch instead. Both SQL text and SQL ID is able to be used for SQL hints injection. The injected hints for your SQL should be placed in hint_text input parameter. There is only one line of Hints text you can use for a SQL and there is no way to define your own query block name for any subqueries’ block. So, if your SQL has multiple subqueries and you want to instruct Oracle to do something in subqueries’ blocks, you must use Oracle default query block names in your injected hints text.
hint_text => ‘ INDEX(@SEL$1 EMPLOYEES) INDEX(@SEL$2 DEPARTMENTS) ‘
This hints text in the above example shows that @SEL$1 and @SEL$2 are default query block names provided by Oracle in the execution plan of the SQL. The Hints tells Oracle use index search for EMPLOYEES table in query block @SEL$1 and also use index search for DEPARTMENTS in query block @SEL$2.
Pros and cons of using SQL Patches to tune SQL
SQL Patches is more flexible to accept hints instructions without SQL Plan Baselines’ limitations, complex hints with parallel operations are normally accepted by SQL patches. There is no additional maintenance effort to tell Oracle to use the SQL Patches after it is created. Oracle will use the stored hints to optimize any SQL with the same SQL ID or SQL Text and generate a better performance execution plan. Furthermore, you can also use SQL Patches to disable a SQL with a destructive hints already written in a package application or even use it to control a bind-aware SQL execution behavior.
As the injected hints text must be placed in one text line and using default query block name only, manually compose a desire Hints to improve a SQL statement will be a difficult task for most SQL developers especially for complex SQL statements with many subqueries.
A tool to automatically create Hints and SQL Patches
There is only one tool in the market so far that is able to generate a better hints and create SQL Patch in a fully automatic way. Tosska SQL Tuning Expert Pro is a tool for users to improve SQL performance without touching their program source code. Users can deploy different performance query plans for various sizes of production databases without the effort of keeping multiple versions of the program source, and it is especially suitable for package application users who don’t own the source code of their applications. The tool will try most useful hints combinations to tune your bad performance SQL statement, the best Hints combination SQL performance will be benchmarking side by side with the original SQL. Users will get the exact performance improvement without any guesswork or uncertain cost assessment only.
