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,
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.
Stored procedures are increasing in popularity in Oracle Database and SQL Server because of quicker execution. Earlier, application code mostly resided in external programs. However, its shift toward database engine interiors compels database professionals to keep their memory requirements in mind.
This is as necessary as planning for times when the code related to database access will be present within the database. They also need to know how they can handle these stored procedures to maintain ideal database performance. We will look at some of these methods and the advantages of using stored procedures and triggers in the Oracle database.
Perks of Stored Procedures for Oracle Database Performance Tuning
Until recently, a majority of Oracle databases had limited code within their stored procedures. This shift in trends is because of the various advantages that come with placing larger amounts of code, such as the following:
Performance Improvement – Using more stored procedures means you don’t require Oracle database performance tuning as much. That’s because each of these only has to load once into the shared pool. Executing them, therefore, is quicker than running external code.
Code Segregation – The stored procedures have all the SQL codes which turn all the application programs into calls for those procedures. This is an improvement in the data retrieval process because changing databases gets simpler.
Therefore, one advantage you get through stored procedures is the ability to transfer large amounts of SQL code to the data dictionary. Doing this will enable you to perform SQL tuning without involving the application layer.
Group Data Easily – You can gather relational tables with data that shares certain behaviour before looking for Oracle performance tuning tips. Simply use Oracle stored procedures as methods, along with suitable naming conventions. For example, link the behaviour of the table data to the table name in the form of prefixes.
The users may then request the data dictionary to display all the traits connected to one table. This makes it more convenient to recognise and reuse code with the help of stored procedures.
Other Reasons Behind the Increasing Popularity of Stored Procedures
There are plenty of other reasons stored procedures and triggers take less time in comparison with conventional code. One of these has something to do with SGA caching in Oracle database and SQL.
Once the shared pool within the SGA gets a hold of a stored procedure, it keeps it there until the procedure gets paged out from the memory. The SGA mostly does this to create space for other stored procedures. The paging out process takes place based on a Least Recently Used or LRU algorithm.
Two parameters help determine the amount of space that Oracle uses on startup. These are the Cache Size and the Shared Pool Size parameters. They also help users check how much storage space is available for various tasks. These include caching SQL code, data blocks, and stored procedures.
Stored procedures will run extremely fast once you load them into the shared pool’s RAM – as long as you can avoid pool thrashing. This is important because several procedures compete for varying quantities of memory in the shared pool.
Here the following is an example SQL shows you that select the maximum emp_address which is not indexed in the EMPLOYEE table with 3 million records, the emp_grade is an indexed column.
select max(emp_address) from employee a
where emp_grade<4000
As 80% of the EMPLOYEE table’s records will be retrieved to examine the maximum emp_address string. The query plan of this SQL shows a Table Access Full on EMPLOYEE table is reasonable.
How many ways to build an index to improve this SQL?
Although it is simple SQL, there are still 3 ways to build an index to improve this SQL, the following are the possible indexes that can be built for the SQL, the first one is a single column index and the 2 and 3 are the composite index with a different order.
1. EMP_ADDRESS
2. EMP_GRADE, EMP_ADDRESS
3. EMP_ADDRESS, EMP_GRADE
Most people may use the EMP_ADDRESS as the first choice to improve this SQL, let’s see what the query plan is if we build a virtual index for the EMP_ADDRESS column in the following, you can see the estimated cost is reduced by almost half, but this query plan is finally not being used after the physical index is built for benchmarking due to actual statistics is collected.
The following query shows the EMP_ADDRESS index is not used and the query plan is the same as the original SQL without any new index built.
Let’s try the second composite index (EMP_GRADE, EMP_ADDRESS), the new query plan shows an Index Fast Full Scan of this index, it is a reasonable plan which no table’s data is needed to retrieve. So, the execution time is reduced from 16.83 seconds to 3.89 seconds.
Let’s test the last composite index (EMP_ADDRESS, EMP_GRADE) that EMP_ADDRESS is placed as the first column in the composite index, it creates a new query plan that shows an extra FIRST ROW operation for the INDEX FULL SCAN (MIN/MAX), it highly reduces the execution time from 16.83 seconds to 0.08 seconds.
So, indexing sometimes is an art that needs you to pay more attention to it, some potential solutions may perform excess your expectation.
The best index solution is now more than 200 times better than the original SQL without index, this kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.
The performance of an Oracle database and SQL query speed can directly affect the organisation it belongs to. If the queries running in the database are slow, they will surely have a negative impact. However, its severity may vary based on the database’s role, its architecture, and the industry your organisation operates in.
Regardless of the extent, it would be unwise to ignore them, which is why we are going to talk about all their effects in this blog.
How Slow Statements Affect Oracle Database and SQL Users
In this fast-paced world, everything needs to work fast and offer a quick response time to its end-users. The data that a web page displays generally comes directly from the database with very few interactions.
This implies the dependency of the application’s response time on the time it takes for the queries to run and the database to respond. Slow statements will take more time, resulting in loading screens before the desired information shows up. This is when Oracle database performance tuning becomes a requirement.
Such speeds don’t affect only the application, however; they leave an impact on the other parts of a system as well. The reason behind this is the location of the database in a majority of web architectures.
Take a look at the three-tier architecture, for example – the database lies at the bottom in most cases, forming the foundation. An increase in latency here is likely to cause the same in the higher levels along with other areas in the system.
Another way in which slow queries negatively impact the system is by making the database use more resources than is actually necessary. Some of these are available in limited quantities, such as I/O and CPU, since other applications share these resources.
On the other hand, not using existing resources sufficiently leads to their inefficient usage and slow queries as well. This may be the case with your database, so you may want to consider a few Oracle performance tuning tips that deal with this particular issue.
Top Reasons Behind Slow and Inefficient Queries
Given below are the three most significant causes of queries slowing down:
Too many tasks: Executing a statement includes multiple tasks, such as retrieving data, making calculations, and arranging data in the order as the query specifies. All of these involve plenty of factors, any of which can increase the amount and complexity of work done, from joining and grouping to filtering and sorting.
Too much waiting: Sometimes, statements don’t have too much to do, nor are they stuck waiting for resources. The reason why they are sluggish is that they are waiting on other statements that are locking resources or requiring higher levels of activity.
Too few resources: Query execution works alongside other tasks taking place within a system. This means they share resources such as network throughput, disk I/O, and CPU. Statement execution is likely to take more time when these are completely occupied.
Locating and Working on Slow Queries in Oracle Database
Slow queries don’t get faster on their own – DBAs must take steps to speed them up. For starters, they can use the Database Performance Monitor (DPM) in the following ways:
Viewing all the queries that are taking up execution time using the query profiler. Such queries are often running in the absence of indexes, so it’s a good idea to add one to improve Oracle database performance and execution speed.
Automatically collecting explain plans to get a quick glance at the ones that contain information regarding slow queries and the changes related to them, if any.
Assessing Oracle database and SQL to find out whether a statement can perform better with the help of some improvements.
Visiting the charts page to go through properly arranged metrics pertaining to system performance. This allows the DBA to set a threshold alert and note changes every time a system resource is reaching maximum use.
Conclusion
Based on your architecture and application, slow statements can affect more aspects of your business than just the database. Therefore, ignoring them is not recommended as it often results in a detrimental impact on both end-users and your organisation.
Consider enlisting the help of professional tuning tools to improve slow query performance in Oracle and SQL Server databases.
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.
The following is an example that shows a SQL statement with an EXISTS subquery. The SQL counts the records from the EMPLOYEE table if the OR conditions are satisfied in the subquery of the DEPARTMENT table.
select countn(*) from employee a where
exists (select ‘x’ from department b
where a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary
)
Here the following is the query plan in the Tosska proprietary tree format, it takes 4 minutes and 29 seconds to finish.
The query plan shows a Nested Loops from EMPLOYEE to full table scan DEPARTMENT, it is the main problem of the entire query plan, the reason is the SQL Server cannot resolve this OR conditions ”a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary” by other join operations.
Let me rewrite the OR conditions in the subquery into a UNION ALL subquery in the following, the first part of the UNION ALL in the subquery represents the “a.emp_id=b.dpt_manager” condition, the second part represents the “a.emp_salary=b.dpt_avg_salary” condition but exclude the data that already satisfied with the first condition.
select count(*)
from employee a
where exists ( select ‘x’
from department b
where a.emp_id = b.dpt_manager
union all
select ‘x’
from department b
where ( not ( a.emp_id = b.dpt_manager )
or b.dpt_manager is null )
and a.emp_salary = b.dpt_avg_salary )
Here the following is the query plan of the rewritten SQL, it looks a little bit complex, but the performance is very good now, it takes only 0.447 seconds. There are two Hash Match joins that are used to replace the original Nested Loops from EMPLOYEE to full table scan DEPARTMENT.
Although the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 600 times fastAlthough the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 600 times faster than the original SQL.
