How is the order of the columns in a composite index affecting a subquery performance for Oracle?

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.

FROM   department D
         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.

How to use ORDERED Hint to Tune a SQL with subquery for Oracle?

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.

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:

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.

How to tune a SQL that cannot be tuned ?

oracle sql performance tuning

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.

  AND SAL_SALARY <200000

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).

  AND  SAL_SALARY <200000
  AND  GRD_ID<1200


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.


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.

Importance of Backup & Recovery in MySQL Database and SQL

MySQL database and SQL

A database is the cornerstone of any application. For this reason, maintaining one or more backup and recovery options remains a priority for every database professional. There are multiple alternatives you can choose from as per the specific needs of your organization’s database.

In this post, we will examine some of the most popular back-ups and restore strategies for MySQL database and SQL. We will also touch upon the reasons why databases require backups on a regular basis.

Why Do We Need Backups for MySQL Database?

As a DBA, you’ll need backup and recovery to support data in multiple cases, such as:

Discrepancies in Data: Users may accidentally delete or update incorrect data in the primary or replica node.

Data Centre Failure: An indefinite power outage or internet connectivity issue can spell trouble for your organization.

Disk Damage: If the disk is stalling for too long due to some kind of damage, it can greatly reduce performance. In cloud services for Oracle database, it translates into a broken DB instance that cuts access.

Broken Data: In case of a power outage, MySQL may fails to write data and close files as usual. There are also instances where MySQL fails to restart and doesn’t work despite the crash recovery process because of corruption in data.

Legislation/Regulation: Backups and recovery options ensure business value and client satisfaction.

Various Kinds of Backups for MySQL Database

Given below are some common backup categories that suit a range of needs:

Physical: These comprise the exact copies of database files and may contain part or all of the MySQL directory. The most common use of this type of backup is to make a new replica node and respond to host failure in a convenient manner. Experts recommend restoring data with the help of the same MySQL database version.

Offsite: This is one of the most recommended backup alternatives as it guarantees an untouched copy in case of data centre or host failure. It involves copying the data to the cloud, an external file server or another external source. However, sometimes it may take longer to download the files from the cloud or server than the recovery process. Therefore, experienced database professionals keep about a week of data locally on a backup server for quick recovery.

Logical: It is useful for smaller quantities of data as it is slower in comparison to physical backup methods. It essentially consists of dumps from INSERT and CREATE statements. It is useful in addressing data corruption or when you must recover a subset of tables. Although the output is greater in logical backups, especially when that data is present in text format, you can perform a quick compression if the software you’re using requires it. For instance, you can use Mydumper and mysqldump to compress and redirect the data to the zip folder.

Incremental: This type of backup contains all the changes made in the organization’s Oracle database and SQL server since the last backup. It is, therefore, quite useful for enormous datasets since it allows you to take small backups (experts recommend this after you’ve taken a full backup) as data comes later.

Differential: It consists of copying the modifications since your previous backup. One advantage of a differential backup is that it saves disk space. This is because the data in these backups mostly remains the same, so the result leads to backups that are substantially smaller in size.

Oracle Database and SQL: The Pros & Cons of Bind Variables

SQL Server database and SQL

Bind Variables are typically considered as one of the major aspects of enhanced SQL query performance. According to Oracle documentation, they serve as a placeholder in a SQL query, getting replaced by a particular value that helps in statement execution.

The use of these variables enables users to create statements that can receive time-running parameters or inputs. One may think of bind variables as “value” given to the SQL query that acts as any function in programming languages. Here, we will talk more about them as well as their advantages and disadvantages in Oracle database and SQL.

Bind Variables: Examples of Their Uses

Consider the following statements in SQL –

Select * from Staff where S_No = 1 ;

Select * from Staff where S_No = :a ;

In the first query, a proper value (1) is applied to operate the query, whereas, in the next query, we have used a bind variable (:a) to operate the statement. This bind variable is given to Oracle when the query is run.

Defining a bind variable in the SQL statements in the place of literal values ensures that a single Parent Cursor is utilized by Oracle for the query. This helps improve database performance because Oracle searches for precise text matches for the query to check whether it already exists in the shared pool. Using a bind variable rather than a literal value saves an expensive hard parse for each run of that query.

Bind variables prove especially useful in OLTP-type environments because their use facilitates soft parsing. In other words, it takes less processing time to re-generate execution plan.

How Bind Variables Help Improve Database Performance

Given below are some advantages of using bind variables:

  1. Optimal Use of Shared Pool – The Shared Pool in Oracle Database needs to hold a single query instead of possibly numerous queries, thanks to bind variables.
  2. Improved Performance Due to Zero Hard Parsing – There is no need for hard parsing because SQL queries only diverge in terms of values.
  3. Decreased “library cache” Latch Contention – Since library cache latch contention is needed during a hard parse, its requirement reduces when bind variables are used.

Shortcomings of Bind Variables

