January 27, 2021
Hot Topics:

An Oracle Tune-Up: Tuning Your SQL for Maximum Performance

  • By David Thurmond
  • Send Email »
  • More Articles »

The same thing could be accomplished by telling Oracle to exclude the payroll_transaction_idx03 index specifically by executing the following query:

   /*+NO_INDEX(payroll_transactions payroll_transaction_idx03)*/
   from payroll_transactions
   where transaction_date > to_date('01/01/2008',
                                    'mm/dd/yyyy') and
   employee_id = '123456789';

This would result in the same execution plan above where payroll_transaction_idx04 is used.

A word of caution is necessary here concerning table aliases. Aliases are often used as shorthand to make complicated queries a little easier to read. If aliases were to be used, as in the following query:

select /*+INDEX(payroll_transactions
   payroll_transaction_idx04)*/ b.first_name, b.last_name,
   from payroll_transactions a, employees b
   where a.transaction_date > to_date('01/01/2008',
                                      'mm/dd/yyyy') and
   a.employee_id = '123456789' and
   a.employee_id = b.employee_id;

the hint specified would be ignored. Instead, you must use the alias within the hint in place of the table name:

select /*+INDEX(a payroll_transaction_idx04)*/ sum(amount)
   from payroll_transactions
   where transaction_date > to_date('01/01/2008',
                                    'mm/dd/yyyy') and
   employee_id = '123456789';

Now, consider another handy hint. As discussed earlier, performing a full-table scan is usually very inefficient, and should be avoided, but this is not always the case. Consider the following query on the Departments table:

select department_id from departments
   where department_name = 'Accounting';

If the Departments table contains a small number of rows, it may be more efficient to read the entire table than it is to use an index. To force Oracle to bypass the index, you can specify a hint as follows:

select /*+FULL(departments)*/ department_id from departments
   where department_name = 'Accounting';

This may seem counterintuitive, but small tables are likely to be cached in memory, and would require no disk reads. Specifying the hint actually cuts down on I/O and speeds up the query.

Another example where a full-table scan might prove to be more efficient than an index is a case where the makeup of the data is not very unique. Consider a Gender column on the Employees table with only M for male or F for female employees. To get a count of the number of male and female employees:

select gender, count(*) from employees group by gender;

Oracle would need to scan all rows in the table to get the counts anyway, so using any index would just add overhead to the process. If an index were used, each row lookup would require that Oracle find it in the index, requiring one I/O operation, and then another to read the data from the table when the row is found from the index. This would nearly double the amount of I/O required to execute the query.

Hints should be used only in cases where a database design change or creation of a well-thought-out index does not help boost performance. Ideally, once a query is tuned using a hint, changes should be put in place so that Oracle's cost-based optimizer can run the query efficiently without the hint. Still, hints can be extremely helpful when solving performance issues, and are still necessary in cases where changes to the database are not possible.

There are dozens of Oracle hints out there to satisfy almost any query tuning need. A complete discussion of hints could fill an entire book, but a good summary of common hints and their uses can be found at http://askanantha.blogspot.com/2007/08/oracle-hints.html.

The Numbers Game

So far, the cost value shown in the query execution plan has been used to gauge how well a query will perform. Now, it's time to take a look at how that cost is calculated.

For each table, index, and column in the database, Oracle keeps statistics on the composition of the data.

For tables, this includes the number of rows in the table, the amount of storage required to store all of the table's data, and the average size of each row in the table. The number of rows is important when determining, for example, whether it is more efficient to use an index or to scan through a table sequentially. The number of data blocks would give Oracle an idea of how much I/O would be necessary to read through the table's data.

For indexes, Oracle tracks the number of index entries and the uniqueness of these entries. This is how Oracle calculates the cardinality number seen earlier in the query execution plans.

For columns, Oracle tracks the number of distinct values, null values, and the density of the data. For example, consider a Gender column in a table that only contains M or F for male or female. Oracle would use the information about the relative non-uniqueness of this data when determining an execution plan.

All of this information helps Oracle's optimizer to make wise decisions about query execution.

Statistics are usually recomputed periodically by the database administrator based either on a period of time since the last time the statistics were gathered, or on the amount of data that has changed in the table since that time. If statistics become so out-of-date that they no longer reflect the actual makeup of the table's data, Oracle's optimizer may make bad decisions about query execution plans. Likewise, if a significant amount of data has changed over a short period of time, query performance might suddenly take a nose-dive. To address this, most DBAs use scripts to periodically re-analyze tables whose statistics are becoming old. Oracle also keeps track of the amount of data that has changed since the last table analysis, and can flag the statistics as stale if too much activity has occurred since the last analysis.

So, how could stale statistics impact database performance? Suppose the Employees table has only 2 records in it when statistics are created. At that point, the Oracle query optimizer would decide that a full-table scan of Employees is the quickest way to find the desired row. Then, suppose that 100,000 rows are inserted into the table, and statistics are not re-created. The query optimizer would still be working under the assumption that the table is very small, and would recommend a full-table scan, which would obviously perform poorly now that the table contained much more data.

The first way to gather statistics is to have Oracle analyze the complete table data and recompute all of the statistics for the table. The command to do this is as follows:

Analyze table Employees compute statistics;

Page 4 of 5

This article was originally published on June 2, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date