The disadvantages of using bind variables are few. Although bind variables prove excellent if you want to improve Oracle database performance, there are instances where their use can negatively impact results:

  • They can decrease the flow of information needed to compute the best access path for CBO (Cost Based Optimizer). The CBO, in turn, may fail to identify the correct selectivity and create insufficient bad execution plans, opting for a complete table scan instead of using indexes.
  • Sometimes, the CBO requires the literal value to be used by SQL in order to build a robust execution plan. With bind variables, the literal value gets “hidden”, so the CBO is likely to create a subpar plan.

To overcome this issue, Oracle has tried to provide further assistance to the CBO by enabling it to take a look at the bind variable’s value during execution plan creation, which is known as “Bind Variable Peeking”.


The use of bind variables is extremely useful in Oracle database performance, especially when it comes to OLTP environments. However, as a user, you need to be careful while using bind variables. . It is recommended to use bind variables for short runtime SQL, but use literals for long time SQL statements to more information to CBO to generate good query plans.

How to Tune SQL Statement with OR conditions in a Subquery for SQL Server?

sql performance monitoring

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.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

How to Tune SQL Statements to Run SLOWER… but Make Users Feel BETTER (Oracle)?

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 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.

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.

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.

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.

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.

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;

Optimization in SQL: Determining Stale Statistics in Oracle

Optimization in SQL

You can determine whether statistics are stale in Oracle using two methods. The first is to make Oracle tell you if it considers the stats are stale.

The second involves a comparison of the statistics of what the DBMS assumes a table to be – and what it really is. Here, we’ll help you understand and determine whether the stats are stale.

Other Reasons Why Oracle May Pick a Bad Plan (Aside from Stale Statistics)

Good statistics aren’t necessarily always perfect – they only have to be correct to a degree for the information in the table. In case your statements are running sluggishly because Oracle picked a bad plan, you may also take it as a fair sign that your statistics are stale.

That said, stale statistics aren’t the only culprits behind Oracle selecting a bad plan, triggering the need for optimization in SQL. There are others, such as the following:

  • If your data doesn’t include enough histograms, extended statistics, or joins (both correlated and anti-correlated ones), it may not be uniform enough for Oracle to perceive it as it should.
  • Oracle might find it hard to calculate the amount of data to expect. This generally happens when a query is written in a way that confuses the DBMS.
  • When Oracle lacks a precise representation of the duration involving the completion of one or more operations. The system statistics may be incorrect in such cases.
  • The data set may not be sufficiently represented in Oracle’s version of the statistics. This usually happens in large tables with highly varied data that a histogram cannot accurately represent.
  • Certain indexes typically used by Oracle may have become invalid.

Coaxing Oracle to Determine Stale Statistics

You can have Oracle tell you about stale stats easily if your priority is to improve performance of SQL query.  The catch is, you won’t be able to determine how stale those stats are. However, you will know if there have been enough changes in a table for Oracle to consider regathering statistics on it.

To find out if that’s the case, you’ll need to view the stale stats column in DBA_STATISTICS which you can do with the following query:

select stale_stats

from dba_statistics

where owner = ‘<name of table owner>’’

AND table_name = ‘<name of table>’

The column may return “YES”, “NO” or other results, indicating Oracle’s stance on the stats. “YES” means Oracle is ready to re-gather statistics, whereas “NO” shows Oracle believes the statistics don’t need updating.

The column may return null, indicating incomplete or absent stats altogether. Take care to enter the correct table owner and table names as the query won’t return any rows otherwise.

Checking Stats on Your Own: What to Do in Oracle Database and SQL

When you do this manually, you can find out “how stale” your stats are. That’s because you’ll be able to collect stats on the “stalest” tables first, reducing the number of changes needed to be made to the database. This way, you could also avoid accumulating stats in situations that could lead to contention.

The goal here is to draw a comparison between Oracle’s values and the table’s actual values. Typically, a difference of up to 10 per cent between the two is acceptable. Also, this method requires us to check two separate kinds of statistics –

  • Table Level Statistics – As the name suggests, you can verify various aspects of the table like the following:
  1. A number of rows and empty blocks – You can use a query like this:


select count(*)

from <table name>;

  1. Number of Empty blocks and
  2. A number of blocks taken up by the table – The statement below will help you retrieve the number of “occupied blocks”:



select count(distinct substr(rowid, 7, 9))

from <table name>;

  • Column Level Statistics – These will help you determine things like:
  1. How many distinct values exist in a column – This proves useful for expressions that include “COLUMN = <any value>”.

Try the following:

select count(distinct <columnname>)

from <table name>;

  1. The column’s high and low values – These values prove useful for situations that need range-based predicates, such as those involving COLUMN <= <any value> or COLUMN between <START> and <END>.
  1. The number of nulls in a column – This query should help if you want to view the stats of a particular column: 

with cte (x)



   select /*+ inline */ <column name>

   from <table owner name>.<table name that you want to check>


select (select approx_count_distinct(x) from cte) distinct_values

     , (select count(*) – count(x) from cte) num_nulls

     , (select min(x) from cte) low_value

     , (select max(x) from cte) high_value

from dual